SQL Server Performance

Slow clustered index seek

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Steph2004, Jan 22, 2008.

  1. Steph2004 New Member

    Hi,
    I have a table with 100 M+ rows. As the table grows, the select queries are getting slower. In the execution plan, everything looks fine since I'm having clustered index seek. But a query can take more than 2 minutes which is too long.
    Here's a typical query :
    select top 500 sum(nb) as nbCount, itemId as item,
    DATEADD(wk, DATEDIFF(wk, 0, dateadd(hh, 0, tbl_computedBAseReportLogs.date)), 0) as sDate
    from tbl_computedBAseReportLogs where
    tbl_computedBAseReportLogs.date between '2007-07-01 00:00:00' and '2008-01-22 23:59:00' and
    tbl_computedBAseReportLogs.fk_tbl_websites_Id = 10449 and
    tbl_computedBAseReportLogs.fk_tbl_baseReports_id = 108
    group by tbl_computedBAseReportLogs.itemId,
    DATEADD(wk, DATEDIFF(wk, 0, dateadd(hh, 0, tbl_computedBAseReportLogs.Date)), 0) order by nbCount desc
    And here's the table structure:
    CREATE TABLE [dbo].[tbl_computedBaseReportLogs](
    [date] [smalldatetime] NOT NULL CONSTRAINT [DF_tbl_computedBaseReportLogs_date] DEFAULT (getdate()),
    [fk_tbl_websites_id] [int] NOT NULL,
    [fk_tbl_baseReports_id] [int] NOT NULL,
    [itemId] [int] NOT NULL CONSTRAINT [DF_tbl_computedBaseReportLogs_itemId] DEFAULT ((0)),
    [secondaryItemId] [int] NOT NULL CONSTRAINT [DF_tbl_computedBaseReportLogs_secondaryItemId] DEFAULT ((0)),
    [nb] [int] NULL CONSTRAINT [DF_tbl_computedBaseReportLogs_nb] DEFAULT ((0)),
    CONSTRAINT [PK_tbl_computedBaseReportLogs] PRIMARY KEY CLUSTERED
    (
    [date] ASC,
    [fk_tbl_websites_id] ASC,
    [fk_tbl_baseReports_id] ASC,
    [itemId] ASC,
    [secondaryItemId] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    What I'm am doing wrong? How could I get the query to run faster? How can I optimize the index?
    Thanks for any idea
    Stephane
  2. Luis Martin Moderator

    You can use DTA to test if any other index is necessary.
  3. ndinakar Member

    Thats a pretty wide clustered index. You might benefit from moving some columns out to another non-clustered index so SQL server can do separate retrieves and a hash match to join them. Try it out on a dev server first.
  4. satya Moderator

    You have clustered index in place, but are you performing optimization tasks or not such as REINDEXING the indexes on the frequently used tables.
  5. satya Moderator

  6. ScottPletcher New Member

    I agree that you need to look at the table itself for fragmentation. The clustered index looks like it should support that query well.
    PIease issue this command and report the results:
    DBCC SHOWCONTIG(tbl_computedBaseReportLogs)
  7. Steph2004 New Member

    @satya Yes, I reindex once a week
    @Scott Here's the result. What does that tell you?DBCC SHOWCONTIG scanning 'tbl_computedBaseReportLogs' table...
    Table: 'tbl_computedBaseReportLogs' (607341228); index ID: 1, database ID: 10
    TABLE level scan performed.
    - Pages Scanned................................: 490342
    - Extents Scanned..............................: 61416
    - Extent Switches..............................: 71471
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 85.76% [61293:71472]
    - Logical Scan Fragmentation ..................: 2.43%
    - Extent Scan Fragmentation ...................: 2.72%
    - Avg. Bytes Free per Page.....................: 105.4
    - Avg. Page Density (full).....................: 98.70%
  8. ScottPletcher New Member

    The table is in decent to good shape. Density is relatively high, fragmentation is low.
    I guess there's just a large number of rows that fall into that time period. Keep in mind that in order to find the TOP 500 totals, SQL must accumulate *all* the totals and sort them before finally being able to determine the "top" totals.
  9. madhuottapalam New Member

    I am not sure how best can this index be utilised by this query... SInce you have function (DateDiff() probably it may not use this index. Do we have a chance to remove this function by storing the way you want? And you may also want to change the index or create covering index. The best method will be to analyse Execution plan.
    Madhu
  10. ScottPletcher New Member

    Since you can't shorten the index, you might as well try adding the last column, nb, to it, since you are using it in your query. That would mean that all queries would be satisfied directly from the index pages rather than having to use any data pages at all.

Share This Page