February 17, 2011 at 9:05 am
TheSQLGuru (2/17/2011)
1) That's an interesting script for dupe index checking Jason. Haven't seen one like that before. I need to give it a try.2) Duplicate indexes can indeed cause SIGNIFICANT issues with concurrency and DML response time. I had one client contact me after their system became almost unresponsive due to someone going hog-wild with DTA combined with a major data addition to the database. It took me a LONG to completely clean up that mess, but we eventually got to a great balance of fast SELECT performance AND fast/concurrent DML activity. Come to think of it, I have had to clean up quite a few DTA SNAFUs.
Thanks, I found two different scripts which I adapted for my use from online somewhere. I was re-examining it and thinking it should also be updated to use the SQL 2005+ DMOs instead of the backward compatible views.
As for DTA and hypothetical indexes, I have a script for that too. That script will script out the create statements for those hypothetical indexes and store that in a table and then drop the hypo indexes. It has been quite handy.
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
February 17, 2011 at 9:49 am
CirquedeSQLeil (2/17/2011)
TheSQLGuru (2/17/2011)
1) That's an interesting script for dupe index checking Jason. Haven't seen one like that before. I need to give it a try.2) Duplicate indexes can indeed cause SIGNIFICANT issues with concurrency and DML response time. I had one client contact me after their system became almost unresponsive due to someone going hog-wild with DTA combined with a major data addition to the database. It took me a LONG to completely clean up that mess, but we eventually got to a great balance of fast SELECT performance AND fast/concurrent DML activity. Come to think of it, I have had to clean up quite a few DTA SNAFUs.
Thanks, I found two different scripts which I adapted for my use from online somewhere. I was re-examining it and thinking it should also be updated to use the SQL 2005+ DMOs instead of the backward compatible views.
As for DTA and hypothetical indexes, I have a script for that too. That script will script out the create statements for those hypothetical indexes and store that in a table and then drop the hypo indexes. It has been quite handy.
Let me add my thanks - added your script to my sandbox DB and sure would like to encourage you to post the DTA script so I can "crib" that one and save some brain cells for more pleasant thoughts.
February 17, 2011 at 10:05 am
I think I will be posting those solutions to my blog instead.
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
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply