How to check Performance ON Cluster Index on table

Question

How to check Performance ON Cluster Index on table

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 1

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

santoshkal SSC-Addicted Points: 437 More actions · Answer 2

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

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

santoshkal SSC-Addicted Points: 437 More actions · Answer 6

so can u suggest me to create indexes on that table to improve performance

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 7

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 8

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software