August 26, 2015 at 10:55 am
I have some stored procedures that sometimes execute much slower than usually. When examining history from our monitoring tool, these S.P. showed high PAGEIOLATCH_SH wait type, however at some other times these same S.P. have normal executions, without delays.
From what I read from different sources, this is when query waits for pages to be loaded into buffer cache. But what practically I can do about these queries? Index fragmentation is low, statistics is regularly updated. What my available options?
Thanks
August 26, 2015 at 11:26 am
It suggests an I/O issue. A couple of things could be causing that. First, have you looked at the execution plans when the query is doing more I/O? If they're the same as when it's doing less I/O, we can focus on resource contention, blocking, or some problem with the I/O sub-system. If they're different, it could just be something along the lines of bad parameter sniffing.
Do you have a blocking monitor in place? Are you experiencing blocking during these periods? If you're not hitting blocking, it might be something wrong at the disk level. Check the OS and the SAN for errors.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2015 at 11:47 am
Yes, I checked execution plan, and executed it on empty and filled buffer cache in test server, it all executes within seconds. But my PageIOLatches happen in production, and not for every execution. These two S.P's execute about 5-10 times a day, and only 1-2 executions are much slower.
Yes, we have monitoring tool. Ignite. This is where I took these waits from. There were no blockings for these S.P.
I probably have some another idea, or maybe a question. These two tables, that my S.P.s select from, have a lot of indexes, and I checked buffer cache contents from buffer descriptors sys view before and after executing these S.P.; all these indexes are being loaded into buffer cache. I checked their usage (we have a daily job that collects metrics for that) and found that about a half of these indexes are never or very rarely used. So my next question is: if I drop these unused indexes, will it solve my problem? Will SQL Server need to spend less time for loading indexes into cache? Will it reduce PageIOlatches?
August 26, 2015 at 12:12 pm
I can't know that it'll solve the problem since we're not sure where the contention is coming from. However, I can say with some assurance, it's definitely going to somewhat alleviate the problem. Maintaining unused indexes is certainly going to cause additional processing and contention at the I/O level.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2015 at 12:17 pm
Also, you say you checked the plan, I mean, did you look at the plan when it's running slow to compare it to when it's running fast. If you're hitting a bad parameter sniffing issue that leads to a different execution plan for a short period of time, you could see intermittent periods of bad I/O leading to the latch contention.
I'd also suggest gathering the additional metrics outlined in this white paper by Paul Randal. It'll help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2015 at 12:43 pm
Grant Fritchey (8/26/2015)
Also, you say you checked the plan, I mean, did you look at the plan when it's running slow to compare it to when it's running fast. If you're hitting a bad parameter sniffing issue that leads to a different execution plan for a short period of time, you could see intermittent periods of bad I/O leading to the latch contention.I'd also suggest gathering the additional metrics outlined in this white paper by Paul Randal. It'll help.
Hmmm... The problem is that I don't know in advance when it's going to run. I receive email notification about timeout, and then check Ignite for this time frame for reported stored procedure. I only had a chance to run execution plan in test server, which of course has different loads than production. I checked exec plan on clean cache and then on filled cache; they were the same. The only suggestion that exec plan showed is to create a new index. Actually, we already had an index with all listed columns, just in different order. So I dropped it and created a new one according to exec plan suggestion. It showed little improvement, it reduced total cost from about 7 to about 6. That was all in test server.
Thanks for the white paper, I will study it.
August 26, 2015 at 8:53 pm
SQL Guy 1 (8/26/2015)
Grant Fritchey (8/26/2015)
Also, you say you checked the plan, I mean, did you look at the plan when it's running slow to compare it to when it's running fast. If you're hitting a bad parameter sniffing issue that leads to a different execution plan for a short period of time, you could see intermittent periods of bad I/O leading to the latch contention.I'd also suggest gathering the additional metrics outlined in this white paper by Paul Randal. It'll help.
Hmmm... The problem is that I don't know in advance when it's going to run. I receive email notification about timeout, and then check Ignite for this time frame for reported stored procedure. I only had a chance to run execution plan in test server, which of course has different loads than production. I checked exec plan on clean cache and then on filled cache; they were the same. The only suggestion that exec plan showed is to create a new index. Actually, we already had an index with all listed columns, just in different order. So I dropped it and created a new one according to exec plan suggestion. It showed little improvement, it reduced total cost from about 7 to about 6. That was all in test server.
Thanks for the white paper, I will study it.
You can pull the plan out of cache on the server using sys.dm_exec_query_plan. Combine it with sys.dm_exec_query_stats if it's ad hoc, or sys.dm_exec_query_stats if it's not. SSMS is not the only way to get a plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply