WOOHOO!!!!!!!!!!!!!!!!!!!!!

6000 downloads of my projects. Not too bad.

I added a new project - cfSQLMaster, that i worked on for some time when resting from my usual work.
This is a set of functions that should help you understand better what is going on with your MSSQL 2005 DB and will allow also for DB optimization. For example, you can get DB info, you can see what indexes are not used, what indexes you may need to add in order to have the DB work better/faster, etc.


I really hope it will be useful for someone.

Today i will show 2 functions that may help with getting information about fragmentation of your data and indexes in MSSQL.
First function will displays fragmentation information for the data and indexes of the specified mssql table. It take 3 arguments: DB name, table name and if you want to see the data for each particular index or as a general report.

<cffunction name="getFragmentationInfo" output="No" returntype="any" description="Displays fragmentation information for the data and indexes of the specified table">
   <cfargument name="DBName" default="master" type="any">
   <cfargument name="DBObjName" default="" type="any">
   <cfargument name="allIndexes" default="No" type="any">
   <cfset var getFragmentationInfo = "">
   <cfquery name="getFragmentationInfo" datasource="#dsn#" username="#userName#" password="#passwd#">
      use #DBName#
      DBCC SHOWCONTIG(#DBObjName#) with tableresults<cfif allIndexes eq "Yes">, ALL_INDEXES</cfif>
   </cfquery>
   <cfreturn getFragmentationInfo>
</cffunction>

And this function will returns some fragmentation statistics as being the size and fragmentation. Possible parameters are: DB name, table name, index name (if not provided it will show the info for all indexes), mode (possible values are Limited and Detailed) and fragmentation value from which to show the result.

<cffunction name="getFragmentationStats" output="No" returntype="any" description="returns size and fragmentation statistics for all indexes and partitions">
   <cfargument name="DBName" default="master" type="any">
   <cfargument name="DBObjName" default="" type="any">
   <cfargument name="indexName" default="" type="any">
   <cfargument name="mode" default="Limited" type="any">
   <cfargument name="maxFrag" default="50" type="any">
   <cfset var getFragmentationStats = "">
   <cfif ListFindNoCase("Limited,Detailed", mode, ",") eq 0>
      <cfset mode = "Limited">
   </cfif>
   <cfquery name="getFragmentationStats" datasource="#dsn#" username="#userName#" password="#passwd#">
      DECLARE @db_id SMALLINT;
      DECLARE @object_id INT;
      
      SET @db_id = DB_ID(N'#DBName#');
      SET @object_id = OBJECT_ID(N'#DBObjName#');
      
      IF @db_id IS NULL
      BEGIN;
       PRINT N'Invalid database';
      END;
      ELSE IF @object_id IS NULL
      BEGIN;
       PRINT N'Invalid object';
      END;
      ELSE
      BEGIN;
       SELECT i.name, s.*
         FROM sys.indexes i, sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , '#mode#') s
         Where avg_fragmentation_in_percent >= #Val(maxFrag)#
            and i.index_id = s.index_id
            <cfif Len(Trim(indexName)) gt 0>
               and i.name = '#indexName#'
            </cfif>
      END;
   </cfquery>
   <cfreturn getFragmentationStats>
</cffunction>

So, you will get the list of indexes not used since the last time SQL Server was recycled.

<cffunction name="getNotUsedIndex" output="No" returntype="any" description="lists any index not used since the last time SQL Server was recycled.">
   <cfargument name="DBName" default="master" type="any">
   <cfargument name="DBObjName" default="" type="any">
   <cfset var getNotUsedIndex = "">
   <cfquery name="getNotUsedIndex" datasource="yourdsn" username="youruserName" password="yourpasswd">
      use #DBName#
      Select object_name(i.object_id) as objName, i.name as indexName
      From sys.indexes i, sys.objects o
      Where i.index_id NOT IN (
                           Select s.index_id
                           From sys.dm_db_index_usage_stats s
                           Where s.object_id=i.object_id
                               and i.index_id=s.index_id
                           )
         and o.type = 'U'
         and o.object_id = i.object_id
      <cfif Len(Trim(DBObjName))>
         and o.object_id = object_id('#DBObjName#')
      </cfif>
      Order by object_name(i.object_id) asc
   </cfquery>
   <cfreturn getNotUsedIndex>
</cffunction>

This script will lists statistics for existing indexes.

<cffunction name="getIndexStats" output="No" returntype="any" description="lists statistics for existing Indexes, ordered by user_updates.">
   <cfargument name="DBName" default="master" type="any">
   <cfargument name="DBObjName" default="" type="any">
   <cfargument name="indexName" default="" type="any">
   <cfset var getIndexStats = "">
   <cfquery name="getIndexStats" datasource="yourdsn" username="youruserName" password="yourpasswd">
      use #DBName#
      Select   s.*, db_name(s.database_id) as dbName, object_name(s.object_id) as objName, i.name as index_name
      From sys.dm_db_index_usage_stats s, sys.indexes i, sys.objects o
      Where i.index_id = s.index_id
         and o.type = 'U'
         and o.object_id = i.object_id
      <cfif Len(Trim(DBObjName))>
         and s.object_id = object_id('#DBObjName#')
      </cfif>
      <cfif Len(Trim(indexName))>
         and i.name = '#indexName#'
      </cfif>
      Order by user_updates desc
   </cfquery>
   <cfreturn getIndexStats>
</cffunction>

Sometimes in order to identify a problem with your indexes, may needed to be done few statistics snapshots within particular interval.
To do this i wrote this quick function based on examples i found on Microsoft's blogs.

<cffunction name="getIndexStatsSnapshots" output="No" returntype="any" description="retrieves index statistics at N snapshots with given interval in seconds">
   <cfargument name="DBName" default="NULL" type="any">
   <cfargument name="DBObjName" default="NULL" type="any">
   <cfargument name="indexName" default="" type="any">
   <cfargument name="snapshotsNumb" default="2" type="any">
   <cfargument name="snapshotsInterval" default="10" type="any">
   <cfset var getIndexStatsSnapshots = ArrayNew(1)>
   <cfset var getIndexStatsSnapshotsQ = "">
   <cfset var i = 0>
   <cfset var thread = "">
   <cfset var sleepTime = snapshotsInterval*1000>
   <cfloop index="i" from="1" to="#snapshotsNumb#">
      <cfquery name="getIndexStatsSnapshotsQ" datasource="yourdsn" username="youruserName" password="yourpasswd">
         declare @dbid int
         select @dbid = db_id('#DBName#')

         declare @objid int
         select @objid = object_id('#DBObjName#')

         select   s.database_id, db_name(s.database_id) as dbName
                  ,s.object_id, object_name(s.object_id) as objName
                  ,s.index_id
                  ,s.partition_number
                  ,s.leaf_insert_count
                  ,s.leaf_delete_count
                  ,s.leaf_update_count
                  ,s.leaf_ghost_count
                  ,s.nonleaf_insert_count
                  ,s.nonleaf_delete_count
                  ,s.nonleaf_update_count
                  ,s.leaf_allocation_count
                  ,s.nonleaf_allocation_count
                  ,s.leaf_page_merge_count
                  ,s.nonleaf_page_merge_count
                  ,s.range_scan_count
                  ,s.singleton_lookup_count
                  ,s.forwarded_fetch_count
                  ,s.lob_fetch_in_pages
                  ,s.lob_fetch_in_bytes
                  ,s.lob_orphan_create_count
                  ,s.lob_orphan_insert_count
                  ,s.row_overflow_fetch_in_pages
                  ,s.row_overflow_fetch_in_bytes
                  ,s.column_value_push_off_row_count
                  ,s.column_value_pull_in_row_count
                  ,s.row_lock_count
                  ,s.row_lock_wait_count
                  ,s.row_lock_wait_in_ms
                  ,s.page_lock_count
                  ,s.page_lock_wait_count
                  ,s.page_lock_wait_in_ms
                  ,s.index_lock_promotion_attempt_count
                  ,s.index_lock_promotion_count
                  ,s.page_latch_wait_count
                  ,s.page_latch_wait_in_ms
                  ,s.page_io_latch_wait_count
                  ,s.page_io_latch_wait_in_ms
                  ,getdate() as timeStamp
                  ,i.name as index_name
         from sys.dm_db_index_operational_stats(@dbid, @objid, NULL, NULL) s, sys.indexes i, sys.objects o
         Where i.index_id = s.index_id
            and o.type = 'U'
            and o.object_id = i.object_id
         <cfif Len(Trim(indexName))>
            and i.name = '#indexName#'
         </cfif>
      </cfquery>
      <cfset getIndexStatsSnapshots[i] = getIndexStatsSnapshotsQ>
      <cfif i lt snapshotsNumb>
         <cfset thread = CreateObject("java", "java.lang.Thread")>
         <cfset thread.sleep(Evaluate(sleepTime))>
      </cfif>
   </cfloop>
   <cfreturn getIndexStatsSnapshots>
</cffunction>

To list all idexes wit the most blocking in MSSQL 2005 you may use the following code:

<cffunction name="getMostBlockingIndexes" output="No" returntype="any" description="lists indexes with the most contention (blocking)">
   <cfargument name="DBName" default="master" type="any">
   <cfargument name="DBObjName" default="" type="any">
   <cfset var getMostBlockingIndexes = "">
   <cfquery name="getMostBlockingIndexes" datasource="yourdsn" username="youruserName" password="yourpasswd">
      use #DBName#
      declare @dbid int
      select @dbid = db_id()
      Select dbid=database_id, objectname=object_name(s.object_id)
         , indexname=i.name, i.index_id
         , row_lock_count, row_lock_wait_count
         , [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
         , row_lock_wait_in_ms
         , [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
      from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s
         ,sys.indexes i
      where objectproperty(s.object_id,'IsUserTable') = 1
         and i.object_id = s.object_id
         and i.index_id = s.index_id
         <cfif Len(Trim(DBObjName))>
            and s.object_id = object_id('#DBObjName#')
         </cfif>
      order by row_lock_wait_count desc
   </cfquery>
   <cfreturn getMostBlockingIndexes>
</cffunction>