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

Identify the Recovery Model

Hi All,
In this post I will like to share a script which will generate a report about the detail of the databases on the server. The script can be modified to show more information as per requirement. I have used sp_helpdb to get all the information.

Script:
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
from #data
where name not in ('model','master','tempdb','msdb')
drop table #data


Hope this helps a lot of accidental DBA's to perform thier task of reporting.

Tushar

SQL Server Backup and Restore History

Hi All.. This is one place I know most you must have knowledge about. Just mentioning this so that you all have one stop for the information.

To identify the backup history for the databases on the server.

SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
GROUP BY a.name, b.type
ORDER BY a.name, b.type

To identify the restore history of the databases on the server.
select restore_history_id,restore_date,destination_database_name,
user_name,restore_type,backup_set_id from msdb..restorehistory


Tushar

SQL Agent Service running under which account

Well. Today morning got an unusual requirement to identify the account under which the SQL SErver Agent Service is running. I did identify the undocumented stored procedure that helped me out in the work but also had an interesting finding.

If you want to know the aoount under which the SQL Server is running the try the below mentioed query.

DECLARE @ServiceaccountName varchar(250)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccountName OUTPUT,
N'no_output'

SELECT @ServiceaccountName

This gives the service account name for the SQL Server.

To identify the service account for the SQL Server Agent use the undoucmented stored procedure as is mentioned below.
exec msdb..sp_get_sqlagent_properties
Note that there might be some risk factors involved while using undocumented procedures in production.

Tushar

RAID(Redundant Array of Independent Disks)

There are a lot of optimization in SQL Server performance on the Server level. We have to also consider the hardware level optimization possible. The biggest bottle neck in performance in any RDBMS is the I/O bottle neck. There is no standard solution to all the issues of bottle neck and the issues are customised for a particular database with the kind of data and the kind of operations are most frequent in the database. There are a lot of research going around in this area and solid state devices are playing an imporatant part in it. In this article I will focus on the RAID array. This article will be the preface to how and when to choose the specific kind of RAID array.


RAID is a technology that provides increased storage functions and reliability through redundancy. This is achieved by combining multiple disk drive components into a logical unit, where data is distributed across the drives in one of several ways called "RAID levels".

to be continued...

Log File Accidently Deleted!!

Watch out this section for steps to be taken when log file is deleted.

LazyWriter

Well this is no story about a writer who is lazy. Ironically they actually are : ) But then today I thought to document something about SQL Server which I think about lot of times and then as usual forget to document.

Now to start with lazy writer is a process in sql server which helps in clearing the buffer cache. The buffer cache as the name suggests are the cache pages which sql sever uses to load the data from data pages for manipulation. Now Lazy Writer is the process which flushes out batches of dirty, aged buffers and makes them available to user processes. The lazy writer eliminates the need of perfomrning frequent checkpoint in order to create available buffers.

The process of clearing the buffer cache is not that easy. By that I mean it's calculative. Now each page in the buffer cache has a refernce counter and a bit to note whether it is a dirty page. (Now a dirty page is one which has modified data which has yet not been written back to disk.) Comming back to the calculative part the reference counter that I mentined associated to the page gets decremented each time a buffer scan happens. Now if the count is reduced to 0 then the page is written back to the data file page and the buffer page is emptied. This job is done by the lazy writer.

Basically LazyWriters ensure two things:
a) There are adequate resources in buffer pool for the sql server to use.
b) Monitor the usage of commited memory by the buffer pool and ajust it as necessary so that enough physical memory remains free to prevent windows from paging.

LazyWriter can adjust the number of buffers in the buffer pool if the dynamic memory management is enabled in SQL Server. SQL Server estimates the adequate number of buffer pools depending upon the system activity and the number of stalls.(Stall happens when the system has to wait for a buffer free page when a request for memory is raised)

Tushar