Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So I have this table that is ever growing. Most queries are targeting just recent data, say one month old. I suppose this is common problem but I have no idea how it can be solved.

I am open to changing design or if there is mechanism in MsSql to solve this. I have limited options to try different solutions as database is in production and its hard to reproduce.

CREATE TABLE [dbo].[mydata](
[ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Code] [varchar](20) NOT NULL,   -- index1 UNIQUE NONCLUSTERED INDEX
[Data2] [varchar](20) NULL,      
[Data3] [nvarchar](50) NOT NULL,
... bunch of DATA around 5kb
[Time_1] [datetime] NULL,    -- time created, -- index2 NONCLUSTERED INDEX
[Time_2] [datetime] NULL,    -- time finished ( usualy within few days ) -- index3 NONCLUSTERED INDEX
[Status] [int] NOT NULL,     -- active 
[Modid] [timestamp] NOT NULL
)
share|improve this question
1  
Can you post the DDL of the table, as well as any accompanying indexes? Are you basically seeing scans on this table/CI? –  Thomas Stringer Apr 12 '13 at 19:42
1  
Can you add the table CREATE statement in the question (including indexes)? –  ypercube Apr 12 '13 at 19:42
1  
Have you thought about Filegroups and Data Partitioning ? mssqltips.com/sqlservertip/1200/… –  Kin Apr 12 '13 at 19:47
    
Also, if you could post your query(ies) that you're trying to optimize, that would be helpful. –  Thomas Stringer Apr 12 '13 at 19:49
    
How many rows does the table have in all? How many rows added on average per month? –  ypercube Apr 12 '13 at 20:05

1 Answer 1

up vote 5 down vote accepted

Time series should be clustered by time:

CREATE TABLE [dbo].[mydata](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] [varchar](20) NOT NULL,   -- index1 UNIQUE NONCLUSTERED INDEX
[Data2] [varchar](20) NULL,      
[Data3] [nvarchar](50) NOT NULL,
... bunch of DATA around 5kb
[Time_1] [datetime] NULL,    -- time created, 
[Time_2] [datetime] NULL,    -- time finished ( usualy within few days ) -- index3 NONCLUSTERED INDEX
[Status] [int] NOT NULL,     -- active 
[Modid] [timestamp] NOT NULL,
CONSTRAINT NONCLUSTERED PRIMARY KEY ([ID])
);

CREATE CLUSTERED INDEX cdxMyDataTime_1 on dbi.mydata (Time_1);

In time series data the time is almost always specified in queries, and usually as a range. With a clustered key based on the time range queries will scan only the relevant portion of the table.

The ID can continue to server the logical primary key role, but there is little benefit from having the table clustered by it since the ID is never used as a range. So off it goes into a non clustered constraint. Singleton lookups based on ID will need two reads, but who cares, is two fast reads.

If you cannot have the Time_1 as clustered key a frequent trick used is to retrieve the ID range for each day, eg. create table of days and min_ID/max_ID. Then use the ID range that covers the time range you're interested in to restrict the range of the scan on the table. This advantage of this approach is that it works for multiple time columns (you cannot cluster by Time_1 and Time_2...) and it is less invasive (can be tried out right away w/o modifying the table). But this approach is very invasive in the application query design, it requires discipline in remembering to use the ID ranges for the days desired. Note that since the IDs usually don't change, they can be cached in the app.

Simple indexes on Time_1 and Time_2 do not work because they hit the index tipping point. Covering index (with INCLUDE columns) on Time_1 and Time_2 explode the data size as often the included column required are ... all columns.

share|improve this answer
    
This is helpful. Would Filegroups and Data Partitioning further improve performance ? What are benefits and when should I consider partitioning data ? –  IvanP Apr 12 '13 at 22:56
    
Partitioning main benefit is fast switch in/switch out. Specially deleting old data after retention has expired is trivial with partition switch. But aligned partitions require important changes, like impossibility to have a primary key or unique constraint that does not include the paritioning key. Such constraints can only be unaligned and those block partition switch operations. –  Remus Rusanu Apr 13 '13 at 7:44
    
How To Decide if You Should Use Table Partitioning is a good resource. –  Remus Rusanu Apr 13 '13 at 7:45
    
Filtered indexes can be a good solution as well, though it depends on how the queries are written. –  StrayCatDBA Apr 13 '13 at 8:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.