Tell me more ×
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.
up vote 0 down vote favorite

This question is in regards to SQL 2005 and email alerts on long running queries.

Does this sound like the best approach (with the least impact to the server) to receiving alerts to long running queries is the following:

  1. Create a server side trace that monitors across all databases or a specific database for events 10 (RPC:Completed) and 12 (SQL:Completed).
  2. Set a trace filter on Duration for greater than n amount of time (for example, greater than 2 seconds).
  3. Separate stored procedure that runs every n seconds (for example 5 or 10) to check for new entries and sends out an email alert. (To detect new entries I would need to keep track of the last entry detected but that is trivial.)

Thanks.

share|improve this question
You know there are 3rd party products that do this (and much more) so that you don't have to re-invent the wheel. :-) – Aaron Bertrand Mar 9 at 13:01
@AaronBertrand - Yeah I know. But the company does not want to spend the money. Plus the question is a good exercise in knowledge. Figure that the 3rd party products have to be doing something similar. – Jason Mar 9 at 16:30
But they are spending the money. When you plug in a 3rd party product renowned for being good at what you're trying to do, you can spend your time (their money) doing other, more productive things than re-inventing the wheel. The vendors have put an incredible amount of time and effort into collecting the right data in the most efficient way possible, and putting all kinds of scaffolding around it so you can use the information effectively. And different data is collected in different ways. Not trying to convince you to buy but the "spending money" argument doesn't hold a whole lot of water. – Aaron Bertrand Mar 9 at 16:37
I've blogged about this. I understand the education aspect but you can also learn quite a lot from what the tools are doing without having to struggle through the learning curve, find out the hard way why they don't do x or only use method y to get z, skip discovering the same bugs they discovered (and fixed) along the way, etc. I want to make it clear that I'm not just trying to push tools but this is a problem adequately solved already. – Aaron Bertrand Mar 9 at 16:46
And finally, consider that the 3rd party products also have some IP to uphold. If I told you all of the ways we collect data in SQL Sentry Performance Advisor, all of the challenges we came across and how we solved them, then everybody could use that information to build a clone and nobody would buy our software. (Disclaimer: I work for SQL Sentry.) You can figure some of this stuff out on your own, of course, but it would take literal man-years to replicate and fine-tune everything. – Aaron Bertrand Mar 9 at 16:48

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.