February 4, 2015 at 2:59 am
Is there any possibility to trace, most used column in every table on the SQL server?
The intention is, a query which suggests some columns to be indexed; Based on the usage of the column by the queries that run on server
Thanks In Advance
February 4, 2015 at 3:03 am
No, and most used does not mean that it must be indexed. Indexing is a little more complex than that.
See the entries on my blog in the indexing category.
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
February 4, 2015 at 3:13 am
GilaMonster (2/4/2015)
No, and most used does not mean that it must be indexed. Indexing is a little more complex than that.See the entries on my blog in the indexing category.
Ok. I accept most used does not mean that it must be indexed. But chances are there, I thought it was the shortcut to find the column to be indexed.
February 4, 2015 at 4:02 am
No, most used has nothing to do with 'should be indexed'. What if the most used is always in a select and never in the where. Indexing is a little more complicated than 'that column is used, index it'. Please go and read through the indexing posts on my blog.
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
February 4, 2015 at 7:49 am
One source of suggestions (and please, note that word, suggestions) for columns that might need an index are the Missing Index DMVs. But, the problem with using these is two-fold.
1) Some of the suggestions are utter junk.
2) There's no way to tie the suggestions directly to a query.
So, while these can be used, first, you can't trust them. You absolutely must evaluate if what it's suggesting makes sense, because again, it could be utter garbage. And, you need a way to match the suggestions to specific queries. You don't want to put an index in place for a query that was run once and will never be run again. You want it to be for queries that are run often, so that the index will be used in a meaningful way. To do this, you can query the plans that are in cache to look at missing index information on the actual query. I have an example of this[/url] on my blog.
But, remember, these are just suggestions. Further, they're woefully incomplete. You need to put in place monitoring to identify the most frequently called queries and the longest running queries. Then you need to evaluate them by looking at the code and looking at the execution plans to determine if they need to be rewritten, or, if there are places where indexes can benefit the queries. Gail's blog is a great resource for this type of information. I also post a lot of information at my blog. You can also check out the books in my signature.
"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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply