Performance Tips for SQL Server Maintenance
Many organizations fail to update their SQL Servers, leaving them at risk for downed systems, data breaches, and poor performance. This blog covers the essentials for SQL Server maintenance for servers that run on-premises using a virtual machine on Azure.
SQL Server Maintenance: Performance
Two routine maintenance areas that are often overlooked are statistics updates and index maintenance.
SQL Server provides a default feature that will auto-update the server’s statistics. Statistics in SQL Server reflect the distribution of data within the tables. As records are updated, deleted, or added, the system will structure how it will fetch the data based on a combination of the statistics and the indexes. If the statistics are not updated, SQL Server cannot efficiently build the query execution plan to fetch data.
Like a card catalog in a library, indexes tell the server where your data is located in the database, which helps queries run faster. However, suppose the index is not updated regularly. In that case, the server will perform a full scan of the table to find the data, which can result in much longer responses to queries (minutes versus seconds), significantly affecting the performance of your ERP.
Index Maintenance is not something that comes default in SQL Server and must be configured. Depending on how much data is in the database, updating indexes can take a significant amount of time, which will affect how and when the process is run. Many companies run the job after hours when the maintenance window is longer.
There are two ways to update the indexes in the database: perform a complete rebuild or simply reorganize them. A full rebuild is generally warranted if the indexes are fragmented by more than 30%. A rebuild will recreate the indexes from scratch. On the other hand, if the index is fragmented between 5% and 30%, taking the existing indexes and reorganizing them is sufficient and requires less time and resources than a full rebuild.
SQL Standard vs. SQL Enterprise
In SQL Standard, Index Maintenance is an offline process that locks the tables and indexes while it runs, preventing users from accessing the data in that table.
Conversely, SQL Enterprise can run the reindexing procedure while users can still be on the system. SQL Enterprise might be an option if the database is sufficiently large and the maintenance window is small.
Be aware that there is a significant price difference between SQL Standard and SQL Enterprise.
SQL Server Maintenance: Security – Backup and Recovery, Automation, Patching, and More
Backup and Recovery
One problem in some of our clients is that daily or weekly backups are not being performed. Or, in some cases, these clients may have set up automated jobs to create backups, but the jobs have been failing for weeks (or months) and no one has logged into the server to see if the job ran properly or to check for alerts notifying them of the failure. Automated jobs should send alerts that indicate they were completed successfully or not, and users should review these alerts to identify and correct the problems quickly.
Another issue we have seen from time to time is the failure of users to make sure they can reconstruct their database from their backups. A backup is of little use if it cannot be used in database recovery. Part of system maintenance is to periodically make sure backups can actually be used to recover from a database failure.
Another issue is running software patches promptly to keep the system up to date. Software patches not only contain new functionality but also include bug fixes and security patches. Ensuring your software patches are updated helps keep your database (and your data) secure.
DBCC CHECKDB is critical to understanding that the structural integrity of the database file is correct. Structural problems can cause the database to go into Suspect mode or, worse, go offline completely. DBCC CHECKDB will identify these problems and give you an advanced warning if something needs to be corrected.
SQL Server Maintenance Frequency
The frequency at which maintenance tasks should be performed varies based on the amount of data being processed; however, weekly SQL maintenance is sufficient for most organizations. So, for a typical maintenance window (for example, Saturday evening), we recommend three routine tasks to ensure top system performance:
- Run DBCC CHECKDB to check the integrity of the database file
- Reindex the system
- Update the statistics
Consider Moving to Azure SQL
This blog addresses maintenance tasks for SQL Server running on premises or using a virtual machine on Azure. Microsoft provides a SAAS version of SQL Server, Azure SQL, which removes most if not all these tasks from the user. With Azure SQL, Microsoft takes care of all index maintenance, updates and patching, and backups. Plus, Azure SQL includes automatic failover, redundant read-only copies of the data, and automatic site failover.
ArcherPoint Can Help with SQL Server Maintenance and More
ArcherPoint provides service plans that help our clients implement maintenance plans to ensure their SQL Server and NAV/BC ERP applications are performing at their best. Contact ArcherPoint for your SQL server maintenance needs or to determine what it would take to move to Azure SQL.