Secondary Index Quick Fix
Sometimes it is necessary to take a blanket approach to tuning when initially faced with a poorly performing production system, for example just adding lots of memory!
The rationale behind this is that it is more risky to modify code than the database and or hardware and "quick fixes" are often the order of the day .. so .. this script will generate a create index script for any column ending ID ( likely to be a foreign key so used in joins etc. ) which doesn't have an index already. You might like to try it on your databases just to see < grin >
-- Set to required database
use xxxxx
go
-- I've used a # temp table so the contents can be saved or viewed
-- if required
create table #test2(ObjName sysname,ColName sysname,ObjId int,ColId int)
--
insert into #test2(ObjName,ColName,ObjId,ColId)
select obj.name, col.name, obj.id,col.colid
from dbo.sysobjects obj join dbo.syscolumns col on obj.id=col.id
where col.name like '%id'
and obj.xtype='U'
-- remove where column is already indexed
delete from #test2 where convert(varchar,objid)+':'+convert(varchar,colid) in
( select convert(varchar,id)+':'+convert(varchar,colid) from dbo.sysindexkeys )
-- put QA into text output
-- cut and paste output to create indexes
select 'create index idx_'+ObjName+'_'+colname+' on '+objname+'('+colname+')'+char(10)+'go'+char(10)
from #test2
-- once you're happy remove temp table
-- drop table #test2