Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Best practices for creating indexes in MS SQLSERVER

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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

610 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