Friday, July 11, 2014

SQL Server : Complete DBCC commands for Performance Tuning in SQL Server

SQL Server : Complete DBCC commands for Performance Tuning in SQL Server.

Using below queries we can tune the DB.




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





Like and Share to SQL Integrity Blog

No comments:

Post a Comment

Thank You !!!!