Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've created an alert for long run queries. However, the history is always empty even I set the value to 1(second). The following is the generated script. Is there anything wrong?

/****** Object:  Alert [Long run queries]    Script Date: 10/17/2013 12:52:16 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N'Long run queries', 
    @message_id=0, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=5, 
    @category_name=N'[Uncategorized]', 
    @performance_condition=N'SQLSERVER:Transactions|Longest Transaction Running Time||>|1', 
    @job_id=N'eb3ca402-cab2-4395-a2cf-13c47cf89323'

GO
share|improve this question
add comment

2 Answers

Short Answer: This is probably not the best counter for you to do what you are looking for. In fact you should consider writing your own monitoring process or utilizing a third party monitoring product, rather than a SQL Agent alert.

Longer Answer/Background:

As has been established by the original answer from Jonysuise - this is looking at transactions that are long running. Not every query gets a transaction. If it is an update or an insert? Yes. If it is a select query? No, unless someone explicitly wraps it in a transaction. This is important if you are looking to capture long running selects, which in my experience, tend to be the killers in many environments.

Either way here I am not sure that this is the best way to accomplish what you are looking for. I'm not a fan of rolling my own approach to something that a tool already has - and I like SQL Sentry for this reason - among other monitoring needs. So that is a good approach - then it is someone else supporting the long term development to keep up with versions, someone else implementing other alerts - and they have a tried and trued harness for things like this, especially the Top SQL running long. But I don't want this to sound like a commercial for them (I get nothing from them for talking about them positively). There are plenty of other tools also.

So with that out of the way.. Something of an answer:

1.) One problem here is that the polling interval for the SQL Server Agent alerts is not constant - in 2000 it was definitely every 20 seconds, it may be more frequent in later versions, but it is not continuous, that would be too much overhead. So something has to be above the defined threshold for more than 20 seconds. If you have a spike and it is done when the 3 times a minute (20 second) polling runs, you'l miss it. This is just one reason why the Agent Alert is probably not the right tool for this job. If you increase your occurrence count and make sure the SQL SErver agent is running, you'll see an occurrence for this in the history.

2.) Again - I don't love this approach. You miss select queries not explicitly defined as transactions. And even if you could there is no way for you to filter these out. So I would suggest a monitoring tool here or perhaps trying something like I explain at the end here.

I would also, though, suggest asking yourself what the goal is. Do you want to know anytime a query runs over a certain length of time? That is good information, and sometimes you want that depending on your system. Sometimes you care more about the overall health, or perhaps a certain query.

But assuming you want to do this and don't want to buy a tool, there are plenty of guides out there on rolling your own monitoring solutions in SQL Server. One approach I've seen used involves the sp_whoisactive script that Adam Machanic wrote. This is a well accepted, widely adopted free tool that combines several key DMVs and system information to show you what is happening on your SQL Server instance.

One approach (in pseudo code/explanation style) could be:

  1. Log the sp_whoisactive data to a table (Kendra Little has an excellent blog post about this) (I wouldn't do this terribly frequently, and I would monitor your performance while it runs. The tool is well written but it isn't "free" to run from system resourecs perspective. If you were running this every 1 second I'd potentially be concerned. Every 15? 30? 60? It depends - you need to test.
  2. Periodically query this table in a SQL Agent job. Use the information presented in the query (Duration, query text, application, user) to determine if it is "alert worthy" and if it is alert worthy (Duration > set amount, user not in an "acceptable" list, text not in an acceptable list, etc.) either raise an error that an alert can fire off of, or generate an e-mail.
  3. Remember to keep that table pruned and really monitor the approach - it may be that your environment can't handle the load of doing this frequently enough. You can look at some of the tables underneath this procedure and capture less data.

That is just one approach. It may not be the right or best approach - and enough time searching the web will show you at least a half dozen more. But if a client were to offer to pay me an hourly rate to create this for them, I'd suggest they really consider SQL Sentry or one of the tools like it. The price of most of these tools isn't a whole lot more than a couple days of SQL Server consulting help.

share|improve this answer
add comment

This alert is not for long running querys, it's for long running transactions.

share|improve this answer
    
Shouldn't all single statements have implicit transaction? –  u23432534 Oct 18 '13 at 16:11
2  
No. only if SET IMPLICIT_TRANSACTIONS is ON for the connection, which by default is OFF. –  Jonysuise Oct 18 '13 at 19:55
    
The answer of the following question says "All individual SQL Statements are automaticaly "In a Transaction" whether you explicitly say so or not" stackoverflow.com/questions/1171749/… –  u23432534 Oct 18 '13 at 20:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.