May 11, 2014 at 8:53 am
Hi,
Have been reading articles on tuning queries and commonly come across the set statistics io/time to get benchmarks but don't seem to see them talk about clearing the cache first.
Looking to improve on my tuning skills and was wondering using things like
USE <YOURDATABASENAME>;
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
not advisable? time being one of the main things people look for in their queries, I would have thought running a clean query each time is essential in optimizing it?
May 11, 2014 at 10:06 am
In my experience the majority of the queries that need to be tuned are ones that run against active data, not against stuff that must first be pulled up from disk into RAM. Some exceptions to this exist, especially in data warehousing loads, first-page-view web scenarios, etc. Besides, if you make the query most efficient often IO reduction is part of that anyway.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 11, 2014 at 10:28 am
Thanks for the reply.
So you don't think they are of much use?
Scenario I was looking at was query A takes a long time to run to build a table and thought there is a better way of writing it, but the re-write may use cached plans from Query A and seem faster but no truly be the case.
You would just come part the statistics IO for this? using cpu, time benchmarks?
May 11, 2014 at 11:05 am
When you're testing one query against another, they're of great help. You'll be running both queries under the same circumstances. Of course, queries will be faster if you have the data in the buffer, but for testing purposes, you need to remove whatever gives any advantage.
I would recommend that you continue to use them while you are testing.
May 11, 2014 at 11:20 am
Thanks for the reply.
May 11, 2014 at 12:51 pm
What would be the best way of evaluating the performance of an inner query?
say you have something like
SELECT Key, sum() , sum() max()
into #table
from
(select key, case(), case() case()
union
select key()case, case(), case()
) as InnerQuery
group by key
say you have concerns of the innerquery could be done better, would doing a select into temp table from the inner query be a reflection of the performance in this instance? or is the no true way of evaluating it?
May 12, 2014 at 3:38 am
Whether or not you clear the buffers or the cache before you measure performance really comes down to what you're trying to measure. I agree with Kevin that most queries, most of the time, are working on active memory, so the added measure of reading from disk doesn't really do much. In fact, most of the time, when testing queries, I'll run it once without really looking at the time, then run it 2-3 more times and take the average run time. That way I get the compile and loading from disk out of the way and I'm just seeing the time it takes to actually execute the query. But, it does depend on what you're trying to measure.
As to measuring that inner query separately, I'd just run that select as a different statement if I really wanted to measure it. And, of course, check the execution plan to see how the query is being resolved.
"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
May 12, 2014 at 4:34 am
Grant Fritchey (5/12/2014)
In fact, most of the time, when testing queries, I'll run it once without really looking at the time, then run it 2-3 more times and take the average run time. That way I get the compile and loading from disk out of the way and I'm just seeing the time it takes to actually execute the query.
That's how I do the majority of my tuning as well.
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply