SQL Server Checklist
SQL Server environment
Check the memory usage via Task Manager. It should be below 90%. Above 94% is particularly critical.
o Check the hard drives using Explorer. None of them should be full.
SQL Server configuration (requires SSMS[1])
To open the SQL Server configuration, right-click on the SQL Server in the Object Explorer and open the settings.
o Check the value for "Maximum server memory (in MB)" on the "Memory" tab.
o The value should be less than the maximum working memory of the SQL server.
For example, if the server has 30 GB, it should say around 20 GB here.
o Check the "Advanced" tab, "Cost Threshold for Parallelism" value. Default value: 5
o Check the "Advanced" tab, "Max Degree of Parallelism" value. Default value: 0
* This setting specifies the maximum number of CPUs that may be used for an SQL operation
o If there are many simultaneous accesses to the SQL server, a different value such as 2 or 4 could be tested here.
Database configuration
To check the settings of a database configuration, right-click on the database and open Settings.
o Check the "Autogrowth / Maxsize" value on the "Files" tab. (Data and log file).
o This should always be a fixed value (do not use "In Percent").
o The fixed size should be at least 1000 MB.
o Check the "Size (MB)" value for the log file in the "Files" tab.
if this is much too large (e.g., > 50 GB), it indicates that something may be wrong with the maintenance schedule.
o Check the value for "Recovery model" on the "Options" tab.
o If set to "Full," it is recommended to have a good maintenance plan, otherwise there is a risk that the LOG file could escalate in size.
o If set to "Simple," the memory in the LOG file is automatically released as soon as the transaction is completed.
o Check the value for "Compatibility level" on the "Options" tab. This should correspond to the installed SQL Server version.
o The following values should be set on the "Options" tab:
o Auto Create Statistics: True
o Auto Shrink: False
o Auto Update Statistics: True
Maintenance schedule
Even if database backups are performed by other tools (e.g., VEEAM), it is still recommended to have at least one maintenance plan (which takes care of database maintenance).
We can divide the main tasks of maintenance plans into three parts:
1. Complete database backup
2. Transaction log backups (only required if Recovery Mode = Full)
3. Maintenance of databases
o Check whether maintenance plans are available
o Check whether SQL Server Agent is active (this executes the maintenance plans)
o Check the configuration of maintenance plans
o Ensure that there are no "Shrink Database Task" building blocks!
not necessary if the DB is set correctly.
o Shrink jobs only lead to unnecessary fragmentation of the database.
Sample maintenance plan for data maintenance
When maintaining data, the two modules "Rebuild Index Task" and "Update Statistics Task" are particularly important. The "Reorganize Index Task" is not needed because Rebuild already takes care of everything.
Sample maintenance schedule for database backup
the "Check Database Integrity Task" should be the first priority! This ensures that there are no errors in the database.
o For the "Back Up Database Task," "Verify backup integrity" should be enabled to ensure that there are no errors in the backup file.
Sample maintenance plan for transaction log backup
If the Recover Mode is set to "Full," it is recommended to create an extra job for regular transaction log backups. In the example, a backup is created every hour.
Example of a complete maintenance plan (Recover Mode Simple)
1. Check Database Integrity Task: Ensures that there are no errors in the database.
2. Back Up Database Task: Creates a full backup.
3. Rebuild Index Task: Rebuilds the indexes.
4. Update Statistics Task: Updates the index statistics.
5. Maintenance Cleanup Task: Deletes older backup files (in the example, older than 1 week).
6. Maintenance Cleanup Task 1: Delete older maintenance plan text reports.
7. History Cleanup Task: Deletes older history entries.
This maintenance plan works well when the database recovery mode is set to Simple. If the mode were set to Full, one day between full backups could be too long, which could result in a very large LOG file.
IMPORTANT: If recovery mode is set to "Simple," backing up the transaction log will result in an error.
Advanced tests
To check the health of the database, detailed checks can be performed using SQL statements.
Fragmentation of indexes
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), ZERO, ZERO, ZERO, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not zero
AND DDIPS.avg_fragmentation_in_percent > 0
AND DDIPS.page_count > 1000
ORDER BY DDIPS.avg_fragmentation_in_percent desc
If several/many tables have fragmentation > 30%, this indicates that there is no maintenance plan in place.
Index statistics
SELECT sp.stats_id,
stat.name,
filter_definition,
last_updated,
rows,
rows_sampled,
steps,
unfiltered_rows,
modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
INNER JOIN sys.tables t ON t.object_id = stat.object_id
WHERE t.type = 'U'
AND last_updated IS EMERGENCY ZERO
ORDER BY last_updated
There should be no tables with an older last_updated value here. Otherwise, this indicates that either there is no maintenance plan, or the existing one is not running, or there is no job for statistics updates.
[1] SSMS = Microsoft SQL Server Management Studio