Index Maintenance and Performance (Dear SQL DBA Episode 38)

They made their index maintenance job smarter, and their queries got slower in production afterward. Could the index maintenance have harmed performance? In this 29 minute episode…

  • 00:50 Thinking about plan freezing in Query Store and multi-team process
  • 03:15 This week’s question about index maintenance and query performance

Subscribe to my YouTube channel, or check out the audio podcast to listen anywhere, anytime. Links from this episode are in this post below the video and in the YouTube description.

Links and further reading from the show this week…

Free, configurable index maintenance options:

You may enjoy reading…

2 Responses to Index Maintenance and Performance (Dear SQL DBA Episode 38)

  1. Jeff April 13, 2017 at 10:19 am #

    Again, another great topic!

    One thing I would add is to check and see what the fill factor is on those indexes. If they are at 100% or something close to that, then a possible problem is the immediate page splitting that would occur once the system begins it’s normal workload. Which sounds like a familiar problem that I have run into in the past.

    Which of course left me asking (not my first time) what was I thinking when I did that?

  2. Chris Wood April 13, 2017 at 2:19 pm #

    Kendra,

    One situation I had heard of was where say, using Ola’s scripts with User Databases, it pushed out of the cache the database pages that were most used with pages from other databases. This happened because Ola processed the user databases in an order that updated the databases in a particular order. Unfortunately the most used database was worked on first and other databases afterwards. This could also happen if the most used indexes came first when Ola’s script ran and then it updated less used pages. Either way the needed data pages got pushed out and replaced by less needed pages.

    Then when the application started to be used it had to push out un-needed pages to pull in the needed pages.

    Chris

Leave a Reply