SQL Server Performance

Please assist with a slow performing query

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by btt423, Aug 14, 2007.

  1. btt423 New Member

    I have inherited an application from an outside vendor and the database design leaves much to be desired, so we are experiencing performance problems. One of the problems I am having is attempting to find the current status of a record. Here is the scenario...
    1. We track home inspections (table1)
    2. A home inspection can go through multiple statuses. The status of a home inspection is not tracked on table1. There is a statuslog table that has a complete history of statuses for an inspection. That most recent record in the statuslog table is the current status. We currently have about 600,000 records in this table. Not a tremendoud amount of data, but as this table increases, our performance decreases. Here is the structure of the status log table:
    CREATE TABLE [dbo].[dds_intStatusLog](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [SRID] [varchar](50) NULL,
    [Updated] [datetime] NULL CONSTRAINT [DF_dds_intStatusLog_Updated] DEFAULT (getdate()),
    [STATUS] [varchar](25) NULL,
    [Explanation] [varchar](255) NULL,
    CONSTRAINT [PK_dds_intStatusLog] PRIMARY KEY NONCLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    3. We have several indexes on this table:
    -- Create a clustered index on SRID with a 90% fill factor
    CREATE CLUSTERED INDEX [IX_dds_intStatusLog_SRID] ON [dbo].[dds_intStatusLog]
    (
    [SRID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO
    -- Create an index on UPDATED with a 90% fill factor
    CREATE NONCLUSTERED INDEX [IX_dds_intStatusLog_Updated] ON [dbo].[dds_intStatusLog]
    (
    [Updated] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    -- Create a PK on ID
    ALTER TABLE [dbo].[dds_intStatusLog] ADD CONSTRAINT [PK_dds_intStatusLog] PRIMARY KEY NONCLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    3. A view was created to dynamically determine the latest status. A view was chosen so it could be used in a join to get the current status for many records at a time. This view is used in hundreds of places and is the main culprit for our performance problems. Determining the latest status is taking upwards of 15 - 20 seconds when we need it be determined in less than a second. Here is the current view definition:
    ALTER VIEW [dbo].[vw_ui_CurrentStatusBySRID]
    AS
    SELECT SRID, STATUS, Explanation
    FROM
    (
    SELECT ID, SRID, STATUS, Updated, Explanation,
    Row_NUMBER() OVER (PARTITION BY SRID ORDER BY ID DESC) as STATUS_ORDER
    FROM
    dbo.dds_intStatusLog
    ) AS STAT_LOG
    WHERE STATUS_ORDER = 1
    I have attempted a few different changes, but nothing helped. I tried removing the indexes and creating a single composite clustered index on SRID and ID. I also tried rewriting the view to use a CTE instead of a nested query. No of these changes helped.
    Any help would be greatly appreciated!!
    Thanks!
  2. Luis Martin Moderator

    Could you run, using QA:
    set statistics io on
    your query
    set statistics io off
    and post the results?
  3. btt423 New Member

    Sure... here is the results that I got:
    Table 'dds_intStatusLog'. Scan count 5, logical reads 3143, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  4. satya Moderator

    Just checking on service pack level of SQL on what Luis mentioned above, Blogpost fyi.
  5. btt423 New Member

    We are at service pack 2.
  6. sunilmadan New Member

    Did you try creating clustered index on ID instead of SRID ...
  7. btt423 New Member

    Hi... yes, a clustered index on ID is actually what was originally implemented when the system was delivered to us. Thanks though.
  8. satya Moderator

  9. sunilmadan New Member

    A proven way to tackle such kind of thing is to replicate the status in a separate table using triggers and fetch the status from there whenever needed. This would help since most of your queries use that status.
  10. Ray D New Member

    Try to rewrite this view's query as shown below:
    select srid, status, explanation
    from dbo.dds_intStatusLog sl
    where sl.id = (select max(actsl.id) from dbo.dds_intStatusLog actsl where actsl.srid = sl.srid)
    With the following indexes:
    create unique index ix_actsl on dbo.dds_intStatusLog (srid, id desc)
    create unique index ix_sl_status on dbo.dds_intStatusLog (id) include (srid, status, explanation)
    On my laptop the execution takes less than second on 500 000 records.
    If this wouldn't help, then you should consider sunilmadan's advice.

Share This Page