DMVs in SQL Server 2005 - PART I

Introduction:

Microsoft has brought lot of new changes to the way how SQL Server works and handles things in SQL Server 2005. These changes have been incorporated not only to the developers but also to those who administer SQL Server databases and instances. One of the major changes that have been done is a new feature that comes with SQL Server 2005 that is called as Dynamic views. Let’s get to know what it exactly is and does.

What’s a Dynamic View:

Dynamic Management Views and functions are designed to give detailed information about internal working of SQL server 2005. The information gives a insight of what’s going inside SQL server 2005 like information on health of server and databases which can be used to monitor performance, diagnose problems and tune databases. They are designed to be used instead of system tables and functions in SQL server 2000.

DMV’s are of both views and table-valued functions. There are two types of DMV’s.
1. Server scope DMV
2. Database scope DMV

Server scope DMV’s apply to the entire server and are stored in master database whereas Database scope DMV’s are specific to that database.

Dynamic Management views can be identified with their name starting with dm_. They reside in the master database.

Dynamic Management Views and Functions are categorized into twelve as given below.

1. CLR related DMV’s
2. I/O related DMV’s
3. Database Mirroring related DMV’s
4. Query Notifications related DMV’s
5. Database related DMV’s
6. Replication related DMV’s
7. Execution related DMV’s
8. Service Broker related DMV’s
9. Full-Text Search related DMV’s
10. SQL Server Operating System related DMV’s
11. Index related DMV’s
12. Transaction related DMV’s

DMV’s are owned by the schema sys and is to be referenced in two part naming convention as sys.DMV name.

Permission Required:

1. Select permission required on the Dynamic Management View/Functions
2. View server state permission for server scope DMV’s
3. View database state permission for database scope DMV’s

Important:

Microsoft reserves the right to change/drop any DMV’s and DMF’s in future releases.

What's to come next:
------------------------------
1. Some Useful DMV's for Real Time
2. Transaction, IO and OS Related DMV's
3. Some Useful scripts using DMv's and DMF's

So keep checking and update yourself.

Script to backup table @ database level

This script is used to take a backup of the table @ the database level. This is used in the case where the number of records in the table keeps on growing and the historical records are only used for reporting rather than transactional purpose. This script when executed creates a backup of the table with name TABLENAME_BKP_DATE. If this script is executed more than once on the same day, the already backed up table is renamed as TABLENAME_BKP_DATETIME and new backup table is created.

Script:
******
create procedure TABLEARCHIVE
as
BEGIN
SET NOCOUNT ON
/*******************************************************************************************************/
-- This Procedure was written by Sugesh Kumar Rajendran - Date: 31 July 2006
-- This Procedure is used to back up the table
-- Which will have the name TABLENAME_BKP_getdate() when this procedure is run
-- If this procedure is run more than once in the same day the already existing table will be backed up
-- With TIMESTAMP in it
-- No Input Parameters and No Output Parameters
/*******************************************************************************************************/
declare @sql nvarchar(200)
declare @sql1 nvarchar(200)
declare @ddate int
declare @dmonth varchar(3)
declare @dyear int
declare @dhour int
declare @dmin int
declare @bkptable varchar(100)
declare @bkptable1 varchar(100)
declare @date datetime
declare @bsql nvarchar(200)
declare @count int
declare @count1 int
/* GETTING THE DATE MONTH AND YEAR INTO SEPERATE VARIABLES*/
set @ddate = (select DATEPART(dd,getdate()))
set @dmonth = (select DATENAME(month,getdate()))
set @dyear = (select DATEPART(yyyy,getdate()))
set @dhour = (select DATEPART(hh,getdate()))
set @dmin = (select DATEPART(mi,getdate()))
/* DEFINING THE NAME OF THE BACKUP TABLE*/
select @bkptable = 'TABLENAME_BKP_'+cast(@dyear as varchar)+UPPER(@dmonth)+cast(@ddate as varchar)
/* CHECKING IF THE BACKUP TABLE ALREADY EXISTS IN THE SYSTEM CATALOG*/
/* IF SO THIS TABLE IS BACKED UP TO AN ALTERNATE TABLE TO MAINTAIN HISTORY*/
/* AND THE BACKUP TABLE IS DROPPED SO THAT IT CAN BE CREATED AGAIN WITH SAME NAME*/
set @bsql = 'DROP TABLE '+@bkptable
set @count1 = (select count(*) from sysobjects where name like @bkptable)
if @count1 = 1
BEGIN
select @bkptable1 = 'TABLENAME_BKP_'+cast(@dyear as varchar)+UPPER(@dmonth)+cast(@ddate as varchar)+cast(@dhour as
varchar)+cast(@dmin as varchar)
set @sql1 = 'select * into '+@bkptable1+' from '+@bkptable
exec sp_executesql @sql1
if @@error = 0 PRINT 'RECORDS MOVED TO TABLE '+@bkptable1
exec sp_executesql @bsql
END
/* CREATING THE BACKUP TABLE */
set @sql = 'select * into '+@bkptable+' from TABLENAME
exec sp_executesql @sql
if @@error = 0
PRINT 'TABLE ARCHIVED SUCCESSFULLY'
else PRINT 'TABLE ARCHIVE NOT SUCCESSFUL'
/*DELETING THE RECORDS FROM TABLE*/
delete from TABLENAME
select @count = @@rowcount
if @@error = 0
PRINT cast(@count as varchar) +' ROWS DELETED FROM TABLENAME'
else PRINT 'ERROR PROCESSING DELETION FROM TABLENAME'END

My first post

This is my first post in this blog. I don’t want to bore you with all bla…..bla……bla………….’s. You will find some good posts from me for sql server and windows server system. Those posts will have some good scripts, technical articles and suggestions to keep your server alive and performing good. Check for them to keep you updated.