Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Index Management Expand / Collapse
Author
Message
Posted Tuesday, October 20, 2009 6:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 20, 2009 8:02 AM
Points: 2, Visits: 3
The second-to-last call doesn't work due to casting problems.

Is there a way to have something like this for SQL2000?
Post #805633
Posted Tuesday, October 20, 2009 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 5:55 AM
Points: 126, Visits: 139
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.


Post #805695
Posted Tuesday, October 20, 2009 7:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:29 AM
Points: 269, Visits: 306
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?


Post #805702
Posted Tuesday, October 20, 2009 7:42 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 535, Visits: 881
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




Over 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
Post #805705
Posted Tuesday, October 20, 2009 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 20, 2009 8:02 AM
Points: 2, Visits: 3
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 :)
Post #805707
Posted Tuesday, October 20, 2009 7:55 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 535, Visits: 881
I forgot to mention that the article is quite informative and I think with a little tuning I can run this with confidence.


Over 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
Post #805722
Posted Tuesday, October 20, 2009 8:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:29 AM
Points: 269, Visits: 306
Thanks for the revised code. I've added the following to proc_FilltblIndexUsageInfo:

where
so.type = 'u'
and dbo.Uf_GetindexSize(si.index_id, so.object_id) > 0

to prevent divide by zero errors.



Post #805743
Posted Tuesday, October 20, 2009 8:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 8:13 AM
Points: 68, Visits: 400

Procedure proc_FilltblUnusedIndexes (Manage Indexes)

Please check that table tblIndexUsageInfo does not have a field by the name IndexSelectionCrieteria
which you used on the UNION. If it is not a mistake please can you ALTER the Table and show us the value of the column? Otherwise the whole process is very neat.
Post #805750
Posted Tuesday, October 20, 2009 8:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 28, 2013 1:27 AM
Points: 1, Visits: 19
Here the worling Skcript on SQL Server 2008:

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),
IndexSelectionCrieteria int
)
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,
-1
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

Execute proc_InsertMostUsedIndexes 10.00,100
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
(@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.IndexSelectionCrieteria ,
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,
b.IndexSelectionCrieteria
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_FindMisisngIndexes
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(2000),
@Equalitycols VARCHAR(2000),
@InEqualitycols VARCHAR(2000),
@Includedcols VARCHAR(2000) )
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

SET @str =REPLACE(REPLACE(@str,']]',']'),'[[','[')

RETURN @str
END
GO

CREATE PROCEDURE proc_CreateMissingIndexes @significance DECIMAL(10,0)
AS
BEGIN
DECLARE @Count INT,
@SqlStr VARCHAR(8000)

DROP TABLE temp;

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
GO

DELETE FROM [KinoSQL].[dbo].[tblMissingIndexes]
GO

Execute proc_InsertMostUsedIndexes 0.00,0
GO

EXECUTE proc_filltblindexusageinfo
GO
EXECUTE proc_InsertMostUsedIndexes 0.00,0
GO
EXECUTE proc_RebuildSelectedIndexes
GO
EXECUTE proc_FilltblUnusedIndexes 1,0
GO
EXECUTE proc_FindMisisngIndexes
GO
EXECUTE proc_CreateMissingIndexes 0
GO

SELECT TOP 1000 [Sno]
,[DatabaseName]
,[tablename]
,[Significance]
,[CreateIndexStatement]

FROM [KinoSQL].[dbo].[tblMissingIndexes]
GO




Post #805751
Posted Tuesday, October 20, 2009 8:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 308, Visits: 648
The content is sound and fairly well thought out. As stated by others the code is a mess with errors, typos, etc. I do not like the ideal of creating "missing" indexes automatically. This leaves you wide open to problems down the road not to mention source control issues.
In all I think you did a good job here technically but please test your code before posting.



Post #805798
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse