Overview
Note: For MS SQL Server and Azure SQL Server.
Virto Commerce uses MS SQL as master data storage. Unfortunately, modern object-database mappers 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:
We recommend to use this SQL maintenance script: AzureSQL/AzureSQLMaintenance.txt
or simplest one
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-Running 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. Prune PlatformOperationLog and NotificationMessage tables
The recommended retention for PlatformOperationLog and NotificationMessage is 45 days. Usually, it is enough to find the reason for the issues. Create a task and run it either nightly or weekly approach.
Task 5. 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 6. 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 7. 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 8. 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