Database performance optimization checkList

1.       Maintenance of Health Checkup of Production Database on Daily basis.

2.       Implementation of Maintenance Jobs to be scheduled at regular intervals (Daily and Weekly)

a.       Database Integrity Check- Weekly

b.      Index Defragmentation – Rebuild and Reorganizing- Daily

c.       Check for Missing Indexes (to be performed manually)- Weekly

d.      Check for Unused and Duplicate Indexes (to be performed manually)- Weekly

e.      Update Statistics

f.        Check Wait Stats and network latencies for Data loads (to be performed manually)- Weekly

g.       Purging wherever applicable- Weekly

h.      Database Shrinking wherever applicable- Weekly

i.         Checking for costliest queries and their improvement recommendations- weekly

j.        Db growth and disk space checks

k.       Deadlocks and IO Bottlenecks

l.         Checking wait stats.

3.       Server Settings Recommendations as per Microsoft’s Best Practices.

a.       Setting up Minimum and Maximum memory allocation on Sql server.

b.      Analyze the best practice for changing Fill Factor

c.       Analyze the best practice to implement optimize adhoc workload value.

d.      Changing the MAXDOP setting as per available CPUs on server.

4.       Database Settings Recommendations as per Microsoft’s Best Practices.

a.       Setting up database initial size, Auto growth and Max growth.

b.      Checking for Recovery Model.

c.       Checking for Compatibility level

d.      Analysis and implementation of best practices of Automatic options like Auto Create Statistics.

e.      Checking for Page Verify as CheckSum.

f.        Checking for state options.

g.       Setting up multiple mdf files for user database wherever applicable.

h.      Implementation of best practices of TempDB.

i.         Checking of Disk space available for database in drives.

j.        Setting up c drive space as per available RAM.

PS: All Server and Database settings are one time activity and need to be performed in production downtime and also, sql services needs to be restarted after completion of these activities.

Sql database space details query

Find Sql database recovery model, Space used and space freed by MDF and LDF files on all databases.

------------------------------Data file size----------------------------
IF EXISTS (
  SELECT *
  FROM tempdb.sys.all_objects
  WHERE NAME LIKE '%#dbsize%'
  )
 DROP TABLE #dbsize

CREATE TABLE #dbsize (
 Dbname VARCHAR(30)
 ,dbstatus VARCHAR(20)
 ,Recovery_Model VARCHAR(10) DEFAULT('NA')
 ,file_Size_MB DECIMAL(20, 2) DEFAULT(0)
 ,Space_Used_MB DECIMAL(20, 2) DEFAULT(0)
 ,Free_Space_MB DECIMAL(20, 2) DEFAULT(0)
 )
GO

INSERT INTO #dbsize (
 Dbname
 ,dbstatus
 ,Recovery_Model
 ,file_Size_MB
 ,Space_Used_MB
 ,Free_Space_MB
 )
EXEC sp_msforeachdb 'use [?];
  select DB_NAME() AS DbName,
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) , 
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')), 
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB 
from sys.database_files  where type=0 group by type'
GO

-------------------log size--------------------------------------
IF EXISTS (
  SELECT *
  FROM tempdb.sys.all_objects
  WHERE NAME LIKE '#logsize%'
  )
 DROP TABLE #logsize

CREATE TABLE #logsize (
 Dbname VARCHAR(30)
 ,Log_File_Size_MB DECIMAL(20, 2) DEFAULT(0)
 ,log_Space_Used_MB DECIMAL(20, 2) DEFAULT(0)
 ,log_Free_Space_MB DECIMAL(20, 2) DEFAULT(0)
 )
GO

INSERT INTO #logsize (
 Dbname
 ,Log_File_Size_MB
 ,log_Space_Used_MB
 ,log_Free_Space_MB
 )
EXEC sp_msforeachdb 'use [?];
  select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB 
from sys.database_files  where type=1 group by type'
GO

--------------------------------database free size
IF EXISTS (
  SELECT *
  FROM tempdb.sys.all_objects
  WHERE NAME LIKE '%#dbfreesize%'
  )
 DROP TABLE #dbfreesize

CREATE TABLE #dbfreesize (
 NAME VARCHAR(50)
 ,database_size VARCHAR(50)
 ,Freespace VARCHAR(50) DEFAULT(0.00)
 )

INSERT INTO #dbfreesize (
 NAME
 ,database_size
 ,Freespace
 )
EXEC sp_msforeachdb 
 'use ?;SELECT database_name = db_name()
    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
    ,''unallocated space'' = ltrim(str((
                CASE 
                    WHEN dbsize >= reservedpages
                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
                    ELSE 0
                    END
                ), 15, 2))
FROM (
    SELECT dbsize = sum(convert(BIGINT, CASE 
                    WHEN type = 0
                        THEN size
                    ELSE 0
                    END))
        ,logsize = sum(convert(BIGINT, CASE 
                    WHEN type <> 0
                        THEN size
                    ELSE 0
                    END))
    FROM sys.database_files
) AS files
,(
    SELECT reservedpages = sum(a.total_pages)
        ,usedpages = sum(a.used_pages)
        ,pages = sum(CASE 
                WHEN it.internal_type IN (
                        202
                        ,204
                        ,211
                        ,212
                        ,213
                        ,214
                        ,215
                        ,216
                        )
                    THEN 0
                WHEN a.type <> 1
                    THEN a.used_pages
                WHEN p.index_id < 2
                    THEN a.data_pages
                ELSE 0
                END)
    FROM sys.partitions p
    INNER JOIN sys.allocation_units a
        ON p.partition_id = a.container_id
    LEFT JOIN sys.internal_tables it
        ON p.object_id = it.object_id
) AS partitions'

-----------------------------------
IF EXISTS (
  SELECT *
  FROM tempdb.sys.all_objects
  WHERE NAME LIKE '%#alldbstate%'
  )
 DROP TABLE #alldbstate

CREATE TABLE #alldbstate (
 dbname VARCHAR(25)
 ,DBstatus VARCHAR(25)
 ,R_model VARCHAR(20)
 )

--select * from sys.master_files
INSERT INTO #alldbstate (
 dbname
 ,DBstatus
 ,R_model
 )
SELECT NAME
 ,CONVERT(VARCHAR(20), DATABASEPROPERTYEX(NAME, 'status'))
 ,recovery_model_desc
FROM sys.databases

--select * from #dbsize
INSERT INTO #dbsize (
 Dbname
 ,dbstatus
 ,Recovery_Model
 )
SELECT dbname
 ,dbstatus
 ,R_model
FROM #alldbstate
WHERE DBstatus <> 'online'

INSERT INTO #logsize (Dbname)
SELECT dbname
FROM #alldbstate
WHERE DBstatus <> 'online'

INSERT INTO #dbfreesize (NAME)
SELECT dbname
FROM #alldbstate
WHERE DBstatus <> 'online'

SELECT d.Dbname
 ,d.dbstatus
 ,d.Recovery_Model
 ,(file_size_mb + log_file_size_mb) AS DBsize
 ,d.file_Size_MB
 ,d.Space_Used_MB
 ,d.Free_Space_MB
 ,l.Log_File_Size_MB
 ,log_Space_Used_MB
 ,l.log_Free_Space_MB
 ,fs.Freespace AS DB_Freespace_MB
FROM #dbsize d
INNER JOIN #logsize l ON d.Dbname = l.Dbname
INNER JOIN #dbfreesize fs ON d.Dbname = fs.NAME
ORDER BY Dbname

Finding recovery model type for all sql databases in a server.

Finding recovery model type for all sql databases in a server.

SELECT NAME AS [Database Name]
 ,recovery_model_desc AS [Recovery Model]
FROM sys.databases
GO

Fixing database is in use error while restoring sql database.

Following is the one of the most useful query while restoring database. It is used when we get error: Database is in use while restoring db from the backup.
Simply, run below query and then again try to restore database. This time, you will able to restore it. Here is the magical query:

------------------------------------------------------------
--<Config>
------------------------------------------------------------ 
DECLARE @DatabaseName VARCHAR(max)

SET @DatabaseName = 'yourdbname'

------------------------------------------------------------
--</Config>
------------------------------------------------------------
USE master

DECLARE @processes TABLE (ID INT)
DECLARE @databaseID INT

SELECT @databaseID = dbid
FROM sys.sysdatabases
WHERE NAME = @DatabaseName

INSERT INTO @processes
SELECT spid
FROM sys.sysprocesses
WHERE dbid = @databaseid

WHILE EXISTS (
  SELECT TOP (1) *
  FROM @processes
  )
BEGIN
 DECLARE @Process INT
 DECLARE @statement VARCHAR(max)

 SELECT TOP 1 @process = id
 FROM @processes

 SET @statement = 'kill ' + cast(@process AS VARCHAR(20))

 EXEC (@statement)

 DELETE TOP (1)
 FROM @processes
END

#TIP: Query to find the last sql server restart using DMVs

Following script will help us to find the last sql server restart datetime.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Find the dependent sql objects on a defined object

Find the dependent sql objects on a defined object:

Below query shows the dependent sql objects like tables, views etc. of defined object (here. YourObject).


SELECT referencing_schema_name, referencing_entity_name,referencing_id, referencing_class_desc, is_caller_dependentFROM sys.dm_sql_referencing_entities ('YourObject', 'OBJECT');GO

determining index fragmentation and business rules.

Index Fragmentation: It plays an important role in improving the performance of the sql server. The indexes get fragments as more and more records are inserted and updated to the respective table. so in order to check the fragmentation, use the following query to get all information: 

PS: Business Rules defined by Microsoft to determine the fragmentation of the indexes. 
1. If the index fragmentation average is greater than and equal to 30 percent, then rebuild/ recreate the index to reduce fragmentation.
2. If the index fragmentation average is between 5 to 30 percent then reorganize a respective index.
3. If the index fragmentation average is below 5 percent, then no action is required.

Also, in general for page_count, we use to practice page_count=>2000.

So, for example, if certain index at avg_Fragmentation_percent>30 but page_count= 100 (assuming), then we can ignore that index.

Query to determine fragmentation is defined below: 

SELECT ps.database_id
 ,ps.OBJECT_ID
 ,ps.index_id
 ,b.NAME
 ,ps.avg_fragmentation_in_percent
 ,ps.avg_fragment_size_in_pages
 ,ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
 AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
 AND ps.avg_fragmentation_in_percent >= 5
 AND page_count >= 2000
 AND b.NAME IS NOT NULL
ORDER BY ps.avg_fragmentation_in_percent DESC
GO