SQL Server 2008 R2 Performance Tuning
#1. Check fragmentation for indexing.
Check particular all index details based on selected database.
SELECT dbschemas.[name] AS 'Schema' ,dbtables.[name] AS 'Table' ,dbindexes.[name] AS 'Index' ,indexstats.avg_fragmentation_in_percent ,indexstats.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id WHERE indexstats.database_id = DB_ID() ORDER BY indexstats.avg_fragmentation_in_percent DESC
#2. Shrink Database log file.
Shrinking database log file when SQL Server log file size increased.
DBCC SHRINKFILE ( < DB LOG FILENAME > ,1 ) BACKUP LOG < DB NAME > WITH TRUNCATE_ONLY DBCC SHRINKFILE ( < DB LOG FILENAME > ,1 )
#3. Check last backup details for particular database.
Check database specific backup details using this query.
USE MSDB;
DECLARE @DatabaseName SYSNAME
SET @DatabaseName = '<DB Name>'
SELECT DISTINCT d3.user_name
,d3.name AS backup_name
,d3.description
,(datediff(ss, d3.backup_start_date, d3.backup_finish_date)) / 60.0 AS duration
,d3.backup_start_date
,d3.backup_finish_date
,d3.type AS [type]
,CASE
WHEN (d3.backup_size / 1024.0) < 1024
THEN (d3.backup_size / 1024.0)
WHEN (d3.backup_size / 1048576.0) < 1024
THEN (d3.backup_size / 1048576.0)
ELSE (d3.backup_size / 1048576.0 / 1024.0)
END AS backup_size
,CASE
WHEN (d3.backup_size / 1024.0) < 1024
THEN 'KB'
WHEN (d3.backup_size / 1048576.0) < 1024
THEN 'MB'
ELSE 'GB'
END AS backup_size_unit
,d3.first_lsn
,d3.last_lsn
,CASE
WHEN d3.differential_base_lsn IS NULL
THEN 'Not Applicable'
ELSE convert(VARCHAR(100), d3.differential_base_lsn)
END AS [differential_base_lsn]
,b6.physical_device_name
,b6.device_type AS [device_type]
,d3.recovery_model
,d3.backup_set_id
FROM sys.databases d1
INNER JOIN backupset d3 ON (d3.database_name = d1.name)
LEFT OUTER JOIN backupmediaset b5 ON (d3.media_set_id = b5.media_set_id)
LEFT OUTER JOIN backupmediafamily b6 ON (b6.media_set_id = b5.media_set_id)
WHERE (d1.name = @DatabaseName)
ORDER BY backup_start_date DESC
,d3.backup_set_id
,b6.physical_device_name
