How to create index as suggested by execution plan.

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>
LVL 20
chaitu chaituAsked:
Who is Participating?
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
chaitu chaituAuthor Commented:
like this

CREATE NONCLUSTERED INDEX [ix_USER_LOGS_TIME]
 ON [USER_LOGS] ([TIME])
INCLUDE ([DOCUMENT_ID])
GO
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
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.

All Courses

From novice to tech pro — start learning today.