Posts

Shawn Upchurch

SQL Server Performance Tuning – Two Approaches to Index Maintenance

SQL Server Performance Tuning - Two Approaches to Index MaintenanceOriginally published on DMMaxwell.WordPress.com.

Though well-done index maintenance is usually an iterative process, trial and assessment, I have two default modes for how I set up index maintenance tasks for a given database. I refer to them as “Aggressive” and “Relaxed”.

When I set up index maintenance tasks, I set them up to run as often as I can. I’m using Ola Hallengren’s IndexOptimize script, and I usually set it to run nightly, with a time limit dependent on what else goes on within that particular system. For example, on one server I have some backups that kick off at 8PM, and an ETL process that runs around midnight, so I set IndexOptimize to run at 6 PM, with a 90 minute time limit. That gives SQL a half hour to repopulate it’s buffer cache with data needed for queries or complete any running index tasks that may spill over the time limit. This seems to be a good setting for this system. It’s slightly different elsewhere.

The difference between what I consider aggressive, and more relaxed, is how I set the thresholds for fragmentation and index size. For a more aggressive plan, I would set those thresholds lower. For a more relaxed plan, I would set them higher. Much depends on both the usage and the performance of the system, but as a baseline, this is a decent place to start.

Lower thresholds mean that the indexes are going to be maintained more often, but the average fragmentation levels will remain lower. Maintenance jobs will tend to average run times that are a bit longer and use more available RAM. Higher thresholds mean that indexes would be maintained less often, and fragmentation levels would average a bit higher. However, you’ll have shorter running maintenance tasks on average, and more available RAM left for caching query-related data.

Where to set these thresholds depends on the individual system, of course, but here’s what I use as a rough guideline. Keep in mind for the following thresholds that “page count” refers to the number of 8k pages contained in the index, so a page count of 1500 is around 12 MB of data.

Aggressive: Page Count = 1500, Defrag at 5%, Rebuild at 15%.
Relaxed: Page Count = 4000, Defrag at 15%, Rebuild at 30%

So on a system that has lots of available RAM for cache, or smaller indexes, I may be more relaxed with my index maintenance, since many of the indexes will reside in cache and reading them from disk will be more rare. On a system that would have less RAM or larger indexes, I’ll want to be more aggressive with my index maintenance, since I’ll be reading data more often from disk than from cache. It will take more time and resources, but will pay off in better query performance.

On the topic of frequency, index maintenance should ideally be done as often as you can allow. It hurts nothing to do some maintenance on your databases as long as nobody is using them. However, if you’re trying to schedule index maintenance in a tight window in order to keep the database available to users, or run maintenance along with other resource intensive jobs, then setting the thresholds as I describe may alleviate some contention between index maintenance and other process. You will also find it more efficient than the “rebuild everything every time” approach.

Thanks for reading,
-David.

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/microsoft-sql-server-health-check/” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.


<ol>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-introduction/” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-benefits/” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-1-collect-metrics/” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-2-analyze-results/” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-3-report-findings/” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>

</ol>

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

Shawn Upchurch

SQL Server Performance Tuning Script – Create Index Rebuild or Reorg CommandsOriginally published on DMMaxwell.WordPress.com.

The scenario here is, you’ve just performed some massively fragmenting operation, like a shrink. (Don’t look at me like that – it happens, just don’t do it as regular maintenance…) You know you need to get in and do some index maintenance. You could run your nightly index maintenance job, but in this case, you want to do this now, instead of waiting for off hours or running that job in the middle of the day. Furthermore, you want to see what indexes need maintenance and maybe run some of the commands independently. Here’s a quick script to make that easy for you. It outputs the table and index name, page count, and percent fragmentation, as well as a one line command to rebuild or defrag the index, depending on what thresholds you want to use for that.

/* 

  Quick script to create index defrag / rebuild commands.
  Useful for when you KNOW you have the work to do. 
  Not so useful for ongoing index maintenance, and should
  not be used for such.  

  The usual cautions about running this in production, 
  fitness for a particular purpose, or guarantee that this
  will fix anything apply. 

  DMMaxwell, October 2012.

*/

SELECT
   TableName = SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(ips.object_id)
  ,IndexName = ix.name 
  ,Pages = ips.page_count 
  ,Fragmentation = ips.avg_fragmentation_in_percent 
  ,ReindexCmd = 'ALTER INDEX [' + ix.name + '] ON [' + 
    SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME(ips.object_id) + '] ' + 
    CASE 
      WHEN ips.avg_fragmentation_in_percent > 15 THEN 'REBUILD; ' 
        /* BOL recommendation is 30 - being more aggressive. */
      WHEN ips.avg_fragmentation_in_percent > 5 THEN 'REORGANIZE; '  
        /* BOL recommendation I actually like. */
    END
FROM sys.dm_db_index_physical_stats(
  DB_ID('AdventureWorks'),  /* Your database name here. */
  NULL, /* Specify object ID or NULL for all of them.*/
  NULL, /* Specify index ID or NULL for all of them.*/
  NULL, /* Specify partition ID or NULL for all of them.*/
  'LIMITED' /* Going for speed, rather than depth of info. */
  ) ips
INNER JOIN sys.tables t
  ON ips.object_id = t.object_id
INNER JOIN sys.indexes ix 
  ON ips.object_id = ix.object_id
 AND ips.index_id = ix.index_id
WHERE ips.page_count > 500  
        /* Usually 1000 */
  AND ips.avg_fragmentation_in_percent > 5 
        /* Ignore fragmentation less than 5%. */
        /* These numbers vary, based on a multitude of factors. 
           I'm being a bit aggressive today. */
  AND ips.index_type_desc != 'HEAP' 
        /* Can't defragment a heap... */
GO

Thanks for reading.

-David.

About the Author

Data Platform Consultant

David Maxwell

UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.

Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions.  David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.

Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.

In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.

Learn more about David Maxwell at https://upsearch.com/david-maxwell/.

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/microsoft-sql-server-health-check/” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.


<ol>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-introduction/” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-benefits/” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-1-collect-metrics/” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-2-analyze-results/” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-3-report-findings/” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>

</ol>

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.