AI SQL Tuner Recommendations

Critical Confidence: 99%

Establish a Comprehensive Backup Plan

The current backup state represents the single greatest risk to this server. Multiple databases have zero backup history, meaning any storage failure results in complete, permanent data loss.

Database	Recovery Model	Last Full Backup	Status
master	SIMPLE	Never	⛔ No backup
model	FULL	Never	⛔ No backup
msdb	SIMPLE	Never	⛔ No backup
DigitsSolver	FULL	Never	⛔ No backup
tpcc	FULL	Never	⛔ No backup
CRM	FULL	Never	⛔ No backup
tpch10	FULL	Never	⛔ No backup
tpch	FULL	Nov 2025 (147 days ago)	⚠️ Stale
WideWorldImporters	SIMPLE	Oct 2022 (1,289 days ago)	⛔ 3.5+ years old
SQLStorm	SIMPLE	Oct 2025 (175 days ago)	⚠️ Stale

Recommended Actions:

• Immediately take full backups of all databases.
• Establish a maintenance plan or SQL Agent jobs for regular backups (daily full, or weekly full + daily differential).
• Enable backup checksum validation for all backups.
• Store backups on a separate physical volume or off-site location.
• Periodically test restores to verify backup integrity.

Immediate Backup Script (run all full backups now):

-- Enable backup checksum as a server default
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'backup checksum default', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

-- Full backups for all databases (adjust paths as needed)
BACKUP DATABASE [master] TO DISK = N'C:\Backups\master_full.bak'
  WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

BACKUP DATABASE [msdb] TO DISK = N'C:\Backups\msdb_full.bak'
  WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

BACKUP DATABASE [model] TO DISK = N'C:\Backups\model_full.bak'
  WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

BACKUP DATABASE [WideWorldImporters] TO DISK = N'C:\Backups\WideWorldImporters_full.bak'
  WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

BACKUP DATABASE [SQLStorm] TO DISK = N'C:\Backups\SQLStorm_full.bak'
  WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

BACKUP DATABASE [DigitsSolver] TO DISK = N'C:\Backups\DigitsSolver_full.bak'
  WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

BACKUP DATABASE [tpcc] TO DISK = N'C:\Backups\tpcc_full.bak'
  WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

BACKUP DATABASE [tpch] TO DISK = N'C:\Backups\tpch_full.bak'
  WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

BACKUP DATABASE [tpch10] TO DISK = N'C:\Backups\tpch10_full.bak'
  WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

BACKUP DATABASE [CRM] TO DISK = N'C:\Backups\CRM_full.bak'
  WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

Enable backup compression as the server default:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

Critical Confidence: 99%

DBCC CHECKDB Has Never Been Run on 6 of 7 User Databases

The "Jan 1 1900" date indicates DBCC CHECKDB has never been executed on these databases since they were created or attached to this instance. Without regular integrity checks, corruption can go undetected until it's too late to recover.

Database	Last CHECKDB	Status
WideWorldImporters	Never (1900-01-01)	⛔ Never checked
SQLStorm	Never (1900-01-01)	⛔ Never checked
tpcc	Never (1900-01-01)	⛔ Never checked
tpch	Never (1900-01-01)	⛔ Never checked
CRM	Never (1900-01-01)	⛔ Never checked
tpch10	Never (1900-01-01)	⛔ Never checked
DigitsSolver	Apr 12, 2026 (6 days ago)	✅ OK

Recommended Schedule:

• Weekly: Run DBCC CHECKDB on all user databases (or at minimum, before each full backup).
• Immediately: Run CHECKDB now on all databases that have never been checked.

-- Run immediately on all databases with no history
DBCC CHECKDB (N'WideWorldImporters') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;
DBCC CHECKDB (N'SQLStorm') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;
DBCC CHECKDB (N'tpcc') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;
DBCC CHECKDB (N'tpch') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;
DBCC CHECKDB (N'CRM') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;
DBCC CHECKDB (N'tpch10') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;
DBCC CHECKDB (N'DigitsSolver') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;

Consider implementing Ola Hallengren's Maintenance Solution for automated CHECKDB, backups, and index maintenance.

Critical Confidence: 98%

Databases in FULL Recovery Without Log Backups

Five user databases and the model system database are in FULL recovery model but have no transaction log backups. This means:

• Transaction logs will grow continuously and may eventually fill the disk.
• There is no point-in-time recovery capability despite paying the overhead of FULL recovery.
• The FULL recovery model provides no benefit without log backups.

Database	Recovery Model	Log Backups
model	FULL	None
DigitsSolver	FULL	None
tpcc	FULL	None
tpch	FULL	None
CRM	FULL	None
tpch10	FULL	None

Recommended Actions (choose one per database):

Option A: If point-in-time recovery is NOT required, switch to SIMPLE recovery:

-- Switch to SIMPLE if point-in-time recovery is not needed
-- (appropriate for dev/test workloads like tpcc, tpch, tpch10)
ALTER DATABASE [tpcc] SET RECOVERY SIMPLE;
ALTER DATABASE [tpch] SET RECOVERY SIMPLE;
ALTER DATABASE [tpch10] SET RECOVERY SIMPLE;
ALTER DATABASE [DigitsSolver] SET RECOVERY SIMPLE;
ALTER DATABASE [CRM] SET RECOVERY SIMPLE;

Option B: If point-in-time recovery IS required, implement regular log backups:

-- Example: Log backup every 30 minutes (adjust as needed)
-- Create a SQL Agent job for each database in FULL recovery
BACKUP LOG [CRM] TO DISK = N'C:\Backups\CRM_log.trn'
  WITH INIT, CHECKSUM, COMPRESSION, STATS = 10;

Note: Since this appears to be a development workstation (Windows 10 Home, Developer Edition), SIMPLE recovery is likely the best choice for most of these databases. The model database's recovery model serves as the template for new databases, so consider setting it to SIMPLE as well to avoid this issue recurring.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'remote admin connections', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

-- Optional: Set minimum memory to prevent excessive memory release
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'min server memory (MB)', 4096;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

-- Step 1: Ensure Query Store is capturing baselines (already active)
-- Step 2: Upgrade compatibility level
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 160;

-- Step 3: Monitor Query Store for regressions over the next few days
-- If regressions occur, use Query Store plan forcing to fix them

ALTER DATABASE [WideWorldImporters] SET QUERY_STORE (
    FLUSH_INTERVAL_SECONDS = 900,
    MAX_PLANS_PER_QUERY = 200,
    MAX_STORAGE_SIZE_MB = 1000
);

-- Enable memory-optimized tempdb metadata (requires restart)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'tempdb metadata memory-optimized', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
-- Note: Requires SQL Server restart to take effect

-- Disable legacy remote access (if not using linked servers with RPC)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'remote access', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
-- Note: Requires SQL Server restart to take effect

-- Enable blocked process reporting (alerts for blocks lasting > 10 seconds)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'blocked process threshold (s)', 10;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

-- Then set up an Extended Events session or Alert to capture blocked process reports