October 14, 2009 at 5:16 pm
Comments posted to this topic are about the item Index Management
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 19, 2009 at 10:50 pm
technically very good document. please continue do more research on locks also.
October 20, 2009 at 1:49 am
hi am kinda new here
can this be apllied on SQL 2000?
thanks!
October 20, 2009 at 2:30 am
The code posted contains a lot of errors. Misspelled procedure names, missing columns, wrong aliases.
I don't have time to do the error checking right now, but will post my findings/corrections later today (if it is not done by then...)
October 20, 2009 at 2:49 am
darwin.maramot (10/20/2009)
hi am kinda new herecan this be apllied on SQL 2000?
thanks!
No..It doesn't applies to sql 2000
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 20, 2009 at 2:50 am
dagfinn.molde (10/20/2009)
The code posted contains a lot of errors. Misspelled procedure names, missing columns, wrong aliases.I don't have time to do the error checking right now, but will post my findings/corrections later today (if it is not done by then...)
thanks..I will review and update asap...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 20, 2009 at 3:06 am
Sqlfrenzy (10/20/2009)
darwin.maramot (10/20/2009)
hi am kinda new herecan this be apllied on SQL 2000?
thanks!
No..It doesn't applies to sql 2000
ok thanks!
October 20, 2009 at 3:07 am
nice article
"Keep Trying"
October 20, 2009 at 4:50 am
Code posted is very inconsistent.
Uppercase, lowercase, schema names missing.
Prefixes are inconsistent - sometimes "Uf_", othertimes "fn_"
select * is naughty unless you need the full result set...
When using the "EXISTS" keyword you can simply "SELECT 1 WHERE ...."
October 20, 2009 at 6:23 am
When trying to bring your functions to 'life', I changed the proc_FilltblIndexUsageInfo
ALTER Proc [dbo].[proc_FilltblIndexUsageInfo]
AS
Begin
Truncate table tblIndexUsageInfo
insert into tblIndexUsageInfo
select db_name(db_id()) DbName,
so.name as 'TableName',
ISNULL(si.name,'No Index') as IndexName,
si.index_id,
Case When is_primary_key=1 then 'Primary Key Constraint'
Else 'Index' End ConstraintType,
si.type_desc,
dbo.Uf_GetIndexCol(si.index_id,so.object_id,0) As IndexKeyColumn,
dbo.Uf_GetIndexCol(si.index_id,so.object_id,1) As IncludedCols,
spi.user_seeks,spi.user_scans,spi.user_lookups,spi.user_updates,
(user_seeks+user_scans+user_lookups+user_updates) as 'IndexUsage ',
dbo.Uf_GetIndexSize(si.index_id,so.object_id) 'IndexSizeKB',
Cast(
(user_seeks+user_scans+user_lookups+user_updates)/
dbo.Uf_GetIndexSize(si.index_id,so.object_id) As decimal(10,2))
As IndexUsagetoSizeRatio
from sys.objects so inner join sys.indexes si
on so.object_id=si.Object_id
inner join sys.dm_db_index_usage_stats spi
on spi.Object_id=so.Object_id
and si.index_id=spi.index_id
and spi.database_id=db_id()
where so.type='u'
END
October 20, 2009 at 6:36 am
The second-to-last call doesn't work due to casting problems.
Is there a way to have something like this for SQL2000?
October 20, 2009 at 7:35 am
Where will the corrected code be placed? Will it be reposted? The articles topic and goals interested me, but the problems trying to get it to work are very frustrating.
October 20, 2009 at 7:39 am
Agreed. Also, index reorgs can somtimes wreak havoc with TL shipping. I haven't had the chance to completely finish these scripts due to time constraints. Is anybody else having an issue with Uf_GetIndexSize?
October 20, 2009 at 7:42 am
The fixed code... I am not sure if it runs but I can create all the objects.
create table tblIndexUsageInfo
(
Sno int identity(1, 1)
,Dbname varchar(100)
,TableName varchar(100)
,IndexName varchar(300)
,Index_id int
,ConstraintType varchar(25)
,Type_desc varchar(100)
,IndexKeyColumn varchar(1000)
,IncludedColumn varchar(1000)
,user_seeks int
,user_scans int
,user_lookups int
,user_update int
,IndexUsage int
,IndexSizeKB int
,IndexUSageToSizeRatio decimal(10, 2)
)
go
create function Uf_GetIndexCol
(
@index_id int
,@tableid int
,@isincluded bit
)
returns varchar(3000)
as
begin
return
(
stuff(
(select ',' + sc.name from sys.columns sc,
sys.index_columns sic,sys.indexes si
where sc.column_id=sic.column_id
and si.index_id=sic.index_id
and sc.object_id=sic.object_id
and si.object_id=sic.object_id
and sic.is_included_column=@isincluded
and si.object_id=@tableid
and si.index_id=@index_id
for xml path('')),1,1,'')
)
end
go
create function Uf_GetIndexSize
(
@index_id int
,@tableid int
)
returns float
as
begin
return (select (cast(reserved as float)*8192)/(1024) from sysindexes
where indid=@index_id and id=@tableid)
end
go
create proc proc_FilltblIndexUsageInfo
as
begin
truncate table tblIndexUsageInfo
insert into tblIndexUsageInfo
select distinct
db_name(db_id()) DbName
,so.name as 'TableName'
,isnull(si.name, 'No Index') as IndexName
,si.index_id
,case when is_primary_key = 1 then 'Primary Key Constraint'
else 'Index'
end ConstraintType
,si.type_desc
,dbo.Uf_GetIndexCol(si.index_id, so.object_id, 0) as IndexKeyColumn
,dbo.Uf_GetIndexCol(si.index_id, so.object_id, 1) as IncludedCols
,spi.user_seeks
,spi.user_scans
,spi.user_lookups
,spi.user_updates
,(user_seeks + user_scans + user_lookups + user_updates) as 'IndexUsage '
,dbo.Uf_GetindexSize(si.index_id, so.object_id) 'IndexSizeKB'
,cast((user_seeks + user_scans + user_lookups + user_updates) / dbo.Uf_GetindexSize(si.index_id, so.object_id) as decimal(10, 2)) as IndexUsagetoSizeRatio
from
sys.objects so
inner join sys.indexes si
on so.object_id = si.Object_id
inner join sys.dm_db_index_usage_stats spi
on spi.Object_id = so.Object_id
inner join sys.index_columns sic
on sic.object_id = si.object_id and sic.index_id = si.index_id
inner join sys.columns sc
on sc.Column_id = sic.column_id and sc.object_id = sic.object_id
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
on so.name = c.TABLE_NAME
where
so.type = 'u'
end
go
create table tblMostUsedIndexes
(
Sno int identity(1, 1)
,TableName varchar(100)
,IndexName varchar(1000)
,Index_id int
,SchemaName varchar(100)
,TableId int
,IndexUsage int
,IndexUSageToSizeRatio decimal(10, 2)
)
go
create proc proc_InsertMostUsedIndexes
(
@IndexUSageToSizeRatio decimal(10, 2)
,@indexusage int
)
as
begin
insert into tblMostUsedIndexes
select
b.TableName
,b.IndexName
,(
select
index_id
from
sys.indexes
where
name = b.IndexName
) as Index_id
,ss.name as Schemaname
,object_id(tablename)
,IndexUsage
,IndexUSageToSizeRatio
from
tblIndexUsageInfo b
,sys.tables st
,sys.schemas ss
where
(
b.indexusage >= @indexUsage or IndexUSageToSizeRatio >= @IndexUSageToSizeRatio
) and st.name = tablename and st.schema_id = ss.schema_id and b.indexname not in (select
indexname
from
tblMostUsedIndexes)
group by
b.indexname
,b.tablename
,ss.name
,b.IndexUSageToSizeRatio
,b.indexusage
end
go
create proc proc_RebuildSelectedIndexes
as
begin
set NOCOUNT on
/* Code to Rebuild or Reorganise index */
declare
@Schema varchar(200)
,@Tablename varchar(200)
declare
@indexName varchar(400)
,@Qry varchar(1000)
,@RecCount int
declare
@avg_frag decimal
,@dbid int
,@ObjectId int
declare
@IndexCount int
,@TotalRec int
,@Index_type varchar(50)
declare
@IndexRebuildCount int
,@IndexReorgCount int
,@IxOpr varchar(10)
declare @index_id int
set @IndexRebuildCount = 0
set @IndexReorgCount = 0
set @IxOpr = ''
set @dbid = db_id()
select
@RecCount = sno
from
tblMostUsedIndexes
set @TotalRec = @RecCount
while (@RecCount > 0)
begin
select
@Schema = schemaname
,@TableName = TableName
,@ObjectId = tableid
,@index_id = index_id
from
tblMostUsedIndexes
where
sno = @RecCount
select
identity( int,1,1 ) as Sno
,a.[name] IndexName
,avg_fragmentation_in_percent as avg_frag
,type_desc
,a.index_id
into
#temp_2
from
sys.dm_db_index_physical_stats(@dbid, @objectid, @index_id, null, 'Limited') as b
join
sys.indexes as a
on a.object_id = b.object_id and a.index_id = b.index_id and a.index_id > 0
select
@IndexCount = sno
from
#temp_2
while (@IndexCount > 0)
begin
select
@avg_frag = avg_frag
,@IndexName = indexname
,@Index_Type = type_desc
from
#temp_2
where
sno = @IndexCount
if (@avg_frag <= 20)
begin
set @Qry = 'Alter index ' + @IndexName + ' ON ' + @Schema + '.' + @TableName + ' REORGANIZE;'
set @IndexReorgCount = @IndexReorgCount + 1
set @IxOpr = 'REORGANIZE'
end
if (@avg_frag > 20)
begin
set @Qry = 'Alter index ' + @IndexName + ' ON ' + @Schema + '.' + @TableName + ' REBUILD;'
set @IndexRebuildCount = @IndexRebuildCount + 1
set @IxOpr = 'REBUILD'
end
print @qry
execute(@qry)
set @IndexCount = @IndexCount - 1
end
drop table #temp_2
set @RecCount = @RecCount - 1
end
set NOCOUNT off
end
go
create table tblUnusedIndexes
(
UnusedIndid int identity(1, 1)
,Schemaname varchar(100)
,tablename varchar(100)
,IndexName varchar(500)
,IndexUsage int
,IndexUsageToSizeRatio decimal(10, 2)
,IndexKey varchar(1000)
,IncludedCol varchar(1000)
,ConstraintType varchar(1000)
,IndexSizeKB int
,DropQry varchar(4000)
,IndexStatus varchar(20) default 'Active'
)
go
create procedure proc_FilltblUnusedIndexes --1,0
(
@IndexUsageToSizeRatio decimal(10, 2)
,@indexusage int
)
as
begin
insert into tblUnusedIndexes
(
Schemaname
,tablename
,IndexName
,IndexUsage
,IndexUsageToSizeRatio
,IndexKey
,IncludedCol
,ConstraintType
,IndexSizeKB
,DropQry
)
-- Indexes that does not exist in sys.dm_db_index_usage_stats
select
ss.name SchemaName
,so.name as TableName
,isnull(si.name, 'NoIndex') as IndexName
,0 IndexUsage
,0 IndexUsageToSizeRatio
,dbo.Uf_GetIndexCol(si.index_id, so.object_id, 0) as IndexKey
,dbo.Uf_GetIndexCol(si.index_id, so.object_id, 1) as IncludedCol
,case when is_primary_key = 1 then 'Primary Key Constraint'
else 'Index'
end ConstraintType
,dbo.Uf_GetIndexSize(si.index_id, so.object_id) as IndexSizeInKB
,case when (is_primary_key = 1) then ('alter table ' + so.name + ' drop constraint ' + si.name)
else ('Drop Index ' + ss.name + '.' + so.name + '.' + si.name)
end as DropQry
from
sys.objects so
inner join sys.indexes si
on so.object_id = si.Object_id
inner join sys.schemas ss
on ss.schema_id = so.schema_id
where
not exists ( select
*
from
sys.dm_db_index_usage_stats spi
where
si.object_id = spi.object_id and si.index_id = spi.index_id ) and so.type = 'U' and ss.schema_id <> 4 and si.index_id > 0 and si.name not in (
select
indexname
from
tblUnusedIndexes)
union
-- Indexes that doesn't satisfy the Indexusage criteria.
select
ss.name
,b.TableName
,b.IndexName
,b.IndexUsage
,b.IndexUSageToSizeRatio
,dbo.Uf_GetIndexCol(b.index_id, object_id(b.tablename), 0) as IndexKey
,dbo.Uf_GetIndexCol(b.index_id, object_id(b.tablename), 1) as IncludedCol
,b.ConstraintType
,dbo.Uf_GetIndexSize(b.index_id, object_id(b.tablename)) as IndexSizeInKB
,case b.ConstraintType
when 'Index' then ('Drop Index ' + ss.name + '.' + b.TableName + '.' + b.IndexName)
else ('alter table ' + b.TableName + ' drop constraint ' + b.IndexName)
end DropQry
from
tblIndexUsageInfo b
,sys.tables st
,sys.schemas ss
where
(
b.indexusage <= @indexUsage or IndexUsageToSizeRatio <= @IndexUsageToSizeRatio
) and st.name = tablename and st.schema_id = ss.schema_id and b.indexname not in (select
indexname
from
tblUnusedIndexes)
group by
b.indexname
,b.tablename
,ss.name
,ss.schema_id
,b.ConstraintType
,b.index_id
,b.indexusage
,b.IndexUsageToSizeRatio
end
go
create proc proc_DropUnusedIndex @UnusedIndID int
as
begin
declare @SqlStr varchar(4000)
select
@SqlStr = DropQry
from
tblunusedindexes
where
UnusedIndid = @UnusedIndID
begin tran
begin try
execute(@SqlStr)
update
tblunusedindexes
set
IndexStatus = 'Dropped'
where
UnusedIndID = @UnusedIndID
end try
begin catch
select
error_message() as ErrorMessage
if @@TRANCOUNT > 0
rollback transaction ;
end catch
if @@TRANCOUNT > 0
commit transaction
print 'Index dropped Successfully'
end
go
create table tblMissingIndexes
(
Sno int identity(1, 1)
,DatabaseName varchar(100)
,tablename varchar(200)
,Significance decimal(10, 0)
,CreateIndexStatement varchar(8000)
,Status varchar(20) default ('NotCreated')
)
go
create procedure proc_FindMissingIndexes
as
begin
insert into tblMissingIndexes
(
DatabaseName
,tablename
,Significance
,CreateIndexStatement
)
select
db_name(sid.database_id)
,sid.statement
,(avg_total_user_cost * avg_user_impact) * (user_scans + user_seeks) as Significance
,dbo.fn_CreateIndexStmt(sid.statement, sid.equality_columns, sid.inequality_columns, sid.included_columns)
from
sys.dm_db_missing_index_details sid
,sys.dm_db_missing_index_group_stats sigs
,sys.dm_db_missing_index_groups sig
where
sig.index_group_handle = sigs.group_handle and sid.index_handle = sig.index_handle
order by
significance desc
end
go
create function fn_CreateIndexStmt
(
@statement varchar(1000)
,@Equalitycols varchar(1000)
,@InEqualitycols varchar(1000)
,@Includedcols varchar(1000)
)
returns varchar(5000)
as
begin
declare
@STR varchar(5000)
,@tablename varchar(100)
set @tablename = substring(substring(@statement, charindex('.', @statement) + 1, len(@statement)),
charindex('.', substring(@statement, charindex('.', @statement) + 1, len(@statement))) + 1,
len(substring(@statement, charindex('.', @statement) + 1, len(@statement))))
set @Includedcols = replace(replace(@Includedcols, ']', ''), '[', '')
set @Equalitycols = replace(replace(replace(@Equalitycols, ']', ''), ', ', '_'), '[', '')
set @InEqualitycols = replace(replace(replace(@InEqualitycols, ']', ''), ', ', '_'), '[', '')
set @STR = 'Create Index Ix_' + replace(replace(@tablename, ']', ''), '[', '')
set @STR = case when @Equalitycols is null then @STR
else (@str + '_' + isnull(@Equalitycols, ''))
end
set @STR = case when @InEqualitycols is null then @STR
else (@str + '_' + isnull(@InEqualitycols, ''))
end
set @STR = @STR + ' ON ' + @statement + '(' + case when @Equalitycols is null then ''
else replace(isnull(@Equalitycols, ''), '_', ',')
end + case when @InEqualitycols is null then ''
else ',' + replace(isnull(@InEqualitycols, ''), '_', ',')
end + ')'
set @STR = case when @Includedcols is null then @STR
else @STR + 'Include (' + isnull(@Includedcols, '') + ')'
end
return @STR
end
go
create procedure proc_CreateMissingIndexes
@significance decimal(10, 0)
as
begin
declare
@Count int
,@SqlStr varchar(8000)
set @SqlStr = ''
select
identity( int,1,1 ) as Sno
,CreateIndexStatement
into
#temp
from
tblmissingindexes
where
significance > @significance
select
@count = count(*)
from
#temp
while (@count >= 0)
begin
select
@SqlStr = CreateIndexStatement
from
#temp
where
sno = @count
update
tblmissingindexes
set
Status = 'Created'
where
sno = @count
exec(@sqlStr)
set @count = @Count - 1
end
end
October 20, 2009 at 7:43 am
I'd like to mention that the article is really great and the errors in the script are solveable. The one with the wrong column-name was tricky, the rest just a little doing. I already created a lot of new indexes in my databases today 🙂
Viewing 15 posts - 1 through 15 (of 84 total)
You must be logged in to reply to this topic. Login to reply