Which is the best choice based on STATISTICS resul | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Which is the best choice based on STATISTICS resul

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? RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.
i think you are finding out that optimizing SQL is a tricky matter

You have a clear winner here. The only problem you can’t know if the reason is nolock hint or all other hints you used. What are results when you remove the nolock hint from the last try or you add nolock hints to other tries?
]]>