--- title: "Database Performance Maintenance" slug: "database-maintenance" updated: 2025-12-31T12:39:11Z published: 2025-12-31T12:39:11Z canonical: "azure-cost-management-playbook.turbo360.com/database-maintenance" --- > ## Documentation Index > Fetch the complete documentation index at: https://azure-cost-management-playbook.turbo360.com/llms.txt > Use this file to discover all available pages before exploring further. # Database Performance Maintenance When you are considering scaling your Azure SQL Database. You might be leaving money on the table if you do this without first looking at the internals of your database schema, queries and execution plans to see if you have some inefficiencies in your setup. It might be a case that a few small SQL tweaks could remove your need to scale. Below are some suggestions to help you validate this. ## Is the database resource bound Before touching the SKU, prove what the database is waiting on. The below query can help you identify this. ```sql SELECT  end_time,  avg_cpu_percent,  avg_data_io_percent,  avg_log_write_percent,  avg_memory_usage_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC; ``` ` ` - CPU high → compute bound - Data IO high → scans / missing indexes / stale stats - Log IO high → heavy writes / transactions - Memory high → spills / bad plans ## Are there blocking or long waits The slow queries might actually be from waiting rather than executing. The below query can help you identify this. ```sql SELECT  r.session_id,  r.status,  r.wait_type,  r.wait_time,  r.blocking_session_id,  t.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.database_id = DB_ID(); ``` ## Check for healthy statistics Stale statistics can affect your query execution. The below query can help you check the database level settings. ```sql SELECT  is_auto_create_stats_on,  is_auto_update_stats_on,  is_auto_update_stats_async_on FROM sys.databases WHERE name = DB_NAME(); ``` You are looking for all 3 to be on in most cases. The below query can help you find statistics which are old or not updated. ```sql SELECT  OBJECT_NAME(s.object_id) AS table_name,  s.name AS stat_name,  STATS_DATE(s.object_id, s.stats_id) AS last_updated FROM sys.stats s WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 ORDER BY last_updated; ``` ## Check query execution plan Reviewing the query execution plan of the query or procedure is important in evaluating where the bottlenecks are. If you can execute the query you can set the below options on the query execution ```sql SET STATISTICS IO ON; SET STATISTICS TIME ON; -- Ctrl+M in SSMS for actual plan EXEC dbo.YourProc @CustomerId = 123, @FromDate='2025-12-01', @ToDate='2025-12-31'; ``` If you cant run the procedure then you can choose the Display Execution Plan option or CTRL+L. The query plan execution can help you identify areas to tune your database to help reduce the overhead on certain queries which can make a big difference to performance. There is more info about how to interpret an execution plan on this link: [https://www.sqlshack.com/how-to-analyze-sql-execution-plan-graphical-components/](https://www.sqlshack.com/how-to-analyze-sql-execution-plan-graphical-components/) ## Check for missing or mismatched indexes This query tells be about places where I could potentially have query performance savings by adding additional indexes. Using the results from the below queries we should evaluate the query execution plans and look for places where an index may help us. ```sql SELECT TOP (50)  migs.avg_user_impact,  mid.statement,  mid.equality_columns,  mid.inequality_columns,  mid.included_columns FROM sys.dm_db_missing_index_group_stats migs JOIN sys.dm_db_missing_index_groups mig  ON mig.index_group_handle = migs.group_handle JOIN sys.dm_db_missing_index_details mid  ON mid.index_handle = mig.index_handle WHERE mid.database_id = DB_ID() ORDER BY migs.avg_user_impact DESC; ``` If you use the results of this query you will find that AI chat tools like Github Copilot and Chat GPT are really handy at explaining what the results mean and can help you work on suggested indexes. ## Upgrading Decision If you are still feeling that the database is under performing and you have checked all of the below: - Fixed stats - Validated plans - Addressed blocking - Reduced unnecessary IO If you are still feeling you have the below concerns: - Is CPU *still* maxing out? - Is IO *still* saturated *after tuning*? Now is the time to consider scaling ## Learning More - Ola Hallengren has some excellent resources to help you with [https://github.com/olahallengren/sql-server-maintenance-solution](https://github.com/olahallengren/sql-server-maintenance-solution) - Yochanan Rachamim has some good resources here too which are worth checking [https://github.com/yochananrachamim/AzureSQL/blob/master/AzureSQLMaintenance.sql](https://github.com/yochananrachamim/AzureSQL/blob/master/AzureSQLMaintenance.sql)