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


Index Maintenance Strategy


Index Maintenance Strategy

Author
Message
Query Shepherd
Query Shepherd
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 454
Hi SQL-folks,

I've asked myself, what would be the best strategy to keep my indexes always optimal and up-to-date. What are your practices.

I tried several approaches :

- Missing index views and unused index views : I marked that ones, that need to persist, allthough they are not used often through extended property comments and deleted the unused periodically and create the missing new ones. Problems : Danger of creating and deleting the same indexes over an over again, because you got other better indexes, or the index get's shown as missing allthoug you created it (MS Bug in SQL Server 2008 prior to R2). this is a lot of work and not taking care of partitioning etc. Why is impact not a counter I can rely on...what can I rely on?
- DTA : The database tuning advisor seems to be good, but the naming conventions are terrible and the dta doesn't know about indexes that need to be persisted because it speeds up a very important query executed very seldom up to 100%. So it's a lot of extra work an I never get rid of the best and optimal settings for index maintenance in my very special case, because the documentation is very bad. I don't know in deep how the dta works and I want to have control over what I do. Problems : Capturing a workload over such a long period of time, that all interesting queries get captured.
- Manual Analysis : I traced out the queries that took the longest time and tried to speed them up through analysis of the execution plans. Problems : What is the best way to have the optimal amount of indexes serving all statements as best as possible. Is it better to have wide indexes or is it better to have small indexes whit the downside of slowing down inser, updates and deletes.

I hope you know what I mean and can help me with your experience.

Thanks in advance!!! God bless you!!!

Greetz
Query Shepherd
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25807 Visits: 17509
That is a might big topic. I would not rely too heavily on DTA or the missing indexes views. In fact, I would probably not even look at them. The absolute best approach to figuring out what indexes you need on your system can't be handled on an online forum. You have to have access to so many things to dial this thing in. This type of thing also needs to be considered as a process instead of event. You can't just "add all the proper indexes" and then move on. It is an ongoing balance.

You should probably read up on indexes. The stairways series on here is excellent. It starts out basic and gets pretty deep.

http://www.sqlservercentral.com/stairway/72399/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Query Shepherd
Query Shepherd
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 454
Yeah you got it! How do I get the balance over a long time? How can I decide in consideration of all queries fired on a table which are the best indexes (covering also changing structure and changing queries). Where is the entry to the wood i can't see, because ther are so much trees? I red so much about indexing, but I need that last hint to bring light at the end of the tunnel :-D!

Greetz
Query Shepherd
Query Shepherd
Query Shepherd
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 454
Just remembered the articles (e.g. http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/) of Gail, because Sean gave me links to other posts on her blog...I gonna recheck them and come back with my questions

Greetz
Query Shepherd
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31862 Visits: 18550
Sean Lange (4/19/2013)
That is a might big topic. I would not rely too heavily on DTA or the missing indexes views. In fact, I would probably not even look at them. The absolute best approach to figuring out what indexes you need on your system can't be handled on an online forum. You have to have access to so many things to dial this thing in. This type of thing also needs to be considered as a process instead of event. You can't just "add all the proper indexes" and then move on. It is an ongoing balance.

You should probably read up on indexes. The stairways series on here is excellent. It starts out basic and gets pretty deep.

http://www.sqlservercentral.com/stairway/72399/


Bolded text is spot on. I think the most important thing to remember is that you need to know your data and the ways the data is accessed. This is a thing that is developed with time in the database and knowing that tuning is an iterative process.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Query Shepherd
Query Shepherd
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 454
But how do you collect all queries on a table to decide which field is selected the most and interesting for a clustered index? Through long lasting Profiler traces? Or ist there a more efficient (maybe also quicker) way? Are there any DMV's for that?

Found the answer myself in Gail's blogpost! Thanks Gail!

But how do i use SQL Trace or Extended Events to capture my queries? Does anyone of you know step-by-step explanations or existing solutions?

Greetz
Query Shepherd
Fernando Jacinto Alvarez
Fernando Jacinto Alvarez
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 68
You should use http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html and schedule for run weekly or dayly for keep your used objects updated
Query Shepherd
Query Shepherd
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 454
Fernando Jacinto Alvarez (4/23/2013)
You should use http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html and schedule for run weekly or dayly for keep your used objects updated

Thanks, but you definitely missed the point!

Greetz
Query Shepherd
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