Home Dashboard Directory Help
Search

Certain SQL Server Agent Alerts of type 'SQL Server performance condition alert' cause an error. by Brad Deem 2


Status: 

Closed
 as Fixed Help for as Fixed


2
0
Sign in
to vote
Type: Bug
ID: 535716
Opened: 2/23/2010 12:18:20 PM
Access Restriction: Public
2
Workaround(s)
view
2
User(s) can reproduce this bug

Description

sp_sqlagent_get_perf_counters will throw an error when the following Object, Counter, Instance combinations are used for 'SQL Server performance condition alert'. This also affects SQL Server 2008 version 10.0.2531.

Object_Name    Counter    Instance
SQLServer:Catalog Metadata    Cache Hit Ratio     _Total
SQLServer:Cursor Manager by Type    Cache Hit Ratio     _Total
SQLServer:Plan Cache    Cache Hit Ratio     _Total
SQLServer:Catalog Metadata    Cache Hit Ratio Base    _Total
SQLServer:Cursor Manager by Type    Cache Hit Ratio Base    _Total
SQLServer:Plan Cache    Cache Hit Ratio Base    _Total
SQLServer:Buffer Node     Free pages    0
SQLServer:Buffer Partition    Free pages    0
SQLServer:General Statistics    Transactions    
SQLServer:Transactions    Transactions    

This because the sub query that calculates the counter's value does not join on the object name in 'sp_sqlagent_get_perf_counters'. Thus, multiple rows are returned for the sub query causing the error.

The following query can be used to show affected performance counters.

SELECT *
FROM sys.dm_os_performance_counters pc
WHERE EXISTS (
    SELECT counter_name,instance_name
    FROM sys.dm_os_performance_counters pcs
    WHERE pcs.counter_name = pc.counter_name
    AND pcs.instance_name = pc.instance_name
    GROUP BY counter_name,instance_name,cntr_type
    HAVING COUNT(*) > 1)
ORDER BY 2,3,1
Details
Sign in to post a comment.
Posted by Wolfgang Kais on 1/5/2012 at 3:00 AM
Thanks BradD2 for the great workaraound.
Posted by RodrigoMoraes on 6/4/2010 at 4:36 PM
I'm sorry, I didn't saw the alternative solutions pane.
I've already corrected my script.
Posted by RodrigoMoraes on 6/4/2010 at 4:24 PM
My workaround was the distinct clause in all "SELECT DISTINCT CASE spi2.cntr_value WHEN..." statements.
Regards
Posted by Microsoft on 5/14/2010 at 2:04 PM
Brad -

Update - we have fixed this bug for our next Major Release.

Thanks for reporting the issue!

Thanks,

Amy Lewis
Posted by Microsoft on 4/7/2010 at 8:07 AM
Thank you for reporting this issue - we will investigate fixing it for our next release.

Thanks,

Amy Lewis
Posted by Microsoft on 4/7/2010 at 8:07 AM
Thank you for reporting this issue - we will investigate fixing it for our next release.

Thanks,

Amy Lewis
Posted by FletchNZ on 3/29/2010 at 1:13 PM
Thanks for the great workaround!
Sign in to post a workaround.
Posted by Brad Deem 2 on 2/25/2010 at 9:44 AM
Since other users cannot download my attachments, add the following filter to the WHERE clause for the four sub queries used by the [value] column calculation.

(spi1.[object_name] = spi2.[object_name])

Thus, the sub queries become.

SELECT CASE spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
FROM sys.dm_os_performance_counters spi2
WHERE (spi1.[object_name] = spi2.[object_name])
AND (spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('% Base%', spi2.counter_name)))
AND (spi1.instance_name = spi2.instance_name)
AND (spi2.cntr_type = @perfTypeLargeRawBase)
Posted by Brad Deem 2 on 2/23/2010 at 12:27 PM
See attachment 'sp_sqlagent_get_perf_counters_fixed.sql'
File Name Submitted By Submitted On File Size  
sp_sqlagent_get_perf_counters_fixed.sql (restricted) 2/23/2010 -
sp_sqlagent_get_perf_counters_fixed version 2.sql (restricted) 2/25/2010 -