Backup/Restore | SQL Freelancer Blog

Performance Tuning Series – Regular Maintenance

By bradyupton in Backup/Restore, Dynamic Management Views, Extended Events, Maintenance, Monitoring, Performance Series, Performance Tuning October 11, 2024

SQL Server performance isn’t just about the initial setup or database design—it requires continuous maintenance to ensure it runs smoothly over time. Neglecting regular maintenance can lead to fragmentation, slow queries, data integrity issues, and ultimately, downtime. By implementing a comprehensive maintenance strategy, you can ensure that your SQL Server databases remain optimized and healthy, allowing them to perform efficiently even as workloads and data volumes grow.

Why Regular Maintenance is Critical

Over time, SQL Server databases accumulate various inefficiencies that can degrade performance. These include fragmented indexes, outdated statistics, growing transaction logs, and unused or bloated data. Regular maintenance tasks help to mitigate these issues, ensuring that SQL Server can continue to execute queries quickly, handle transactions efficiently, and maintain data integrity.

Key Regular Maintenance Tasks for SQL Server

1. Index Rebuilding and Reorganization

Indexes can become fragmented over time as data is inserted, updated, or deleted. Fragmentation occurs when the logical order of pages in an index no longer matches the physical order on disk. This leads to slower reads, as SQL Server must perform additional I/O to retrieve scattered data.

Rebuild Indexes: Index rebuilds recreate the index from scratch, removing fragmentation and improving query performance. This process locks the table, so it should be scheduled during periods of low activity.
Reorganize Indexes: Reorganizing indexes is a less intrusive process that defragments them without locking the table. This can be done during regular operations but is less effective than a full rebuild.

Best Practice:

Schedule regular index maintenance based on the level of fragmentation. Use SQL Server’s sys.dm_db_index_physical_stats DMV to check fragmentation levels:
- 0-10% fragmentation: No action needed.
- 10-30% fragmentation: Use index reorganization.
- Above 30% fragmentation: Perform a full index rebuild.
Automate index maintenance using SQL Server Agent jobs or a dedicated maintenance tool to ensure this task is performed regularly without manual intervention.

2. Update Statistics

SQL Server uses statistics to estimate the distribution of data values in a table, which helps the query optimizer choose the most efficient execution plan. As data is modified, these statistics can become outdated, leading to suboptimal query plans and slower performance.

Best Practice:
- Regularly update statistics on your tables and indexes to ensure that the query optimizer has the most accurate information. Use the UPDATE STATISTICS command or enable SQL Server’s auto-update statistics feature.
- For large tables, use sampled statistics to balance performance with accuracy. Full scans of very large tables can be resource-intensive.
- If queries slow down unexpectedly, manually update statistics to resolve potential performance issues caused by outdated statistics.

3. Backup and Recovery Management

Regular backups are essential for data protection and business continuity. However, improper backup strategies can lead to bloated transaction logs, excessive disk usage, and even performance degradation during peak times.

Best Practice:
- Implement a full backup strategy based on your business’s recovery point objectives (RPO) and recovery time objectives (RTO). Schedule full backups regularly (e.g., daily) and transaction log backups more frequently (e.g., every 15-30 minutes) for critical databases.
- Use differential backups between full backups to reduce the load on storage and improve recovery times.
- Regularly test your backups by restoring them to a separate environment to ensure that they can be successfully recovered when needed.
- Ensure that backup schedules avoid peak activity times to prevent any impact on performance.

4. Transaction Log Management

SQL Server’s transaction log records every modification made to the database. If not properly managed, the transaction log can grow excessively large, consuming valuable disk space and degrading performance.

Best Practice:
- Use the Full Recovery Model for critical databases to ensure point-in-time recovery, but regularly back up the transaction logs to prevent them from growing too large.
- For less critical databases or databases that don’t require point-in-time recovery, consider using the Simple Recovery Model, which automatically truncates the transaction log after each checkpoint.
- Monitor transaction log size and schedule log backups frequently to avoid excessive growth. Use the sys.dm_db_log_space_usage DMV to monitor log space consumption.

5. Integrity Checks (DBCC CHECKDB)

Database corruption can occur for various reasons, such as hardware failures or improper shutdowns. SQL Server provides the DBCC CHECKDB command to detect and repair corruption in your databases.

Best Practice:
- Run DBCC CHECKDB regularly to ensure data integrity. This process checks for physical and logical corruption in database files.
- Schedule DBCC CHECKDB during off-peak hours to avoid performance impacts, as this operation can be resource-intensive.
- If DBCC CHECKDB identifies corruption, address the issue immediately. Use repair options like REPAIR_ALLOW_DATA_LOSS as a last resort, and restore from backups when possible.

6. Tempdb Maintenance

Tempdb is a shared system database that is heavily used by SQL Server for temporary objects, intermediate query results, and sorting. Over time, tempdb can become a performance bottleneck if it is not properly managed.

Best Practice:
- Ensure that tempdb has multiple data files, especially in high-concurrency environments. Best practice is to configure one data file per logical CPU core (up to 8 cores), which helps reduce contention on system pages (like PFS, GAM, and SGAM).
- Place tempdb on fast storage (preferably SSD or NVMe) to handle its high I/O workload.
- Regularly monitor tempdb space usage to avoid running out of space, which can lead to system crashes. Use sys.dm_db_task_space_usage and sys.dm_db_session_space_usage to track space consumption.

7. Cleanup of Unused or Outdated Data

Over time, databases may accumulate unused data, which can increase table sizes and slow down queries. Regularly cleaning up obsolete data ensures your database remains efficient.

Best Practice:
- Implement a data retention policy that defines how long data should be kept before being archived or deleted. This policy should reflect business requirements while keeping database sizes manageable.
- Periodically archive old data that is not frequently accessed into separate databases or storage systems.
- Use automated scripts to clean up old or unused records, freeing up space and reducing index bloat.

8. Monitor and Tune Performance

SQL Server provides several tools for monitoring performance, including Dynamic Management Views (DMVs) and Extended Events. Regular monitoring can help identify performance bottlenecks before they affect end-users.

Best Practice:
- Regularly monitor key performance metrics like CPU usage, memory usage, disk I/O, and query execution times to ensure the system is operating within optimal thresholds.
- Use SQL Server Profiler or Extended Events to capture detailed information about query performance and diagnose slow-running queries.
- Leverage DMV’s to analyze query patterns and suggest optimizations like new indexes.
- Continuously review and tune your queries, indexes, and database schema based on real-time performance data.

9. Automating Maintenance Tasks

Manually managing routine maintenance tasks can be time-consuming and prone to error. SQL Server provides built-in automation tools, such as SQL Server Agent, to schedule and manage maintenance operations.

Best Practice:
- Set up automated maintenance jobs for tasks like index rebuilding, statistics updates, transaction log backups, and integrity checks. This ensures that these critical operations are performed consistently and without manual intervention.
- Regularly review and adjust job schedules to avoid conflicts during peak business hours.
- Use maintenance plans in SQL Server Management Studio (SSMS) or third-party tools for more advanced scheduling and management of maintenance tasks.

Conclusion

Regular maintenance is essential for keeping your SQL Server database healthy and optimized for performance. Tasks like index rebuilding, updating statistics, managing transaction logs, performing integrity checks, and cleaning up obsolete data all contribute to the overall efficiency and reliability of your SQL Server environment. By automating maintenance operations, monitoring key performance metrics, and regularly tuning the system, you can ensure that your SQL Server databases continue to deliver optimal performance as your workload and data volumes grow.

Quick Backup/Restore Script

By bradyupton in Backup/Restore, Upgrades/Migrations December 9, 2022 0 Comment

Throughout the years I’ve probably performed hundreds if not thousands of migrations or “refreshes” to lower environments. It’s pretty simple to run a quick maintenance plan or SQL job to backup all databases, but the more complex piece is creating a restore script to point to that backup location and the backup name. It can be done, but it takes a little longer, IMO. A lot of backup file names contain numeric characters such as date or time of backup.

John Morehouse created a dynamic script that simplifies everything and it can be easily modified to fit particular needs. I’ve had this script bookmarked for years and use it at least once a week. Kudos to John for sharing this with the SQL community. I’ll link the script below, but here’s a quick snippet.

DECLARE @date CHAR(8)
SET @date = (SELECT CONVERT(char(8), GETDATE(), 112))

DECLARE @path VARCHAR(125)
SET @path = '\\UNCPath\Folder\'

;WITH MoveCmdCTE ( DatabaseName, MoveCmd )
          AS ( SELECT DISTINCT
                        DB_NAME(database_id) ,
                        STUFF((SELECT   ' ' + CHAR(13)+', MOVE ''' + name + ''''
                                        + CASE Type
                                            WHEN 0 THEN ' TO ''D:\SQLData\'
                                            ELSE ' TO ''E:\SQLTLogs\'
                                          END
                                        + REVERSE(LEFT(REVERSE(physical_name),
                                                       CHARINDEX('\',
                                                              REVERSE(physical_name),
                                                              1) - 1)) + ''''
                               FROM     sys.master_files sm1
                               WHERE    sm1.database_id = sm2.database_ID
                        FOR   XML PATH('') ,
                                  TYPE).value('.', 'varchar(max)'), 1, 1, '') AS MoveCmd
               FROM     sys.master_files sm2
  )
SELECT
	'BACKUP DATABASE ' + name + ' TO DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH COMPRESSION, COPY_ONLY, STATS=5',
	'RESTORE DATABASE '+ name + ' FROM DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH RECOVERY, REPLACE, STATS=5 ' + movecmdCTE.MoveCmd
FROM sys.databases d
	INNER JOIN MoveCMDCTE ON d.name = movecmdcte.databasename
WHERE d.name LIKE '%DatabaseName%'
GO

To change the backup/restore path you can modify this section of code to fit your needs:

SET @path = '\\UNCPath\Folder\'

Another modification I perform initially is changing the where clause, for example, if I don’t want system databases I can change:

WHERE d.name LIKE '%DatabaseName%'

WHERE d.database_id > 4

A lot of times I don’t need to move the data and log files to a new location because I’m overwriting other databases so I just comment all of the MoveCmdCTE CTE code out:

DECLARE @date CHAR(8)
SET @date = (SELECT CONVERT(char(8), GETDATE(), 112))

DECLARE @path VARCHAR(125)
SET @path = '\\UNCPath\Folder\'

--;WITH MoveCmdCTE ( DatabaseName, MoveCmd )
--          AS ( SELECT DISTINCT
--                        DB_NAME(database_id) ,
--                        STUFF((SELECT   ' ' + CHAR(13)+', MOVE ''' + name + ''''
--                                        + CASE Type
--                                            WHEN 0 THEN ' TO ''D:\SQLData\'
--                                            ELSE ' TO ''E:\SQLTLogs\'
--                                          END
--                                        + REVERSE(LEFT(REVERSE(physical_name),
--                                                       CHARINDEX('\',
--                                                              REVERSE(physical_name),
--                                                              1) - 1)) + ''''
--                               FROM     sys.master_files sm1
--                               WHERE    sm1.database_id = sm2.database_ID
--                        FOR   XML PATH('') ,
--                                  TYPE).value('.', 'varchar(max)'), 1, 1, '') AS MoveCmd
--               FROM     sys.master_files sm2
--  )
SELECT
	'BACKUP DATABASE ' + name + ' TO DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH COMPRESSION, COPY_ONLY, STATS=5',
	'RESTORE DATABASE '+ name + ' FROM DISK = ''' + @path + '' + name + '_COPY_ONLY_' + @date + '.bak'' WITH RECOVERY, REPLACE, STATS=5 ' --+ movecmdCTE.MoveCmd
FROM sys.databases d
	--INNER JOIN MoveCMDCTE ON d.name = movecmdcte.databasename
WHERE d.name LIKE '%DatabaseName%'
GO

There’s a lot of option here and this script is quick and easy.

Link to script: https://gist.github.com/airtank20/a826c6f37439482edd5070e8aaeb1ee1

Creating a Backup plan on SQL Express using Ola Hallengren’s scripts

By bradyupton in Backup/Restore, Maintenance March 23, 2016 0 Comment

SQL Express doesn’t have the SQL Server Agent so we can’t schedule jobs like normal. Follow this post to create a backup plan that will back up all the databases using Windows Task Scheduler.

Download CommandExecute – https://ola.hallengren.com/scripts/CommandExecute.sql
Download DatabaseBackup – https://ola.hallengren.com/scripts/DatabaseBackup.sql
Execute both of these stored procedures against the target server

Open Notepad on the target server and copy the following code changing the path to where the backups will be stored and the cleanup time. The cleanup time is specified in hours :

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backups',
@BackupType = 'FULL',
@Compress = 'Y', 
@CleanupTime = 48

Save the file with a .sql extension

Start the Task Scheduler by clicking on StartMenu/All Programs/Accessories/System Tools/Task Schedule
Click Create Basic Task to start the Scheduled Task Wizard
Type a name for the Task

Choose Daily from the scheduling options

Click Next, specify the information about the time to run the task. Set Start Time to an appropriate value when the load on the server is low. Set the recur every option to 1 day and click Next

Click Browse. Browse to SQLCMD.exe (C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.EXE) and then click Open
Type the following content to the Add arguments text box and then click Next
- –S ServerName –i “c:\SQLScripts\SQLBackups.sql”

Select the checkbox to Open the Advanced Properties for this task and click Finish

Check Run whether user is logged in or not and Run with highest privileges under Security Options then click OK.

**Note**

You may have to run this in the command prompt until it succeeds and copy exactly from CMD into the arguments section

Restore SQL Server Databases using DateTime functions

By bradyupton in Backup/Restore January 9, 2013 0 Comment

If you take full backups using SQL Server maintenance plans and let SQL Server use the default naming convention, you have probably noticed that usually you’ll have file name in the format of “database name + backup + date + time + .bak”. For example, a backup from the master database may look like this: “master_backup_2012_10_02_220558_8601773.bak”. It can be a challenge to script out automatic restores because the numbers on the end of the backup name constantly change. In this tip I will explain how to script out RESTORE DATABASE statements using DateTime functions.

Let’s say we have a folder full of backups like this:

Let’s say our boss wants us to restore Monday’s production backup (Alabama) every Friday afternoon to our development database (Tide). To accomplish this task, we can use the built-in SQL Server DateTime functions.

The below script will restore from the backup created on the first day of the current week. I’ve added comments to explain the code.

-- Declare variables
DECLARE @backup nvarchar(200) 
DECLARE @datebegin datetime
DECLARE @dateend datetime

-- Initalize variables
-- Set @datebegin equal to the first day of the current week
SELECT @datebegin = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
-- Set @dateend equal to the second day of the current week 
SELECT @dateend = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),1) 

-- Set @backup equal to query dependent on datebegin and dateend 
SELECT TOP 1 @backup = name + '.bak' 
FROM msdb..backupset 
WHERE database_name = 'Alabama' 
AND backup_start_date BETWEEN @datebegin AND @dateend 
AND type = 'D' -- D is for full backups
ORDER BY backup_start_date ASC 

USE [master]

-- Put DB in Single_User Mode
ALTER DATABASE [Tide] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

-- Restore DB using query from @backup variable
RESTORE DATABASE [Tide] FROM  DISK = @backup WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5 
GO

Below is a table of useful DateTime functions that you can use for the @datebegin and @dateend variables.

Day	SQL
Today	SELECT GETDATE()
Yesterday	SELECT DATEADD(d, -1, GETDATE())
First Day of Current Week	SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
Last Day of the Current Week	SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 6)
First Day of the Current Month	SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
Last Day of the Current Month	SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))
First Day of the Current Year	SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
Last Day of the Current Year	SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

Another example may include where you need to take a backup from the first of the month of the production database and restore it weekly to the development database. In this situation you can edit the @datebegin and @dateend variables:

--Set @datebegin equal to the first day of the current month
SELECT @datebegin = DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 

--Set @dateend equal to the second day of the current month
SELECT @dateend = SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),1)

Click here to view the rest of this post.