Estimated Execution Plan & Missing Indexes

Question

Post reply

Estimated Execution Plan & Missing Indexes

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

dpedrelli SSChasing Mays Points: 633 More actions · Answer 1

Here is what I get for selectivity:

0.0000094580600 ROI_STAGE

0.9689775599620 QC_DATETIME_STARTED

0.0000031526860 INVOICE_STATUS_CODE

Here is my current statement, called in a single query session, with a single execution. You will notice that I do have an index hint. I still get the index listed as missing.

drop table #missingindexes

select

db_name(ddmid.database_id) as databasename,

object_name(ddmid.object_id,ddmid.database_id) as TableName,

ddmid.equality_columns,

ddmid.inequality_columns,

ddmid.statement,

ddmid.included_columns,

ddmigs.avg_total_user_cost,ddmigs.avg_user_impact,ddmigs.user_seeks,ddmigs.user_scans,

ddmigs.last_user_scan,ddmigs.last_user_seek,

ddmigs.unique_compiles

into #missingindexes

from

sys.dm_db_missing_index_group_stats ddmigs

inner join sys.dm_db_missing_index_groups ddmig

on ddmigs.group_handle = ddmig.index_group_handle

inner join sys.dm_db_missing_index_details ddmid

on ddmig.index_handle = ddmid.index_handle

SELECT

PK

,FK_USER_QC

,FK_USER_QC_RESERVED

,IS_RUSH_ORDER

,DATETIME_UPLOADED

FROM

tbl_ROI_CATALOG

WITH (INDEX(idx_ROI_QC_ASSIGN_USER_1))

WHERE

ROI_STAGE = 7 -- dbo.udf_ROI_STAGE_QC()

AND QC_DATETIME_STARTED IS NULL

AND INVOICE_STATUS_CODE = 'M'

select

db_name(ddmid.database_id) as databasename,

object_name(ddmid.object_id,ddmid.database_id) as TableName,

ddmid.equality_columns,

ddmid.inequality_columns,

ddmid.statement,

ddmid.included_columns,

ddmigs.avg_total_user_cost,ddmigs.avg_user_impact,ddmigs.user_seeks,ddmigs.user_scans,

ddmigs.last_user_scan,ddmigs.last_user_seek,

ddmigs.unique_compiles

from

sys.dm_db_missing_index_group_stats ddmigs

inner join sys.dm_db_missing_index_groups ddmig

on ddmigs.group_handle = ddmig.index_group_handle

inner join sys.dm_db_missing_index_details ddmid

on ddmig.index_handle = ddmid.index_handle

except

select * from #missingindexes

Gullimeel Hall of Fame Points: 3871 More actions · Answer 2

Looks like that second column is should be the first column of index.

QC_DATETIME_STARTED followed by ROI_STAGE,INVOICE_STATUS_CODE.

Does esimate plan mention any of the columns as included columns? Can you post the estimated plan? first without forcing the new index and by forcing the index...

You can see the cost of both and from there you can be sure why your index is not chosen? If it is not chosen then it must have suggestion for included columns?

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

dpedrelli SSChasing Mays Points: 633 More actions · Answer 3

Hmmm...

I commented-out the index hint and ran the execution plan. It does not say that there are any missing indexes.

SELECT

PK

,FK_USER_QC

,FK_USER_QC_RESERVED

,IS_RUSH_ORDER

,DATETIME_UPLOADED

FROM

tbl_ROI_CATALOG

-- WITH (INDEX(idx_ROI_QC_ASSIGN_USER_1))

WHERE

ROI_STAGE = 7 -- dbo.udf_ROI_STAGE_QC()

AND QC_DATETIME_STARTED IS NULL

AND INVOICE_STATUS_CODE = 'M'

I removed the commented line and ran the execution plan and it lists the index as missing.

SELECT

PK

,FK_USER_QC

,FK_USER_QC_RESERVED

,IS_RUSH_ORDER

,DATETIME_UPLOADED

FROM

tbl_ROI_CATALOG

WHERE

ROI_STAGE = 7 -- dbo.udf_ROI_STAGE_QC()

AND QC_DATETIME_STARTED IS NULL

AND INVOICE_STATUS_CODE = 'M'

Whether I have the index hint or not, your missing_indexes table still lists the index as missing. My first line drops that table. So, it is recreated every time I execute the code.

Here is the execution plan as XML, without the index hint.

<?xml version="1.0" encoding="utf-16"?>

<Batch>

</ColumnGroup>

</MissingIndex>

</MissingIndexGroup>

</MissingIndexes>

</OutputList>

</OuterReferences>

</OutputList>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</ScalarOperator>

</ScalarOperator>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</OutputList>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

dpedrelli SSChasing Mays Points: 633 More actions · Answer 4

I ran a trace and executed the query and ran that through DTA. The closest index that I found in the results are this. I will try this index and see what happens. I just find it strange that SQL keeps recommending an index that already exists.

CREATE NONCLUSTERED INDEX [_dta_index_tbl_ROI_CATALOG_5_1456268493__K49_K102_K162_K1_10_27_91_204] ON [dbo].[tbl_ROI_CATALOG]

(

[ROI_STAGE] ASC,

[QC_DATETIME_STARTED] ASC,

[INVOICE_STATUS_CODE] ASC,

[PK] ASC

)

INCLUDE ( [DATETIME_UPLOADED],

[IS_RUSH_ORDER],

[FK_USER_QC],

[FK_USER_QC_RESERVED]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Save you execution plan as a .sqlplan file and attach that to your post insterad of inserting the XML version in your post. Sorry, but reading the XML is just difficult.

dpedrelli SSChasing Mays Points: 633 More actions · Answer 6

I created the index that I got from DTA and execute my query, without the index hint. No missing query. I ran your code. No missing query. I displayed the execution plan for a stored procedure that uses my original query and not missing query. SQL must be miss identifying what it wants for indexes.

dpedrelli SSChasing Mays Points: 633 More actions · Answer 7

I dropped the index that I just created and saved the execution plan. However, that above index seems to be the issue. The SQL execution plan just gives me the wrong index columns.

Gullimeel Hall of Fame Points: 3871 More actions · Answer 8

Are you just querying the #missingindexes? You have to execute the final full query.

select

db_name(ddmid.database_id) as databasename,

object_name(ddmid.object_id,ddmid.database_id) as TableName,

ddmid.equality_columns,

ddmid.inequality_columns,

ddmid.statement,

ddmid.included_columns,

ddmigs.avg_total_user_cost,ddmigs.avg_user_impact,ddmigs.user_seeks,ddmigs.user_scans,

ddmigs.last_user_scan,ddmigs.last_user_seek,

ddmigs.unique_compiles

from

sys.dm_db_missing_index_group_stats ddmigs

inner join sys.dm_db_missing_index_groups ddmig

on ddmigs.group_handle = ddmig.index_group_handle

inner join sys.dm_db_missing_index_details ddmid

on ddmig.index_handle = ddmid.index_handle

except

select * from #missingindexes

go

BTW, i could not open the query plan sent by you...

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

rojipt SSC Enthusiast Points: 131 More actions · Answer 9

The DTA recommended index is way too liberal. I think that you have the correct index in place. If your predicates are going to be fixed, consider whether you can use Filtered Index.

You may be seeing this bug.

https://connect.microsoft.com/SQLServer/feedback/details/400578/query-plan-missing-index-recommendation-doesnt-check-if-an-index-actually-exists

Rojipt

dpedrelli SSChasing Mays Points: 633 More actions · Answer 10

If you look at my earlier post, I executed all of the statements as a single execution.

The first thing is to drop the table.

Then, I call the select statement that creates table.

I execute my query.

Then, I execute the remainder of your code.

I downloaded the SQL Plan that I posted. I opened it with a text editor and I can see that it is an XML file. If I change it to XML, it does not display. SQL must have created an XML file with an invalid tag.

dpedrelli SSChasing Mays Points: 633 More actions · Answer 11

I just double-clicked the SQL plan and it opened in the query analyzer. Not sure what problem you are having.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 12

you need to include the columns mentioned in the key column for the above execution plan into the non clsutered index for the inner table. You have the right index its simply a matter of adding the include columns to it to avoide the lookup.

Jayanth Kurup[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

I will let others who have been following along here continue to help you with this. But I have a HUGE CAUTION for you: BEWARE doing tuning by running each of your queries and creating all of the recommended indexes to make things "faster". DISASTER awaits because you will wind up with WAY too many indexes and your insert/update/delete activity, statistics update activity, index defrag activity, disk size, backup size, concurrency, etc will all start to SUCK - some of those things REALLY PAINFULLY so. You need WAY more tricks in your bag to tune queries than just creating missing indexes.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gullimeel Hall of Fame Points: 3871 More actions · Answer 14

I will let others who have been following along here continue to help you with this. But I have a HUGE CAUTION for you: BEWARE doing tuning by running each of your queries and creating all of the recommended indexes to make things "faster". DISASTER awaits because you will wind up with WAY too many indexes and your insert/update/delete activity, statistics update activity, index defrag activity, disk size, backup size, concurrency, etc will all start to SUCK - some of those things REALLY PAINFULLY so. You need WAY more tricks in your bag to tune queries than just creating missing indexes.

He has been warned already but i guess in little bit less words..After reading your response surely he will understand it better 🙂

But be careful and do not simply create each and every index suggested by this.
Before creating any index you need to make sure what will be the impcat on dml statements. You have to take into account how many queries will be benfit by this index and what is the overhead of maintaing this index (this includes space as well as updating/insertiing/deleting indexes).
Edited: 5/21/2012 6:17:30 PM by Gullimeel

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

dpedrelli SSChasing Mays Points: 633 More actions · Answer 15

I was just trying to tune two or three queries that are run thousands of times per day and I knew had issues. There are procedures that are not run very often and some are run during off hours. So, I am not that concerned about them.

Thanks,