WebsiteSpark

Saturday 31 December 2011

Index fragmentation

Index fragmentation
This post describes HOW part of WHAT to do for database performance improvement and index fragmentation is to identified as one of the cause of database performance hampering factor. There are other factors which might have negative effect on database performance; we shall deal with those one by one in subsequent posts. For now, let’s consider Index fragmentation
Cause of fragmentation can be attributed to heavy inserts, updates to the clustering key as well as updates resulting in page splits occurring to the data pages or the index pages.
Identifying Index fragmentation
You can determine the amount of fragmentation on the database by wxwcuting the following DBCC commands or the DMVs

use adventureworks2008r2;
go

DBCC ShowContig;

SELECT * FROM sys.dm_db_index_physical_stats (
    DB_ID ('adventureworks2008r2'), OBJECT_ID ('Address'), NULL, NULL, 'DETAILED');
-- mode 'LIMITED', 'DETAILED', 'SAMPLED'
GO

DBCC CHECKTABLE('Person.Address',1);
GO



The DBCC ShowContig; statement returns the fragmentation data for all the tables in the database, let’s select the address table and execute the next statement on Address table
Result of DBCC ShowContig;

/*------------------------
DBCC ShowContig;
------------------------*/
DBCC SHOWCONTIG scanning 'ProductInventory' table...
Table: 'ProductInventory' (2099048); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 7
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 3.5
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 28.57%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 600.0
- Avg. Page Density (full).....................: 92.59%
DBCC SHOWCONTIG scanning 'SpecialOffer' table...
Table: 'SpecialOffer' (14623095); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 5432.0
- Avg. Page Density (full).....................: 32.89%
DBCC SHOWCONTIG scanning 'Address' table...
Table: 'Address' (85575343); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 340
- Extents Scanned..............................: 44
- Extent Switches..............................: 43
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 97.73% [43:44]
- Logical Scan Fragmentation ..................: 0.29%
- Extent Scan Fragmentation ...................: 2.27%
- Avg. Bytes Free per Page.....................: 98.8
- Avg. Page Density (full).....................: 98.78%
DBCC SHOWCONTIG scanning 'ProductListPriceHistory' table...
.
.
Rest of the message is omitted.


Result of executing dm_db_index_physical_stats DMV will return one row per heap page having “IN_ROW” allocation, a row each per index per B-Tree level per partition. For detailed documentation of dm_db_index_physical_stats DMV, please visit msdn: http://msdn.microsoft.com/en-us/library/ms188917.aspx
Result of executing DBCC CHECKTABLE on Person.Address table

/*------------------------
DBCC CHECKTABLE('Person.Address',1); -- Clustered
------------------------*/
DBCC results for 'Person.Address'.
There are 19614 rows in 340 pages for object "Person.Address".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



To view all the pages across the indexes and heap, allocated for the Address table, we can use DBCC IND command as follows

DBCC IND ('adventureworks2008r2','Person.Address',-1);


Defragmentation
There are two ways for defragmenting indexes, first one Executing DBCC INDEXDEFRAG, DBCC DBREINDEX on the specific table and clustered index. The second one is to execute Alter index statement with rebuild or reorder TSQL commands.   
Rebuild corresponds to DBCC REINDEX and Reorganize corresponds to DBCC IndexDefrag. For details please visit technet: http://technet.microsoft.com/en-us/library/ms189858.aspx



USE AdventureWorks2008R2;
GO
DBCC INDEXDEFRAG (AdventureWorks2008R2, [HumanResources.EmployeeDepartmentHistory],PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID)
GO



USE AdventureWorks2008R2;
GO
DBCC DBREINDEX ("HumanResources.EmployeeDepartmentHistory",PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID ,80);
GO


USE AdventureWorks2008R2;
GO
ALTER INDEX
                  PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID
            ON
                  [HumanResources.EmployeeDepartmentHistory]
 REBUILD;
go

USE AdventureWorks2008R2;
GO
ALTER INDEX
                  PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID
            ON
                  [HumanResources.EmployeeDepartmentHistory]
 REORGANIZE;
go




Rule of thumb for deciding between Alter Index Rebuild and alter Index Reorganize is if fragmentation (described by “avg_fragmentation_in_percent” of sys.dm_db_index_physical_stats DMV) is between 5% to 30%, then go for Reorganize if fragmentation is more than 30% then go for Rebuild.

In certain cases, in spite of executing these commands, you may still find the pages with free spaces, which might be because of the index having very few pages and data is not enough to fill the page.

To view this, use the DBCC Ind command to view the allocated pages for the particular table and to view individual page, used the DBCC page command as shown below.



DBCC IND ('adventureworks2008r2','Person.Address',-1);

DBCC Traceon (3604);

DBCC PAGE( 'adventureworks2008r2',1,20320,3);



There are certain overheads like resource locking, transaction logging, IO operations performed during index reorganization or rebuild, which might impact the online operation during this operation.
Recommendation is to perform these kinds of database maintenance activities during the low usage period of the application or down time window of the application, so that the online operations are not impacted.

Enjoy!
Posted by at No comments:
Labels: , , , , , , ,

Thursday 29 December 2011

Tracing activities on SQL Server 2008/R2/2012 (RC 0)

Let me start with the script for implementing tracing in SQL 2008. I will decipher the scripts one by one later on.
The script:

declare @traceid int;
declare @ret int;
declare @maxfilesize bigint;
declare @on bit;
      set @maxfilesize  = 10;
      set @onbit = 1;


--select * from sys.traces;
/* this statement list all the current traces in the system
      Status 0 => stopped
      Status 1 => Runnning
*/
     
exec @ret = sp_trace_create @traceid output,
                                          0,
                                          N'D:\MyPath\MyTraceFile.trc', -- change this name accordingly
                                          @maxfilesize,
                                          Null
                                                                                   
/*Print @ret*/

--select * from sys.traces;
/* above statement should list your new trace with status = 0.
*/

/*Now we have successfully created trace, lets add events and columns to be traced*/

/*==============RPC completed====================*/
exec sp_trace_setEvent @traceid,
                                    10 ,
                                    1,
                                    @onbit;
                                   
exec sp_trace_setEvent @traceid,
                                    10 ,
                                    2,
                                    @onbit;
                                   
exec sp_trace_setEvent @traceid,
                                    10 ,
                                    8,
                                    @onbit;
                                   
exec sp_trace_setEvent @traceid,
                                    10 ,
                                    10,
                                    @onbit;
                                                                       
exec sp_trace_setEvent @traceid,
                                    10 ,
                                    35,
                                    @onbit;
                                   
exec sp_trace_setEvent @traceid,
                                    10 ,
                                    14,
                                    @onbit;

exec sp_trace_setEvent @traceid,
                                    10 ,
                                    15,
                                    @onbit;
/*==============End RPC completed==================== */

/*==============SQL batch  completed==================== */
exec sp_trace_setEvent @traceid,
                                    12 ,
                                    1,
                                    @onbit;
                                   
exec sp_trace_setEvent @traceid,
                                    12 ,
                                    2,
                                    @onbit;
                                   
exec sp_trace_setEvent @traceid,
                                    12 ,
                                    8,
                                    @onbit;
                                   
exec sp_trace_setEvent @traceid,
                                    12 ,
                                    10,
                                    @onbit;
                                                                       
exec sp_trace_setEvent @traceid,
                                    12 ,
                                    35,
                                    @onbit;
                                   
exec sp_trace_setEvent @traceid,
                                    12 ,
                                    14,
                                    @onbit;

exec sp_trace_setEvent @traceid,
                                    12 ,
                                    15,
                                    @onbit;
/*==============End SQL batch  completed==================== */

/*==============SQL Statement  completed==================== */
exec sp_trace_setEvent @traceid,
                                    41 ,
                                    1,
                                    @onbit;
                                   
exec sp_trace_setEvent @traceid,
                                    41 ,
                                    2,
                                    @onbit;
                                   
exec sp_trace_setEvent @traceid,
                                    41 ,
                                    8,
                                    @onbit;
                                   
exec sp_trace_setEvent @traceid,
                                    41 ,
                                    10,
                                    @onbit;
                                                                       
exec sp_trace_setEvent @traceid,
                                    41 ,
                                    35,
                                    @onbit;
                                   
exec sp_trace_setEvent @traceid,
                                    41 ,
                                    14,
                                    @onbit;

exec sp_trace_setEvent @traceid,
                                    41 ,
                                    15,
                                    @onbit;
/*==============End SQL Statement  completed==================== */



exec sp_trace_setfilter @traceid,
                                    35, -- database name
                                    0, -- logical operator 0=> AND
                                    6, -- comparison 6 => like
                                    N'MYDBName';
                                   



exec sp_trace_setStatus @traceid,1;
-- 1 => start 0 => stop 2=> close                                
go                           
                                   
select * from sys.traces;


Above query initializes and activates the trace on SQL server, which writes to the specified file location. The file location has to be on a local drive and the SQL server service account should have full privilege on the specified location.

The output trace when opened with SQL Profiler looks as:



System catalog view sys.traces lists all the current traces and their status.

So the following statement should now list 2 traces with status as 1, one of the trace being the default trace with id = 1, and the second one which we created above.


select * from sys.traces;




Script to disable the trace

declare @traceid int;

select * from sys.traces;
-- get the trace ID you want to disable
set @traceid = 2 ;


exec sp_trace_setStatus @traceid,
                                    0; -- 1 => start 0 => stop 2=> close
                                   
      go                           
                                   
select * from sys.traces;
-- here we have the trace in stoped mode

exec sp_trace_setStatus @traceid,
                                    2; -- 1 => start 0 => stop 2=> close

--Now we have the trace closed and removed from system
select * from sys.traces;
-- select willnot list our trace.



Remember you have to first set the trace to stop before closing the trace.

Again executing a select on sys.traces should result in single row, indicating only the default trace is present on the server



select * from sys.traces;



Enjoy!!
Deciphering our main query
Our main query starts with the statement defining certain properties of our trace

exec @ret = sp_trace_create @traceid output,
                                          0,
                                          N'D:\MyPath\MyTraceFile.trc',
                                          @maxfilesize,
                                          Null



XSP sp_trace_create takes 6 parameters, last 3 being optional, the parameters are as follows:


Param
Values
Description
1
TraceID
Int, Output
Output value containing the generated trace ID
2
Options
0
Default


2
“Trace file rollover”, specifying the tracing engine to generate a new file when Max file limit is reached.


4
“Shutdown on error” SQL shuts down if traces cannot be written to the file


8
“Trace Produce Blackbox”  this is incompaitable with all the other trace levels and max file size cannot be specified along with this.
3
maxFileSize
Bigint
Specifies maximum trace file in MB
4
StopTime
Datetime
Date time at which this trace will stop.
5
FileCount
Int
Maximum number of trace files to be maintained (older trace files are deleted)


For further information please refer to msdn : http://msdn.microsoft.com/en-us/library/ms190362.aspx
The second step is to select events and columns for tracing.
In SQL server 2008 r2 as well as in 2012, there are 180 events divided into 21 categories
The following queries on trace catalog views list the events, categories and join between them


select * from sys.trace_categories;


select * from sys.trace_events;

select
      c.name as 'Category',
      e.name as 'TraceEvent',
      e.trace_event_id as 'EventID'
from
      sys.trace_events e
      join sys.trace_categories c
      on e.category_id = c.category_id
Order by 1,2


Event columns are described by sys.trace_columns catalog view and the association of trace columns to trace events is defined by sys.trace_event_bindings. There are 66 trace event columns. Following query gets the event column definition for all the events. This query on SQL 2008 R2 should return 4304 rows.


select
      ev.trace_event_id as 'EventID',
      ev.name as 'Event',
      cl.trace_column_id as 'ColID',
      cl.name as 'ColName',
      cl.type_name as 'ColType'
     
from
      sys.trace_event_bindings bi
      join sys.trace_events ev on bi.trace_event_id = ev.trace_event_id
      join sys.trace_columns cl on bi.trace_column_id = cl.trace_column_id



Based on output from the catalog query above, provide inputs to sp_trace_setEvent XSP
XSP sp_trace_setEvent takes the following inputs


Param
Values
Description
1
TraceID
Int
ID of trace tobe modified
2
EventID
Int
ID of event
3
ColumnID
Int
ID of column to be traced
4
OnBit
Bit
0 => off, 1 => on (delete & add as well).


Setting filter to trace
We would certainly do not want to log events happening across all the databases on the server. Definitely we would like to filter, XSP sp_trace_setfilter does the filtering for us.
XSP sp_trace_setfilter takes the following parameters


Param
Values
Description
1
TraceID
Int
ID of trace tobe modified
2
ColumnID
Int
The column id to be compared (35 indicates database name, from sys.trace_columns catalog view
3
Logicaloperator
0 / 1
Used in conjunction with other filters 0 => AND 1 => OR operation
4
Comparison
0 – 7
0.   =
1.   <>
2.   >
3.   <
4.   >=
5.   <=
6.   Like
7.   Not Like
5
Value
NVarchar



For detailed reference, please refer msdn: http://msdn.microsoft.com/en-us/library/ms174404.aspx
Running the trace
By default when we first create the trace, its status is “0”, indicating its created but in stop mode. To set the trace to start mode, use the following query.

exec sp_trace_setStatus @traceid,
                                    1; -- 1 => start 0 => stop 2=> close


Now you should have the trace up and running.
Caution
Remember trace generates and writes data on to local file system. For a server already facing IO crunch, this might significantly add up to the resource crunch. In case of C: drive, this might fill up the OS drive.

To view the trace file, you can either open the trace file with SQL Server Profiler (run as administrator) but will not be able to refresh the new entries.
Other way is to use the following SQL query for viewing the trace file. This returns all the available 66 columns.

select
      *
from
      fn_trace_gettable( N'C:\Path\DBTrace.trc',0);


Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)