October 20, 2009 at 7:55 am
I forgot to mention that the article is quite informative and I think with a little tuning I can run this with confidence.
October 20, 2009 at 8:06 am
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.
October 20, 2009 at 8:09 am
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.
October 20, 2009 at 8:11 am
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
ELSE (@str + '_' + ISNULL(@Equalitycols,''))
END
SET @STR=
CASE
WHEN @InEqualitycols IS NULL THEN
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
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
October 20, 2009 at 8:59 am
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.
October 20, 2009 at 9:08 am
The create index syntax appears flawed as well. There are several occurrences where the column names are not parsed correctly. It does create a good result set to analyze where indexes may be needed.
October 20, 2009 at 9:21 am
Peter;
Cool.... This is a complete thing. It doesn't mean the original scripts were wrong, there were very fantastic only some typo errors. But we know that sometimes we can not make assumptions on what somebody was trying to do......
One thing again, I will be very grateful if somebody can come out and show me how to script Logins in SQL 2005 or 2008 as in SQL 2000 where we use the sp_revlogin when migrating from 2000 to 2005.
I want to script my Logins to move from an old Server to a new Server with the same Version (Or from one instance of SQL 2005 to another Instance)
Once again thanks Peter...........
October 20, 2009 at 9:27 am
One needs to be cautious about eliminating or filtering out 'unused' indexes. I think that sys.dm_db_index_usage_stats only shows indexes that were used since the last time SQL server was started. If an index is dropped that is only used at the end of the month (e.g. payroll), you might start hearing complaints from your users.
Also, I believe that index rebuilds can only be done offline unless you're using enterprise edition.
October 20, 2009 at 9:39 am
jbuttery, you are right but this is an issue where we assume everyone is using Enterprise Edition and again your server is always up. I am talking about a LIVE environment where everything is LIVE 24/7
365 days...........Sorry if you are not using Enterprise.
October 20, 2009 at 10:32 am
Here is my spin on this. First, I can appreciate the effort taken forth here. Anyone who has been in a DBA role for any period of time will soon find out how much effort was put into place in performance tuning a system. Case in point the database structures that I manage are probably the best in design. Best in normalization as well as denormalization. However out of all of the preceding DBAs it appears that none of them put forth an effort in performance tuning the database. Also the dynamics of a database changes as more and more data is stored. By taking a proactive approach to keep ahead of the curve of performance issues/problems is key. Initially I've taken a reactive approach for performance tuning... This often comes to me in the form of "how come this process is taking so much time"? You get the picture.
So what to do? As a good DBA knows insure that proper index maintenance is being performed. Then find the bottlenecks in processes. I have found that most performance issues can be resolve by throwing indexes to improperly indexed tables. There have been times where I've gone ahead and re-engineered processes to get them to perform better.
So I applaud the effort taken here. It may not work in all environments but it is a solution that worked in the Ahmads.
My previous DBA role, working on a VLD, I got creative when it came time to manage indexes. The process evaluated the index fragmentation and only re-indexed only those that were more than 10% fragmented which turned out to be less than 10% of the indexes but most used.
Like others in this discussion I will look at the scripts presented and make my own assessment. If I see value in what Ahmad presented then I will consider using it in my own environment.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
October 20, 2009 at 2:40 pm
pnewhart (10/20/2009)
The create index syntax appears flawed as well. There are several occurrences where the column names are not parsed correctly. It does create a good result set to analyze where indexes may be needed.
thanks...plz provide with the case where you get the error..I would update the script as required.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 20, 2009 at 2:44 pm
jbuttery (10/20/2009)
One needs to be cautious about eliminating or filtering out 'unused' indexes. I think that sys.dm_db_index_usage_stats only shows indexes that were used since the last time SQL server was started. If an index is dropped that is only used at the end of the month (e.g. payroll), you might start hearing complaints from your users.Also, I believe that index rebuilds can only be done offline unless you're using enterprise edition.
yes...sys.dm_db_index_usage_stats only shows indexes that were used since the last time sql server was started...that's the main idea behind the article...all information is stored in tables...thus preserving the information in case of a restart....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 20, 2009 at 2:45 pm
Nice content author, keep up the good work.
Would like to bring up a dissenting opinion though. I struggle with the auto creation of indexes. I have used the missing index dmv many times, and I think it is an excellent choice for pointing you in the direction of where there is an index problem. But I do not agree with the automation of the index creation.
Some thoughts:
-Huge risk of over indexing the entity, which can really hurt dml
-Still need manual intervention to determine if the table is clustered correctly, as the suggested index
may in fact need to be the sort order of the table.
- In my experience the index that you would create would be really really wide. This is probably a design flaw with the table to begin with
Am I off base here, are we at the point where literally SQL can index itself? Again, I feel this view should only be used as a suggestion of where there are index problems, not the seed for the index bot.
October 20, 2009 at 2:46 pm
ghughes (10/20/2009)
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.
I have updated the contents..it will be available soon
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 20, 2009 at 2:50 pm
jmcgarvey (10/20/2009)
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.
I have updated the content and it will be available soon...Every missing index you create is saved in a table...thus you can easily get the script whenever required...also you need not create every missing index...also every missing index you create is checked for its usefulness in step 1 and step 2.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
Viewing 15 posts - 16 through 30 (of 84 total)
You must be logged in to reply to this topic. Login to reply