Posted on 2013-10-10
Medium Priority
Last Modified: 2013-10-11
I would like to index my SQL db to make things run faster.  I configured it to index under maintenance plans.  Does that take care of explicitly indexing the db
Question by:al4629740
LVL 66

Expert Comment

by:Jim Horn
ID: 39563630
>I configured it to index under maintenance plans.
Define for us exactly what you did here.   Indexing is not one of my stronger suits, but afaik tables and sometimes views are indexed, databases are not.
LVL 32

Accepted Solution

Daniel Wilson earned 668 total points
ID: 39563792
The maintenance plan offers to rebuild the indexes you have defined.  This is good maintenance ... but does not define the indexes in the first place.
LVL 40

Assisted Solution

lcohan earned 668 total points
ID: 39563808
I suggest install and use SQL own Performance Dashboard reports (I run it on PROD systems with absolutely no issues and much trust) that has a Missing Index report:


Or http://technet.microsoft.com/en-us/library/ms345417(v=sql.105).aspx
Or use Brent Ozar's BlitzIndex stored proc: http://www.brentozar.com/first-aid/downloads/end-user-license-agreement/downloads/

OR you can run a query like below to find missing indexes in a SQL database:

select --top 10000
      object_name(d.object_id) tablename, gs.unique_compiles, gs.user_seeks, gs.avg_user_impact,
       d.equality_columns, d.inequality_columns, d.included_columns, d.statement as fully_qualified_object--, gs.*
from  sys.dm_db_missing_index_groups g
       join sys.dm_db_missing_index_group_stats gs on gs.group_handle = g.index_group_handle
       join sys.dm_db_missing_index_details d on g.index_handle = d.index_handle
where  d.database_id =  d.database_id and d.object_id =  d.object_id
            and gs.avg_user_impact > 90
order by gs.avg_user_impact desc, gs.unique_compiles desc, gs.user_seeks desc
order by gs.avg_user_impact desc

--then locate the appropriate CREATE INDEX statement from query below:
  mig.index_group_handle, mid.index_handle,
  CONVERT (decimal (28,1),
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
  ) AS improvement_measure,
  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig with (nolock)
INNER JOIN sys.dm_db_missing_index_group_stats migs with (nolock) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid with (nolock) ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
and mid.statement like '%Schedule%'
--ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

      Other useful code below

--top ten missing indexes from SQL Books on Line
FROM sys.dm_db_missing_index_group_stats with (nolock)
where avg_user_impact > 90
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

LVL 49

Assisted Solution

PortletPaul earned 664 total points
ID: 39564920
as an alternate to Brent Ozar's BlitzIndex, this suite of utilities is also highly regarded:

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance
The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold and Silver winner in the 2012, 2011, and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free.
Ola Hallengren
and a recommended video

I recommend the video if considering this suite as Ola is presenting and answering - I found it very informative.

also refer to

Author Comment

ID: 39565540
So if I define index the speed of my queries should go radically up?

Author Comment

ID: 39565662
I have SQL 2012 btw

Author Comment

ID: 39565705
Is this the same thing if I produce a columnstore index
LVL 66

Expert Comment

by:Jim Horn
ID: 39565732
al4629740 - Just so you know, you're asking a lot of beginner questions, and not that there's anything wrong with that, but when put them together it's larger than the scope of a typical EE question.  

You might want to consider breaking these out into separate questions.

Also, you can probably google search a lot of this stuff, so this question doesn't start resembling an endless meander through everything about indexes.

Hope you understand.

Author Comment

ID: 39565944
Ok thanks for the advice

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

627 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