| Author |
Topic |
|
bones
Starting Member
4 Posts |
 Posted - 2007-09-28 : 12:09:04
|
| Hi,I was asked this question by someone and I didn't know the answer (hence I'm here and hoping someone else does!). They have an SQL database which is accessed via a web front end (not sure of the language) to return query results. Sometimes the queries take a long time (?) to return, whilst at other times the same query is very much faster. They have heard that it is possible to setup a query in such a way that if it takes more than a certain time to run it will generate an alert. This could then be scheduled as a job to aid diagnostics. Anyone know of a way to do this - measure the time taken to run a query and generate an alert if longer than a given time.Thanks. |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2007-09-28 : 13:17:28
|
| This is difficult to do. It's possible , that within the application you could do something like : track how long the query is taking , if it it takes more then a certain amount of time , record the sql statement and trigger alert.If the statements are taking so long ,run a trace with appropriate filters , debug that wayJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-28 : 14:39:20
|
More likely their queries need optimising I'm afraid. That involves all the hard work part, without any quick fix  The "sometimes quick, sometimes slow" is a symptom of several possible causes:1) Server busy, queries slower. Server may be busy because lots of people using it, of course, but also due to poorly thought through housekeeping, or poorly performing queries - a bad query makes the server slow, that slows the other queries running at the same time, users start pressing RETRY, problem gets worse before it gets better!2) Data may be in memory already, or not. When "not" then query takes longer.3) Query may not be designed to be cached. Redesign, and optimisation, will increase the frequency of queries being cached, and the cached queries will perform faster. If this is the problem then the likelihood is that the "runs faster" queries are cached because someone already asked the same / similar query shortly before, and its still cached.4) And that leads to adopting best practice. Things like parameterized queries, putting the owner name in front of tables, good index usage, and so on. No quick fixes for rectifying these type of things I'm afraid.Kristen |
|
|
|
bones
Starting Member
4 Posts |
Posted - 2007-09-28 : 15:42:03
|
| Thanks for the replies. I think perhaps I may have been slightly misleading in my question. If the details of my first post are ignored, another way of stating it would be:i) Is it possible in SQL to track the time taken to run *any* query.ii) Is it possible to generate some kind of alert based on the result.The idea would be to create a standard query and run it a few times (perhaps with other queries in between to eliminate caching, thanks for that pointer) to get an average run time for it. Then setup a scheduled job that ran say every 15 mins that would generate an alert if it took "too long".I agree there is a possibility that it is a poorly written routine, however the coder responsible is apparently very experienced but unfortunately now is no longer around (moved, not passed away!). It also apparently isn't connected to server load. Caching could be the answer though...Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-28 : 15:47:54
|
| SQL Profiler should be able to do that. It could alert for ANY query running over some limit, and not necessarily limited to your "canned test" query. |
|
|
|
bones
Starting Member
4 Posts |
Posted - 2007-09-28 : 16:15:58
|
| Can SQL Profiler be run as a scheduled task? From what I've been able to find out in the last few minutes (excuse my ignorance of this!) it looks as if it is a user driven tool? Or would the idea be to leave the profiler running for a few days and monitoring the scheduled query that ran every x mins and alerting if it took longer than y? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-28 : 16:23:33
|
| "Or would the idea be to leave the profiler running for a few days and monitoring the scheduled query that ran every x mins and alerting if it took longer than y?"That's probably a good plan for starters.There is a template for "Long duration" queries which you could try for a kick off. That shouldn;t record much data if the system runs smoothly for most of the time.Might even be enough, or a useful recordal tool, to download ServerAlive and point that at the box and record how often it times out.Kristen |
|
|
|
bones
Starting Member
4 Posts |
Posted - 2007-09-28 : 17:17:10
|
| Very helpful. Thanks very much for your suggestions :o) |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2007-09-29 : 05:09:33
|
| What would be the likely overheard of having a trace running in the background?Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-29 : 05:12:18
|
You shouldn't run it directly on the server, but its supposed to be quite "light" running from a remote machine.Probably depends how much you actually wind up tracing, and probably better that the second machine is on the LAN with the Server, and not down a dial up internet connection  having said that, we have run it on the serve directly when we have had no other choice (permissions / bandwidth preclude being able to run it elsewhere). And although that does slow down the server a bit, I saw it as being a necessary evil / medicineKristen |
|
|
|
|
Scheduled diagnostic query