October 9, 2014 at 7:17 am
Koen Verbeeck (10/9/2014)
Jason Shadonix (10/9/2014)
And look at the disk space the table is using before and after the index. Especially if you do a lot of included columns in that index.It's a clustered index, it can't have included columns. 😉
And now I remember why I never answer posts before 6AM when I'm not fully awake yet. 🙂
The Redneck DBA
October 9, 2014 at 7:26 am
If we create composite clustered index on all columns does it effect any thing ??
when i run the query it always taking non clustered index scan on execution plan when i check its not taking clustered index ... why so any idea???
thanks
October 9, 2014 at 7:31 am
santoshkal (10/9/2014)
If we create composite clustered index on all columns does it effect any thing ??when i run the query it always taking non clustered index scan on execution plan when i check its not taking clustered index ... why so any idea???
thanks
Maybe because selecting from the nonclustered index is more efficient?
Who knows? Who haven't seen any table/index DDL, we haven't seen any query and no execution plans as well.
If you so desperately want the query to use the clustered index, why do you have a nonclustered index at all?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 9, 2014 at 7:32 am
santoshkal (10/9/2014)
If we create composite clustered index on all columns does it effect any thing ??
Yes, it affects lots of things.
when i run the query it always taking non clustered index scan on execution plan when i check its not taking clustered index ... why so any idea???
No idea, can't see the query, can't intuit the execution plan. Probably because the clustered index isn't useful to the query.
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
October 9, 2014 at 8:18 am
santoshkal (10/9/2014)
If we create composite clustered index on all columns does it effect any thing ??when i run the query it always taking non clustered index scan on execution plan when i check its not taking clustered index ... why so any idea???
thanks
A composite key affects a lot of things. First off, the size of the index will be bigger. You'll have fewer entries per page both on the b-tree pages and on the leaf pages. This can make scans slower, possibly lead to more page splits and other things. Your density graph for the statistics of the compound key will be much higher than for a single key index since a compound key is much more selective.
If it's scanning, it means that either you don't have a WHERE clause at all, or, the WHERE clause you have is doing something to prevent efficient index and statistic use, or, your index or statistics are not selective enough to satisfy the WHERE clause, or, the scan of the non-clustered index is faster than scanning the clustered index because the non-clustered index is smaller. Since we have never seen the queries you're running or the definitions of the other indexes, I'd be guessing to suggest which of these it might be.
You're all over the map on this one. Take a step back. Slow down. One step at a time. Get your existing structure and the query you're seeking to tune. Understand why the query is behaving as it is before you start trying to rearrange indexes. Once you understand why you're seeing the behavior you're seeing, you can then start to make informed choices on how to modify that behavior, whether it's adding or modifying an index, updating statistics, or, most likely, changing your T-SQL query.
"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
October 10, 2014 at 3:18 am
so can u suggest me to create indexes on that table to improve performance
October 10, 2014 at 3:22 am
santoshkal (10/10/2014)
so can u suggest me to create indexes on that table to improve performance
How are we supposed to do that when we don't know the table schema and what queries are issued against this table???
We cannot see your computer screen, we cannot read your mind and we do not have a crystal ball (it's still missing).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 10, 2014 at 5:03 am
Best suggestion, you take it a step at a time. Be structural in your approach. Understand what's happening and why, then make adjustments to change the behaviors you don't like. Best suggestion, get my books and read them. Query tuning and index choice is a very big topic.
"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 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply