Indexing Recommendation Tools for SQL Server

Question

Indexing Recommendation Tools for SQL Server

IT researcher

SSCertifiable

Points: 7600
More actions
October 24, 2024 at 11:58 am

#4473166

I'm looking for indexing recommendations for the entire SQL Server database, not just for individual queries.
I have come across tools like Microsoft SQL Server Profiler and Dynamic Management Views (DMVs), which only recommend query indexing.
https://www.sqlshack.com/a-great-tool-to-create-sql-server-indexes/
Are there tools that analyze the entire database structure, usage patterns, and workload to recommend indexing strategies?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

I'm going to advise some good caution here. There are a ton of tools, including but not limited to SQL Server's built-in "missing indexes" tool but you should never just up and apply them as designed because a lot of them don't actually work and some require damned near the same space as the original data. Many times, it actually IS better to change what the Clustered Index is (not necessarily the PK, which can be a non-clustered constraint).

Here's a decent bit of Microsoft Documentation on their tools...\

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/tune-nonclustered-missing-index-suggestions

If you search for one of the dmvs, as in the following search, you'll come up with a wealth of articles on the subject. Again, be cautious... the wrong kind of indexes can cause some pretty nasty slowdows... especially if you make the mistake of rebuilding or reorganizing them because of logical fragmentation.

https://duckduckgo.com/?q=sys.dm_db_missing_index_details

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I use sp_BlitzIndex which is part of Brent Ozar's Frist Responder Toolkit or you can use Glenn Berry's Missing Indexes by Index Advantage script. Both rely on the information in the missing index DMV's and you should have a minimum of a week of uptime before you really consider making any changes. And as Jeff has mentioned you need to be careful because all the tools really look at specific queries and not overall usage, they also often recommend similar indexes with just differences in included columns and often, include all columns in the table which is not normally a good index or indicates you have the wrong clustered index. I use those tools as a starting point for investigation and not as the truth. Many times the end result of an investigation is modifying an existing index instead of adding a new one.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

Jack Corbett wrote:

I use those tools as a starting point for investigation and not as the truth. Many times the end result of an investigation is modifying an existing index instead of adding a new one.

A million likes, Mr. Corbett!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)