SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Management


Index Management

Author
Message
Alexander Suprun
Alexander Suprun
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 1516
So after 2 years this management script is still has a logic error which makes using it poitless if not saying harmful.

Major issues:
'IndexUsage' column calculated as (user_seeks+user_scans+user_lookups+user_updates) is used to decide whether we need to rebuild or drop the index.
So in case there is a totally useless index on highly active table where user_seeks+user_scans+user_lookups = 0 but user_updates is big enough then such an index would be rebuilt each and every time we run the script consuming cpu and creating extra load on I/O system. When it's obvious that such an index should be dropped as useless one.
And what is so called "Unused Indexes" are not affecting performance at all, if there are no updates on these indexes then there are no maintenance costs. The only reason you may want to drop them is because of the disk space.
Usually to find good/bad indexes one should use some kind of difference between (user_updates) and (user_seeks+user_scans+user_lookups)

Minor issues:
Limitition of Uf_GetIndexSize to only non-partitioned tables.
Reorganizing heavily used indexes every time even if they don't have fragmentation (@avg_frag<=20)
REDUILD index without ONLINE option (for enterprise edition) may lock the whole table and it is very critical for 24/7 systems
There is no deletes from tblUnusedIndexes, so I'm assuming it's a history table? If so then there is no timestamp in it. Analyzing such a mess can become a challenging task for a DBA.
Dropping index by some fake ID - @UnusedIndID is not a wise decision as well.


Alex Suprun
johnzabroski
johnzabroski
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 191
alexander.suprun (6/10/2011)
So after 2 years this management script is still has a logic error which makes using it poitless if not saying harmful.

Major issues:
'IndexUsage' column calculated as (user_seeks+user_scans+user_lookups+user_updates) is used to decide whether we need to rebuild or drop the index.
So in case there is a totally useless index on highly active table where user_seeks+user_scans+user_lookups = 0 but user_updates is big enough then such an index would be rebuilt each and every time we run the script consuming cpu and creating extra load on I/O system. When it's obvious that such an index should be dropped as useless one.
And what is so called "Unused Indexes" are not affecting performance at all, if there are no updates on these indexes then there are no maintenance costs. The only reason you may want to drop them is because of the disk space.
Usually to find good/bad indexes one should use some kind of difference between (user_updates) and (user_seeks+user_scans+user_lookups)

Minor issues:
Limitition of Uf_GetIndexSize to only non-partitioned tables.
Reorganizing heavily used indexes every time even if they don't have fragmentation (@avg_frag<=20)
REDUILD index without ONLINE option (for enterprise edition) may lock the whole table and it is very critical for 24/7 systems
There is no deletes from tblUnusedIndexes, so I'm assuming it's a history table? If so then there is no timestamp in it. Analyzing such a mess can become a challenging task for a DBA.
Dropping index by some fake ID - @UnusedIndID is not a wise decision as well.


Why are people using this script? Just use Ola Hallengren's IndexOptimize stored procedure to take care of your existing indices, and set-up a table to keep track of your indices data. The script in this article just has too many dependencies.

This script is bad for many reasons, including the fact that it does not automatically break down fragmentation into external and internal fragmentation. It also divides by zero. Small tables will constantly be rebuilt with this script, which is pointless. SSMS's Index Physical Statistics standard report has the same epic failure.

As a brief aside, although I love Ola Hallengren's scripts, I really hate the lack of higher-order functions in TSQL stored procedures. Ola's scripts are not that only TSQL scripts that emulate the usefulness of passing higher-order functions as parameters via passing in strings and parsing the string during execution. The problem with parsing strings is that if Ola or whoever wrote the string parser didn't think of a scenario you had in mind, then you either have to edit the script or do some complex logic before invoking the script.

Please also see Limitations on using Missing Indexes feature on MSDN.
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2872 Visits: 4674
missed this 2 years ago but will check this out now and see how it fits in with what i built in the last 6 months. i have the DMV's running and putting data into a table and then i use SSRS to generate a few reports from the data. unused indexes, missing indexes, usage of indexes for critical tables and indexes that haven't been used in the last 30 days

unused indexes i mostly see on subscribers. i deleted a lot of them and it improved performance with replication. we have hundreds of replication jobs and used to get the dreaded error pop ups every few weeks. not it's once every few months

missing indexes i'm still playing with since a lot of the data on SQL 2005 seems to be buggy

where did the significance factor come from in the calculation? i've never read about it. my missing index report is filtered based on a minimum number of seeks or whatever that SQL says would be saved. i think i filter anything over 1000
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2872 Visits: 4674
added your special columns to my table for this so that it now looks like

USE [Performance]
GO

/****** Object: Table [dbo].[perf_index_usage_stats] Script Date: 06/16/2011 15:41:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[perf_index_usage_stats](
[server_name] [nvarchar](128) NULL,
[date_read] [datetime] NULL,
[database_id] [smallint] NULL,
[database_name] [nvarchar](128) NULL,
[table_name] [nvarchar](128) NULL,
[index_id] [int] NULL,
[index_name] [nvarchar](128) NULL,
[user_seeks] [bigint] NULL,
[user_scans] [bigint] NULL,
[user_lookups] [bigint] NULL,
[user_updates] [bigint] NULL,
[last_user_seek] [datetime] NULL,
[last_user_scan] [datetime] NULL,
[last_user_lookup] [datetime] NULL,
[last_user_update] [datetime] NULL,
[system_seeks] [bigint] NULL,
[system_scans] [bigint] NULL,
[system_lookups] [bigint] NULL,
[system_updates] [bigint] NULL,
[last_system_seek] [datetime] NULL,
[last_system_scan] [datetime] NULL,
[last_system_lookup] [datetime] NULL,
[last_system_update] [datetime] NULL,
[pk_id] [bigint] IDENTITY(1,1) NOT NULL,
[IndexUsage] [int] NULL,
[IndexSizeKB] [int] NULL,
[IndexUSageToSizeRatio] [decimal](10, 2) NULL,
[ConstraintType] [varchar](25) NULL,
[IndexKeyColumn] [varchar](1000) NULL,
[IncludedColumn] [varchar](1000) NULL,
[type_desc] [nvarchar](60) NULL,
CONSTRAINT [PK_perf_index_usage_stats] PRIMARY KEY NONCLUSTERED
(
[pk_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



for the first part of capturing the index usage info instead of using the functions i'm using a single query

select server_name = @@servername, date_read = getdate(), a.database_id, database_name = db_name(), table_name = OBJECT_NAME(a.[object_id]),
a.index_id, c.name as index_name, co.name as column_name,
case when ic.is_included_column = 1 then 'Included Column'
else 'Key Column' end Column_Type,
case when c.is_Primary_key = 1 then 'PK'
else 'I' end Index_Type,
c.type_desc,
(a.user_seeks + a.user_scans + a.user_lookups + a.user_updates) as 'IndexUsage',
a.user_seeks, a.user_scans, a.user_lookups, a.user_updates,
a.last_user_seek, a.last_user_scan, a.last_user_lookup, a.last_user_update,
a.system_seeks, a.system_scans, a.system_lookups, a.system_updates,
a.last_system_seek, a.last_system_scan, a.last_system_lookup, a.last_system_update
from sys.dm_db_index_usage_stats a
inner join sys.indexes c on c.object_id = a.object_id and a.index_id = c.index_id
inner join sys.columns co on co.object_id = c.object_id
inner join sys.index_columns ic on ic.object_id = co.object_id and ic.index_id = c.index_id
inner join sys.objects so on so.object_id = a.object_id
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS con
on so.name=con.TABLE_NAME where so.type='u'
and a.database_id in (10)
AND OBJECTPROPERTY(a.[object_id], 'IsMsShipped') = 0



tomorrow i'm going to work in the index size data but instead of the function, i have years of saved data from running sys.dm_db_index_physical_stats either daily or every few days and will use that.

and going to add the significance to my missing index scans to see if i can filter out some more data
azhar.iqbal499
azhar.iqbal499
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 525
Dear its nice article, but I face One issue here.
When I run proc_FilltblIndexUsageInfo sp then I get following error.

Msg 8134, Level 16, State 1, Procedure proc_FilltblIndexUsageInfo, Line 5
Divide by zero error encountered.
The statement has been terminated.

I thing this message is generated against Statement

Cast(
(user_seeks+user_scans+user_lookups+user_updates)/
dbo.Uf_GetindexSize(si.index_id,so.object_id) As decimal(10,2))
As IndexUsagetoSizeRatio.

Please review the Code because I cant Proceeded further unless this error is removed.
Thanks

Azhar Iqbal
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search