Some periodic SqlServer maintenance can improve query performance. Abstract: Periodic SqlServer database maintenance steps, like 1)updating statistics; and 2)defragment indexes can contribute to query performance. 1 Query performance problem – check DB maintenance Our application is ASP.NET MVC on SqlServer database and we received complaints from one of our customers that some queries are timing out in the production system. So, we have a query performance problem. We of course looked into a number of places for potential improvements, like rewriting C#/EF queries, Indexing strategies for Tables involved, looked into the query Execution plan, activating the “Query Store” tool, etc. But also, one area appeared as a candidate for improvement, which is typically in the domain of the customer’s DBA/Database Analysts. Some database maintenance steps can contribute to query performance. 1.1 DB maintenance steps of interest We identified 2 potential steps of interest: Update database statistics
Defragment Indexes Step 1. While doing query execution plan analysis via different tools available on our test system, we noticed that some statistics were not updated in 2 years. Database statistics are important since the query optimizer uses info like table cardinality to create an optimal execution plan. So, there is no harm in triggering the update procedure periodically by ourselves. Step 2. Some tables in our database are growing very fast, due to the number of daily transactions. So, indexes could become fragmented over time. Periodic index examination for fragmentation and rebuild can be beneficial. Only, rebuilding an index can interfere with the regular operation of a database. Typically it locks the table whose index is rebuilt against writes. 1.2 Automatic or manual execution Depending on the customers' specific situation, we are considering 2 strategies for execution of the above steps. Manual maintenance execution
Automatic periodic execution Approach 1. The idea is to enable ASP.NET application administrator to manually activate maintenance execution, based on need, via the application menu option at the time when he/she thinks is suitable for that environment. App administrators might not have DBA access or skills to run necessary stored procedures manually. Approach 2. The idea is to schedule periodic execution of the maintenance steps above, for example, once a week, when the database workload is low. Rebuilding an index can interfere with the regular operation of a database. Typically it locks the table whose index is rebuilt against writes. 2 Update database statistics Based on [1]: in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.


updating statistics causes queries to recompile. We recommend not updating statistics too frequently


If using UPDATE STATISTICS or making changes through SQL Server Management Studio, requires ALTER permission on the table or view.


If using sp_updatestats, requires membership in the sysadmin fixed server role, or ownership of the database (dbo). --Update all statistics in a table
USE AdventureWorks2022;
GO
-- The following example updates the statistics for all indexes on the SalesOrderDetail table.
UPDATE STATISTICS Sales.SalesOrderDetail;
GO
-----------------------------------------
--Update all statistics in a database
USE AdventureWorks2022;
GO
-- The following example updates the statistics for all tables in the database.
EXEC sp_updatestats; 3 Index defragmentation Based on [2]: Use solutions such as “Adaptive Index Defrag” to automatically manage index defragmentation and statistics updates
This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, among other parameters, and update statistics with a linear threshold.
An intelligent defrag on one or more indexes, as well as required statistics update
You need to 1) create the usp_AdaptiveIndexDefrag and its supporting objects by using script form [2]; 2) then execute the script with custom parameters. Typical Usage: EXEC dbo.usp_AdaptiveIndexDefrag The defaults are to defragment indexes with fragmentation greater than 5%; rebuild indexes with fragmentation greater than 30%; defragment ALL indexes; commands WILL be executed automatically; defragment indexes in DESC order of the RANGE_SCAN_COUNT value; time limit was specified and is 480 minutes (8 hours); ALL databases will be defragmented; ALL tables will be defragmented; WILL be rescanning indexes; the scan will be performed in LIMITED mode; LOBs will be compacted; limit defrags to indexes with more than 8 pages; indexes will be defragmented OFFLINE; indexes will be sorted in the DATABASE; indexes will have its ORIGINAL Fill Factor; only the right-most populated partitions will be considered if greater than 8 page(s); statistics WILL be updated on reorganized indexes; defragmentation will use system defaults for processors; does NOT print the t-sql commands; does NOT output fragmentation levels; waits 5s between index operations;


EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014' Same as above, except its scope is only the 'AdventureWorks2014' database.


EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014', @tblName = 'Production.BillOfMaterials' Same as above but only acting on the BillOfMaterials table. 4 Sample execution 4.1 Update statistics Here is a run on my test database, SqlServer version 15.0 (Microsoft SQL Server 2019): 4.2 Index Defrag Here we first use the script from [2] to create usp_AdaptiveIndexDefrag: Then verify that new objects are there: Then execute the script: 5 References [1] Update Statisticshttps://learn.microsoft.com/en-us/sql/relational-databases/statistics/update-statistics?view=sql-server-ver16 [2] AdaptiveIndexDefraghttps://github.com/Microsoft/tigertoolbox/tree/master/AdaptiveIndexDefrag Some periodic SqlServer maintenance can improve query performance. Abstract: Periodic SqlServer database maintenance steps, like 1)updating statistics; and 2)defragment indexes can contribute to query performance. Abstract: 1 Query performance problem – check DB maintenance Our application is ASP.NET MVC on SqlServer database and we received complaints from one of our customers that some queries are timing out in the production system. So, we have a query performance problem. ASP.NET We of course looked into a number of places for potential improvements, like rewriting C#/EF queries, Indexing strategies for Tables involved, looked into the query Execution plan, activating the “Query Store” tool, etc. But also, one area appeared as a candidate for improvement, which is typically in the domain of the customer’s DBA/Database Analysts. Some database maintenance steps can contribute to query performance . database maintenance steps can contribute to query performance database maintenance steps can contribute to query performance 1.1 DB maintenance steps of interest We identified 2 potential steps of interest: Update database statistics Defragment Indexes Update database statistics Update database statistics Defragment Indexes Defragment Indexes Step 1 . While doing query execution plan analysis via different tools available on our test system, we noticed that some statistics were not updated in 2 years. Database statistics are important since the query optimizer uses info like table cardinality to create an optimal execution plan . So, there is no harm in triggering the update procedure periodically by ourselves. Step 1 query optimizer uses info like table cardinality to create an optimal execution plan Step 2 . Some tables in our database are growing very fast, due to the number of daily transactions. So, indexes could become fragmented over time. Periodic index examination for fragmentation and rebuild can be beneficial. Only, rebuilding an index can interfere with the regular operation of a database. Typically it locks the table whose index is rebuilt against writes. Step 2 1.2 Automatic or manual execution Depending on the customers' specific situation, we are considering 2 strategies for execution of the above steps. Manual maintenance execution Automatic periodic execution Manual maintenance execution Manual Automatic periodic execution Automatic Approach 1 . The idea is to enable ASP.NET application administrator to manually activate maintenance execution, based on need, via the application menu option at the time when he/she thinks is suitable for that environment. App administrators might not have DBA access or skills to run necessary stored procedures manually. Approach 1 ASP.NET application administrator to manually activate maintenance Approach 2 . The idea is to schedule periodic execution of the maintenance steps above, for example, once a week , when the database workload is low. Rebuilding an index can interfere with the regular operation of a database. Typically it locks the table whose index is rebuilt against writes. Approach 2 periodic execution once a week 2 Update database statistics Based on [1]: in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates. updating statistics causes queries to recompile. We recommend not updating statistics too frequently If using UPDATE STATISTICS or making changes through SQL Server Management Studio, requires ALTER permission on the table or view. If using sp_updatestats, requires membership in the sysadmin fixed server role, or ownership of the database (dbo). in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates. in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates. UPDATE STATISTICS sp_updatestats updating statistics causes queries to recompile. We recommend not updating statistics too frequently updating statistics causes queries to recompile. We recommend not updating statistics too frequently If using UPDATE STATISTICS or making changes through SQL Server Management Studio, requires ALTER permission on the table or view. If using UPDATE STATISTICS or making changes through SQL Server Management Studio, requires ALTER permission on the table or view. UPDATE STATISTICS If using sp_updatestats, requires membership in the sysadmin fixed server role, or ownership of the database (dbo). If using sp_updatestats , requires membership in the sysadmin fixed server role, or ownership of the database (dbo). sp_updatestats --Update all statistics in a table
USE AdventureWorks2022;
GO
-- The following example updates the statistics for all indexes on the SalesOrderDetail table.
UPDATE STATISTICS Sales.SalesOrderDetail;
GO
-----------------------------------------
--Update all statistics in a database
USE AdventureWorks2022;
GO
-- The following example updates the statistics for all tables in the database.
EXEC sp_updatestats; --Update all statistics in a table
USE AdventureWorks2022;
GO
-- The following example updates the statistics for all indexes on the SalesOrderDetail table.
UPDATE STATISTICS Sales.SalesOrderDetail;
GO
-----------------------------------------
--Update all statistics in a database
USE AdventureWorks2022;
GO
-- The following example updates the statistics for all tables in the database.
EXEC sp_updatestats; 3 Index defragmentation Based on [2]: Use solutions such as “Adaptive Index Defrag” to automatically manage index defragmentation and statistics updates This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, among other parameters, and update statistics with a linear threshold. An intelligent defrag on one or more indexes, as well as required statistics update You need to 1) create the usp_AdaptiveIndexDefrag and its supporting objects by using script form [2]; 2) then execute the script with custom parameters. Use solutions such as “ Adaptive Index Defrag ” to automatically manage index defragmentation and statistics updates Adaptive Index Defrag This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, among other parameters, and update statistics with a linear threshold. An intelligent defrag on one or more indexes, as well as required statistics update You need to 1) create the usp_AdaptiveIndexDefrag and its supporting objects by using script form [2]; 2) then execute the script with custom parameters. usp_AdaptiveIndexDefrag Typical Usage: EXEC dbo.usp_AdaptiveIndexDefrag The defaults are to defragment indexes with fragmentation greater than 5%; rebuild indexes with fragmentation greater than 30%; defragment ALL indexes; commands WILL be executed automatically; defragment indexes in DESC order of the RANGE_SCAN_COUNT value; time limit was specified and is 480 minutes (8 hours); ALL databases will be defragmented; ALL tables will be defragmented; WILL be rescanning indexes; the scan will be performed in LIMITED mode; LOBs will be compacted; limit defrags to indexes with more than 8 pages; indexes will be defragmented OFFLINE; indexes will be sorted in the DATABASE; indexes will have its ORIGINAL Fill Factor; only the right-most populated partitions will be considered if greater than 8 page(s); statistics WILL be updated on reorganized indexes; defragmentation will use system defaults for processors; does NOT print the t-sql commands; does NOT output fragmentation levels; waits 5s between index operations; EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014' Same as above, except its scope is only the 'AdventureWorks2014' database. EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014', @tblName = 'Production.BillOfMaterials' Same as above but only acting on the BillOfMaterials table. EXEC dbo.usp_AdaptiveIndexDefrag The defaults are to defragment indexes with fragmentation greater than 5%; rebuild indexes with fragmentation greater than 30%; defragment ALL indexes; commands WILL be executed automatically; defragment indexes in DESC order of the RANGE_SCAN_COUNT value; time limit was specified and is 480 minutes (8 hours); ALL databases will be defragmented; ALL tables will be defragmented; WILL be rescanning indexes; the scan will be performed in LIMITED mode; LOBs will be compacted; limit defrags to indexes with more than 8 pages; indexes will be defragmented OFFLINE; indexes will be sorted in the DATABASE; indexes will have its ORIGINAL Fill Factor; only the right-most populated partitions will be considered if greater than 8 page(s); statistics WILL be updated on reorganized indexes; defragmentation will use system defaults for processors; does NOT print the t-sql commands; does NOT output fragmentation levels; waits 5s between index operations; EXEC dbo.usp_AdaptiveIndexDefrag The defaults are to defragment indexes with fragmentation greater than 5%; rebuild indexes with fragmentation greater than 30%; defragment ALL indexes; commands WILL be executed automatically; defragment indexes in DESC order of the RANGE_SCAN_COUNT value; time limit was specified and is 480 minutes (8 hours); ALL databases will be defragmented; ALL tables will be defragmented; WILL be rescanning indexes; the scan will be performed in LIMITED mode; LOBs will be compacted; limit defrags to indexes with more than 8 pages; indexes will be defragmented OFFLINE; indexes will be sorted in the DATABASE; indexes will have its ORIGINAL Fill Factor; only the right-most populated partitions will be considered if greater than 8 page(s); statistics WILL be updated on reorganized indexes; defragmentation will use system defaults for processors; does NOT print the t-sql commands; does NOT output fragmentation levels; waits 5s between index operations; EXEC dbo.usp_AdaptiveIndexDefrag EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014' Same as above, except its scope is only the 'AdventureWorks2014' database. EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014' Same as above, except its scope is only the 'AdventureWorks2014' database. EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014' EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014', @tblName = 'Production.BillOfMaterials' Same as above but only acting on the BillOfMaterials table. EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014', @tblName = 'Production.BillOfMaterials' Same as above but only acting on the BillOfMaterials table. EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014', @tblName = 'Production.BillOfMaterials' 4 Sample execution 4.1 Update statistics Here is a run on my test database, SqlServer version 15.0 (Microsoft SQL Server 2019): 4.2 Index Defrag Here we first use the script from [2] to create usp_AdaptiveIndexDefrag : usp_AdaptiveIndexDefrag Then verify that new objects are there: Then execute the script: 5 References [1] Update Statistics https://learn.microsoft.com/en-us/sql/relational-databases/statistics/update-statistics?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/statistics/update-statistics?view=sql-server-ver16 [2] AdaptiveIndexDefrag https://github.com/Microsoft/tigertoolbox/tree/master/AdaptiveIndexDefrag https://github.com/Microsoft/tigertoolbox/tree/master/AdaptiveIndexDefrag

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

This story contains AI-generated text. The author has used AI either for research, to generate outlines, or write the text itself. 

Samsung SSDs Have Become So Fast, They Make Competitors Look Like Floppy Disks

How Periodic SqlServer Maintenance Boosts Query Performance

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

AI is Still a Long Way From Directly Replacing Programmers

Log Slow Postgres Query Plans Automatically: An Introduction to auto-explain

Optimize Data Migration in MongoDB: Resharding Techniques for Speed and Scalability

Strategies for Database Size Reduction in PostgreSQL: A Usage-Based Approach

The Power of Functional Indexes in MySQL: A Comprehensive Guide

AI is Still a Long Way From Directly Replacing Programmers

Log Slow Postgres Query Plans Automatically: An Introduction to auto-explain

Optimize Data Migration in MongoDB: Resharding Techniques for Speed and Scalability

Strategies for Database Size Reduction in PostgreSQL: A Usage-Based Approach

The Power of Functional Indexes in MySQL: A Comprehensive Guide

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps