Solved

Best practices for creating indexes in MS SQLSERVER

Posted on 2016-08-09
3
28 Views
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?
0
Comment
Question by:szadroga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 20

Accepted Solution

by:
Russ Suter earned 250 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.
0
 

Author Comment

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

Assisted Solution

by:Éric Moreau
Éric Moreau earned 250 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
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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