Sunday, 14 April 2013

SQL Server : Some DBCC commands and other T-SQL Script for Performance Tuning in SQL Server

Few below important T-SQL Scripts and DBCC commands for Tuning in SQL Server. Using below queries you can tune the Database in SQL Server-

/*Returns size and fragmentation information for the data and indexes of the specified table or view*/

SELECT * FROM sys.dm_db_index_physical_stats

/*This command is used to scan the current DB
and display the pages and extens and fragemention*/


/*This command is used to scan a particular table
and display pages,extens,avg size,row size,
and scanned pages of a table*/


SELECT * FROM sys.dm_db_index_physical_stats('HRMS_DB','tbl_Emp' , NULL,NULL , 'LIMITED');

/* To display total pagesize,datapage size,used pages on a database*/

select * from sys.allocation_units

/*To display the rows spread accross the pages in a table*/


/*To check the overall Database and tables with rows spread across the pages*/


/*DBCC CLEANTABLE reclaims space after a
variable-length column is dropped.
A variable-length column can be one of the following data types:
varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml.
The command does not reclaim space after a fixed-length column is dropped */
/*For the below example let's create a table
and insert some data and then drop the
column and then use the command and see the result*/
/*Step 1: Create table */

drop table dbo.CleanTableTest
CREATE TABLE dbo.CleanTableTest
    (FileName nvarchar(4000),
    DocumentSummary nvarchar(max),
    Document varbinary(max)

/*Step 2: Insert the data into the table
using wizard or from the table which contains
large records */

/*Now Verify the current page counts
 and average space used */

 SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'HRMS_DB'), OBJECT_ID(N'dbo.CleanTableTest'), NULL, NULL ,'DETAILED');

/*Now find out from the output few columns like
avg_pagesize,record count,pagecount,max record byte size,
avg_record byte size*/

/*Step 3: drop the columns of the table*/
ALTER TABLE dbo.CleanTableTest
DROP COLUMN FileName, Document;

/*Now Check the size of the table*/

SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'HRMS_DB'), OBJECT_ID(N'dbo.CleanTableTest'), NULL, NULL ,'DETAILED');

/*Now Clean the table*/
/*Even though you drop the column the space
is still reserved so now take a screen shot
and test the min,max,avg,row sized */
/*(After DBCC CLEANTABLE now check the
space used the min,max,row sizes will be different*/

DBCC CLEANTABLE (HRMS_DB,'dbo.CleanTableTest');

/*Now check the space of the table*/

SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'HRMS_DB'), OBJECT_ID(N'dbo.CleanTableTest'), NULL, NULL ,'DETAILED');


/*This Command is used Removes residual information left on database pages
because of data modification routines in SQL Server*/
/*Before this command check the DBSpace using

sp_helpdb HRMS_DB */
EXEC sp_clean_db_free_space
@dbname = N'HRMS_DB' ;

/*To get the complete infor of all the data type*/

EXEC sp_datatype_info

/*This Command display's all the objects that references the table tbl_Emp
like views,sp,indexes releated to this table*/

EXEC sp_depends @objname = N'tbl_Emp';

/*To Display the Memory Status*/

DBCC MemoryStatus

Note: Please do not run the below query on production server if it's not required. To run above queries we can tune the our database easily.

No comments:

Post a Comment