March 7, 2007 at 7:13 pm
Hi,
I am trying to tune a query on SQL Server 2005 (SP1), and I was taking
note of results of STATISTICS IO and STATISTICS TIME. Here are the
results with various combinations on the query.
a) WITHOUT INDEX AND WITHOUT LOOP JOIN HINT:
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 'tblCodeGroup'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'tblBusnPartTm'. Scan count 0, logical reads 21576, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table 'tblBusnPartTmMbr'. Scan count 1, logical reads 5509, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table '#6F96FBE9'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 178 ms.
b) WITH INDEX AND WITHOUT LOOP JOIN HINT:
Table 'tblBusnPartTmMbr'. Scan count 1, logical reads 5509, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table 'tblCodeGroup'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
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 'tblBusnPartTm'. Scan count 7, logical reads 411, physical reads
0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table '#6F96FBE9'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
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.
SQL Server Execution Times:
CPU time = 62 ms, elapsed time = 152 ms.
c) WITHOUT INDEX AND WITH LOOP JOIN HINT:
Table 'tblCodeGroup'. Scan count 5394, logical reads 10788, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table 'tblBusnPartTm'. Scan count 0, logical reads 21576, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table 'tblBusnPartTmMbr'. Scan count 1, logical reads 5509, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table '#6F96FBE9'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 126 ms.
d) WITH INDEX AND WITH LOOP JOIN HINT [WITH
(INDEX(IDXBusnPartTm1),NOLOCK and OPTION (LOOP JOIN)]:
Table 'tblCodeGroup'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'tblBusnPartTmMbr'. Scan count 379, logical reads 4462, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table '#6F96FBE9'. Scan count 4, logical reads 762, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'tblBusnPartTm'. Scan count 7, logical reads 411, physical reads
0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
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 '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.
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 36 ms.
I am trying to find out which should be good for the SQL Server to
give the results quicker without any timeouts or any other stress.
In case more details are needed, I shall that too. Can somebody help
in deciding the factors?
Thanks in advance!
Viking
March 8, 2007 at 7:59 am
If this is part of the
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=360&messageid=346291
thread:
1. I would put the index on the FK and let the optimizer work out whether to use a MERGE or LOOP join based on the current STATS. (Nested loop joins only work well when the number of rows is relatively small.)
2. In the above thread, did the COALESCE version of the query produce the same query plan as the INNER LOOP JOIN version?
March 19, 2007 at 1:08 pm
1) Be VERY careful comparing time runs between query executions. You must flush both the RAM cache and Procedure cache between each execution (or do a few runs first if everything fits in RAM). DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS if memory serves.
2) Likewise be VERY careful in thinking you are smarter than the optimizer. Forcing a query plan for a certain set of inputs (such as WHERE clause values) can result in a VERY good plan for one set of values and absolutely HORRID query plans for other values.
3) Tuning for fast response doesn't necessarily mean reducing I/O, nor does having lower I/O always mean faster query responses. The steps along the way to returning your set (especially join methods) play an important part in having an 'optimum' query.
4) Unless this query gets called very frequently, I am not sure I would sweat a few millisecs either way.
5) Do you have appropriate indexes on your tables?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 28, 2007 at 6:09 am
Tuning queries is best achieved without hints, use hints and you may find with sp2 your query doesn't work well any more. I don't agree that you always need to clear cache to tune a query, hopefully you'll be tuning queries that are cached. I'd always use profiler if I was looking at execution times, that way I get better granuality. as to times vs io it all depends on the precise circumstances of the query and the size of the data set. In a real concurrent user environment you may find execution times vary anyway, for testing you should either go for time or io and consider scalability, how will your data sets grow - chances are your hints won't work with all sizes of data.
You also have to consider what happens as your table stats go out of date, table(s) become fragmented, contention in a multi-user environment may interfere with your query, so you'd possibly want lowest io to avoid lookups, scans and so on.
The real answer is that there isn't an absolute answer.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 14, 2007 at 8:13 pm
Use SQL 2005 Database tuning advisor.
I would suggest usiing the option in DTA that says "Ignore existing data stuctures" i.e. index/stats/partitioned views.
that would tell you an initial view from SQL on necessary indexes and stats.
Side note:
don't use hints unless you have to.
HTH
April 15, 2007 at 7:51 am
I advise all of my clients to avoid the Database Tuning Advisor like the plague. I have seen it do unbelievably bad things to a database in the hands of untrained users. And I give this advice despite the fact that I get paid good money to clean up the mess! :-))
There is NO subsitute for 1) knowing your data and data access patterns, 2) training and 3) experience when it comes to indexing your data structures and to tuning SQL Server in general.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply