Index Management

Question

Post reply

Index Management

Viewing 15 posts - 61 through 75 (of 84 total)

You must be logged in to reply to this topic. Login to reply

crousset SSC Rookie Points: 28 More actions · Answer 1

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

INNOCENT GUMBO Old Hand Points: 378 More actions · Answer 2

INNOCENT GUMBO

Old Hand

Points: 378

November 3, 2009 at 3:04 am

#1074177

Kristof

sure, checked. Kristof is right.

gavinparnaby SSC Enthusiast Points: 143 More actions · Answer 3

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?

INNOCENT GUMBO Old Hand Points: 378 More actions · Answer 4

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)

INNOCENT GUMBO Old Hand Points: 378 More actions · Answer 5

INNOCENT GUMBO

Old Hand

Points: 378

November 3, 2009 at 7:18 am

#1074254

gavinparnaby :

Sorry, I will check your sub query and I will give you feedback tomorrow. Please bear with us.

karl.spam Valued Member Points: 62 More actions · Answer 6

karl.spam

Valued Member

Points: 62

November 3, 2009 at 2:11 pm

#1074515

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.

INNOCENT GUMBO Old Hand Points: 378 More actions · Answer 7

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-2 int;

SET @a = 2;

SET @b-2 = 0

SELECT @a/@b;

(2)

DECLARE @a Money, @b-2 int;

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.....

gavinparnaby SSC Enthusiast Points: 143 More actions · Answer 8

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.

JRoughgarden Ten Centuries Points: 1119 More actions · Answer 9

JRoughgarden

Ten Centuries

Points: 1119

January 18, 2010 at 11:09 am

#1105041

Has anyone implemented this in production? If so, could you share your experience? Any tips or tricks or gotcha's?

Thanks in advance.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 10

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

SQLCrazy SSC Journeyman Points: 86 More actions · Answer 11

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