•  
  • Archives for November 2010 (3)

INDEX DEFRAGMENTATION SCRIPT

 

INDEX DEFRAGMENTATION SCRIPT for 2008 / 2005

I dedicate this article to my senior DBA “Roshan Joe Joseph” who has helped me to write a script.

I have posted the updated INDEX DEFRAGMENTATION script here, which is already published in sqlservercentral (SSC).


Script changes:

·         I used while loop instead of a cursor.
·         I used to rebuild the index if the page level locking is disabled.

Msg 2552, Level 16, State 2, Line 1
The index “index name” (partition 1) on table “table name” cannot be reorganized because page level locking is disabled

 

Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance. When you perform any DML operation (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. If you use predefined maintenance plan it will take much server resource and time. Here is a custom stored procedure.

If you want to determine the level of fragmentation, you can use the SYS.DM_DB_INDEX_PHYSICAL_STATS statement. The SYS.DM_DB_INDEX_PHYSICAL_STATS DMV displays fragmentation information for the data and indexes of the specified object.

Here I used 4 conditions:

1. Fragmentation >=30 AND PAGES>1000 then rebuild
2. Fragmentation between 15 to 29 AND PAGES>1000 then reorganize & update statistics
3. If the page level locking is disabled then rebuild
4. If the above conditions are false and statistics are outdated more than 10 days then update the statistics

Before you run the procedure create the tables provided for history propose

Read Gail Shaw’s post ‘What is fragmentation’ and how it will affect the performance http://sqlinthewild.co.za/index.php/2008/10/20/what-is-fragmentation/

 

-- For SQL-2005/2008
USE MSDB;
go
CREATE TABLE [dbo].[dba_defrag_maintenance_history]
(
[db_name] [SYSNAME] NOT NULL,
[table_name] [SYSNAME] NOT NULL,
[index_name] [SYSNAME] NOT NULL,
[frag] [FLOAT] NULL,
[page] [INT] NULL,
[action_taken] [VARCHAR](35) NULL,
[last_stats_date] [datetime] NULL,
[date] [DATETIME] NULL DEFAULT (GETDATE())
)
go
--Archive the data's in master DB
USE MASTER;
go
CREATE TABLE [dbo].[dba_defrag_maintenance_history]
(
[db_name] [SYSNAME] NOT NULL,
[table_name] [SYSNAME] NOT NULL,
[index_name] [SYSNAME] NOT NULL,
[frag] [FLOAT] NULL,
[page] [INT] NULL,
[action_taken] [VARCHAR](35) NULL,
[last_stats_date] [datetime] NULL,
[date] [DATETIME] NULL DEFAULT (GETDATE())
)
go

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[indexdefragmentation]    Script Date: 09/07/2012 05:18:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--drop proc [indexdefragmentation]
CREATE PROC [dbo].[indexdefragmentation]@p_dbname SYSNAME
/*
Summary:     Remove the Index Fragmentation to improve the query performance
Contact:        Muthukkumaran Kaliyamoorhty SQL DBA
Description:   This Sproc will take the fragmentation details and do four kinds of work.

1. Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
2. Check the fragmentation between 5% to 30% and pages greater than 1000 then reorganize & update stats
3. Check the fragmentation between 5% to 30% and pages greater than 1000 and page level lock disabled then rebuild
4. Update the statistics if the above conditions are false
/*statistics will be updated if its gets 10 days older */
ChangeLog:
Date              Coder                                                             Description
2012-sep-7        Muthukkumaran Kaliyamoorhty                        created
2012-sep-8        Muthukkumaran Kaliyamoorhty                        updates                Stats date    
*************************All the SQL keywords should be written in upper case*************************
*/
AS
BEGIN
SET NOCOUNT ON
DECLARE
@db_name SYSNAME,
@tab_name SYSNAME,
@ind_name VARCHAR(500),
@schema_name SYSNAME,
@frag FLOAT,
@pages INT,
@min_id INT,
@max_id INT,
@stats_date VARCHAR(8)

SET @db_name=@p_dbname

--------------------------------------------------------------------------------------------------------------------------------------
--inserting the Fragmentation details
--------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #tempfrag
(
id INT IDENTITY,
table_name SYSNAME,
index_name VARCHAR(500),
frag FLOAT,
pages INT,
schemaName SYSNAME,
statsDate VARCHAR(8)
)            

EXEC ('USE ['+@db_name+'];
INSERT INTO #tempfrag (table_name,index_name,frag,pages,schemaName,statsDate)
SELECT OBJECT_NAME(F.OBJECT_ID) obj,i.name ind,
f.avg_fragmentation_in_percent,
f.page_count,OBJECT_SCHEMA_NAME(f.object_id),
CONVERT(VARCHAR(8),STATS_DATE(I.OBJECT_ID,I.index_id),112)as statsdate
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL) F
JOIN SYS.INDEXES I
ON(F.OBJECT_ID=I.OBJECT_ID)AND i.index_id=f.index_id
JOIN INFORMATION_SCHEMA.TABLES S
ON (s.table_name=OBJECT_NAME(F.OBJECT_ID))
AND f.database_id=DB_ID()
AND OBJECTPROPERTY(I.OBJECT_ID,''ISSYSTEMTABLE'')=0
AND alloc_unit_type_desc = ''IN_ROW_DATA''
AND is_disabled = 0
'

)
SELECT @min_id=MIN(ID)FROM #tempfrag
SELECT @max_id=MAX(ID)FROM #tempfrag
TRUNCATE TABLE msdb.dbo.dba_defrag_maintenance_history

--SELECT * FROM #tempfrag

WHILE (@min_id<=@max_id)
/*While loop begin*/
BEGIN
SELECT
@tab_name=table_name,
@schema_name=schemaname,
@ind_name=index_name ,
@frag=frag ,
@pages=pages,
@stats_date=statsDate
FROM #tempfrag WHERE id = @min_id

--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF (@ind_name IS NOT NULL)
/*First if condition*/
BEGIN
IF (@frag>30 AND @pages>1000)
/*Sub if condition 1st */
BEGIN
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REBUILD',@stats_date,GETDATE())
END
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 5% to 30% and pages greater than 1000 then reorganize
--------------------------------------------------------------------------------------------------------------------------------------
ELSE IF((@frag BETWEEN 5 AND 30) AND @pages>1000  )
/*Sub if condition 2nd */
BEGIN
BEGIN TRY
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REORGANIZE ')
IF (@stats_date <CONVERT(VARCHAR(8),DATEADD(HH,1,GETDATE()-10),112))
BEGIN
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' )
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REORGANIZE & UPDATESTATS',@stats_date,GETDATE())
END
END TRY
BEGIN CATCH
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 15% to 29% and pages greater than 1000 and page level
--lock disabled then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF ERROR_NUMBER()=2552
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'PLLD_REBUILD',@stats_date,GETDATE())
END CATCH
END

--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics  for all indexes if the first three conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
/*Sub if condition 3rd*/
ELSE
BEGIN
IF (@stats_date < CONVERT(VARCHAR(8),DATEADD(HH,1,GETDATE()-10),112))
BEGIN
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) '  )
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'UPDATESTATS',@stats_date,GETDATE())
END
END
END
ELSE

BEGIN
IF (@stats_date < CONVERT(VARCHAR(8),DATEADD(HH,1,GETDATE()-10),112))
BEGIN
--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics  for all tables if the first three conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+']')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,'HEAP',@frag,@pages,'UPDATESTATS',@stats_date,GETDATE())
END
/*End of the first if condition*/
END

SET @min_id=@min_id+1
/*While loop end*/
END

DROP TABLE #tempfrag
INSERT INTO master.dbo.dba_defrag_maintenance_history
SELECT * FROM msdb.dbo.dba_defrag_maintenance_history
END
GO

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

SQL server basics for beginners

 

In this article, I am going to discuss about the basics of SQL server which is helpful for newbies.

SQL-Structured Query Language (pronounced as SEQUEL) is a Database, computer language designed for managing data in relational database management system (RDBMS).

Most of the people have to know SQL server query has designed for set based (declarative) .

The SQL commands are

 

DQL (Select)

DML (Insert, Update, Delete)

DDL (Create, Alter, Drop, Truncate)

DCL (Grant, Revoke, Deny)

TCL (Commit, Rollback)

 

Database

A Database stores all the data information (like objects) and has the default data file and log file. The data file is called mdf, ndf…. (One-mdf,more than one ndf). The log file is called ldf (More than one ldf)

Types

1. System databases

A system database will create by default when the SQL server installed.

2. User databases

A user database will create by the user.

 

SQL Server System Databases

1. Master

2. Resource (2005 onwards)

3. Tempdb

4. Model

5. Msdb

 

1. Master

  • The Master database manages the SQL Server instance. It stores system configuration, logins details etc..
  • In SQL Server 2005 onwards Master database stores only the logical information in the system objects residing in the sys schema.
  • In the previous editions of SQL Server, the Master database physically stores all the system objects, (tables, Sprocs etc.) information.
  • One of the main things is master and resource DB must be located in the same directory. Otherwise the server goes down :-)

 

2. Resource

  • Introduced in SQL Server 2005 to help in managing the upgrade and rollback of the system objects (service pack).
  • Prior to SQL Server 2005 the system related data were stored in the master database.
  • It’s a hidden and read-only database that is not accessible via SQL server management studio.

3. TempDB

  • TempDB is a temporary database to store temporary tables like #local, ##global, table variables, cursors, work tables sorted in TempDB, etc…
  • When the SQL Server instance is rebooted, the TempDB database is recreated every time.

4. Model

  • Model is a template database for all user defined databases.
  • So we could use the model as a template for other user databases (such as the recovery model, DB size, SPs etc.…) i.e. Whenever you create a new database the template will taken from the model database.

5. MSDB

  • MSDB is the main database to manage the SQL Server Agent configurations.
  • It provides some of the configurations for the SQL Server Agent service (such as jobs alerts etc..) and it stores database backup details.

 

Important: Keep taking the system database backup regularly except tepmDB.

For More: http://msdn.microsoft.com/en-us/library/ms178028.aspx

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

sql server internal architecture

by Muthukkumaran kaliyamoorthy Published on: November 12, 2010
Comments: 4 Comments
Tags: ,
Categories:Internals

Folks,

I am very glad to share my experience. I have two reasons to write this article here.

First one is I had an interview with one of the company, they asked about the SQL server internals. (On that time I struggled)

When I Goggled I didn’t get entire information in any of the website.

The thing is many of DBAs don’t know the SQL server internals Allocation Map.

I try to write everything in this post using my simple English. Hope, someone gets benefit by reading this article.

Database Physical Structure & Internals:

Database:

A Database is a collection of data that is stored (or) organized. We can manage and access data easily.
When we create a new database, by default two files will be created physically.
We can create more than one log files and data files (NDF).

  • Data file (MDF)
  • Log file (LDF)

Data File:
All the data will be stored in a data file. Internally the data’s are stored in data pages.

A database file has unique FileID and PageID.

Log files do not have pages. They have series of log records. I will cover the Log file section in my next article.

Data Page:

The data page is a fundamental unit of data storage. Each data page has 8KB size (128 pages per MB). Each page has 96 byte header system information.

 

All the data rows are stored in data page. Expect BLOB (text, ntext, image, xml, varchar (max), nvarchar & varbinary)

Extents:

Extents are fundamental units in which space is allocated to table (or) an index. Extents are 8 contiguous pages 64KB size. (16 extents per MB).

Two Types of extents

  • Uniform/Dedicated extents
  • Mixed extents

 

Uniform Extents:

Uniform extents are owned by a single object. All the 8-pages can be used to owning object.

 

Mixed Extents:

When we create a new table (or) an index of default pages are allocated to mixed extents first.

When the data grow and its reach 8 pages then it switched to uniform extents.

 

How the Data Storage Size Internally For Page & Extents:


Storage Size Page Extents
Size per KB 8KB(1-Page) 64KB (8-Pages)
Size per MB 128 Pages 16 Extents


IAM: (Index Allocation Map)
IAM page tracks with which extents within the specified GAM interval belongs to a single entity (or) file. A Database file has multiple files some of the DB size more than 4 GB

 

IAM interval size is 64000 extents i.e. 4GB

 

GAM: (Global Allocation Map)

Data files split up into GAM intervals (Conceptually)

Bit –>1 Free

An extent is available for allocation.

Bit –> 0 Allocated

An extent is already allocated.

 

GAM interval size is 64000 extents i.e. 4GB

 

SGAM: (Shared Global Allocation Map)

SGAM bitmaps are exactly same as GAM.

Bit –> 1 mixed extent one (or) more free page available

An extent is mixed one (or) more unallocated page available.

Bit –> 0 Either Uniform/Mixed extent No unallocated pages

An extent is either uniform (or) mixed all are already allocated.

 

SGAM interval size is 64000 extents i.e. 4GB

PFS: (Page Free Space)
PFS has tracks with which pages have free space.

 

A PFS page is the first page after the file header page in a data file (with page number 1).
Next comes a GAM (with page number 2) followed by an SGAM (page 3).

PFS interval size is 8088 pages i.e. 64MB

PFS does not have bitmap only have byte map.
DCM: (Differential Changed Map)

DCM tracks extents that have changed since the last BACKUP DATABASE statement.

Bit –> 1 extent modified

Extent has been modified since the last BACKUP DATABASE.

Bit –> 0 extents is not modified

Extent has not been modified since the last BACKUP DATABASE.

 

BCM: (Bulk Changed Map)

BCM tracks extents that have been modified by bulk operations since the last BACKUP LOG statement.

Bit –> 1 extents modified

Extent has been modified by a bulk logged operation after the last BACKUP LOG statement.

Bit –> 0 extent is not modified

Extent has not been modified by bulk logged operations .Although the BCM pages appear by default in all databases.

 

How the Data Stored/Allocated Internally for the entity:

(Allocation Maps)


Type of storage

(Bit Type)

GAM
SGAM
DCM
BCM
Bit 1 Extent Free Mixed Extent at least one free pages available Extent modified Extent modified
Bit 0 Extent Allocated Either Uniform (or) Mixed No free space Extent not modified
Extent not modified






Referred links :

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

page 1 of 1

Welcome , today is Saturday, August 2, 2014