April 4, 2011 at 7:06 am
Good morning, Group.
I have a simple one-table query that uses a non-covering non-clustered index. Here are the time and i/o stats:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ResourcePermission'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Normally, this query completes in less than a millisecond. So far, so good. (This is measured by a third-party tool from the code side, which tells the user how long different operations of the code takes.)
In practice, the query is called dozens, if not hundreds, of times via a parameterized query in code. It's a workhorse.
The problem is that a few times per hour, the query starts taking 300 ms. Normally, not an issue, but multiplied by dozens of times, and it causes significant delay.
I'm putting a server-side trace on it today, but as far as I know, all I can get from a trace is the fact that the query did indeed take that long. Is this correct? Where would I go to determine potential causes?
This is on a non-production system. The application itself did not do any updates, inserts, or deletes. Would a [nolock] help?
Thanks!
April 4, 2011 at 7:20 am
josephsheppard (4/4/2011)
Would a [nolock] help?
No, unless you understand that NOLOCK means reading inconsistent data and can live with it.
josephsheppard (4/4/2011)
Normally, this query completes in less than a millisecond....The problem is that a few times per hour, the query starts taking 300 ms. Normally, not an issue, but multiplied by dozens of times, and it causes significant delay.
Could be parameter sniffing. Does the query plan change or is it always the same plan?
Could also be blocking issues. Any evidence of this?
-- Gianluca Sartori
April 4, 2011 at 7:33 am
Start a trace that filters on duration > 50 ms or whatever you feel will get the data back.
Then investigate. It's most likely a set a parameters that could use a different plan. Could have been blocking but it can happen if the system is truly read only.
April 4, 2011 at 7:37 am
Really? I was under the impression that if all the parameters in the query (WHERE clause) are covered by the index, that same index would be used every time, and the query plan would (implicitly) be the same every time?
Thanks!
April 4, 2011 at 7:47 am
Not necessarily. The optimiser will look at the statistics to try to determine how selective your where clause is - for example, one particular parameter value may restrict the results to a single result and another may return 100,000 rows, so the optimal plan may be different.
In a case where cached plans are being used (often, but not restricted to Stored Procedures), the cached plan will be based on the first set of parameters that it was executed with, so it may have a sub-optimal plan for other sets of parameters (known as Parameter Sniffing).
April 4, 2011 at 7:51 am
It's actually "worse" than that. If you want a garanteed index seek, the predicate needs to return 0.3% OR LESS of the data in the table.
Needless to say that a few of the values might break that rule.
Where it could explain your case is that you may have values in the 1% range where a seek is still faster than a whole table scan, but still way more expansive than any other values in the table.
April 4, 2011 at 10:16 am
It absolutely could be parameter sniffing, yes, but it also could be contention on resources. Does the table have inserts, updates or deletes that occur? If so, you might be occasionally waiting for them to clear. It could be other things as well. At this point, based on what we know, it's hard to say what it is, but merely to speculate.
If possible, I would suggest trying to capture wait state information while the query is running slow. That's going to be hard to do since "slow" in this case is 300ms, but you need to determine what the query is waiting on.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 4, 2011 at 10:37 am
Grant Fritchey (4/4/2011) since "slow" in this case is 300ms
Tell me about it. Not a good one to really cut my teeth on.
The Tuning Advisor recommended my covered index (woohoo, I'm on the right track!), as well as three statistics, which to me are almost like voodoo, for all of my understanding of when to create them.
At any rate, I used a trace file to observe the actual query when it was running "slow". I used the parameters the actual parameterized query was using, and the same Exectution Plan was used.
So, why would the same plan take 1 ms at one execution, and 400x that in another?
It sounds like I will: 1) Chase the "wait state" data. 2) Implement the index and stats suggestion
I'm still working on this, and open to suggestions! I'm learning a lot of good stuff...Thanks!
April 4, 2011 at 10:39 am
How up to date are your stats???
Index fragmentation?
Those 2 could also cause this problem if the table is big enough.
April 4, 2011 at 11:20 am
Grubb (4/4/2011)
Grant Fritchey (4/4/2011) since "slow" in this case is 300ms
Tell me about it. Not a good one to really cut my teeth on.
The Tuning Advisor recommended my covered index (woohoo, I'm on the right track!), as well as three statistics, which to me are almost like voodoo, for all of my understanding of when to create them.
At any rate, I used a trace file to observe the actual query when it was running "slow". I used the parameters the actual parameterized query was using, and the same Exectution Plan was used.
So, why would the same plan take 1 ms at one execution, and 400x that in another?
It sounds like I will: 1) Chase the "wait state" data. 2) Implement the index and stats suggestion
I'm still working on this, and open to suggestions! I'm learning a lot of good stuff...Thanks!
I would take anything the DTA tells you with a grain of salt (a grain somewhere between 5-10 tons in size). Test anything and everything it suggests before you put it into production.
With identical execution plans, in all likelihood, you're looking at waits. So, capture query starts and query completes using a server-side trace and then figure out when this query is running at 300ms, what other queries, likely to be locking the table(s) in question and then see what they're doing. That's the best I can give you at the moment.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 4, 2011 at 11:31 am
Grubb (4/4/2011)
So, why would the same plan take 1 ms at one execution, and 400x that in another?
1) Blocking
2) Waiting for resource
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
April 4, 2011 at 1:05 pm
I like what you are saying, Grant.
I'm not familiar with the term "wait state" info. Is this something out of MSSQL, or from Perfmon or something?
Thanks!
April 4, 2011 at 2:10 pm
Grubb (4/4/2011)
I like what you are saying, Grant.I'm not familiar with the term "wait state" info. Is this something out of MSSQL, or from Perfmon or something?
Thanks!
SELECT * FROM master..sysprocesses. There's a last wait type column there. Wait States are what populate these, and there's a number of ways to look up what the wait state is. These are indicators to help you find (they're not the be all/end all) where to start looking at the real cause of your problem.
EDIT: That google recommendation didn't work... heh.
http://sqlserverpedia.com/wiki/Wait_Types
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2011 at 2:38 pm
Craig Farrell (4/4/2011) SELECT * FROM master..sysprocesses.
Thanks, Craig. The problem with that is that the query only runs ~400 ms. Do you know of a way to kick off a query when a particular process runs for some (sub-second) amount of time?
I've found sys.dm_os_wait_stats. The best I can think of right now is to get a percentage of the time spent in the top 10 lock states, before a performance anomoly, and then run it again as soon as I can after, and hopefully the slight bump can tip me off :unsure:
-Grubb
April 4, 2011 at 6:55 pm
Grubb (4/4/2011)
Craig Farrell (4/4/2011) SELECT * FROM master..sysprocesses.
Thanks, Craig. The problem with that is that the query only runs ~400 ms. Do you know of a way to kick off a query when a particular process runs for some (sub-second) amount of time?
I've found sys.dm_os_wait_stats. The best I can think of right now is to get a percentage of the time spent in the top 10 lock states, before a performance anomoly, and then run it again as soon as I can after, and hopefully the slight bump can tip me off :unsure:
-Grubb
No, to really get the wait states based on a particular event, you need to go to extended events, and I'm just learning them myself, so I'm not going to be able to advise well here. But that is the answer.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply