Alert type not available

I've installed a new instance of SQL Server 2000 and applied SP3a.
I want to create a new SQL Server performance condition alert from the
Enterprise Manager, but this type of alert is not available. Only the type
SQL Server event alert is available. How can I make the performance condition
alerts available?
Wilbert,
Are your performance counters missing also in perfmon? If so, this
article should fix your problem.
PRB: SQL Performance Monitor Counters Missing
http://support.microsoft.com/?id=227662
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've installed a new instance of SQL Server 2000 and applied SP3a.
> I want to create a new SQL Server performance condition alert from the
> Enterprise Manager, but this type of alert is not available. Only the type
> SQL Server event alert is available. How can I make the performance condition
> alerts available?
|||Hello Mark,
The problem seems to be another instance of SQL on the same machine that is
used for Veritas. In this instance, the performance counters are available.
Is there a way to get the counters available for the second instance aswell?
Regards, Wilbert
"Mark Allison" wrote:

> Wilbert,
> Are your performance counters missing also in perfmon? If so, this
> article should fix your problem.
> PRB: SQL Performance Monitor Counters Missing
> http://support.microsoft.com/?id=227662
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Wilbert wrote:
>

Alert type not available

I've installed a new instance of SQL Server 2000 and applied SP3a.
I want to create a new SQL Server performance condition alert from the
Enterprise Manager, but this type of alert is not available. Only the type
SQL Server event alert is available. How can I make the performance conditio
n
alerts available?Wilbert,
Are your performance counters missing also in perfmon? If so, this
article should fix your problem.
PRB: SQL Performance Monitor Counters Missing
http://support.microsoft.com/?id=227662
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've installed a new instance of SQL Server 2000 and applied SP3a.
> I want to create a new SQL Server performance condition alert from the
> Enterprise Manager, but this type of alert is not available. Only the type
> SQL Server event alert is available. How can I make the performance condit
ion
> alerts available?|||Hello Mark,
The problem seems to be another instance of SQL on the same machine that is
used for Veritas. In this instance, the performance counters are available.
Is there a way to get the counters available for the second instance aswell?
Regards, Wilbert
"Mark Allison" wrote:

> Wilbert,
> Are your performance counters missing also in perfmon? If so, this
> article should fix your problem.
> PRB: SQL Performance Monitor Counters Missing
> http://support.microsoft.com/?id=227662
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Wilbert wrote:
>sql

Alert type not available

I've installed a new instance of SQL Server 2000 and applied SP3a.
I want to create a new SQL Server performance condition alert from the
Enterprise Manager, but this type of alert is not available. Only the type
SQL Server event alert is available. How can I make the performance condition
alerts available?Wilbert,
Are your performance counters missing also in perfmon? If so, this
article should fix your problem.
PRB: SQL Performance Monitor Counters Missing
http://support.microsoft.com/?id=227662
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Wilbert wrote:
> I've installed a new instance of SQL Server 2000 and applied SP3a.
> I want to create a new SQL Server performance condition alert from the
> Enterprise Manager, but this type of alert is not available. Only the type
> SQL Server event alert is available. How can I make the performance condition
> alerts available?|||Hello Mark,
The problem seems to be another instance of SQL on the same machine that is
used for Veritas. In this instance, the performance counters are available.
Is there a way to get the counters available for the second instance aswell?
Regards, Wilbert
"Mark Allison" wrote:
> Wilbert,
> Are your performance counters missing also in perfmon? If so, this
> article should fix your problem.
> PRB: SQL Performance Monitor Counters Missing
> http://support.microsoft.com/?id=227662
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Wilbert wrote:
> > I've installed a new instance of SQL Server 2000 and applied SP3a.
> > I want to create a new SQL Server performance condition alert from the
> > Enterprise Manager, but this type of alert is not available. Only the type
> > SQL Server event alert is available. How can I make the performance condition
> > alerts available?
>

Alert for DeadLock

I am trying to create an alert to monitor deadlocks with following
specification.
Name = "Deadlock(s) detected . . ."
Type = "SQL Server performance condition alert.
Object = "SQL Server:Locks"
Counter = "Number of Deadlocks/sec"
Instance = "Database"
Alert if counter = "becomes equal to"
Value = "1"
Once the alert is created, I execute the deadlock script. But found that the
counter in alert is not increasing.
Can someone put some light on this? Am I missing something?why dont you just use SQL servers Deadlock monitoring?
Check out trace flags 1204, 1205
Immy
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:0849692C-24D2-4424-BA4D-A49F0BF22F6C@.microsoft.com...
>I am trying to create an alert to monitor deadlocks with following
> specification.
> Name = "Deadlock(s) detected . . ."
> Type = "SQL Server performance condition alert.
> Object = "SQL Server:Locks"
> Counter = "Number of Deadlocks/sec"
> Instance = "Database"
> Alert if counter = "becomes equal to"
> Value = "1"
> Once the alert is created, I execute the deadlock script. But found that
> the
> counter in alert is not increasing.
> Can someone put some light on this? Am I missing something?|||I want a NOTIFICATION when ever dead lock happens.|||I would still use the inbuilt function from MS!
If you want to continue with your own method, you'll need to post your DDL
and SQL statements so we can offer some advice.
Immy
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:75F4E5FD-0B99-47E6-90A3-EA887A4FD9FB@.microsoft.com...
>I want a NOTIFICATION when ever dead lock happens.|||[vbcol=seagreen]
How this works?|||See my previous post re: trace flags! :-)
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:C5CB693A-CB0E-4E43-B743-0C51115361D8@.microsoft.com...
>
> How this works?
>
>

Alert for DeadLock

I am trying to create an alert to monitor deadlocks with following
specification.
Name = "Deadlock(s) detected . . ."
Type = "SQL Server performance condition alert.
Object = "SQL Server:Locks"
Counter = "Number of Deadlocks/sec"
Instance = "Database"
Alert if counter = "becomes equal to"
Value = "1"
Once the alert is created, I execute the deadlock script. But found that the
counter in alert is not increasing.
Can someone put some light on this? Am I missing something?why dont you just use SQL servers Deadlock monitoring?
Check out trace flags 1204, 1205
Immy
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:0849692C-24D2-4424-BA4D-A49F0BF22F6C@.microsoft.com...
>I am trying to create an alert to monitor deadlocks with following
> specification.
> Name = "Deadlock(s) detected . . ."
> Type = "SQL Server performance condition alert.
> Object = "SQL Server:Locks"
> Counter = "Number of Deadlocks/sec"
> Instance = "Database"
> Alert if counter = "becomes equal to"
> Value = "1"
> Once the alert is created, I execute the deadlock script. But found that
> the
> counter in alert is not increasing.
> Can someone put some light on this? Am I missing something?|||I want a NOTIFICATION when ever dead lock happens.|||I would still use the inbuilt function from MS!
If you want to continue with your own method, you'll need to post your DDL
and SQL statements so we can offer some advice.
Immy
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:75F4E5FD-0B99-47E6-90A3-EA887A4FD9FB@.microsoft.com...
>I want a NOTIFICATION when ever dead lock happens.|||>> I would still use the inbuilt function from MS!
How this works?|||See my previous post re: trace flags! :-)
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:C5CB693A-CB0E-4E43-B743-0C51115361D8@.microsoft.com...
>> I would still use the inbuilt function from MS!
> How this works?
>
>sql

Alert for CPU>80%

I am trying to create an alert which notifies me through
net send when CPU rises above 90% of the usage.
I tried to look under performance alerts through
enterprise manager but cant see the CPU options
Any ideasHi
I would set hardware (type) alerts in Performance/System Monitor and not a
SQL Server alert.
John
"Apok" wrote:

> I am trying to create an alert which notifies me through
> net send when CPU rises above 90% of the usage.
> I tried to look under performance alerts through
> enterprise manager but cant see the CPU options
> Any ideas
>|||Hi
I replied to this in microsoft.public.sqlserver.programming.
John
"Apok" wrote:

> I am trying to create an alert which notifies me through
> net send when CPU rises above 90% of the usage.
> I tried to look under performance alerts through
> enterprise manager but cant see the CPU options
> Any ideas
>

Alert for CPU>80%

I am trying to create an alert which notifies me through
net send when CPU rises above 90% of the usage.
I tried to look under performance alerts through
enterprise manager but cant see the CPU options
Any ideas
Hi
I would set hardware (type) alerts in Performance/System Monitor and not a
SQL Server alert.
John
"Apok" wrote:

> I am trying to create an alert which notifies me through
> net send when CPU rises above 90% of the usage.
> I tried to look under performance alerts through
> enterprise manager but cant see the CPU options
> Any ideas
>
|||Hi
I replied to this in microsoft.public.sqlserver.programming.
John
"Apok" wrote:

> I am trying to create an alert which notifies me through
> net send when CPU rises above 90% of the usage.
> I tried to look under performance alerts through
> enterprise manager but cant see the CPU options
> Any ideas
>
sql

Alert for CPU>80%

I am trying to create an alert which notifies me through
net send when CPU rises above 90% of the usage.
I tried to look under performance alerts through
enterprise manager but cant see the CPU options
Any ideasHi
I would set hardware (type) alerts in Performance/System Monitor and not a
SQL Server alert.
John
"Apok" wrote:
> I am trying to create an alert which notifies me through
> net send when CPU rises above 90% of the usage.
> I tried to look under performance alerts through
> enterprise manager but cant see the CPU options
> Any ideas
>|||Hi
I replied to this in microsoft.public.sqlserver.programming.
John
"Apok" wrote:
> I am trying to create an alert which notifies me through
> net send when CPU rises above 90% of the usage.
> I tried to look under performance alerts through
> enterprise manager but cant see the CPU options
> Any ideas
>

Aggregations for Max Performance

In the past, I blindly opted for MOLAP and set the percentage to 100% while designing the aggregations for my cubes. Of course, it rarely got to 100%, but I figured that was the best way to get the maximum performance for my cubes in terms of aggregations.

Now fast forward to today, I decided to go through the wizard again because I've recently added a few measure groups and they currently do not have any aggregations right now.

I went through each partition again, opted for MOLAP with 100%, and noticed that most of them went down in percentage. Why is that the case?

Perhaps more importantly, anything "wrong" with my approach of MOLAP 100%? I assume that the only concern I have with doing so is the processing time of the cube? If so, I'm not worried about that as we doing nightly.

Thanks!

Hello

You should take a look at this great post by Chris Webb:

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

More aggregations are not always better. In some cases, overdoing the number of aggregations can have a negative impact on performance.

Aggregations

Hi,

designed a cube anr run several queries on it. The cube itself works. Now, to gain the objected performance boost, i worked through aggregationdesigner for every single partition. It told me, that there will be a lot of aggregations with a huge amount of diskspace. After processing full, reports work as slow as before and there is no more space needed in the file system.

How can I find out, if aggregations where calculated and when SSAS uses the precalculated stuff. Where gets the aggregation data stored in filesystem?

How can I find out, if something went wrong while aggregating, so that aggregation wasn't finished?

Thanks in advance

T.

One technique is to view your aggregations is to open your cube project in BIDS (you may need to use an import project). In Solution Explorer, click the Show All button and then expand your cube to see the partition definition file. Right-click that file and select View Code. In the XML displayed, search for <AggregationDesigns> to see the aggregations.

If you have SP2, there is an Aggregation Utility that makes this a bit easier. Appendix C of the Performance Guide covers this in detail. You can get the utility as part of the SQL Server 2005 Samples (Feb 2007 release). A link to both these are below. (The utility is located in the \Analysis Services\Administrator\AggregationManager folder below the installation folder. Read the ReadMe file in this folder for instructions on compiling the utility.)

Good luck,

Bryan Smith

Performance Guide : http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc.

SQL 2005 SP2 Samples: http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||

Hi, thanks for your reply.

There is <aggregationdesigns> part in partition file.

I bet the aggregation design wizard did his work to design the aggregations who should be best. But I'm not sure, due to lack of check possibilities and performance gain, that they get processed.

As there is no additional disk space used by AS, I'm really súre that the designed aggregations didn't go to work this morning.

Any hints?

|||

Hi, Bryan,

Are you saying the Aggregation Utility is used to improve the cube aggregation process? or how it works? Thanks.

With best regards,

Yours sincerely,

|||

The agg utility just helps you see and edit your aggregations. This should be a last resort. I recommend you use the wizards for this purpose. (Documentation on the agg utility is provided in the links in the previous posting.)

Please note, if you right-click a partition in SSMS and script it's definition, it will indicate the name of the aggregation design but will not script the design itself. This may incorrectly lead you to believe you do not have aggregations defined.

The easiest way to see the aggregation design is to follow the steps listed in the previous email. The BIDS interface provides fairly simple access to the structure. Alternatively, you can find the design XML file in your data directory folder and read it there. As an example, the agg design for my Adventure Works DW cube's Reseller Sales measure group is located at: C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Adventure Works DW.0.db\Adventure Works DW.0.cub\Fact Reseller Sales.0.det\AggregationDesign.0.agg.xml on my computer.

Please note, that the aggregation design is editable in the xml file listed above as well as the XML displayed in BIDS. However, it is STRONGLY recommended you DO NOT EDIT the xml. Instead, use the agg utility if you feel you must go this route. (Again, make sure you've fully explored the use of the wizards before doing even this.)

Regarding processing, I have seen a couple situations where processing failed and an error was not properly addressed by applications. In these situations, the cube was rolled back to its prior state. If you suspect this is the case with your cube, check the data to insure it is up-to-date. If it is, you probably processed successfully.

Good luck,
Bryan

|||

Hi, Bryan,

Thanks a lot for your very advanced and kind advices. It's very helpful.

With best regards,

Yours sincerely,

Aggregation Utility

Reading the interesting document Microsoft SQL Server 2005 Analysis Services Performance Guide, I discover the existence of an Aggregation Utility.

Wher can I download it ?

What the name of the executable ... and where it is ?

Cosimo

It's called Aggregation Manager and you can download it here as part of the SQL 2005 samples:

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

HTH,

Chris

|||

We have an improved version of the Agg Manager incorporated into BIDSHelper too

www.codeplex.com/bidshelper

Aggregation Issue

I have a cube that I designed aggregation with 12% performance in MOLAP storage mode. However, when I ran query it read from partition not from aggregation.

How can I change so that the query read from aggregation?

Thanks in advance,
A. Imamuddin

Hi Ashari

1. Ensure that you have designed hierarchies on your dimensions even though they seem to be unnecessary. I found that aggregations are created when these exist

2. I find that creating aggregations manually by editing the XMLA for the measure group works better. You do this by scripting the measure group in the SQL Server Man Studio and add/edit your aggregations.

Let me know if this helps?

Thanks

John

|||Hi John,

Thanks for your replay. I would like to inform you, I do not applied point 1 because I have already had hierarchies. I have applied point 2, but after processing the partition, the query still read from partition. FYI, I also design aggregation using Usage Based Optimization Wizard.

Thanks,
A. Imamuddin|||

It's likely that, even after usage-based optimisation, you still haven't build any aggregations useful for your query. Rather than change your query, to make sure you're building the right aggregations take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

HTH,

Chris

Aggregation Issue

I have a cube that I designed aggregation with 12% performance in MOLAP storage mode. However, when I ran query it read from partition not from aggregation.

How can I change so that the query read from aggregation?

Thanks in advance,
A. Imamuddin

Hi Ashari

1. Ensure that you have designed hierarchies on your dimensions even though they seem to be unnecessary. I found that aggregations are created when these exist

2. I find that creating aggregations manually by editing the XMLA for the measure group works better. You do this by scripting the measure group in the SQL Server Man Studio and add/edit your aggregations.

Let me know if this helps?

Thanks

John

|||Hi John,

Thanks for your replay. I would like to inform you, I do not applied point 1 because I have already had hierarchies. I have applied point 2, but after processing the partition, the query still read from partition. FYI, I also design aggregation using Usage Based Optimization Wizard.

Thanks,
A. Imamuddin|||

It's likely that, even after usage-based optimisation, you still haven't build any aggregations useful for your query. Rather than change your query, to make sure you're building the right aggregations take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

HTH,

Chris

again and again... query analyzer and wrong SQL statement plan... + outer join?

Hi,
again we have some SQL statement performance problems...
I've 2 table with 140 000 records each.
I join the 2 tables and the result is also 140 000, but the query analyzer
estimate 1000 rows only.
Its a simple join with 5 equals in the join
select * from tableA inner join table B on A.ID1 = B.ID2.....
all the stats for my tables are ok.
its not a so big problem, but when I want to estimate before an execution
and when the result is far different, its a big surprise for me!
Also, we have aproblem with a query which use 1 inner join and 3 left outer
join.
Each outer join is a sub-query which contain a group by:
select *from A inner join B on A.ID = B.ID
left outer join (Select ID1, ID2 from C group by ID1, ID2) CC
on A.ID1 = CC.ID1 and A.ID2 = CC.ID2
...
(the 2 others outer join use the same syntax)
This query takes more then 10 minutes, but if I precalculate each sub-query
in a temporary table and if I use these tables instead-of the sub-queries,
the result appear in only 19seconds!!!!!
how can I tell SQL Server to use the same way without creating myself the
temporary tables?
there is any table hint option to force SQL Server to treat the sub queries
has "physical tables".
I know its not the first time you have this type of questions... so thanks
for your time (again)
Jerome.
create an Index for those 5 columns on each table.. But only those 5 columns
ll be used..
And let SQL estimate the proper Index for the execution
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:uX1Ih0htEHA.3604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> again we have some SQL statement performance problems...
> I've 2 table with 140 000 records each.
> I join the 2 tables and the result is also 140 000, but the query analyzer
> estimate 1000 rows only.
> Its a simple join with 5 equals in the join
> select * from tableA inner join table B on A.ID1 = B.ID2.....
> all the stats for my tables are ok.
> its not a so big problem, but when I want to estimate before an execution
> and when the result is far different, its a big surprise for me!
> Also, we have aproblem with a query which use 1 inner join and 3 left
outer
> join.
> Each outer join is a sub-query which contain a group by:
> select *from A inner join B on A.ID = B.ID
> left outer join (Select ID1, ID2 from C group by ID1, ID2) CC
> on A.ID1 = CC.ID1 and A.ID2 = CC.ID2
> ...
> (the 2 others outer join use the same syntax)
> This query takes more then 10 minutes, but if I precalculate each
sub-query
> in a temporary table and if I use these tables instead-of the sub-queries,
> the result appear in only 19seconds!!!!!
> how can I tell SQL Server to use the same way without creating myself the
> temporary tables?
> there is any table hint option to force SQL Server to treat the sub
queries
> has "physical tables".
> I know its not the first time you have this type of questions... so thanks
> for your time (again)
> Jerome.
>

again and again... query analyzer and wrong SQL statement plan... + outer join?

Hi,
again we have some SQL statement performance problems...
I've 2 table with 140 000 records each.
I join the 2 tables and the result is also 140 000, but the query analyzer
estimate 1000 rows only.
Its a simple join with 5 equals in the join
select * from tableA inner join table B on A.ID1 = B.ID2.....
all the stats for my tables are ok.
its not a so big problem, but when I want to estimate before an execution
and when the result is far different, its a big surprise for me!
Also, we have aproblem with a query which use 1 inner join and 3 left outer
join.
Each outer join is a sub-query which contain a group by:
select *from A inner join B on A.ID = B.ID
left outer join (Select ID1, ID2 from C group by ID1, ID2) CC
on A.ID1 = CC.ID1 and A.ID2 = CC.ID2
...
(the 2 others outer join use the same syntax)
This query takes more then 10 minutes, but if I precalculate each sub-query
in a temporary table and if I use these tables instead-of the sub-queries,
the result appear in only 19seconds!!!!!
how can I tell SQL Server to use the same way without creating myself the
temporary tables?
there is any table hint option to force SQL Server to treat the sub queries
has "physical tables".
I know its not the first time you have this type of questions... so thanks
for your time (again)
Jerome.create an Index for those 5 columns on each table.. But only those 5 columns
ll be used..
And let SQL estimate the proper Index for the execution
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:uX1Ih0htEHA.3604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> again we have some SQL statement performance problems...
> I've 2 table with 140 000 records each.
> I join the 2 tables and the result is also 140 000, but the query analyzer
> estimate 1000 rows only.
> Its a simple join with 5 equals in the join
> select * from tableA inner join table B on A.ID1 = B.ID2.....
> all the stats for my tables are ok.
> its not a so big problem, but when I want to estimate before an execution
> and when the result is far different, its a big surprise for me!
> Also, we have aproblem with a query which use 1 inner join and 3 left
outer
> join.
> Each outer join is a sub-query which contain a group by:
> select *from A inner join B on A.ID = B.ID
> left outer join (Select ID1, ID2 from C group by ID1, ID2) CC
> on A.ID1 = CC.ID1 and A.ID2 = CC.ID2
> ...
> (the 2 others outer join use the same syntax)
> This query takes more then 10 minutes, but if I precalculate each
sub-query
> in a temporary table and if I use these tables instead-of the sub-queries,
> the result appear in only 19seconds!!!!!
> how can I tell SQL Server to use the same way without creating myself the
> temporary tables?
> there is any table hint option to force SQL Server to treat the sub
queries
> has "physical tables".
> I know its not the first time you have this type of questions... so thanks
> for your time (again)
> Jerome.
>

Affecting performance?

I have a table with 23 columns. 8 columns are not always filled by users and so valued to default. Would this affect the overall performance of querying this table? Should I separate those 8 columns and link with a one-to-one relation?

Thanks

Any idea?|||From a database design point of view I would seperate them.

Affecting performance?

I have a table with 23 columns. 8 columns are not always filled by users and so valued to default. Would this affect the overall performance of querying this table? Should I separate those 8 columns and link with a one-to-one relation?

Thanks

Any idea?|||From a database design point of view I would seperate them.