When to Reorganize/Rebuild Index

I had posted about using SQL Server 2005 DMV in the past. I had been thinking about adding some more details for a long time. Finally here it is..

When the data in the underlying tables change, indexes become fragmented (ie. logical ordering of index does not match with the physical ordering of the underlying table). When indexes get fragmented, the performance of the queries will suffer.

So most of the DBAs add to rebuild/reorganize the indexes in their maintenance jobs. Here are the differences between reorganizing and rebuilding the indexes.

Reorganize	Rebuild	Rebuild online in SQL Server 2005
It causes the data in the outermost data pages to reorder and will compact the index	Drops the desired index and creates a new index.	The database engine creates a new index and it is needs additional disk space to accommodate two concurrent indexes. During the process the new index is a write-only index and modifications to original index and applied to the new one. Once the process it done, it drops the original index.
Takes less system resouces and it is an online operation	Since the index is removed and recreated, it is an offline operation	The database engine will still have the original index available to the users while it building a new index. It uses Row Versioning to maintain the transactional consistency and integrity.

I personally look at the page count along with the avg_fragmentation_in_percent because high fragmentation on big indexes will have higher performance impact.

Even if you rebuild all the indexes, you may notice fragmentation for some tables will remain high. This is because if there are less than 1000 pages affected then the fragmentation will not be affected. For smaller tables, SQL server will prefer to use a table scan instead of an index.

Here is the script that I use

--Script to Reorganize/Rebuild indexes

DECLARE @cmd VARCHAR(1000)

DECLARE @Table VARCHAR(255)

DECLARE @SchemaName VARCHAR(255)

DECLARE @IndexName VARCHAR(255)

DECLARE @AvgFragmentationInPercent DECIMAL

DECLARE @fillfactor INT

DECLARE @ThresholdLowerLimit VARCHAR(10)

DECLARE @ThresholdUpperLimit VARCHAR(10)

DECLARE @PageCount INT

/* You can specify your value for reorganize and rebuild indexes

If avg_fragmentation_in_percent >= 5 and <30> 1000 Then Reorganize the Index

If avg_fragmentation_in_percent >= 30 AND Page_Count > 1000 Then Rebuild the Index

*/

SET @fillfactor = 90 --you can specify value for fill factor

SET @ThresholdLowerLimit= '5.0' -- this value is percentage

SET @ThresholdUpperLimit = '30.0' -- this value is percentage

SET @PageCount = 1000 --if pages are less then fragmentation would potentially remain same; This is not a magical cut off point but a start

-- ensure the temporary table does not exist

IF (SELECT OBJECT_ID('tempdb..#IndexList')) IS NOT NULL

DROP TABLE #IndexList;

--Select all the indexes that have >= 5% percent fragmentation and PageCount > 1000

SELECT OBJECT_NAME(S.OBJECT_ID) AS [TableName],schema_name(T.schema_id) AS [SchemaName],

OBJECT_NAME(S.object_id) [IndexName],S.page_count, S.avg_fragmentation_in_percent, 0 AS IsDefraged INTO #IndexList

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) S

JOIN sys.tables T WITH (nolock) ON S.OBJECT_ID = T.OBJECT_ID

WHERE T.is_ms_shipped = 0 --We only need user tables

AND S.avg_fragmentation_in_percent >= CONVERT(DECIMAL, @ThresholdLowerLimit)

AND S.page_count > @PageCount

ORDER BY S.page_count DESC

WHILE EXISTS ( SELECT 1 FROM #IndexList WHERE IsDefraged = 0 )

BEGIN

SELECT TOP 1 @Table = TableName, @AvgFragmentationInPercent = avg_fragmentation_in_percent,

@SchemaName = SchemaName, @IndexName = IndexName

FROM #IndexList

WHERE IsDefraged = 0

--Reorganizing the index if avg_fragmentation_in_percent > 5 and < style="">

IF((@AvgFragmentationInPercent >= @ThresholdLowerLimit) AND (@AvgFragmentationInPercent < @ThresholdUpperLimit))

BEGIN

SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REORGANIZE'

EXEC (@cmd)

PRINT N'Reorganize Index completed successfully for [' + @Table + ']' + CONVERT(VARCHAR, GETDATE())

END

--Rebuilding the index if avg_fragmentation_in_percent > 30

ELSE IF (@AvgFragmentationInPercent >= @ThresholdUpperLimit )

BEGIN

SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ', STATISTICS_NORECOMPUTE = OFF)'

EXEC (@cmd)

PRINT N'Rebuild Index completed successfully for [' + @Table + ']' + CONVERT(VARCHAR, GETDATE())

END

--Update the temp table

UPDATE #IndexList

SET IsDefraged = 1

WHERE TableName = @Table

AND IndexName = @IndexName

END

DROP TABLE #IndexList

Get the id of the newly inserted row

There are times when we need to retrieve the last inserted identity of a row. Here is a scenario

1. You need to insert a record into a table
2. You need the Id of the row you inserted, so you can use it to insert rows into tables referencing the first row

SQL Server has the options:

SCOPE_IDENTITY()– Gives the last identity value that was generated in the current scope. In other words, it will give the last identity value that you have explicitly created.

@@Identity –Gives the last identity value that was generated in the current session but in any scope.

IDENT_CURRENT(’tablename’) – Gives the last identity value produced in the table regardless if the connection it the scope.

In our scenario, SCOPE_IDENTITY() works perfect to return the identity of the recently added row.

SQL Server 2008 Resource Governor

Resource Govenor enables to manage SQL Server workload and resource utilization.

Terminology:

Resource Pool – It is a collection of physical resources. SQL Server 2008 has two built in pools, default and internal. It allows to create maximum of 18 user defined pools.

Workload Group – It is a container for the requests that are similar to the classification criteria applied to each request. Like Resource pools there are two built in workload groups, default and internal.

Classifier Function – It is a user written criteria that is being applied to the requests.Each user session would be assigned to a particular workgroup load using the classifier function.

Limitations:

1. Limited to only CPU bandwidth and memory management
2. We can only control resource utlization for database engine. It cannot be used with SSIS, Analysis or Reporting services
3. Only one instance of SQL Server can be managed

Here is just an example of creating resource pool and workload group

--Enable Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

USE [master]

GO

-- Create resource pool to be used

CREATE RESOURCE POOL poolAdhoc

WITH (

-- allocate at least % of the CPU bandwidth for admin queries

MIN_CPU_PERCENT = 10

-- do not let them exceed % either

, MAX_CPU_PERCENT = 20

-- admin queries should be small and we will allocate about %

-- of the server memory for them

, MIN_MEMORY_PERCENT = 5

, MAX_MEMORY_PERCENT = 5 )

GO

-- Create adhoc workload group

CREATE WORKLOAD GROUP groupAdhoc

WITH (

-- use all defaults

IMPORTANCE = MEDIUM

, REQUEST_MAX_MEMORY_GRANT_PERCENT =25

, REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0

, REQUEST_MAX_CPU_TIME_SEC = 50

, MAX_DOP = 0

, GROUP_MAX_REQUESTS = 0

)

USING

poolAdhoc

GO

-- Create admin workload group

CREATE WORKLOAD GROUP groupAdmin

WITH (

-- use all defaults

IMPORTANCE = MEDIUM

, REQUEST_MAX_MEMORY_GRANT_PERCENT =25

, REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0

, REQUEST_MAX_CPU_TIME_SEC = 30

, MAX_DOP = 0

, GROUP_MAX_REQUESTS = 0

)

USING

poolAdhoc

GO

CREATE FUNCTION [dbo].[rgClassifier_Adhoc]()

RETURNS sysname

WITH SCHEMABINDING

AS

BEGIN

-- Define the return sysname variable for the function

DECLARE @grp_name AS sysname;

SET @grp_name = 'default';

-- Specify the T-SQL statements for mapping session information

-- with Workload Groups defined for the Resource Governor.

IF (IS_SRVROLEMEMBER ('sysadmin') = 1)

SET @grp_name = 'groupAdmin'

IF (APP_NAME() LIKE '%Management Studio%')

SET @grp_name = 'groupAdhoc'

RETURN @grp_name;

END

GO

-- Set the classifier function for Resource Governor

ALTER RESOURCE GOVERNOR

WITH (

CLASSIFIER_FUNCTION = dbo.[rgClassifier_Adhoc]

)

GO

-- Make changes effective

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

SQL Server 2008 – Where is Activity Monitor?

For a minute I thought they deprecated the feature. I use it a lot to check the activity going on the databases.

They moved it to the launch point and the new one is really cool. Here is a blog with screenshots.

http://blogs.msdn.com/dtjones/archive/2008/07/10/where-the-heck-is-activity-monitor.aspx

SQL Server 2005 Pivots with Dynamic columns

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Report Development - Report Requirements Document

The purpose of report requirements document is to define the requirements for each report or group of reports.

Personally I don’t like to read 80 pages of technical documentation for a report. Nor would I want to scratch my head wondering what the report is doing Vs what it is supposed to do.

Here is a quick checklist of items to include in the report requirements documentation

• Business need for the report
• Requirements from the point of view of the end users
• The selection and the filter criteria
• Data elements in the report
• Calculations if any
• Parameters in the report
• Sort, grouping and totaling requirements
• Appearance and style requirements (i.e. layout of the report, column names to be shown etc)
• Performance requirements (speed and latency)
• Security requirements (who needs access to the report)

Microsoft Chart Controls

Great blog about ASP.net charting control

http://weblogs.asp.net/scottgu/archive/2008/11/24/new-asp-net-charting-control-lt-asp-chart-runat-quot-server-quot-gt.aspx