Monday, November 26, 2012

index auditing script

Index counts:

select
o.name, COUNT(*)
from  sys.indexes i inner join sys.objects o on i.object_id=o.object_id
where o.type='U' and i.index_id<>1
group by o.name
order by COUNT(*) desc

index usage/unused indexes

select
--db_name(db_id()) as [database]
--, objectname=object_name(s.object_id)
ROW_NUMBER() over (partition by o.name,i.name order by i.index_id) as rowcouter
,
'CREATE ' +
CASE i.IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +
'NONCLUSTERED ' + ' INDEX '+
i.NAME + ' ' +' ON ' +
OBJECT_NAME (i.OBJECT_ID) +
'('+
DBO.fGetIndexCols (object_NAME(i.OBJECT_ID), i.index_id) + ')'
+Case when dbo.fGetIncludedIndex(object_NAME(i.OBJECT_ID), i.index_id) is null then '' else ' INCLUDE ('+dbo.fGetIncludedIndex(object_NAME(i.OBJECT_ID), i.index_id)+')' end +
' ON ['+
( SELECT GROUPNAME
FROM SYSFILEGROUPS
WHERE GROUPID = DATA_SPACE_ID
) + ']' IndexScript
,
'drop index ' + i.name + ' on ' + o.name as dropscript
,    Indexed_Columns  = LEFT(ixColumns, LEN(ixColumns) -1)
     , Included_Columns = LEFT(includedColumns, LEN(includedColumns) -1)
, o.name  as tablename
, indexname=i.name
, i.index_id 
, user_seeks
, user_scans
, user_lookups
, user_updates
--, user
--, reads=user_seeks + user_scans + user_lookups 
--, writes =  user_updates 
from
 sys.indexes i inner join sys.objects o on i.object_id=o.object_id
left join sys.dm_db_index_usage_stats s on i.index_id=s.index_id and i.object_id=s.object_id
 CROSS APPLY (
            SELECT sc.Name + CASE WHEN is_descending_key = 1 THEN ' DESC'
                                  ELSE '' END
                 + ', '
              FROM sys.index_columns ic
              JOIN sys.columns sc
                on ic.object_id = sc.object_id
               AND ic.column_id = sc.column_id
             WHERE i.object_id = ic.object_id
               AND is_included_column = 0
               and i.index_id = ic.index_id
             ORDER BY ic.Key_Ordinal
              FOR XML PATH('') ) ix (ixColumns)
CROSS APPLY (
            SELECT sc2.Name + ', '
              FROM sys.index_columns ic2 JOIN sys.columns sc2 on ic2.object_id = sc2.object_id
               AND ic2.column_id = sc2.column_id
             WHERE i.object_id = ic2.object_id
               AND is_included_column = 1
               and i.index_id = ic2.index_id
             ORDER BY ic2.Key_Ordinal
              FOR XML PATH('') ) nc (includedColumns)
             
 where o.type='U' --and s.database_id=DB_ID()
  and i.index_id <>1
  and o.name not like 'tem%'
  and (isnull(user_seeks,0) + isnull(user_scans,0) + isnull(user_lookups,0))= 0  --comment out to show all indexes usage
  and i.name is not null
   --and i.index_id=0
--order by o.name, user_seeks, user_scans,user_lookups, user_updates


Missing indexes

;WITH I AS (

-- Missing Indexes current database by Index Advantage

-- This DMV Query written by Glenn Berry
SELECT user_seeks * avg_total_user_cost *
      (avg_user_impact * 0.01) AS [index_advantage],

migs.last_user_seek,
mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns,
mid.included_columns,migs.unique_compiles, migs.user_seeks,
migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
      AND user_seeks * avg_total_user_cost *
      (avg_user_impact * 0.01) > 10000 -- Set this to Whatever
)

Note: multiple sources were used for the above script.

Saturday, November 17, 2012

free web stress test tools

Since LoadRunner is somewhat expensive and I am interested to find out if there is any free tools available that can be similar job, so far I have read a few good reviews and articles, it seems the combination of Fiddler + WCat and Badboy+Jmeter might be a good way to go, will try to do some test soon.