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

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'Sparsed'), NULL, NULL , 'DETAILED');

/*This command is used to scan the current DB

and display the pages and extens and fragemention*/

DBCC SHOWCONTIG

/*This command is used to scan a particular table

and display pages,extens,avg size,row size,

and scanned pages of a table*/

DBCC SHOWCONTIG(tbl_Emp)

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*/

DBCC CHECKTABLE ('tbl_Emp')

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

DBCC CHECKDB (HRMS_DB)

/*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');

DBCC CheckDB(HRMS_DB)

/*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.