Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query tuning


Query tuning

Author
Message
roblew 15918
roblew 15918
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 132
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?
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8296
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 at GMail
roblew 15918
roblew 15918
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 132
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?
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8468 Visits: 18080
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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
roblew 15918
roblew 15918
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 132
Thanks for the reply.
roblew 15918
roblew 15918
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 132
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?
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17524 Visits: 32251
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47091 Visits: 44342
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search