?
Solved

Best practices for creating indexes in MS SQLSERVER

Posted on 2016-08-09
3
Medium Priority
?
35 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 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.
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 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
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 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