Solved

How to create index as suggested by execution plan.

Posted on 2013-01-10
4
415 Views
Last Modified: 2013-01-14
Below inner select query taking lot of time and when i execute execution plan i got below suggestion.how to create below index as suggested by execution plan in sql server 2008 r2.

what is the syntax to create below and on which columns.

USER_LOGS
*********
DOCUMENT_ID,
TIME,
USERID,
USERTEXT

query
*****

SELECT MM.DOC_NUM,MM.TYPE
                   FROM
                   (
 SELECT  m.UOI_ID,m.DOC_NUM,m.ASSET_TYPE,
                   row_number() over(partition by l.DOCUMENT_ID order by TIME desc) RANK
                       FROM
                       [DOCUMENT_METADATA] m,
                         [USER_LOGS] l
                       where
                     m.META_ID=l.DOCUMENT_ID AND
                     m.DOC_NUM IS NOT NULL  AND
                    l.TIME > DATEADD (D, -1, GETDATE())            
)
 AS  IN1,[DOCUMENT_METADATA] MM
                       where
                       IN1.DOC_NUM=MM.DOC_NUM  
                         and RANK =1                   
                    
Missing Index Details from SQLQuery58.sql - not connected
The Query Processor estimates that implementing the following index could improve the query cost by 90.6913%.
*/

/*

GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [USER_LOGS] ([TIME])
INCLUDE ([DOCUMENT_ID])
GO
*/

 <MissingIndexes>
              <MissingIndexGroup Impact="90.6913">
                <MissingIndex Database="[XXXX]" Schema="[XX]" Table="[USER_LOGS]">
                  <ColumnGroup Usage="INEQUALITY">
                    <Column Name="[TIME]" ColumnId="5" />
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[DOCUMENT_ID]" ColumnId="10" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
0
Comment
Question by:chaitu chaitu
[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
  • 2
4 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 38766106
Quick answer, change [<Name of Missing Index, sysname,>] to a real index name like ix_USER_LOGS_TIME and run it.
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [USER_LOGS] ([TIME])
INCLUDE ([DOCUMENT_ID])
GO

Open in new window

0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 38766114
like this

CREATE NONCLUSTERED INDEX [ix_USER_LOGS_TIME]
 ON [USER_LOGS] ([TIME])
INCLUDE ([DOCUMENT_ID])
GO
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 250 total points
ID: 38766123
yes, that will  create the index recommended by the execution plan.  Just a note, do not just create all indexes even if they are "recommended" by MS.  First test performance before and then again after to make sure it really helps, sometimes it still does not.  Also, the evaluation by the execution plan is in isolation, not taking everything else your system may do in consideration, so it is up to you to make the final decission on if it is really the best thing for your overall system.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 38767901
Definitely don't automatically create all indexes the query plan "tells" you to.

However, in the this case the index looks small so I would create it.

In fact, it might be best to consider clustering the table on time, but that would require more investigation to determine properly.
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

Suggested Solutions

Title # Comments Views Activity
MS SQL Server select from Sub Table 14 49
How can I get the entire database script? 7 33
Finding Where Clause Value in SQL Views and SP 21 60
What does "Between" mean? 6 42
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
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…

739 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