Indexing Advice required
Here is the structure of the table and I am using SQL 2008 R2 EE
Create table Table1
I have around 300,000 rows in table with 154,000 datapages. 80% of time, table is read, 10% of time, new rows are added, remaining time it is updated.
The above table does not have an identity column or a column that uniquely defines a row.
Although nObjectType is not unique, here is distribution of values
objecttype no.of rows
Max length of tPropId column = 1993
Max length of tConditionId column = 293
Typical select on this table would be…
Select tSQLFilter From table1 With(Nolock)
Where tPropID = @nPropertyIDs and tCondition = @nConditions and nObjectType = @nObjectType
With the constraint that I cant change table structure in any way, here is my thought
Create clustered index PK_Table1_nObjectType
create nonclustered index IX_Table1_Condition_Includes
on Table1 (tCondition)
I decided to create a clustered index because I believe every table should have a clustered index. Because of datatypes, nObjecttype is only viable index
For non clustered index, although I wanted to include index on tPropId, because it can exceed 900 byte limitation on index, i cant use it in my index.
For tCondition column, although declared as 8000 bytes, the max it has ever taken is 600 bytes, so I am willing to take risk that it will not exceed.
since tSQLFilter is nvarchar(max), i have added this as a include column.
I have done couple of testing and it has significantly decreased page reads, but i would like to listen to your opinions and advise?
I wonder if compression would reduce the read effort for you in this case? Technically that is a table ALTER though, so I'm not sure if that's an option in this case? Compression would reduce the Disk IO for this table. I think given your column types you'd have to look at page compression.
- Huge data skewness in statistics, thus poor estimates while building execution plans, thereby bad plans
- Key lookups if exists would highly suffer since it has to now use the uniquifier
- Too much of fragmentation and page splits when you insert/update/delete data
- Range of X/IU locks on clustered index cascading to non clustered indexes. If range goes beyond 5000, there could be lock escalations
- there are many more…the list goes on…
Your clustered index key need not necessarily be part of your query. If nFilterid column has high selectivity, choose that as a clustered index or do consider composite key.