SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index usage overview


Index usage overview

Author
Message
Gail Wanabee
Gail Wanabee
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1889 Visits: 1408
Wilfred and Jason,

Thanks to both of you for taking the time to implement and publish your queries.

I have an immediate use for them at work.

LC
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67523 Visits: 18570
crainlee2 (3/31/2010)
Wilfred and Jason,

Thanks to both of you for taking the time to implement and publish your queries.

I have an immediate use for them at work.

LC



You are welcome.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Wilfred van Dijk
Wilfred van Dijk
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2526 Visits: 1363
I just posted an updated version (waiting for approval, so be patient). I removed the join to sysobjects (thanks to Jason who pointed to this). I replaced the <TAB> character to <SPACE>, hope this will eliminate the goofy character issue (if not, copy/paste into an editor etc)

Wilfred
The best things in life are the simple things
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67523 Visits: 18570
You're welcome.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Wilfred van Dijk
Wilfred van Dijk
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2526 Visits: 1363
Another minor modification: indexes with only updates and no hits now have a negative ratio (instead of 0). The ratio calculation for those indexes is updates * -1.

For those of you who already want the modification, change the "ratio" calculation to

, case
when a.user_seeks + a.user_scans + a.user_lookups = 0
then - a.user_updates
else cast(a.user_seeks + a.user_scans + a.user_lookups AS REAL) / cast(case a.user_updates when 0 then 1 else a.user_updates end as REAL)
end "ratio"



Waiting for approval!

Wilfred
The best things in life are the simple things
Wilfred van Dijk
Wilfred van Dijk
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2526 Visits: 1363
Another update:
- new columns: perc_seeks, perc_scans, perc_lookups. these columns represents the index method represented as a percentage
- The ratio calculation is changed: Since a lookup is better than a seek and a seek better than a scan, the ratio is now calculated: scan * .8 + seek + lookup * 1.2
- column stats_date moved to the end (before SQLCmd)
- SQLCmd now only shows a command for performance indexes

Waiting for approval!

Wilfred
The best things in life are the simple things
Wilfred van Dijk
Wilfred van Dijk
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2526 Visits: 1363
Another update! I was triggered by the Question of the day
can a CTE be used in a view
. (The answer is yes :-D )
So I moved the select part to a CTE and did the calculation afterwards.
This results in a cleaner code and as a bonus the subtree cost is much lower w00t

Also added column pressure This column says something about how frequent this index is updated, compared to the size of the index. The calculation is: (hits-updates) / size of index.
A general rule: a high pressure means a lot of updates (update overhead).
For example: if your indexsize = 0MB and the pressure is high, it's probably a temporary table.

Till the next update! (waiting to approve this version)

Wilfred
The best things in life are the simple things
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67523 Visits: 18570
Wilfred van Dijk (4/21/2010)
Another update! I was triggered by the Question of the day
can a CTE be used in a view
. (The answer is yes :-D )
So I moved the select part to a CTE and did the calculation afterwards.
This results in a cleaner code and as a bonus the subtree cost is much lower w00t

Also added column pressure This column says something about how frequent this index is updated, compared to the size of the index. The calculation is: (hits-updates) / size of index.
A general rule: a high pressure means a lot of updates (update overhead).
For example: if your indexsize = 0MB and the pressure is high, it's probably a temporary table.

Till the next update! (waiting to approve this version)


I think you may want to rethink the pressure formula. (hits - updates) / size could yield very low numbers for a very update heavy index.

eg. (100 - 99) / 1024 = 0.0009765625
(100 - 1000) / 1024 = -0.87890625

The second query yields more updates but a lower pressure.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Wilfred van Dijk
Wilfred van Dijk
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2526 Visits: 1363
It's how you define pressure: I was considering a negative pressure as no pressure (I had to explain that Unsure ).

Wilfred
The best things in life are the simple things
Wilfred van Dijk
Wilfred van Dijk
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2526 Visits: 1363
5/5: New version deployed

- FILEGROUP_NAME() function implemented (actually ... discovered :WhistlingSmile
- removed join to sysindexes (not used)

Waiting for approval!

Wilfred
The best things in life are the simple things
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search