June 10, 2011 at 7:44 am
Good dtuff, thank you, but I was getting "division by zero" so I wrapped relevant code of proc_FilltblIndexUsageInfo in a CASE:
CASE WHEN dbo.Uf_GetindexSize(si.index_id, so.object_id) != 0 THEN
CAST((user_seeks + user_scans + user_lookups + user_updates)
/ dbo.Uf_GetindexSize(si.index_id, so.object_id) AS DECIMAL(10,2))
ELSE 0 END AS IndexUsagetoSizeRatio
June 10, 2011 at 10:58 am
Nice article SqlFrenzy!!
I had an in-database index sproc but I like the additional functionality of needed and unused indexes.
I am updating the code to run from a Maintenance type database and to run for a specific database. The independent database allows me to update indexes for third party apps and Share Point databases. I am a bit leery of the automatic drop and create of indexes but that looks like a scheduled job on the weekends.
THANKS!
Daryl
June 10, 2011 at 11:24 am
A couple of comments:
1. I appreciate the offer of these utilities and the benefit of the work put into this.
2. However, I get the "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= ..."
3. The code appears to be written in a form to purposefully obfuscate it's meaning.
4. If the forum software could make the "code windows" on the site more "copy/paste friendly" that would also be a great help--when I copy/paste it spits everything out in one huge line and seems to strip whitespace, linefeeds, etc, so I have to spend tedious time finding the linebreaks, etc.
5. I don't mean to "look a gift horse in the mouth" but I would greatly appreciate it if an updated version could be posted--without the errors, etc.
6. Bottom line--this does not work at all--and again--I appreciate free code but if it doesn't work it actually costs me time trying to get it to work so it's not free anymore.
7. If I'm out of line, please let me know. I just can't get this bit of code to work when I copy it as posted.
Thank you.
June 10, 2011 at 11:50 am
This is a great article, and well thought out. I have been in the process of writing something that I can use here - but not to this extent.
I am going to implement it to a point and run tests.
I am unwilling to automatically drop or create indexes, but will use it to a point. I would, as some have said here, want to evaluate the data which is conveniently stored in a table and decide. But, to me, the theory is sound. I look forward to following this thread further.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
June 10, 2011 at 11:50 am
Not sure what your pasting into Grasshopper, code block I copy, pastes well formed for me in NOTEPAD, NOTEPAD2 and into a query pane.
😀
ACN is the world's largest direct seller of telecommunications and essential services.
http://helpu.acndirect.com/
ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
destinations around the world, including India, Mexico and the UK!
:hehe:
June 10, 2011 at 12:30 pm
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.
June 10, 2011 at 2:17 pm
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.
June 16, 2011 at 8:17 am
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
June 16, 2011 at 1:54 pm
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
July 6, 2011 at 1:11 am
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
Viewing 10 posts - 76 through 84 (of 84 total)
You must be logged in to reply to this topic. Login to reply