jeudi 26 février 2015

What is the best approach to index user identifiers?


This is an index tuning question for the DBA experts. I'm just a lonely developer.


I have a few large tables that are clustered on date and time. This is because most of my queries are primarily filtered based on time first and other fields second and so forth. The approach works well, but now some of my queries are changing towards filtering users more and more.


I try to stay away from a lot of indexes. They are costly. But, I wanted to investigate the possibility of indexing users. Are there any benefits to clustering a index based on datetime and numeric based user identifiers (2 column index) when majority of the queries used to read data are based on time first and then user second? Or keep what I have and non-cluster on users?



CREATE CLUSTERED INDEX [IX_TestIndex] ON [dbo].[test_table]
(
[Date] ASC,
[UserID] 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) ON [PRIMARY]
GO




Aucun commentaire:

Enregistrer un commentaire