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!
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.
Hi... yes, a clustered index on ID is actually what was originally implemented when the system was delivered to us. Thanks though.
Follow the blog posts below to analyze where it is lacking the performance: ToolsTotroubleshoot CacheTopSP statsoptimizer Cachingplans Rowcount
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.
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.