July 16, 2018 at 9:37 am
Hi all
I'm trying to resolve a performance issue we are having with a stored proc - i've recently added a new index and this has brought down the runtime from 4 min to a few seconds. However, if I look at the index usage stats, I can see my index is there, but all the stats are NULL. Seems to indicate that my index is not being used, but i know it is because as soon as i drop the index, the query goes back to taking 4 min. Here is query i use to query dm_db_index_usage_stats:
SELECT
@@SERVERNAME AS [ServerName]
, DB_NAME() AS [DatabaseName]
, SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName]
, [sObj].[name] AS [ObjectName]
, CASE
WHEN [sObj].[type] = 'U' THEN 'Table'
WHEN [sObj].[type] = 'V' THEN 'View'
END AS [ObjectType]
, [sIdx].[index_id] AS [IndexID]
, ISNULL([sIdx].[name], 'N/A') AS [IndexName]
, CASE
WHEN [sIdx].[type] = 0 THEN 'Heap'
WHEN [sIdx].[type] = 1 THEN 'Clustered'
WHEN [sIdx].[type] = 2 THEN 'Nonclustered'
WHEN [sIdx].[type] = 3 THEN 'XML'
WHEN [sIdx].[type] = 4 THEN 'Spatial'
WHEN [sIdx].[type] = 5 THEN 'Reserved for future use'
WHEN [sIdx].[type] = 6 THEN 'Nonclustered columnstore index'
END AS [IndexType]
, [sdmvIUS].[user_seeks] AS [TotalUserSeeks]
, [sdmvIUS].[user_scans] AS [TotalUserScans]
, [sdmvIUS].[user_lookups] AS [TotalUserLookups]
, [sdmvIUS].[user_updates] AS [TotalUserUpdates]
, [sdmvIUS].[last_user_seek] AS [LastUserSeek]
, [sdmvIUS].[last_user_scan] AS [LastUserScan]
, [sdmvIUS].[last_user_lookup] AS [LastUserLookup]
, [sdmvIUS].[last_user_update] AS [LastUserUpdate]
, [sdmvIUS].[last_system_update] AS [LastSystemUpdate]
, [sdmfIOPS].[leaf_insert_count] AS [LeafLevelInsertCount]
, [sdmfIOPS].[leaf_update_count] AS [LeafLevelUpdateCount]
, [sdmfIOPS].[leaf_delete_count] AS [LeafLevelDeleteCount]
, [sdmvIUS].[system_seeks] AS [TotalSystemSeeks]
, [sdmvIUS].[system_scans] AS [TotalSystemScans]
FROM
[sys].[indexes] AS [sIdx]
INNER JOIN [sys].[objects] AS [sObj]
ON [sIdx].[object_id] = [sObj].[object_id]
LEFT JOIN [sys].[dm_db_index_usage_stats] AS [sdmvIUS]
ON [sIdx].[object_id] = [sdmvIUS].[object_id]
AND [sIdx].[index_id] = [sdmvIUS].[index_id]
AND [sdmvIUS].[database_id] = DB_ID()
LEFT JOIN [sys].[dm_db_index_operational_stats] (DB_ID(),NULL,NULL,NULL) AS [sdmfIOPS]
ON [sIdx].[object_id] = [sdmfIOPS].[object_id]
AND [sIdx].[index_id] = [sdmfIOPS].[index_id]
WHERE
[sObj].[type] IN ('U','V') -- Look in Tables & Views
--AND [sObj].[name] = '%%'
AND [sIdx].[name] LIKE 'idxNB%'
AND [sObj].[is_ms_shipped] = 0x0 -- Exclude System Generated Objects
AND [sIdx].[is_disabled] = 0x0 -- Exclude Disabled Indexes
My index (called 'idxNB...' appears in the list and has NYLL for TotalUserSeeks/Scans/Loohups/Updates and LastUserSeek/Scan/Lookup/Update.
It's bizarre and can't seem to find anything on google giving a reason for this.
Any insights?
Thanks
July 16, 2018 at 11:56 am
Have you looked at the actual execution plan of the slow query to see that the new index is being used? Building an index also adds new statistics to the table, which maybe the engine was able to determine a better plan without the index itself. The only way to be sure that the index is being used or not is looking at the execution plan.
July 17, 2018 at 7:18 am
Maybe it's your query. You have columns in the WHERE clause for a table in a LEFT JOIN. Move those out of the WHERE clause up to the JOIN criteria and see what data you get back.
However, I agree with Chris, check the execution plan to see how the index is being used.
"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
July 17, 2018 at 8:59 am
Hmmm... I wonder if it's simply because when you create an index, it also creates a new set of stats that go with it, which CAN be used by the optimizer even if the index is not.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2018 at 9:43 am
Jeff Moden - Tuesday, July 17, 2018 8:59 AMHmmm... I wonder if it's simply because when you create an index, it also creates a new set of stats that go with it, which CAN be used by the optimizer even if the index is not.
Hmmm, Do you have an example of where this could happen? I am not sure how to create such a circumstance nor do I have the available bandwidth at the moment to play with it either. May a little down the road I might.
July 17, 2018 at 11:47 am
Lynn Pettis - Tuesday, July 17, 2018 9:43 AMJeff Moden - Tuesday, July 17, 2018 8:59 AMHmmm... I wonder if it's simply because when you create an index, it also creates a new set of stats that go with it, which CAN be used by the optimizer even if the index is not.Hmmm, Do you have an example of where this could happen? I am not sure how to create such a circumstance nor do I have the available bandwidth at the moment to play with it either. May a little down the road I might.
No. I've never seen such a thing happen before. I just think that it's possible. This could be a "first" instance of such a thing happening with stats.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2018 at 11:54 am
Jeff Moden - Tuesday, July 17, 2018 11:47 AMLynn Pettis - Tuesday, July 17, 2018 9:43 AMJeff Moden - Tuesday, July 17, 2018 8:59 AMHmmm... I wonder if it's simply because when you create an index, it also creates a new set of stats that go with it, which CAN be used by the optimizer even if the index is not.Hmmm, Do you have an example of where this could happen? I am not sure how to create such a circumstance nor do I have the available bandwidth at the moment to play with it either. May a little down the road I might.
No. I've never seen such a thing happen before. I just think that it's possible. This could be a "first" instance of such a thing happening with stats.
For what it's worth (near or at zero I realize) I've been told that this can happen by various members of the optimizer team as well. I'm not saying this is the case here. It is a possibility. And no, I don't have a working example either. Same thing with constraints. I'm told that a unique constraint can change the choices made by the optimizer even if it doesn't actually use the constraint in the plan (and again, no example at hand).
"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
July 17, 2018 at 12:49 pm
Grant Fritchey - Tuesday, July 17, 2018 11:54 AMJeff Moden - Tuesday, July 17, 2018 11:47 AMLynn Pettis - Tuesday, July 17, 2018 9:43 AMJeff Moden - Tuesday, July 17, 2018 8:59 AMHmmm... I wonder if it's simply because when you create an index, it also creates a new set of stats that go with it, which CAN be used by the optimizer even if the index is not.Hmmm, Do you have an example of where this could happen? I am not sure how to create such a circumstance nor do I have the available bandwidth at the moment to play with it either. May a little down the road I might.
No. I've never seen such a thing happen before. I just think that it's possible. This could be a "first" instance of such a thing happening with stats.
For what it's worth (near or at zero I realize) I've been told that this can happen by various members of the optimizer team as well. I'm not saying this is the case here. It is a possibility. And no, I don't have a working example either. Same thing with constraints. I'm told that a unique constraint can change the choices made by the optimizer even if it doesn't actually use the constraint in the plan (and again, no example at hand).
That latter part is one of the things that frequently comes up about articles that show how to find supposedly unused indexes so your comments are worth a whole lot, actually.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 20, 2018 at 8:58 am
Sorry for the delay in getting back to you guys, i had a couple days off. So here's what's odd - the query isn't using the index, which explains why it was NULL in index usage stats. However when i posted this question on the forum, removing the index was definitely causing the query time to increase to around 4 minutes, and recreating it would take the query back down to a few seconds. However, when running the query today (with the index) it is now taking 3 minutes which i cannot explain as nothing should have changed in this test environment. I promise i'm not going crazy - i know for certain that the index had an effect a few days ago despite not being used in the plan. So i think the point about using the stats without utilising the index in the plan may very well be valid!! Just can't prove it right now as my query is back to performing badly........arghgh!
Thanks for your interest.
July 20, 2018 at 9:02 am
doodlingdba - Friday, July 20, 2018 8:58 AMSorry for the delay in getting back to you guys, i had a couple days off. So here's what's odd - the query isn't using the index, which explains why it was NULL in index usage stats. However when i posted this question on the forum, removing the index was definitely causing the query time to increase to around 4 minutes, and recreating it would take the query back down to a few seconds. However, when running the query today (with the index) it is now taking 3 minutes which i cannot explain as nothing should have changed in this test environment. I promise i'm not going crazy - i know for certain that the index had an effect a few days ago despite not being used in the plan. So i think the point about using the stats without utilising the index in the plan may very well be valid!! Just can't prove it right now as my query is back to performing badly........arghgh!Thanks for your interest.
So try updating the stats for the index.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2018 at 8:03 am
What Jeff said. And in situations like this, execution plans are our friends.
"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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply