indexes question

Question

indexes question

sqlfriend

SSC Guru

Points: 52794
More actions
July 26, 2011 at 10:49 am

#244855

I did a query using DMV to get info for index
SELECT d.name, t.name, i.name, ius.*
FROM sys.dm_db_index_usage_stats ius
JOIN sys.databases d ON d.database_id = ius.database_id
JOIN sys.tables t ON t.object_id = ius.object_id
JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id = ius.index_id
ORDER BY user_updates DESC
And as far as I know it's recommended :Column user_updates in particular shows the number of updates caused by INSERT, UPDATE or DELETE operations. If this is high in relation to the number of reads, consider dropping the index.
But I see in our case it shows if the user_updates is higher than user_seek, or user_scans, it is usually a Primary key, and the table usually should have the primary key.
So we shouldn't drop the primary key index for those tables, correct?

Viewing 9 posts - 1 through 9 (of 9 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You need to filter all the unique constraints from that report. The PK is there for a reason. It is also index to speed up operations, not the other way around.

P.S. I can't smell this type of query (not yours, just the type). It doesn't take into account that sometimes a single seek operation can save millions of reads. While the write operation will just write 1 or 10 rows into a single page. And once in a while do a split).

I've been meaning to take this on but it hasn't been on top of my list.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Do note that the information there is since the last time the database was started....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52794 More actions · Answer 3

Thanks,

when you say: You need to filter all the unique constraints from that report.

How can I do this?

By the way I'm reading and learning from this article:

http://www.sqlservercentral.com/articles/Indexing/74510/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

WHERE i.is_unique = 0 AND i.type_desc NOT IN ('HEAP', 'CLUSTERED')

If I were you I'd read up on those tables in BOL. There's a ton of info in the new sys views. You can do pretty much anything you want.

sqlfriend SSC Guru Points: 52794 More actions · Answer 5

Thanks, but the sys.databases and sys.tables and sys.indexes, they are the new sys view, aren't they?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

New as in 6 years old, yes. Introduced with SQL 2005.

Doesn't change that there's lots of info in Books Online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

GilaMonster (7/26/2011)
New as in 6 years old, yes. Introduced with SQL 2005.
Doesn't change that there's lots of info in Books Online.

Ok I was late moving to sql 2k5, but I'm still having fun with all the "new" stuff :hehe:.

sqlfriend SSC Guru Points: 52794 More actions · Answer 8

And I also found out these kind of article focus how to troubleshoot, and more practical, vs bookonline is just like a library, and collection of rules, but didn't teach you how to use it in real life.