We recently spotted that one of the systems we use did not make use of clustered indexes on any tables out of the box. As a result performance was not as good as it could have been. The below script allows for an easy win optimisation by finding all tables which do not include a clustered index, and converting the table’s primary key to be a clustered index.
In a future post I’ll put up more details on what clustered indexes are, why you should always (pretty much) use them and other useful info for anyone playing with databases.
--This script is designed for MS SQL Server
use DbNameToOptimise
go
declare @sqls table(object_id bigint, sort int, sql nvarchar(max))
insert @sqls
select t.object_id, ic.key_ordinal, case when ic.key_ordinal=1 then 'CREATE UNIQUE CLUSTERED INDEX [' + i.name + '] ON [' + t.name + ']([' else ',[' end + c.name + case when ic.key_ordinal=icagg.maxko then ']) WITH DROP_EXISTING' else ']' end sql
from sys.tables t
inner join sys.indexes i on t.object_id = i.object_id
inner join sys.index_columns ic on i.object_id=ic.object_id and i.index_id = ic.index_id
inner join sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
inner join (
select object_id, index_id, MAX(key_ordinal) maxko from sys.index_columns group by object_id,index_id
) icagg on i.object_id = icagg.object_id and i.index_id = icagg.index_id
where t.is_ms_shipped=0
and i.is_primary_key=1
and not exists (
--ignore tables which already have a clustered index
select 1
from sys.indexes i2
where t.object_id = i2.object_id
and i2.type = 1
)
order by t.name, i.name, ic.key_ordinal
declare @objid bigint
, @sql nvarchar(max)
while exists (select top 1 1 from @sqls)
begin
set @sql=''
select top 1 @objid=object_id from @sqls
select @sql = @sql + sql from @sqls where object_id=@objid order by sort
delete from @sqls where object_id = @objid
exec (@sql)
end
function GetCurrentDateTime()
{
var result = new Date();
return ToXmlDate(result);
}
function ToXmlDate(d)
{
//I'm using the UTC date to avoid timezone issues
return d.getUTCFullYear()
+ '-' + ToMonth(d.getUTCMonth())
+ '-' + LPad(d.getUTCDate(),'0',2)
+ 'T' + LPad(d.getUTCHours(),'0',2)
+ ':' + LPad(d.getUTCMinutes(),'0',2)
+ ':' + LPad(d.getUTCSeconds(),'0',2)
+ '.' + LPad(d.getUTCMilliseconds(),'0',3)
+ 'Z';
}
function ToMonth(i)
{
i++; //the month is given as a zero based index, rather than the month value
return LPad(i,'0',2);
}
function LPad(s,c,l)
{
return (""+s).PadLeft(l,c); //ensures that s is a string, then uses the strings padleft function
}
]]>
</msxsl:script>
</xsl:stylesheet>
