January 18, 2012 at 4:34 am
Hi all,
I am fairly new to SQL server performance tuning and would appreciate your input.
Our SQL server suffers from intermittent performance problems and my initial analysis seems to suggest that this is due to spikes in IOPS.
Something I don't quite understand is the meaning of the "Reads" column in SQL server profiler. Is it:
1. The number of reads from both the buffer cache and disk
2. The number of reads from disk only
3. The number of reads from the buffer cache only
The reason I ask is that one query, in poor conditions has a duration of 3859725 (around 3.8 seconds) and has a "Reads" value of 56203.
In good conditions, the exact same query has a duration of 43894 (around 0.04 seconds) and has a "Reads" value of 4351.
At a guess I would say this means that "Reads" means the number of reads from the disk, and in poor conditions the SQL server is paging rather than using the buffer cache.
Please could someone point me in the right direction and performance give me some appropriate PerfMon metrics to correlate?
Thanks in advance.
Ben
January 18, 2012 at 4:43 am
They are logical reads, from the buffer pool.
All SQL reads are from the buffer pool, the higher levels of the storage engine and the query processor don't access the files directly. So if a page is needed, it's requested from disk (that's a physical read in the output of STATISTICS IO) and then the page is read from the buffer pool for the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2012 at 4:47 am
Thanks for your reply,
In that case I'm not sure why the same query would generate significantly more reads from the buffer pool.
I would have thought more reads from buffer pool = less reads from disk, meaning a quicker query but as you can see the duration tells a very different story.
Any further insight into this?
January 18, 2012 at 5:02 am
bathawes 16438 (1/18/2012)
I would have thought more reads from buffer pool = less reads from disk
No, not necessarily. More reads from buffer pool = more reads from buffer pool, nothing else. One query read and processed 56 thousand pages, one read and processed 4 thousand.
I think you may also have missed what I said about disk reads. ALL reads done by queries are from the buffer pool, none come directly from disk. If pages are needed from disk the storage engine pulls them into the buffer pool and the the query reads them from the buffer pool (counting as a logical and a physical read)
meaning a quicker query but as you can see the duration tells a very different story.
Not necessarily. IO is not the only thing that slows a query down. Could be blocking or other waits. Could be pure CPU time. That said, the slower query is processing more than 10 times the amount of data than the faster one is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2012 at 5:17 am
Not necessarily. IO is not the only thing that slows a query down. Could be blocking or other waits. Could be pure CPU time. That said, the slower query is processing more than 10 times the amount of data than the faster one is.
Thanks for your tip about all data being read from the buffer pool.
Now you mention CPU, it's worth noting the following:
The "slow" query shows 2340 in the "CPU" column.
The "fast" query shows 31 in the "CPU" column.
What's strange about this is that the queries originate from a very basic application that I wrote that executes an almost identical query. I have confirmed this by checking the "TextData" column.
I noticed in PerfMon that during slow conditions the "Disk Transfers/sec" metric was an order of magnitude higher than when queries were running normally (around 1000 vs. 100) so perhaps the problem is the sheer volume of queries on the server.
Another point of interest is that the server has quite a small quantity of RAM (4GB) which probably explains it averaging around 93 pages/sec.
Does any of that help narrow down the problem?
Ben
January 18, 2012 at 5:19 am
No.
The problem is going to be in the definition of the query and the execution plan chosen
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2012 at 5:44 am
The problem is going to be in the definition of the query and the execution plan chosen
I was afraid you might say that.
As this is an out of the box SharePoint query changes are outside of the scope of support. I am happy to raise this with Microsoft but need to understand something first:
Surely if this were an issue with the query itself it would always be slow?
If a bunch of queries execute fine normally, but occasionally take a long time to run surely that points to a server problem.
January 18, 2012 at 5:59 am
bathawes 16438 (1/18/2012)
Surely if this were an issue with the query itself it would always be slow?
No, not at all. In fact, I gave an hour long presentation last year on the subject of queries with erratic execution characteristics, and barely scraped the surface of the topic.
If a bunch of queries execute fine normally, but occasionally take a long time to run surely that points to a server problem.
Maybe, but if this were a system I had control of (so not sharepoint), the queries, indexes and execution plan would be my first place to investigate. It might be hardware, but it often isn't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply