Solved

Best practices for creating indexes in MS SQLSERVER

Posted on 2016-08-09
3
31 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

696 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