June 22, 2009 at 3:16 pm
During my performance tuning tests I found that the number in physical_io in sys.sysprocesses does not match I/O numbers returned after setting statisstics io on.
I have a relatively simple query which joins data from two tables and performs couple of aggregate funcrtions on them. Before each test I ran dbcc dropcleanbuffers and dbcc freeproccache, so all the the tests ran under clean both data and library chaches.
Sum of all scan counts = 2, logical reads = 19465, physical reads: 92, Read-ahead reads: 6788
And here how I compared it to sysprocesses, at the top of my script I put :
declare
@cpu int,
@io int
select @cpu = cpu, @io = physical_io
from master.sys.sysprocesses
where spid = @@spid
And at the end:
select cpu = cpu-@cpu, io = physical_io - @io
from master.sys.sysprocesses
where spid = @@spid
, and ran test again with clean buffers and set statistics io off.
The result is cpu = 125, but physical_io = 926.
What these 926 mean ? It does not match any my sum from stats IO or any of its combinations. You will say me that don't trust sysprocesses IO, but in my case I actually wanted to get CPU, and since I can't trust I/O, should I trust its CPU ? Did I do anything wrong ?
Thanks
June 23, 2009 at 1:38 am
Although this article relates to SQL Server 6.5, it does explain why physical_io isn't accurate, and I suspect it is also relevant to later versions.
June 23, 2009 at 3:02 am
Yes, Ian is absolutely right - the information in the article he posted still applies.
Nevertheless, sysprocesses is deprecated - so you could (and probably should) take a bit of time now to decide what it is you are trying to measure.
My favourite measure of query performance is not physical I/O - and nor is it the almost universal 'logical I/O'.
I almost exclusively use the total_worker_time measure on the sys.dm_exec_query_stats dynamic view. This is indeed a measure of CPU usage - and I am not looking to start a debate about that here.
For anyone interested in why the total_worker_time measure is the one to use (in non-parallel plans in 2005 only) rather than logical or physical I/O - take a look at a truly outstanding blog entry by Joe Chang:
June 23, 2009 at 3:46 am
take a look at a truly outstanding blog entry by Joe Chang:
You're right, it is an interesting blog, as much for the discussion that follows, which shows the other side of the argument.
I am not looking to start a debate about that here
It seems to me that the blog to comes to a (sort of) agreement that there is a definite correlation between logical i/o and cpu usage, so I won't argue with you.
June 23, 2009 at 3:52 am
Ian Scarlett (6/23/2009)
take a look at a truly outstanding blog entry by Joe Chang:
You're right, it is an interesting blog, as much for the discussion that follows, which shows the other side of the argument.
I am not looking to start a debate about that here
It seems to me that the blog to comes to a (sort of) agreement that there is a definite correlation between logical i/o and cpu usage, so I won't argue with you.
I think you read a different page 😉 😀
Never mind. Everyone is free to make their own assessment, as always, I guess.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply