Optimizing SQL queries

  • Hi All,

    I am sitting with a situation where I have a table that is 27 MB in size and a query that runs against it that possibly needs to be optimized. The query takes less than a sec to bring back the data however I have run that estimated execution plan and it shows that in the query there is a Clustered Index Scan of 74%. See query below.

    SELECT

    *,

    table2.column1 AS <columnname>

    FROM

    table1

    LEFT OUTER JOIN table2

    ON

    table1.columnn2 = table2.column2

    JOIN [PARAMETER]

    ON

    [PARAMETER] = 'ColumnData'

    AND column3> (GETDATE() - CAST(PARAMETERVALUE AS INT))

    AND column4 = 'columndata'

    WHERE

    table1.column2 = 5000

    ORDER BY

    column3 DESC

    The number of records that is returned amounts to 100. The is a clustered index on the primary key. I have added an non-clustered index to table1.column2. The query plan is still showing that a clustered index scan is at 75% cost. Can anyone please assist. Does this query need to be optimized any further?

    Regards

    IC

  • Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    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
  • I can't post that information:

    See statistics results:

    Table 'Table1'. Scan count 1, logical reads 3512, physical reads 3, read-ahead reads 3508, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'table2'. Scan count 0, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 47 ms, elapsed time = 39 ms.

  • Hi All,

    I create the missing index as the estimated query plan suggested which improves statistics on the query.

    Thanks!!

  • Imke Cronje (12/17/2010)


    I can't post that information:

    It's very hard (near impossible) to provide correct advice on performance problems without seeing what I asked for. Without knowing what the table looks like and what indexes are already there, any suggestions can be completely wrong.

    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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply