I was trying to test sp_blocker_pss80 (and SHERLOCK) on an SQL 2005 system and noticed that it gave much more output than on an SQL 2000 system . This was because the waittypes of many spids were not zero while under SQL 2000 the same ones were zero. Why is that?
For instance here is the subset of the query which is part of the sp_blocker_pss80 run on an sql2005 and sql2000 respectively
select spid, status, blocked, open_tran, waittype,
waittime, cmd, lastwaittype
from master..sysprocesses
SQL 2005
spid status blocked open_tran waittype waittime cmd lastwaittype
- -- -- - --
1 background 0 0 0x0000 0 RESOURCE MONITOR SLEEP_TASK
2 background 0 0 0x0060 250 LAZY WRITER LAZYWRITER_SLEEP
3 suspended 0 0 0x007F 18062 LOG WRITER LOGMGR_QUEUE
4 background 0 0 0x0080 4828 LOCK MONITOR REQUEST_FOR_DEADLOCK_SEARCH
5 background 0 0 0x009D 195558000 SIGNAL HANDLER KSOURCE_WAKEUP
6 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
7 background 0 0 0x00A2 640 TRACE QUEUE TASK SQLTRACE_BUFFER_FLUSH
8 sleeping 0 0 0x0000 0 UNKNOWN TOKEN MISCELLANEOUS
9 background 0 0 0x00A9 195579953 BRKR TASK BROKER_TRANSMITTER
10 suspended 0 0 0x0081 894421 CHECKPOINT CHECKPOINT_QUEUE
11 background 0 0 0x007E 195583703 TASK MANAGER ONDEMAND_TASK_QUEUE
12 background 0 0 0x00AD 195557968 BRKR EVENT HNDLR BROKER_EVENTHANDLER
13 background 0 0 0x00A9 195579875 BRKR TASK BROKER_TRANSMITTER
14 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
15 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
16 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
17 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
18 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
19 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
20 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
21 sleeping 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
51 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
54 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
55 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
56 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
57 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
58 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
59 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
61 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
62 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
63 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
64 runnable 0 0 0x0000 0 SELECT MISCELLANEOUS
65 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
66 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
67 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
68 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
69 sleeping 0 0 0x0000 0 AWAITING COMMAND MISCELLANEOUS
(37 row(s) affected)
SQL 2000
spid status blocked open_tran waittype waittime cmd lastwaittype
- -- -- - --
1 background 0 0 0x0000 625 LAZY WRITER SLEEP
2 sleeping 0 0 0x0000 29750 LOG WRITER RESOURCE_QUEUE
3 background 0 0 0x0000 2625 LOCK MONITOR RESOURCE_QUEUE
4 background 0 0 0x0000 0 SIGNAL HANDLER PAGEIOLATCH_SH
5 background 0 0 0x0000 0 TASK MANAGER WRITELOG
6 background 0 0 0x0000 100035093 TASK MANAGER RESOURCE_QUEUE
7 sleeping 0 0 0x0000 738921 CHECKPOINT SLEEP RESOURCE_QUEUE
8 background 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
9 background 0 0 0x0000 0 TASK MANAGER WRITELOG
10 background 0 0 0x0000 0 TASK MANAGER MISCELLANEOUS
11 background 0 0 0x0000 0 TASK MANAGER WRITELOG
12 background 0 0 0x0000 0 TASK MANAGER WRITELOG
51 sleeping 0 0 0x0000 0 AWAITING COMMAND NETWORKIO
52 sleeping 0 0 0x0000 0 AWAITING COMMAND NETWORKIO
53 sleeping 0 0 0x0000 0 AWAITING COMMAND PAGELATCH_SH
54 sleeping 0 0 0x0000 0 AWAITING COMMAND NETWORKIO
55 runnable 0 0 0x0000 0 SELECT NETWORKIO
select spid, status, blocked, open_tran, waittype,
waittime, cmd, lastwaittype
from master..sysprocesses
where blocked!=0 or waittype != 0x0000
So because of the or waittype != 0x0000 I get a much more output under SQL 2005
It looks like all of the wonky ones are under spid 50 - which is for system pids - can anyone elaborate on that?|||I am not sure I understand what you mean. The spids where the waittype is not 0 range from 1 to 13 except 6 and 8|||You can't compare the tracking in 2000 to 2005. The number of wait types in 2005 is almost triple what it was in 2000. While several of these are due to new features such as Service Broker or Database Mirroring, many are due to a much more granular level of tracking. In 2000, a dozen of more things contributed to an OLE DB wait type whereas in 2005, each one is split out separately. You are almost always going to see system processes with a non-zero wait type and that is due to some architectural changes.
There is another side effect that you have to take into account. If the account that you are running this under does not have VIEW SERVER_STATE authority, you will get exactly 1 row from the wait stats view that is a dummy entry.
I'm not sure what you are really trying to accomplish here.
|||My point is that Microsoft whould have come up with a new version of sp_blocker_pss80 (let's call it sp_blocker_pss90) only for SQL 2005 which would have excluded these system processes with a non-zero wait type and would have made the diagnostics cleaner. Is this not a reasonable request?
|||Let's see if I understand this. You are trying to run sp_blocker_pss80, a procedure that was explicitly created for SQL Server 2000 as evidenced by the fact that part of the name is 80 in reference to version 8.0 which is the version number of SQL Server 2000, against version 9.0 - SQL Server 2005 and it is giving you different results. Microsoft extended the KB article to encompass SQL Server 2005, because it was determined that the same procedure was also applicable to SQL Server 2005, therefore a new version was never created. All code that you get is governed by their EULA, which pretty specifically states the warranties. This was also separate code that was written by someone and published as a KB article, because they thought it might be useful to someone. Is it supposed to be Microsoft's problem to make sure that before a product is released that they find every single utility script which might have been created by anyone within the entire organization and update all of them to the newest version of the product?
I have libraries of thousands of utility scripts from Sybase 4.9 through 11.5 as well as SQL Server 4.21a through 2005 that contain thousands of utility scripts. Some of them I've updated to SQL Server 2005, the vast majority haven't been touched yet. Some of the stuff from SQL Server 6.0 still works against 2005 although it gives different results.
I fail to see where this is Microsoft's issue. I also fail to see where the procedure is causing any problems. It explicitly pulls anything with a non-zero wait time. The reason that it pulls anything with a non-zero wait time is that regardless of whether it is a system process or a user process, it can possibly cause blocking and performance issues on the system which is the entire point of the procedure in the first place. Additionally, all of the code is published. If you don't like the way it is coded or works, then you are free to create your own version that does exactly what you want.
We explained what those SPIDs are and what they mean. The output from the proc you are running is still completely valid.
|||
Microsoft extended the KB article to encompass SQL Server 2005, because it was determined that the same procedure was also applicable to SQL Server 2005, therefore a new version was never created.
Well thank you for telling me that explicitly . I was determined to find out if a new version exists. Since proving the negative is all that much more difficult that proving the positive ( I just had to find a single example of sp_blocker_pss90) your explicit statement that no such code exists helps.
All code that you get is governed by their EULA, which pretty specifically states the warranties. This was also separate code that was written by someone and published as a KB article, because they thought it might be useful to someone
Apparently Microsoft itself found it useful since Roberto Farah who works at Microsofts's PSS is the co-author of Sherlock which pulls together the information from sp_blocker_pss80 and provides it in an easy to read format. Microsoft has been using it internally and decided to release it to the public.
Is it supposed to be Microsoft's problem to make sure that before a product is released that they find every single utility script which might have been created by anyone within the entire organization and update all of them to the newest version of the product?
Since Microsoft used it internally and then released it publicly I would consider it reasonable to indicate to the public to whom hey have released the tool to if any changes are due under SQL 2005 .
I have libraries of thousands of utility scripts from Sybase 4.9 through 11.5 as well as SQL Server 4.21a through 2005 that contain thousands of utility scripts. Some of them I've updated to SQL Server 2005, the vast majority haven't been touched yet. Some of the stuff from SQL Server 6.0 still works against 2005 although it gives different results.
You may also have some code written by Konrad Zuse, but I do no think it is relevant to the question I asked.
I fail to see where this is Microsoft's issue. I also fail to see where the procedure is causing any problems.
I explained the Microsoft connection above. I never said that the procedure is causing any problems. It will just produce much larger outputs than before.
If you don't like the way it is coded or works, then you are free to create your own version that does exactly what you want.
That is precisely what I intend to do . My old version of pss_blocker80 had already been modified anyway. I did consider it reasonable to inquire what is available before I change the code
We explained what those SPIDs are and what they mean.
Yes thank you. But you explained it only after I opened this query. Perhaps the same comment can be added in the source of sp_blocker_pss80 to indicate that extra SPID will appear under SQL 2005
No comments:
Post a Comment