March 21, 2016 at 5:38 pm
WHen you look at query plans or query DMVs for missing indexes,
it is an obvious practice not to implement them all. What is one most important criteria
is indicative of which index we should and which ones we should not implement in prod?
Likes to play Chess
March 21, 2016 at 8:04 pm
VoldemarG (3/21/2016)
WHen you look at query plans or query DMVs for missing indexes,it is an obvious practice not to implement them all. What is one most important criteria
is indicative of which index we should and which ones we should not implement in prod?
The short answer, and I'm really not trying to be a sarcastic, is: the index that gives you the most bang for your buck. You're not going to figure that out by looking at missing indexes alone. You need to also know which queries run most often, which ones are the slowest and how much the missing index will improve performance by testing it.
It's also worth noting that the index you need may not exist in your DMVs at all. Take the following 2 "missing indexes" for example:
CREATE NONCLUSTERED INDEX xxx ON yourtable(c1, c2) INCLUDE (c4);
CREATE NONCLUSTERED INDEX xxx ON yourtable(c1) INCLUDE (c4, c5);
The best index might be actually be:
CREATE NONCLUSTERED INDEX xxx ON yourtable(c1, c2) INCLUDE (c4, c5);
-- Itzik Ben-Gan 2001
March 22, 2016 at 4:53 am
There's no good way to know that at all. It's why I'm not that crazy about the missing index report as anything other than some suggestions, and not necessarily good suggestions. It doesn't show you which query would benefit, how often that query is called, to what degree it's going to benefit (don't trust the cost estimates), anything useful for making good decisions.
A better approach is use the sys.dm_exec_query_stats or sys.dm_exec_procedure_stats DMVs in combination with sys.dm_exec_query_plan. You can then gather some metrics on the queries that are currently in cache to order by the most frequently called or the longest running, or the ones using the most I/O. With that, you can then run an XPath query against the execution plan itself to see if there is missing index information for that query. Then you have some assurance that the index will have a positive impact. However, even there, I would test it to be sure.
Here's an example query [/url]that might help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 22, 2016 at 1:12 pm
In addition to the answers you already have, I would suggest also looking at existing indexes. Building on Alan's example, let's say that you find an existing index on (c2, c1) with no included columns. I would investigate how that index is used.
If it is only used for queries that would benefit from an index on (c1, c2) just as well, then I would be even more tempted to create the index suggested by Alan and then drop this existing index - because I have found a way to help a few queries without significantly increasing the total index overhead, and without reducing performance of existing queries.
If it is mostly used for such queries but very infrequently for queries that really need the column order to be (c2, c1), then I might still consider building the new index as a replacement and sacrificing the performance of those few queries for the improved performance of other queries.
If it is often used for queries that require the order to be (c2, c1), or for a few but they are very important, then I would not drop it. I would still consider the suggestion Alan makes, but only if the performance gain outweighs the overhead I introduce by adding an extra index.
March 22, 2016 at 6:16 pm
The only benefit from using missing index DMV is that it might show you the query that might have some performance issues. Then you need to look at query plan, how often it runs and how much resources it consumes, and then decide what index you want to create.
In order to find a query by a missing index, just put your index_handle id from sys.dm_db_missing_index_details into the following script. It will try to find a query in cache without actually parsing all the cache. Obviously it will work only if plan is still in the cache.
/* Generated in SQL Explorer v.1.6.3.17678 */
DECLARE @index_handle INT
SET @index_handle = ###
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000
DECLARE @missing_index_search_string nvarchar(max)
DECLARE @search_start_date DATETIME
DECLARE @search_end_date DATETIME
SELECT
@missing_index_search_string = '%<MissingIndex ' +
+ 'Database="\' + QUOTENAME(DB_NAME(database_id)) + '" ' +
+ 'Schema="\' + QUOTENAME(object_schema_name(object_id, database_id)) + '" ' +
+ 'Table="\' + QUOTENAME(object_name(object_id, database_id)) + '">'
+ ISNULL('%<ColumnGroup Usage=\"EQUALITY\">%' + REPLACE(REPLACE(equality_columns, ', ', '%'), '[', '<Column Name="\['), '')
+ ISNULL('%<ColumnGroup Usage=\"INEQUALITY\">%' + REPLACE(REPLACE(inequality_columns, ', ', '%'), '[', '<Column Name="\['), '')
+ ISNULL('%<ColumnGroup Usage=\"INCLUDE\">%' + REPLACE(REPLACE(included_columns, ', ', '%'), '[', '<Column Name="\['), '')
+ '%</MissingIndex>%'
FROM sys.dm_db_missing_index_details
WHERE index_handle = @index_handle
SELECT TOP 1
@search_start_date = migs.last_user_seek,
@search_end_date = DATEADD(SECOND, 1, migs.last_user_seek)
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs ON migs.group_handle = mig.index_group_handle
WHERE mig.index_handle = @index_handle
SELECT
DatabaseName = DB_NAME(epa.dbid),
ObjectName = DB_NAME(qt.[dbid]) + '.' + OBJECT_SCHEMA_NAME(qt.objectid, qt.[dbid]) + '.' + OBJECT_NAME(qt.objectid, qt.[dbid]),
sql_text = substring(qt.text, (statement_start_offset/2)+1 , case when statement_end_offset IN(0,-1) then 2147483647 else ((statement_end_offset - statement_start_offset)/2) + 1 end),
qp.query_plan,
qs.last_execution_time,
exec_count = qs.execution_count,
CAST(ISNULL(1.0*execution_count/NULLIF(DATEDIFF(second, creation_time, GETDATE())/60., 0), 0) AS DECIMAL(20, 2)) AS [exec/min],
total_time_ms = CAST(qs.total_elapsed_time/1000. AS DECIMAL(20,2)),
avg_time_ms = CAST(qs.total_elapsed_time/qs.execution_count/1000. AS DECIMAL(20,2)),
total_cpu_ms = CAST(qs.total_worker_time/1000. AS DECIMAL(20,2)),
avg_cpu_ms = CAST(qs.total_worker_time/qs.execution_count/1000. AS DECIMAL(20,2)),
total_logical_reads = qs.total_logical_reads,
avg_logical_reads = CAST(1.0*qs.last_logical_reads/qs.execution_count AS DECIMAL(20,2)),
total_physical_reads = qs.total_physical_reads,
avg_physical_reads = CAST(1.0*qs.total_physical_reads/qs.execution_count AS DECIMAL(20,2)),
qs.plan_handle,
start_offset = qs.statement_start_offset,
end_offset = qs.statement_end_offset
FROM (SELECT 1 AS PH) PredicatePushDown
INNER JOIN sys.dm_exec_query_stats qs
ON (qs.last_execution_time BETWEEN @search_start_date AND @search_end_date)
OR (qs.last_execution_time >= DATEADD(SECOND, -3, GETDATE()))
OUTER APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) tqp
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
OUTER APPLY (SELECT TOP 1 CONVERT(smallint, value) AS dbid
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute IN ('dbid', 'dbid_execute') and value <> 32767
ORDER BY attribute) AS epa
WHERE tqp.query_plan LIKE @missing_index_search_string ESCAPE '\'
March 22, 2016 at 6:32 pm
That's exactly what my final goal is, to find either SQL or better SP or FN names that would benefit from each missing index.
Also, a certain % of queries will not be in cash, sometimes fluctuating to high %...
But even with using your metod, it means you need to go index by index? Not all at once as a result of 1 or 2 statements run? The closest I find are complex queries that start with WITH XML NAMESPACE...
and they take forever and huge amount of server resources (it is a 1+ T database..).
That is where i got stuck. Need to see Index DDL, then Object_To_Benefit, and/or SQL to benefit.
That appears impossible..
Likes to play Chess
March 23, 2016 at 4:31 am
It actually kind of is impossible. The missing indexes are, at best, suggestions. You can't simply blindly apply them. Further, if you look through them, there's a lot of duplication in the suggestions, so applying them all wouldn't simply be less than helpful, it could be outright harmful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2016 at 5:39 am
Well, this is exactly why I am looking to find out what SQL (and preferrably Calling_Object_Name (SP or FN...) that is associated with each missing indexes, so that we look through the calling SQL (that would benefit from each missing index), so that out of 200 suggested indexes we pick like 20 to create , that would be most benefitial/
Attached is the screenshot of the best result i got so far (can see SQL statements in second column (Doc attached) . this is the best i got so far.
but looking if it is possible to add just one more column: Calling_Object_Name
Likes to play Chess
March 23, 2016 at 5:53 am
Your best bet would be to offload the XQuery part of the work. Instead of trying to run the XPath stuff, which will do what you want, on your production system, export the existing cache to another system, then run the XPath query against that export. You'll get exactly what you want without having to do it against the active production server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2016 at 7:18 am
How exactly is it done? I mean, exporting the existing cache to another system ? (I have not found anything on www on that..).
Your help will be really appreciated because our prod db = > larger than 1 T, this xpath qry not only takes 40 minutes to run but also it becomes the heaviest process on the server, gets heavily blocked by the other spids, etc.
THANK YOU !
Likes to play Chess
March 23, 2016 at 7:26 am
Nothing special. Treat the cache like a table. Run a query to export through BCP. Import that to another database as a table. Done. No special tricks, not moving memory to memory or anything crazy. It's just data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2016 at 8:13 am
You mean to completely restore backups on an off-load server ? (Dev.. )
Likes to play Chess
March 23, 2016 at 8:18 am
You mean the complete backup and restore the Db on another (offload, Dev) server ?
Likes to play Chess
March 23, 2016 at 8:19 am
Got it ! THANK YOU !
Likes to play Chess
March 23, 2016 at 8:30 am
Right. Not a backup. That wouldn't work. BCP a SELECT statement against the cache using the DMVs.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply