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 Tuesday, November 3, 2009 2:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:55 AM
Points: 4, Visits: 36
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

Post #812805
Posted Tuesday, November 3, 2009 3:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 27, 2014 12:53 AM
Points: 68, Visits: 412

Kristof

sure, checked. Kristof is right.
Post #812820
Posted Tuesday, November 3, 2009 3:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 19, 2012 8:12 AM
Points: 11, Visits: 44
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?
Post #812823
Posted Tuesday, November 3, 2009 7:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 27, 2014 12:53 AM
Points: 68, Visits: 412
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)


Post #812908
Posted Tuesday, November 3, 2009 7:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 27, 2014 12:53 AM
Points: 68, Visits: 412
gavinparnaby :

Sorry, I will check your sub query and I will give you feedback tomorrow. Please bear with us.
Post #812910
Posted Tuesday, November 3, 2009 2:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, October 26, 2014 10:20 PM
Points: 32, Visits: 87
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.
Post #813225
Posted Tuesday, November 3, 2009 11:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 27, 2014 12:53 AM
Points: 68, Visits: 412
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)
DECLARE @a Money, @b int;

SET @a = 2;
SET @b = 0

SELECT @a/@b;

(2)
DECLARE @a Money, @b int;

SET @a = 2;
SET @b = 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.....
Post #813437
Posted Wednesday, November 4, 2009 3:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 19, 2012 8:12 AM
Points: 11, Visits: 44
I'm still playing with this and trying to learn.

The following code
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.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.
Post #813505
Posted Monday, January 18, 2010 11:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:06 PM
Points: 37, Visits: 176
Has anyone implemented this in production? If so, could you share your experience? Any tips or tricks or gotcha's?

Thanks in advance.



Post #849292
Posted Monday, January 18, 2010 11:46 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #849311
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse