March 29, 2010 at 2:45 pm
crainlee2 (3/29/2010)
Wilfred,What is the purpose of this elaborate query?
(NOTE: I have commented out the ALTER INDEX (DISABLE) statement until I get an explanation of what the query is supposed to do.)
I've run it against several test databases using SS2005 and SS2008. Without the ALTER INDEX (DISABLE) clause enabled, it appears to do nothing.
LC
If I may, that line is to create the command to disable an index. It looks to be purely optional and is only the text of the command. The command would need to be copied and executed separately.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 29, 2010 at 2:45 pm
Nice Script. I think I can find some use for it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 29, 2010 at 4:03 pm
crainlee2 (3/29/2010)
Wilfred,What is the purpose of this elaborate query?
(NOTE: I have commented out the ALTER INDEX (DISABLE) statement until I get an explanation of what the query is supposed to do.)
I've run it against several test databases using SS2005 and SS2008. Without the ALTER INDEX (DISABLE) clause enabled, it appears to do nothing.
LC
It's an easy way to disable a low performing index, by copy/paste this disable statement
Wilfred
The best things in life are the simple things
March 29, 2010 at 6:13 pm
Thanks.
LC
March 31, 2010 at 4:18 pm
I have taken this script provided by Wilfred and updated it to SQL 2005.
If interested, you can read about it at:
http://jasonbrimhall.info/?p=407
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 31, 2010 at 5:52 pm
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
March 31, 2010 at 6:00 pm
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[/url]
Learn Extended Events
April 1, 2010 at 1:56 am
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
April 1, 2010 at 9:18 am
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[/url]
Learn Extended Events
April 6, 2010 at 3:24 am
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
April 14, 2010 at 8:18 am
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
April 21, 2010 at 1:39 am
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 :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
April 21, 2010 at 9:49 am
Wilfred van Dijk (4/21/2010)
Another update! I was triggered by the Question of the daycan 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 :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[/url]
Learn Extended Events
April 22, 2010 at 1:03 am
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
May 5, 2010 at 9:33 am
5/5: New version deployed
- FILEGROUP_NAME() function implemented (actually ... discovered :Whistling:)
- removed join to sysindexes (not used)
Waiting for approval!
Wilfred
The best things in life are the simple things
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply