User_update high but no user seek/scan. Is this an effective index?

Question

User_update high but no user seek/scan. Is this an effective index?

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
July 18, 2017 at 2:23 pm

#339029
Hello,
Seeing the execution plan created one index for which I can see that the User_update is v high but no User seek. Is this an effective index to be retained or an overhead?
Per definition -
- user_updates – The number of times the index has been modified due to a change in the table's data.
Please advise
Thanks.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

the main problem with using data from sys.dm_db_index_usage_stats directly is that the data there gets reset when the service restarts.. Do you know the uptime of this server?
SELECT s.up_hours / 24 AS up_days, s.up_hours % 24 AS up_hours FROM (SELECT TOP 1 DATEDIFF(HOUR, login_time, GETDATE()) AS up_hours FROM sys.dm_exec_sessions ORDER BY login_time) s
You may need to watch usage stats over time to get a real picture of if and how this index is used. There may be a weekly or monthly process that uses it.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

SQL_Hunt

SSC-Dedicated

Points: 33518

July 18, 2017 at 3:22 pm

#1951516

4 days

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

So in 4 days, the index has been modified a large number of times as data changed, but never read.
Does that sound efficient and useful?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Super Cat SSCertifiable Points: 7334 More actions · Answer 4

I would say that is not long enough to decide whether the INDEX is useful. I like to have stats for a least 3 months before making any sort of decisions.

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 5

I agree with Talib, four days doesn't tell you much.

However, where did this index come from? Execution plans don't create them on their own as you stated. What drove you to choose to create this index? Let's say it was for a report that's run once a month (just making stuff up since we don't have context, index, code, or execution plans to actually pursue factual discussion), and it was the CEO running the report. The index shaves the time from a 10 minute report to a 10 second report. Is the index worth it? Heck yes. Now let's imagine a different scenario. There was a query that was run once, but it will never be run again. Within that query, there was an index suggestion added to the missing index tables. For some reason someone decided to just add that index. Is that index worth it, especially in light of the fact that it doesn't seem to help any other queries (based on four days of data) and that the original query will never be run again. Heck no.

In order to make these types of determinations, you have to understand more than just simple mechanics (missing index, create one, index doesn't seem to be used over a short period of time, drop it). The purposes of the database, the queries against, the business needs and cycles, all these have to be taken into account along with the mechanics of the execution plans and query optimizer when deciding where and when to add, modify or drop indexes.

"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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

Well, I do not use the missing indexes std script to use the indexes. I check the execution plan and find the bottleneck say (index scans, clustered index scans, book mark index, index spool and so on..... and on..) and then try to come up with a right index. I run that and check the usage and size. If that looks to be a profitable one I use or else I drop.

Thanks.

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 7

SQL-DBA-01 - Wednesday, July 19, 2017 6:36 AM
Well, I do not use the missing indexes std script to use the indexes. I check the execution plan and find the bottleneck say (index scans, clustered index scans, book mark index, index spool and so on..... and on..) and then try to come up with a right index. I run that and check the usage and size. If that looks to be a profitable one I use or else I drop.

Cool. That's the right way to do it. So, on this query, what made you decide it needed that index? How often is the query called? Did adding the index improve performance of the query in question? This ought to be major factors in deciding if the index is useful.

"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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

what made you decide it needed that index? --In o/p results the columns were missing, so added them.

How often is the query called? ---This is not part of any adhoc query. All run part of diff stored procedure. I assume this runs in every few mins.

Did adding the index improve performance of the query in question? --> Hard to know. As there are thousands of code run, app team can not say if this alone help. For me I can only judge if the usage says (Index Seek).

Can you suggest some of the best practices and quick tips?

Thanks.

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 9

SQL-DBA-01 - Wednesday, July 19, 2017 7:17 AM
what made you decide it needed that index? --In o/p results the columns were missing, so added them.
How often is the query called? ---This is not part of any adhoc query. All run part of diff stored procedure. I assume this runs in every few mins.
Did adding the index improve performance of the query in question? --> Hard to know. As there are thousands of code run, app team can not say if this alone help. For me I can only judge if the usage says (Index Seek).
Can you suggest some of the best practices and quick tips?

Yeah, measure the performance of any given query before and after you do any kind of modification leading to a change in the execution plan. Capture both the query runtime and I/O. Preferably use Extended Events to make the capture because this has the lowest observer overhead of any of the approaches that you can use (and there are tons).

You don't want to assume performance improvements. You want to know. Scans are not necessarily a bad thing. Seeks are not necessarily a good thing. Both can be good for a query or bad for query depending on the data & processing occurring. This is why we measure performance as well as look at the execution plan.

"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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 10

Can you suggest how do you use the Extended event to capture the details? I mostly use set IO profile On and get the details. But here in my case the problem is- procedures are bigger and the issues are not directly tired to select statement. They have table variables / temp tables and getting data from multiple other procedures, so running simply the piece of code will not work for me.
If you use some useful script to quickly gather the details and then can use my idea to use it appropriately would be v useful.
"Capture both the query runtime and I/O. Preferably use Extended Events to make the capture because this has the lowest observer overhead of any of the approaches that you can use (and there are tons). "

Thanks.

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 11

The basics are very straight forward. Scroll down to the section on Extended Events in this article I wrote. It gives you the two basic metrics, some additional measures, and how to output it all to file. For an overall introduction to extended events, Microsoft has a great set of documentation. Here's an article that includes some information on filtering. You may need to do that in your situation. You might want to focus on the query hash as a filter. It's an action, not a column built into an event, so that means a tiny amount of added overhead, but judicious use of actions doesn't hurt.

That should get you going.

"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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 12

I will use that as an additional measurement as you said will try to check the query hash option.

by the way, are query_hash and query_plan_hash are same?

Thanks.

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 13

SQL-DBA-01 - Wednesday, July 19, 2017 8:04 AM
I will use that as an additional measurement as you said will try to check the query hash option.
by the way, are query_hash and query_plan_hash are same?

Nope. Query hash is a hash (a calculation based on the text) of the query. The query plan hash is a hash of the query plan.

"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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 14

One question -
If there is no index vs an index scan / clustered index scan, will that be the same?

Thanks.