SQL server 2008 R2 Performance tuning

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