May 21, 2012 at 10:08 am
Hi,
I am trying to rewrite queries, for better performance. I rewrote one and displayed the execution plan. It gave me a missing index. I created the index and displayed the plan again. It gave me another missing index, which I created. I displayed the plan again and had no missing indexes.
Today, I did the same thing with another query. I displayed the execution plan and go a missing index. I created the index, but when I display the plan, I still get the same index is missing.
I went back to my first query and displayed the execution plan. It gave me another missing query, which I created. However, no matter how many times I create the index and display the plan, it still tells me that the index that I created is missing.
Thanks,
David
May 21, 2012 at 11:13 am
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
First run the above query query.Then run the query you want to tune.
then run below 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
This will give you the missing indexes on your query. However, note that if at the same time someone else run any other query and if there are some missing indexes on those queries.Then those will be shown in this as well.
But i am sure you will be able to find the missing indexes for your query. Column avg_user_impcat will provide you how much improvement in query execuetion will you gain when you implement that index.
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).
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 21, 2012 at 11:40 am
Thanks.
I ran those statements.
The result listed the following columns, which are the same columns that the execution plan listed:
[ROI_STAGE], [QC_DATETIME_STARTED], [INVOICE_STATUS_CODE]
I have created the following index, several times:
CREATE NONCLUSTERED INDEX idx_ROI_QC_ASSIGN_USER_1 ON tbl_ROI_CATALOG (ROI_STAGE, QC_DATETIME_STARTED, INVOICE_STATUS_CODE) WITH DROP_EXISTING
If I go to the table properties, it is listed as an index. If I run your code or the execution plan again, I still get the above index as missing.
May 21, 2012 at 11:47 am
drop table #missingindexes
go
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
go
--run the query which is missing indexes
select * from Sales.SalesOrderHeader soh where soh.BillToAddressID = '123'
go
--run the query to find the missing indexes
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
--create the index
create nonclustered index idx1 on sales.salesorderheader(BillToAddressID)
go
--run the query again which was missing index
select * from Sales.SalesOrderHeader soh where soh.BillToAddressID = '123'
go
--now again run the query you ran to find missing indexes..This should return 0 rows..
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
I tried and I did not get the missing index row after creating the index. Did it shows any column as included column? Can you post the query?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 21, 2012 at 11:59 am
Originally, I confirmed that it is in SSMS, under the table properties. The column names and order matches what is given in the "missing index" statement. I just checked both sysindexes and sys.indexes and it is listed in both places.
In fact, I just ran the below statement and get that index listed as missing:
Returned as missing by select * from #missingindexes
Table
tbl_ROI_CATALOG[
Columns
ROI_STAGE], [QC_DATETIME_STARTED], [INVOICE_STATUS_CODE]
drop table #missingindexes
CREATE NONCLUSTERED INDEX idx_ROI_QC_ASSIGN_USER_1 ON tbl_ROI_CATALOG (ROI_STAGE, QC_DATETIME_STARTED, INVOICE_STATUS_CODE) WITH DROP_EXISTING
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
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
May 21, 2012 at 12:03 pm
Can you put the output of this 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
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 21, 2012 at 12:09 pm
Database DSS_Legacy
Table - tbl_ROI_CATALOG
Equality - [ROI_STAGE], [QC_DATETIME_STARTED], [INVOICE_STATUS_CODE]
Inequality - NULL
Statement - [DSS_Legacy].[dbo].[tbl_ROI_CATALOG]
Included Columns - NULL
Avg Cost - 10.908600269265
Avg Impact - 99.96
Seeks - 2
Scans - 0
Last Scan - NULL
Last Seek - 2012-05-21 14:07:13.687
Compiles - 3
May 21, 2012 at 12:23 pm
Since your seeks and scans are low , then maybe you don't have the right order when you create the index.
Granted this is only one idea.
[ROI_STAGE], [QC_DATETIME_STARTED], [INVOICE_STATUS_CODE]
Maybe it should be
[QC_DATETIME_STARTED], [INVOICE_STATUS_CODE],[ROI_STAGE]
OR
[INVOICE_STATUS_CODE],[ROI_STAGE],[QC_DATETIME_STARTED]
It all depends on how the columns are accessed but it could also be your statistics,procedure cache.. so on and so on.
May 21, 2012 at 12:23 pm
Did you run it? or just checking the estimated execution plan? Try executing the query in separate window and run it with actual execution plan..If this gives you a missing index.(which it should not)..Then try to force the index and execute it...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'
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 21, 2012 at 12:25 pm
May 21, 2012 at 12:26 pm
With your code, I ran the query, and not just the execution plan. The order of the columns was based on what the execution plan told me, but it matches what I get from the missing_indexes table. I will try some different orders and see what I get.
May 21, 2012 at 12:35 pm
I have been running my select statement in-line with they other statements. I have actually been running it and not just the execution plan. I tried all six possible column orders and every time I get the index is missing with the following column order
[ROI_STAGE], [QC_DATETIME_STARTED], [INVOICE_STATUS_CODE]
May 21, 2012 at 12:35 pm
Ususally the order specified from missing index dmv's the best one.But still to be more caustious check the selectivity of these columns.
run the following.It will giev you the selectivity. Columns in index should generally be with high value first and so on..
select COUNT(distinct ROI_STAGE)*1.0/COUNT(*)*1.0 ,COUNT(distinct QC_DATETIME_STARTED )*1.0/COUNT(*)*1.0,
COUNT(distinct INVOICE_STATUS_CODE )*1.0/COUNT(*)*1.0,
from tbl_ROI_CATALOG
Did you run the query by forcing index hint ?
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 21, 2012 at 12:36 pm
I captured a trace file and I have tried running it through the DTA, but it keeps failing. I have been trying to capture a new trace file, but it bogs the system to the point that database queries timeout, and people cannot work. I do not have a second SQL server to use for that purpose. I am trying to make some individual improvements and then try DTA again.
May 21, 2012 at 12:41 pm
You can run the dta with just the query you want to tune..But yes,dta will consume resources so you should be careful while running it.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply