sql server fields with most usage

Question

is there an sql query that can give me the fields that are used in most stored procedures or updated, selected most in a given table. I am asking this cause i want to figure out which fields to put indexes on. thanks

Answer 1

Wish it was that easy.... You need to do a search for SQL Query Optimization. There are lots of tools to use for what you need.

And knowing which fields are used often does not tell you much about what you need to do to optimize access.

Answer 2

Take a look at the missing indexes article on SQLServerPedia http://sqlserverpedia.com/wiki/Find_Missing_Indexes

Answer 3

You might have some luck by searching your code for all the queries and then running a SELECT EXPLAIN on them. This should give you some glaring numbers when a query is poorly indexed. Unfortunately it won't give you an idea of which statements are called most frequently.

Also - I've noticed some issues with type mismatching in queries. When you pass a string containing a number and it's querying an index based on integers then it will scan the entire table.

Answer 4

I think you are looking at the problem the wrong way around.

What you first need to identify are the most expensive (cumulative: so both single-run high cost, and many-runs lower cost) queries in your normal workload.

Once you have identified those queries, you can analyse their query plans and create appropriate indexes.

This SO Answer might be of use: How Can I Log and Find the Most Expensive Queries? (title and tags say SQL Server 2008, but my accepted answer applies to any version).

Answer 5

Most used fields are by no means index candidates. Good index candidates are those that correctly balance the extra storage requirements with SARGability and query projection coverage, as described in Index Design Basics. You should follow the advice the very engine is giving you, using the Missing Indexes feature:

• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_columns

A good action plan is to start from the most expensive queries by IO obtained from sys.dm_exec_query_stats and then open the plan of the query with sys.dm_exec_query_plan in Management Studio and at the top of the query plan view will be a proposed index, with the CREATE INDEX just ready to copy and paste into execution. In fact you don't even have to run the queries to find the most expensive query in the plan cache, there are already SSMS reports that can find it for you.