These are our SQL Server Tools, hope you like them.

Missing Indexes - Part 1

This script creates a list of runnable CREATE INDEX statements by using the Missing Indexes feature in SQL Server 2005.  It is applicable to all editions of SQL Server 2005.  Please do not create all indexes for every table.  In an OLTP the optimal number of indexes is 7, however in an OLAP or DSS server you may want to have every index under the sun.

SELECT ' CREATE INDEX ix_' +

REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(equality_columns, ''), ',', ''),'[',''), ']', ''), ' ', '') +
REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(inequality_columns, ''), ',', ''),'[',''), ']', ''), ' ', '') +
' ON ' + statement + '('+ISNULL(equality_columns,'') +
CASE WHEN inequality_columns IS NOT NULL AND equality_columns IS NOT NULL THEN ',' ELSE '' END +
CASE WHEN inequality_columns IS NULL THEN '' ELSE inequality_columns END +
CASE WHEN included_columns is not null then ') INCLUDE (' + included_columns + ')' ELSE ')' END+
' --' +
CAST(user_seeks * avg_user_impact as VARCHAR) as TotalCost
FROM
sys.dm_db_missing_index_group_stats as a
join
sys.dm_db_missing_index_groups as b
on a.group_handle = b.index_group_handle
join
sys.dm_db_missing_index_details as c
on b.index_handle = c.index_handle
ORDER BY user_seeks * avg_user_impact DESC

Missing Indexes - Part 2


I like to run these two scripts and contrast and compare the difference in output.  Previously I was using the user_seeks *  avg_total_user_cost as my  Total Cost figure but since defected to user_seeks * avg_user_impact method.
 

select
a.user_seeks,
a.avg_total_user_cost,
a.avg_user_impact,
c.statement,
c.equality_columns,
c.inequality_columns,
c.included_columns,
user_seeks * avg_total_user_cost as TotalCost
from
sys.dm_db_missing_index_group_stats as a
join
sys.dm_db_missing_index_groups as b
on a.group_handle = b.index_group_handle
join
sys.dm_db_missing_index_details as c
on b.index_handle = c.index_handle
--order by user_seeks desc--order by avg_total_user_cost
order by TotalCost desc

select
a.user_seeks,
a.avg_total_user_cost,
a.avg_user_impact,
c.statement,
c.equality_columns,
c.inequality_columns,
c.included_columns,
user_seeks *
avg_user_impact as TotalCost
from
sys.dm_db_missing_index_group_stats as a
join
sys.dm_db_missing_index_groups as b
on a.group_handle = b.index_group_handle
join
sys.dm_db_missing_index_details as c
on b.index_handle = c.index_handle
--order by user_seeks desc--order by avg_total_user_cost
order by TotalCost desc

Routine Maintenance

Some of my clients have wild servers and databases are added without discrimination.  Unfortunately as I only visit there once a month it's possible for the default Full recovery model to eat all the space up.

Heres a routine to turn all but certain databases to the simple recovery model.


set nocount on

declare @DBName nvarchar(255)

declare @ExecSQL nvarchar(1024)

set @DBName = null

select @DBName = MIN(name) from sys.databases

while @DBName is not null

begin

if @DBName NOT IN ('master', 'msdb', 'tempdb', 'model', 'distribution', 'userdatabasename')

begin

set @ExecSQL = ''

set @ExecSQL = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE'

select @ExecSQL

exec @ExecSQL

end

select @DBName = MIN(name) from sys.databases where name > @DBName

end