Maintenance Tasks for SQL Server

Overview

Note: For MS SQL Server and Azure SQL Server.

Virto Commerce uses MS SQL as master data storage. Unfortunately, modern object-database mapper like Entity Framework hides all Maintenance Tasks from the developers. Everything works successfully on DEV, QA and Acceptance environments. And once when you go to live, you could see performance degradation on the production environments, because SQL Server databases require regular maintenance and this can be crucial for the E-Commerce solution.

In this topic, I will try to explain common Maintenance Tasks for SQL Server which we recommend to use on a regular base. And you never see the screen like this:

Task 1. Check Index Defragmentation and Rebuild Index

What is index fragmentation and why should I care about it:

  • Fragmentation exists when indexes have pages in which the logical ordering within the index, based on the key value of the index, does not match the physical ordering inside the index pages.
  • The Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data. For example, the addition of rows in a table may cause existing pages in rowstore indexes to split to make room for the insertion of new key values. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file.
  • Heavily fragmented indexes can degrade query performance because additional I/O is required to locate data to which the index points. More I/O causes your application to respond slowly, especially when scan operations are involved.

If avg_fragmentation_in_percent value is greater than 30%, I recommend to rebuild it.

Step 1. Run the query to find out % of fragmentation:

SELECT
 DB_NAME() AS DBName
 ,OBJECT_NAME(ps.object_id) AS TableName
 ,i.name AS IndexName
 ,ips.index_type_desc
 ,ips.avg_fragmentation_in_percent
 FROM sys.dm_db_partition_stats ps
 INNER JOIN sys.indexes i
 ON ps.object_id = i.object_id
 AND ps.index_id = i.index_id
 CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
 ORDER BY ips.avg_fragmentation_in_percent desc, ps.object_id, ps.index_id

Step 2. Run the query to rebuilding indexes:

DECLARE @TableName varchar(255)
 
 DECLARE TableCursor CURSOR FOR
 (
 SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
 FROM INFORMATION_SCHEMA.TABLES IST
 WHERE IST.TABLE_TYPE = 'BASE TABLE'
 )
 
 OPEN TableCursor
 FETCH NEXT FROM TableCursor INTO @TableName
 WHILE @@FETCH_STATUS = 0
 
 BEGIN
 PRINT('Rebuilding Indexes on ' + @TableName)
 Begin Try
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
 End Try
 Begin Catch
 PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild')
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
 End Catch
 FETCH NEXT FROM TableCursor INTO @TableName
 END
 
 CLOSE TableCursor
 DEALLOCATE TableCursor

Task 2. Find and Create Missing Index

One of the really cool things that SQL Server does is keep up with index statistics behind the scenes. When you use Entity Framework, very easy to skip the required index and decrease the performance of the solution.

A quick overview is the higher the improvement_measure the more it should help your performance. If you see a missing index with a high user_seeks value, which is the number of times the index could have been used, you will want to strongly consider adding it.

You should not create more than 5-10 indexes per table.

Run the query to find missing indexes and copy SQL command to create them.

SELECT CONVERT (varchar, getdate(), 126) AS runtime,
       mig.index_group_handle,
       mid.index_handle,
       CONVERT (decimal (28,1),
        migs.avg_total_user_cost *
        migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans))
        AS improvement_measure,
       'CREATE INDEX missing_index_' +
       CONVERT (varchar, mig.index_group_handle) +
       '_' +
       CONVERT (varchar, mid.index_handle) +
       ' ON ' +
       mid.statement +
       ' (' + ISNULL (mid.equality_columns,'') +
       CASE WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL
        THEN ','
        ELSE ''
        END + ISNULL (mid.inequality_columns, '') +
        ')' +
        ISNULL (' INCLUDE (' + mid.included_columns + ')',
                '') AS create_index_statement,
        migs.*,
    mid.database_id, mid.[object_id]
    FROM sys.dm_db_missing_index_groups mig
    INNER JOIN sys.dm_db_missing_index_group_stats migs
        ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details mid
        ON mig.index_handle = mid.index_handle
    WHERE CONVERT (decimal (28,1),
                   migs.avg_total_user_cost *
               migs.avg_user_impact *
              (migs.user_seeks + migs.user_scans)) > 10
    ORDER BY migs.avg_total_user_cost *
             migs.avg_user_impact *
         (migs.user_seeks + migs.user_scans) DESC

Task 3. Control and Eliminate Long-Eunning Queries

This script helps to find slow and long-running queries:

SELECT TOP 20 total_worker_time/execution_count AS [avg_cpu_time],
    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
      ((CASE WHEN statement_end_offset = -1
         THEN
            (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
         ELSE
            statement_end_offset
         END)
      - statement_start_offset) / 2+1))  AS sql_statement,
    execution_count,
    plan_generation_num,
    last_execution_time,   
    total_worker_time,
    last_worker_time,
    min_worker_time,
    max_worker_time,
    total_physical_reads,
    last_physical_reads,
    min_physical_reads,  
    max_physical_reads,  
    total_logical_writes,
    last_logical_writes,
    min_logical_writes,
    max_logical_writes,
  s1.sql_handle
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
WHERE s2.objectid is null
ORDER BY  (total_worker_time/execution_count) DESC

Task 4. Controls Size of Tables

Very important to control the size of tables, more time is required for apply any operation on a bigger table. If you have temporary or log tables, don’t forget to clean up the expired data.

Run the script to returns size of table in Mbytes.

select    
      sys.objects.name, sum(reserved_page_count) * 8.0 / 1024
from    
      sys.dm_db_partition_stats, sys.objects
where    
      sys.dm_db_partition_stats.object_id = sys.objects.object_id

group by sys.objects.name
ORDER BY 2 DESC

Task 5. Other Queries

Finding Blocking Queries in SQL Azure

SELECT TOP 10 r.session_id, 
r.plan_handle,      
r.sql_handle, 
r.request_id,      
r.start_time, 
r.status,      
r.command, 
r.database_id,      
r.user_id, 
r.wait_type,     
 r.wait_time, 
r.last_wait_type,      
r.wait_resource, 
r.total_elapsed_time,      
r.cpu_time, 
r.transaction_isolation_level,      
r.row_count, 
st.text  
FROM sys.dm_exec_requests r  
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st  
WHERE r.blocking_session_id = 0       
and r.session_id in       
(SELECT distinct(blocking_session_id) FROM sys.dm_exec_requests)  
GROUP BY r.session_id, r.plan_handle, r.sql_handle,
r.request_id, r.start_time, r.status, 
r.command, r.database_id, r.user_id, r.wait_type,      
r.wait_time, r.last_wait_type, r.wait_resource, 
r.total_elapsed_time, r.cpu_time, 
r.transaction_isolation_level, r.row_count, st.text  
ORDER BY r.total_elapsed_time desc

Max concurrent request and sessions

SELECT COUNT(*) AS [Concurrent_Requests] FROM sys.dm_exec_requests R
SELECT COUNT(*) AS [Sessions] FROM sys.dm_exec_connections

Task 6. Use Azure SQL Elastic Pool and Several Databases Instead of the Big One

Azure SQL Pools are well suited for Virto Commerce solutions. Virto Commerce allows to create own connection string for every module. The more databases you can add to a pool the greater your savings become. Depending on your application utilization pattern, it’s possible to see savings with as few as two S3 databases.

Task 7. Use Azure tools

Azure supports a big list of the service to improve Maintenance Tasks for SQL Server.

Here the list of automatic tuning:

  • Automated performance tuning of Azure SQL databases
  • Automated verification of performance gains
  • Automated rollback and self-correction
  • Tuning history
  • Tuning action T-SQL scripts for manual deployments
  • Proactive workload performance monitoring
  • Scale-out capability on hundreds of thousands of databases
  • Positive impact to DevOps resources and the total cost of ownership
2 Likes