Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query tuning Expand / Collapse
Author
Message
Posted Sunday, May 11, 2014 8:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:57 AM
Points: 25, Visits: 70
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?
Post #1569623
Posted Sunday, May 11, 2014 10:06 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 4,402, Visits: 6,263
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
Post #1569631
Posted Sunday, May 11, 2014 10:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:57 AM
Points: 25, Visits: 70
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?
Post #1569633
Posted Sunday, May 11, 2014 11:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:03 PM
Points: 3,774, Visits: 8,471
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1569635
Posted Sunday, May 11, 2014 11:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:57 AM
Points: 25, Visits: 70
Thanks for the reply.
Post #1569638
Posted Sunday, May 11, 2014 12:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:57 AM
Points: 25, Visits: 70
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?
Post #1569647
Posted Monday, May 12, 2014 3:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 13,890, Visits: 28,285
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1569733
Posted Monday, May 12, 2014 4:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:38 PM
Points: 40,201, Visits: 36,603
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 2008, MVP
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

Post #1569754
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse