Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Index usage overview Expand / Collapse
Author
Message
Posted Wednesday, March 31, 2010 5:52 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:34 PM
Points: 259, Visits: 1,090
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
Post #894301
Posted Wednesday, March 31, 2010 6:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #894307
Posted Thursday, April 1, 2010 1:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 930, Visits: 1,135
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
Post #894491
Posted Thursday, April 1, 2010 9:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
You're welcome.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #894920
Posted Tuesday, April 6, 2010 3:24 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 930, Visits: 1,135
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
Post #897411
Posted Wednesday, April 14, 2010 8:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 930, Visits: 1,135
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
Post #903173
Posted Wednesday, April 21, 2010 1:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 930, Visits: 1,135
Another update! I was triggered by the Question of the day
can a CTE be used in a view
. (The answer is yes )
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

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
Post #907502
Posted Wednesday, April 21, 2010 9:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
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 )
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

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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #907859
Posted Thursday, April 22, 2010 1:03 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 930, Visits: 1,135
It's how you define pressure: I was considering a negative pressure as no pressure (I had to explain that ).

Wilfred
The best things in life are the simple things
Post #908352
Posted Wednesday, May 5, 2010 9:33 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:52 AM
Points: 930, Visits: 1,135
5/5: New version deployed

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

Waiting for approval!


Wilfred
The best things in life are the simple things
Post #916195
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse