Best practices for creating indexes in MS SQLSERVER

Posted on 2016-08-09
Medium Priority
Last Modified: 2016-08-09
I stood up a new database that has 5 tables and I am looking to create indexes on the table columns to increase performance.  When running SQL containing JOINs the perfomance is not great.

Are there obvious columns where indexes should be created?  Primary keys?  Columns used in JOINs?
Question by:szadroga
LVL 20

Accepted Solution

Russ Suter earned 1000 total points
ID: 41749171
Primary keys are always indexed. It can certainly help to add indexes to columns used in joins.

What I tend to do is use SQL Server Management Studio's built-in query analyzer. Just put the query into a new query window, right click, and select "Display Estimated Execution Plan" from the context menu.
SSMS Context MenuIt often provides good suggestions for performance enhancements.

You should also check that your queries aren't using functions around join columns. These tend to prevent SQL from properly discovering indexes.

Author Comment

ID: 41749178
So if I have created tables without primary keys, do you recommend i assign them first?
LVL 71

Assisted Solution

by:Éric Moreau
Éric Moreau earned 1000 total points
ID: 41749213
a table should (almost) always have a primary key. There are some tools that will not work if you don't have one (like Red Gate SQL Data Compare). A primary key should be as small as possible because it will be duplicated in all indexes.

You will also need indexes on the main columns used in JOINs. Have a look at your execution plan and search for the table scans

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question