chaitu chaitu
asked on
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>
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
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>
ASKER
like this
CREATE NONCLUSTERED INDEX [ix_USER_LOGS_TIME]
ON [USER_LOGS] ([TIME])
INCLUDE ([DOCUMENT_ID])
GO
CREATE NONCLUSTERED INDEX [ix_USER_LOGS_TIME]
ON [USER_LOGS] ([TIME])
INCLUDE ([DOCUMENT_ID])
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window