3.6 Index Maintenance for Enterprise Environments (43m)
You’ve learned over time that maintenance plans are the wrong way to go, and you’ve implemented index maintenance scripts from Ola Hallengren, the MidnightDBAs, or Michelle Ufford. However, you’re not really sure exactly what you’re fixing, or if it’s the least invasive way to improve performance. Learn the difference between internal and external fragmentation, see how fill factor destroys performance, and discover a simple set of parameters that can keep everybody happy.
- 1.1 Intro and DBA Skills Quiz (33m)
- 1.2 Building and Testing Reliable SQL Servers Part 1 (25m)
- 1.2 Building and Testing Reliable SQL Servers Part 2 (21m)
- 1.3 Architecture for HA and DR Part 1
- 1.3 Architecture for HA and DR Part 2
- 1.4 Design Quorum for Failover Clusters (23m)
- 1.5 Triaging Failure in Availability Groups (32m)
- 1.6 Recovering from Failover Part 1 (40m)
- 1.6 Recovering from Failover Part 2 (12m)
- 1.7 Building an Inventory and a Support Matrix (39m)
- 2.1 Database Mirroring Field Medic Guide (40m)
- 2.2 Transaction Log Shipping Tips and Tricks (45m)
- 2.3 Troubleshooting Backup and Restore Problems (35m)
- 2.4 Optimizing DBCC CHECKDB (53m)
- 2.5 Availability Group Backup and CHECKDB Part 1 (8m)
- 2.5 Availability Group Backup and CHECKDB Part 2 (32m)
- 2.6 Cloud for the Senior DBA (38m)
- 2.7 Homework: Deciding Between Availability Solutions Part 1 (11m)
- 2.7 Homework: Deciding Between Availability Solutions Part 2 (27m)
- 3.1 Shared Storage Part 1 (28m)
- 3.1 Shared Storage Part 2 (32m)
- 3.2 Advanced SAN Features – Storage Tiering and Snapshots (31m)
- 3.3 Virtualization Management and Troubleshooting (61m)
- 3.4 Server Hardware Sizing (36m)
- 3.5 Homework Part 1 (11m)
- 3.5 Homework Part 2: Answers (28m)
- 3.7 Recap and Q&A (28m)
10 Comments. Leave new
Hi Brent. Loving the material so far. Crisp and easy to follow. I have a question which sort of a debate-cal here with my team so thought of getting an expert view 🙂 IN our maintenance routine we only update index statistics. Design team firmly believe in skipping the system created stats. Lately we had issues, where running update stats on table fixes query time outs (almost 3-4 times now). I firmly believe to update system stats as well, but here its a ball game where they want to dump work on app developers to create indexes rather than we maintaining the stats. What are your thoughts on this one index vs system stats as per me maintaining system stats should be less overhead than creating indexes?
OK, so let’s take a step back:
* What’s the problem you’re trying to solve?
* How would you measure the problem? (Meaning, how would you prove that it’s a problem?)
* How would you measure that your solution solved the problem?
* We are trying to solve multiple time out occurrences which have happened in last couple of months and seems to occur once in a week with no fixed pattern of day.
* Every time when time out occurs, for various SP’s every time running update stats table name for specific tables as involved in SP’s were done. This has resolved time outs for every occurrence.
* Current routine job for index maintenance ‘update stats’ is done only for index created stats and system stats are skipped. We can see that auto update stats also never kicked off for those columns in tables with time out issues. As we did update stats , somehow it refreshes those stats and seems to have work. Therefore whats the harm in updating system stats? Shouldn’t one be doing it rather than asking dev to create indexes for those system stats as that seems to be a larger overhead?
Why not just turn on auto update stats async?
https://www.mssqltips.com/sqlservertip/2904/sql-servers-auto-update-statistics-async-option/
That’ll let queries move ahead without waiting for the stats update to finish.
Thanks Brent. But i am not sure how is that going to help. My bad, i forgot to mention the SQL server version, it’s SQL 2014. And unfortunately we do not have the Trace flag enabled for new stats mechanism. I’ve informed my design team to use those TF as the tables we are talking are pretty big like in TB’s with over 800 Million rows where auto update stats of column stats hardly get updated due to expected high modification counter. So i believe even with auto update stats async will also go same problem of not updating.
Kapil – you said the problem you were trying to solve was multiple timeout occurrences when queries were waiting for statistics updates to finish. With auto update stats async, the query is allowed to go ahead and run with the existing stats, and the stats updates are kicked off asynchronously in the background without impeding your query. The timeouts would stop.
I’m going to be brutally honest: it feels like you’re pushing me for the answer you want rather than listening to what I’m saying. Before you respond again, take a day or two as a breather, and read – seriously, read – the resources that I’ve given you. Take the time to run some experiments. Be open to the fact that maybe you’re on the wrong track.
Hope that helps!
Thanks Brent. I took some time to understand more on this and definitely was not pushing for any answer. My bad if it made you felt that day, may be just got too excited if that’s a correct terminology :). OK so i did few tests last week, turning to async, definitely reduced some of the time-outs, but introduced few new time outs as well since application has a time out settings for those process of 10 S. Again, doing manually update stats somehow clear the air. Now we are working on improving the overall update stats job to may be run more often. Appreciate all your suggestions.
Hey Brent, I’ve dropped an email as well at help@brentozar.com for some technical issue. I am not able to access any of the content saying I’ve been temporarily blocked due to high connection. Why would that be a case when i have been using the same devices for last couple of years and now suddenly seems to block? i verified the Captcha but with no success. Will appreciate if this can be checked, thanks!
Kapil – we don’t have any firewalls that would block you due to high connections. That would be something on your end – may need to check with your Internet provider.
Thanks for the information. I understand why you don’t have a slide deck to download. However, in this and other videos the bottom right corner of the slides is hidden making it difficult to take notes.