SQL DB Performance Tweaking
-------------------------------Rebuild Index of table-----------------------------
1.
USE <<DatabaseName>>;
GO
ALTER INDEX ALL ON DatabaseName.TableName
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
2.
sp_updatestats
----------------------------Shrink Database-----------------------------------------------------
ALTER DATABASE Insight SET RECOVERY SIMPLE
DBCC SHRINKDATABASE (Insight);
ALTER DATABASE Insight SET RECOVERY FULL
------------------------SP to Shrink Database Log-------------------------------------------------------------------------
USE master;
GO
CREATE PROCEDURE dbo.ShrinkLogAndResetRecovery
@DatabaseName SYSNAME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @LogFileName SYSNAME;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @BackupPath NVARCHAR(500);
DECLARE @DateSuffix NVARCHAR(20);
-- Build date suffix like: 20250630_1845
SET @DateSuffix = CONVERT(VARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '');
-- 1. Change to SIMPLE recovery model
SET @SQL = 'ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY SIMPLE';
EXEC sp_executesql @SQL;
-- 2. Get the logical name of the log file
SET @SQL = '
SELECT @LogFileName_OUT = name
FROM [' + @DatabaseName + '].sys.database_files
WHERE type_desc = ''LOG''';
EXEC sp_executesql @SQL, N'@LogFileName_OUT SYSNAME OUTPUT', @LogFileName OUTPUT;
-- 3. Shrink the log file to 1024 MB
SET @SQL = 'USE [' + @DatabaseName + ']; DBCC SHRINKFILE ([' + @LogFileName + '], 1024)';
EXEC sp_executesql @SQL;
-- 4. Change back to FULL recovery model
SET @SQL = 'ALTER DATABASE [' + @DatabaseName + '] SET RECOVERY FULL';
EXEC sp_executesql @SQL;
-- 5. Take full backup
SET @BackupPath = 'I:\SQLBackups\' + @DatabaseName + '_FullBackup_' + @DateSuffix + '.bak';
SET @SQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO DISK = N''' + @BackupPath + ''' WITH INIT, FORMAT';
EXEC sp_executesql @SQL;
PRINT 'Shrink and recovery reset complete for database: ' + @DatabaseName;
END;