August 14, 2008 at 4:21 am
Performance tuning is not one of my strongest points and I would just like some guidance on creating indices.
I have a SP that returns data used in a report. The SQL query has 3 WHERE clause conditions and returns 15 columns. I have run this query and viewed its Query Execution Plan and it is doing a clustered Index Scan. This table size will increase quick and I am worried that the query might become slow. I have tried creating a non clustered index and the 3 columns but it still does the CLustered Index Scan, I then added all the other column needed on the report into the Include Columns section and it then does a an Index seek.
Is this a good index or is adding so many columns into the Include Columns section not a good idea?
Or am I fine by the query just doing a Clustered Index Scan?
August 14, 2008 at 4:55 am
My guess, there are too many rows returned for the index seek + bookmark lookup to be efficient, so unless the index is covering, SQL elects to scan the cluster.
Check the time and reads of the seek and the time and reads for the clustered scan. That's what says which is good or bad. Sometimes a scan of the cluster is the best way, other times it is not.
15 columns is a little high for the number of included. If the report is critical and has to run fast, or the index is likely to be reused, you're probably OK. There aren't any hard or fast rules for this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2008 at 5:10 am
Thanks for your help, I will check the tme and reads! 🙂
August 14, 2008 at 5:18 am
Mark Beringer (8/14/2008)
Thanks for your help, I will check the tme and reads! 🙂
STATISTICS IO
and
STATISTICS TIME
are the best for this. (both SET options)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2008 at 6:47 am
Check the time and reads of the seek and the time and reads for the clustered scan.
When it's doing clustered scan How can we check the time and reads of the seek.
August 14, 2008 at 7:13 am
Paresh, you have to create the appropriate index that will cause a Index Seek. So First test without the Non clustered index which results in a Clustered Index scan, then create the Non Clustered Index and test check the results of the Index Seek.
After testing the STATISTICS IO & TIME I have found that the Clustered Index Scan has more reads but took less time to execute then the Index Seek.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
-- Clustered Index Scan
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Securities'. Scan count 1, logical reads 153, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ValidationExceptionLog'. Scan count 1, logical reads 345, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 149 ms.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 149 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
-- Non Clustered Index Seek
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Securities'. Scan count 1, logical reads 153, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ValidationExceptionLog'. Scan count 1, logical reads 121, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 156 ms.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 164 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
August 14, 2008 at 7:17 am
Thanks and sorry for not reading your original post completely.
August 14, 2008 at 8:33 am
Mark Beringer (8/14/2008)
After testing the STATISTICS IO & TIME I have found that the Clustered Index Scan has more reads but took less time to execute then the Index Seek.
The times look so close that the difference barely matters. How many columns are there total in this table?
What you may want to try is to add a lot more data to this table (in a dev environment of course), up to what you're expecting in the future and test the indexes out there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2008 at 8:38 am
In case it matters, make sure the columns that aren't part of the Where clause or Join clauses (if any) are only Included in the index, not part of the main body of the index. That will generally speed up index operations.
If you're not sure what I mean, check out Include under Create Index in Books Online.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2008 at 9:38 am
The tables has about 40 Columns, I have 14 reports that all expect the 3 WHERE clause conditions, the only difference are the columns that are returned, so I would defenatly need to include almost all the columns in the Index. I will load the table with more data and do some more tests.
GSquared, yes I have added the non condition columns as Include columns on the Index.
Thanks for everyones help!
August 14, 2008 at 9:50 am
Mark Beringer (8/14/2008)
The tables has about 40 Columns, I have 14 reports that all expect the 3 WHERE clause conditions, the only difference are the columns that are returned, so I would defenatly need to include almost all the columns in the Index. I will load the table with more data and do some more tests.
Urgh. Not a good idea. What's the other activity on this table? Inserts, updates, other selects?
Could you maybe post the table def and current indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2008 at 12:08 pm
If those three are used in every query, is it possible to set them as the clustered index? Since that's included in every non-clustered index, it might speed things up quite nicely, without having to have a ton of indexing on the table.
If that will mess with your inserts, it won't work, but do check that possibility.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 14, 2008 at 4:12 pm
I believe that if you have a clustered index on the table the execution plan will always have to use it at some point. Having a non-clustered index on the 3 columns in your WHERE clause could be beneficial when the table gets large if the column the clustered index is on is not in the WHERE clause of your queries. But the way SQL Server works, a non-clustered index has a reference to the clustered index, and the clustered index defines the physical storage of the table.
If the column the clustered index is on is not part of your WHERE clause, it may make sense to either not have a clustered index at all on this table or change what column(s) the clustered index is on.
August 15, 2008 at 2:15 am
The only transactions that happen on this table are inserts and the selects for the reports.
I have attached the table def, it has the Clustered Index which is on the Id of the table and I have added the Non Clustered Index which I am still trying to work out if it is suitable.
August 15, 2008 at 2:26 am
Considering that, I will recommend recreating the pk as a nonclustered index, and putting the clustered index on the three columns
[ValidationExceptionId]
[ValuationPoint]
[ValuationPointDate]
Since there are no queries using the PK to filter, the cluster there is a little wasted. If you're doing frequent inserts, watch the fragmentation of the index as it will increase faster than if the cluster was on the identity. I think, in this case, the tradeoff is worth it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply