February 5, 2015 at 3:45 am
New Born DBA (2/4/2015)
ScottPletcher (2/4/2015)
The key thing for performance is the clustered index. Yes, you can instead create gazillions of nonclustered covering indexes, but that actually doesn't help overall performance much, and could even hurt it, possibly dramatically.I have no idea of whether C1000020525 is generally sequential or not. That is certainly best for a clustered index. But if you always report based on it, that could be the best clustered index regardless. We really should review the missing index and index usage stats at least to fill in more details about best indexing.
CREATE CLUSTERED INDEX [T657_INDEX_CL]
ON dbo.T657 ( C1000020525, C814000429 )
WITH ( FILLFACTOR = 95 ) --set proper fillfactor as needed
ON [PRIMARY] --set filegroup name on which the table is to be created
Edit: Added keyword "INDEX".
We already have a clustered index on the table.
It's not sequential. Look at the attachment plz
1 more thing.
This is the view, so should I create an index on the view or on the underlying table?
Remember, a view is not a table. In order to put an index on a view, you're creating what's called a materialized view. That would be creating a new object within the database. It would be a clustered index, I assume on the columns in question. That might be a good solution, but without seeing the execution plan and understanding the underlying structure, why we're using a view instead of just a query, etc., hard to say.
Probably, just guessing, it's better to put the index on the underlying table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply