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 «««56789

Index Management Expand / Collapse
Author
Message
Posted Friday, June 10, 2011 12:30 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 10:45 AM
Points: 185, Visits: 915
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
Post #1123568
Posted Friday, June 10, 2011 2:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 6, 2014 1:24 PM
Points: 49, Visits: 189
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.
Post #1123654
Posted Thursday, June 16, 2011 8:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 1:38 PM
Points: 1,414, Visits: 4,536
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #1126610
Posted Thursday, June 16, 2011 1:54 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 1:38 PM
Points: 1,414, Visits: 4,536
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







https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #1126937
Posted Wednesday, July 6, 2011 1:11 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, June 26, 2014 8:54 AM
Points: 137, Visits: 443
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

Post #1137048
« Prev Topic | Next Topic »

Add to briefcase «««56789

Permissions Expand / Collapse