1 of 1 people found this helpful
Here is the custom alert I did
This alert is for SQL Server instances only and will tell you when SQL statements have been marked as 'Critical' for the past 2 days.
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
Alert Name: SQL Server - SQL marked as Critical in Advisor
Execution Interval: 10 minutes
Notification Text: These queries have been labeled as Critical during the past 2 days.
select 'Query: ' +Item + ' has been labled as critical', Count(item)
from CON_PROBLEM_SUMMARY_#DBID# ps
INNER JOIN CON_PROBLEM_ANALYSIS_#DBID# pa
ON pa.ID = left(ps.problemid,Charindex('-',ps.problemid)-1)
WHERE ps.ALARMLEVEL = 'Critical'
and DATEDIFF(day,pa.STARTTIME, CURRENT_TIMESTAMP) < 2
group by item
Execute Against: Repository
Description: Returns the SQL Hash of those queries labeled as critical
High Threshold: 1 min, max empty
If I plug the resulting hash value into this and run it in the instance triggering the alert, I get nothing:
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.query_hash = CONVERT(VARBINARY(8), <HashFromAlert>);
This is because DPA stores a hash that is not the full varninary SQL_handle but, is a hash based on that value. Please up vote storing the full value display feature request.
Currently the only place to get the full value is in the Current tab and then under show active sessions we display the DPA hash and the sql_handle. The drawback here is you have to use the value you have and then look up the varbinary. you also have to catch it while it is running (or idle with show idle sessions sessions selected).
I upvoted the feature request.
That workaround might be barely acceptable if it actually worked, but there is too much likelihood that the query won't be listed there. In my case, I did find it, but when I stick a 0x on the front and plug it in to a query and run it on the correct instance, I still get nothing:
SELECT SQLText = st.text
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)st
WHERE qs.sql_handle = 0x0100070068A27D1060D25B461000000000000000
Neither of the queries shown in the Advisors tab have the sql_hash value shown by the alert:
I actually agree it isn't much or a workaround this value is displayed there but, isn't retained anywhere to map back to at the moment unfortunately.
Did you add this also on the content exchange ?