November 3, 2009 at 2:41 am
Grassohopper
I'am sorry but I disagree with you. I still think that it's not normal.
If you look at the tblIndexUsageInfo table you will see that if the table as one index you will have one line with the index name,
If the table has two index you will have four line two for each index,
If the table have three indexes you will have nine line three for each indexes and so on.
More important you will see that the user_seeks, user_scans etc value are the same and are repeated.
This is due to a cross join because it miss the "and spi.index_id=si.index_id" statement for the join on dm_db_index_usage_stats.
If you look at
select * from sys.dm_db_index_usage_stats
where database_id='9' (put the database_id you wish)
you will clearly see that there is only one and unique index_id for each object_id (table) and one and unique values for user_seeks, user_scans etc....
for example here what it gives you without the correct join a select on tblIndexUsageInfo:
tablename indexname seeks scans lookups update
---------- ------------------------------ ------ ------ ------- ------
extract_sd IX_extract_sd_wan 0 0 0 11714
extract_sd IX_extract_sd_wan 0 0 0 46838
extract_sd IX_extract_sd_wan 46837 5 0 11714
extract_sd IX_extractsd_exsd_honoraire 0 0 0 11714
extract_sd IX_extractsd_exsd_honoraire 0 0 0 46838
extract_sd IX_extractsd_exsd_honoraire 46837 5 0 11714
extract_sd PK_Extract_sd 0 0 0 11714
extract_sd PK_Extract_sd 0 0 0 46838
extract_sd PK_Extract_sd 46837 5 0 11714
with the correct join you will have
tablename indexname seeks scans lookups update
---------- ------------------------------ ------ ------ ------- ------
extract_sd IX_extract_sd_wan 46837 5 0 11714
extract_sd IX_extractsd_exsd_honoraire 0 0 0 11714
extract_sd PK_Extract_sd 0 0 0 46838
regards
Kristof
Kristof
November 3, 2009 at 3:04 am
Kristof
sure, checked. Kristof is right.
November 3, 2009 at 3:08 am
Ok, I'm following what Kristoff has done there. I'm still unable to run because of the subquery returning multiple row problem.
Am I correct that having multiple indexes named the same is a bad thing? Is there actually a requirement for the subquery
(select index_id from sys.indexes where name=b.IndexName) As Index_id
in the first place as Index_ID is in the IndexUsageInfo table?
November 3, 2009 at 7:15 am
gavinparnaby :
If I undestend you correclty you are saying you are getting there is a resultset of duplicate indexes of differnt tables. I need to be corrected here. Indices are other data structures grouped in a DMV called sys.indexes. So, these tables have the same index names. check the names of the tables and run this stored proc under that Database :
sp_helpindex '<tablename>' . you will make sure that they share the same names OR
run : select o.Object_id, o.name AS TableName, i.Name AS IndexName
FROM sys.Objects o
INNER JOIN sys.indexes i
ON o.Object_id = i.object_ID
AND o.type = 'U'
ORDER By o.name , i.Name
You will be able to see duplicates. If the TableName and Object_Id are the same then conclude that you are actually duplicating one thing(same object)
November 3, 2009 at 7:18 am
gavinparnaby :
Sorry, I will check your sub query and I will give you feedback tomorrow. Please bear with us.
November 3, 2009 at 2:11 pm
I get a divide by Zero error when running proc_FilltblIndexUsageInfo. I believe this is caused by having indexes of 0 bytes when trying to calculate the ratio.
November 3, 2009 at 11:34 pm
karl;
Dont avoid dividing by Zero. SQL has a very good facility to overcome that. For instance, run these 2 queries and tell me the results:
(1)
SET @a = 2;
SET @b-2 = 0
SELECT @a/@b;
(2)
SET @a = 2;
SET @b-2 = 0
SELECT @a/ NULLIF(@b,0);
ALTERNATIVELY, you dont want a NULL but 0 : SELECT ISNULL(@a/NULLIF(@b,0),0)
This issue should not be a show stopper! Always use it when doing divisions, you will not feel bad.....
November 4, 2009 at 3:25 am
I'm still playing with this and trying to learn.
The following codeselect 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.udf_GetIndexCol(si.index_id,so.object_id,0) As IndexKeyColumn,
dbo.udf_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.udf_GetIndexsize(si.index_id,so.object_id) as 'IndexSizeKB',Cast(
(user_seeks+user_scans+user_lookups+user_updates)/
dbo.udf_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 and spi.index_id=si.index_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'
which is used in the initial IndexUsageInfo sp seems to only pull through details on tables which have a Primary Key. I have a whole host of tables which don't have PK's set but do have some indexing, and yet they're not appearing. Why?
Edit: - I've just spotted it. if changing the inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS to LEFT JOIN, it returns all indexes.
January 18, 2010 at 11:09 am
Has anyone implemented this in production? If so, could you share your experience? Any tips or tricks or gotcha's?
Thanks in advance.
January 18, 2010 at 11:46 am
Just found this article. I thought this was a well-thought out article. There is useful information in the article - with some good ideas.
Like others, I am not in favor of auto-creating any missing indexes. Nor am I in favor of dropping any indexes that "aren't" used.
Since everything is logged to a table though, I think I would use the script to that point and then take the opportunity to add or drop indexes from there.
Thanks for the article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 8, 2010 at 12:51 pm
I have been working through this article and have found very instructional and also useful. I have not noticed anyone having problems with proc_InsertMostUsedIndexes. I am stuck. Whenever I run it I get the following error:
Msg 512, Level 16, State 1, Procedure proc_InsertMostUsedIndexes, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I am at a deadend so if anyone could give me any help it would be greatly appriciated.
Thanks in advance.
Post #861979
April 1, 2010 at 10:13 am
I haven't reviewed this article in a while. Is the original article updated with all of the changes mentioned in this discussion?
April 19, 2010 at 6:27 am
It doesn't look like it has been updated...
SQLFrenzy - can you update the original article with the fixed code please?
Thanks much 🙂
April 23, 2010 at 1:59 am
am working on automating this across all servers and databases. It may take some time.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 21, 2010 at 8:02 am
Hey SQLFrenzy, great work.
We all should expect articles to be a good starting point and not a perfect solution. Your thought process is sound to me.
Sean
[font="Tahoma"]Cheers,
Sean :-D[/font]
Viewing 15 posts - 61 through 75 (of 84 total)
You must be logged in to reply to this topic. Login to reply