August 14, 2006 at 8:43 am
Hi,
I need some assistance in reading the below output. One of the people here thought it be a wise idea to run an audit tracking system for all PCs three times a day but after doing some initial analysis on the database server I have notice a performance difference. The output shows processess and I would like to verify in bring this matter up to kill the number of times this system should be running.
Thanks,
| Minutes | spid | waittype | cpu | physical_io | Server Name | spid | last_batch | cmd | ||
| 56075676 | 52 | 0x0000 | 0 | 2 | Server Name | Track-It! 7.0 | Aug 14 2006 10:36AM | 52 | 1/1/1900 | AWAITING COMMAND |
| 56075676 | 53 | 0x0000 | 0 | 0 | Server Name | Track-It! 7.0 | Aug 14 2006 10:36AM | 53 | 1/1/1900 | AWAITING COMMAND |
| 56075676 | 54 | 0x0000 | 0 | 0 | Server Name | Track-It! 7.0 | Aug 14 2006 10:36AM | 54 | 1/1/1900 | AWAITING COMMAND |
| 56075676 | 55 | 0x0000 | 0 | 0 | Server Name | Track-It! 7.0 | Aug 14 2006 10:36AM | 55 | 1/1/1900 | AWAITING COMMAND |
| 56075676 | 56 | 0x0000 | 0 | 0 | Server Name | Track-It! 7.0 | Aug 14 2006 10:36AM | 56 | 1/1/1900 | AWAITING COMMAND |
| 56075676 | 57 | 0x0000 | 0 | 0 | Server Name | Track-It! 7.0 | Aug 14 2006 10:36AM | 57 | 1/1/1900 | AWAITING COMMAND |
| 56075676 | 58 | 0x0000 | 0 | 0 | Server Name | Track-It! 7.0 | Aug 14 2006 10:36AM | 58 | 1/1/1900 | AWAITING COMMAND |
| 56075676 | 59 | 0x0000 | 0 | 0 | Server Name | Track-It! 7.0 | Aug 14 2006 10:36AM | 59 | 1/1/1900 | AWAITING COMMAND |
| 56075676 | 60 | 0x0000 | 0 | 0 | Server Name | Track-It! 7.0 | Aug 14 2006 10:36AM | 60 | 1/1/1900 | AWAITING COMMAND |
| 56075676 | 61 | 0x0000 | 0 | 0 | Server Name | Track-It! 7.0 | Aug 14 2006 10:36AM | 61 | 1/1/1900 | AWAITING COMMAND |
| 56075676 | 64 | 0x0000 | 0 | 0 | Server Name | Track-It! 7.0 | Aug 14 2006 10:36AM | 64 | 1/1/1900 | AWAITING COMMAND |
| 797 | 1 | 0x0000 | 656 | 0 | Server Name | Aug 14 2006 10:36AM | 1 | 8/13/2006 | LAZY WRITER | |
| 797 | 2 | 0x0000 | 1750 | 0 | Server Name | Aug 14 2006 10:36AM | 2 | 8/13/2006 | LOG WRITER | |
| 797 | 3 | 0x0000 | 156 | 0 | Server Name | Aug 14 2006 10:36AM | 3 | 8/13/2006 | LOCK MONITOR | |
| 797 | 4 | 0x0000 | 31 | 0 | Server Name | Aug 14 2006 10:36AM | 4 | 8/13/2006 | SIGNAL HANDLER | |
| 797 | 5 | 0x0000 | 0 | 0 | Server Name | Aug 14 2006 10:36AM | 5 | 8/13/2006 | TASK MANAGER | |
| 797 | 6 | 0x0000 | 0 | 0 | Server Name | Aug 14 2006 10:36AM | 6 | 8/13/2006 | TASK MANAGER | |
| 797 | 7 | 0x0000 | 0 | 254 | Server Name | Aug 14 2006 10:36AM | 7 | 8/13/2006 | TASK MANAGER | |
| 797 | 8 | 0x0000 | 1625 | 84 | Server Name | Aug 14 2006 10:36AM | 8 | 8/13/2006 | CHECKPOINT SLEEP | |
| 797 | 9 | 0x0000 | 0 | 0 | Server Name | Aug 14 2006 10:36AM | 9 | 8/13/2006 | TASK MANAGER | |
| 797 | 10 | 0x0000 | 0 | 259 | Server Name | Aug 14 2006 10:36AM | 10 | 8/13/2006 | TASK MANAGER | |
| 797 | 11 | 0x0000 | 0 | 2 | Server Name | Aug 14 2006 10:36AM | 11 | 8/13/2006 | TASK MANAGER | |
| 797 | 12 | 0x0000 | 0 | 8 | Server Name | Aug 14 2006 10:36AM | 12 | 8/13/2006 | TASK MANAGER | |
| 797 | 13 | 0x0000 | 0 | 210 | Server Name | Aug 14 2006 10:36AM | 13 | 8/13/2006 | TASK MANAGER | |
| 797 | 15 | 0x0000 | 0 | 94 | Server Name | Aug 14 2006 10:36AM | 15 | 8/13/2006 | TASK MANAGER | |
| 797 | 66 | 0x0000 | 0 | 0 | Server Name | SQLAgent - Emai | Aug 14 2006 10:36AM | 66 | 8/13/2006 | AWAITING COMMAND |
| 797 | 63 | 0x0000 | 62 | 42 | Server Name | SQLAgent - Gene | Aug 14 2006 10:36AM | 63 | 8/13/2006 | AWAITING COMMAND |
| 796 | 51 | 0x0000 | 157 | 16 | Server Name | Aug 14 2006 10:36AM | 51 | 8/13/2006 | AWAITING COMMAND | |
| 4 | 68 | 0x0000 | 0 | 1 | Server Name | SQL Query Analy | Aug 14 2006 10:36AM | 68 | 8/14/2006 | SELECT |
August 15, 2006 at 7:32 am
You should filter out spids less than 52 (<=51), the system processes wont need to be looked at, and order that list!
I am guessing the minute count is high because there is no last batch (and you couldnt use seconds - arithemtic overflow - I set last batch = 01/01/1978 if last batch = 01/01/1901).
Those transactions will not cause any noticeable performance degradation since they are only connections that have been opened and are not representative of actual queries. It DOES reek of a bad application design that may be indicative of other poorly structured queries / schemas. (its perfectly normal for these AWAITING COMMAND's to show for a split second until the batch is sent)
Other than that, spid 66 and 63 may need a look to see what they are doing.
Sp_who3 (if you have full sa access), or sp_what (if you dont) will give you a better idea of what is going on as far as process times and blocked processes. SQL Profiler is the ultimate tool for figuring out intermittent or not readily apparent performance problems.
Did you obfuscate the server names? If so, can you number them server 1 2 3 etc so we can see what is being issued and from where?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply