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.

use #DBName# DBCC SHOWCONTIG(#DBObjName#) with tableresults, ALL_INDEXES

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.

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 and i.name = '#indexName#' END;

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

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 and o.object_id = object_id('#DBObjName#') Order by object_name(i.object_id) asc

This script will lists statistics for existing indexes.

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 and s.object_id = object_id('#DBObjName#') and i.name = '#indexName#' Order by user_updates desc

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.

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 and i.name = '#indexName#'

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

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 and s.object_id = object_id('#DBObjName#') order by row_lock_wait_count desc

Today's piece of code identifies potentially useful indexes and makes recommendations for changes:

select d.* , s.avg_total_user_cost , s.avg_user_impact , s.last_user_seek ,s.unique_compiles from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g ,sys.dm_db_missing_index_details d where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle and d.statement = '[#DBName#].[#DBUserName#].[#DBTblName#]' order by s.avg_user_impact desc
Enjoy.