optimisng database maintenance plans

Fisrt step will be to identify the database files read,writes,i/o stats. For that we may utilise the below metioned queries.

1. To list NumberReads,NumberWrites,IOStallMS and Recovery_Model use the below mentioned script.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


CREATE TABLE #tbl_DB_FileStats
(
DatabaseName varchar(255),
FileName varchar(255),
NumberReads numeric(18,0),
NumberWrites numeric(18,0),
IOStallMS numeric(18,0)
)



EXECUTE master.dbo.sp_msforeachdb '
DECLARE @DBID int;
USE [?];
SET @DBID = DB_ID();
INSERT INTO #tbl_DB_FileStats
SELECT
DB_NAME(DBID) AS DatabaseName,
FILE_NAME(FileID) AS FileName,
NumberReads,
NumberWrites,
IoStallMS
FROM ::fn_virtualfilestats(@DBID, -1);'





create table #data (name sysname , db_size nvarchar(13), owner sysname, dbid smallint, created datetime, status varchar(600),
compatability_level tinyint)
insert into #data Exec sp_executesql N'sp_helpdb'

select name as 'Database',db_size,owner,substring(status,patindex ('%Recovery%',status)+9,patindex ('%, Ver%',status)- patindex ('%Recovery%',status)-9 ) as Recovery_Model
into #t1
from #data
where name not in ('model','master','tempdb','msdb')

select DatabaseName as 'Database' ,NumberReads,NumberWrites,IOStallMS
into #t2
from #tbl_DB_FileStats
where FileName not like '%log'
and DatabaseName not in ('model','master','tempdb','msdb')




select t1.[Database],t1.db_size,t1.[owner],t2.NumberReads,t2.NumberWrites,t2.IOStallMS,t1.Recovery_Model
from #t1 t1
inner join #t2 t2 on t1.[Database]=t2.[Database]




drop table #tbl_DB_FileStats
drop table #data
drop table #t1
drop table #t2

2. Database Index Fragmentation Information use the below mentioed script

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


CREATE TABLE #tbl_DB_IDX_Stats
(
[Database] sysname,
[Object] sysname,
[Index_ID] int,
[Index_Name] sysname,
avg_fragmentation_in_percent numeric(18,0)
)


EXECUTE master.dbo.sp_msforeachdb '
DECLARE @DBID int;
USE [?];
SET @DBID = DB_ID();
INSERT INTO #tbl_DB_IDX_Stats
SELECT db_name(ps.database_id) as [Database], Object_name(ps.OBJECT_ID) as [Object],
ps.index_id as [Index_ID], b.name as [Index_Name],ps.avg_fragmentation_in_percent as avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@DBID, 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 avg_fragmentation_in_percent>70
and name is not null
and @DBID not in (1,2,3,4)
ORDER BY ps.OBJECT_ID;'

select * from #tbl_DB_IDX_Stats

drop table #tbl_DB_IDX_Stats


Tushar