SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Management


Index Management

Author
Message
crousset
crousset
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
INNOCENT GUMBO
INNOCENT GUMBO
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 469
Kristof

sure, checked. Kristof is right.
gavinparnaby
gavinparnaby
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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?
INNOCENT GUMBO
INNOCENT GUMBO
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 469
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
INNOCENT GUMBO
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 469
gavinparnaby :

Sorry, I will check your sub query and I will give you feedback tomorrow. Please bear with us.
karl.spam
karl.spam
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 89
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
INNOCENT GUMBO
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 469
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.....
gavinparnaby
gavinparnaby
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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.
JRoughgarden
JRoughgarden
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 195
Has anyone implemented this in production? If so, could you share your experience? Any tips or tricks or gotcha's?

Thanks in advance.



SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64513 Visits: 18570
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search