SQL Server Performance

Which is the best choice based on STATISTICS resul

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by RoyalSher, Mar 7, 2007.

  1. RoyalSher New Member

    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.
  2. joechang New Member

    i think you are finding out that optimizing SQL is a tricky matter
  3. mmarovic Active Member

    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?

Share This Page