indexes

Can I Use Statistics to Design Indexes? (Dear SQL DBA Episode 18)

Can I Use Statistics to Design Indexes? (Dear SQL DBA Episode 18)

Should you look at automatically created statistics on your tables in SQL Server to help you design better indexes? Learn why in this 20 minute video, or subscribe to the Dear SQL DBA podcast. No time to watch? Scroll on down, everything is written in article form below the video. Here’s this week’s question: Dear SQL DBA, I’ve noticed that many indexes in my data warehouse aren’t used frequently.
Index Usage Stats Bug Fixed in SQL Server 2012 SP2 CU12 / SP3 CU3

Index Usage Stats Bug Fixed in SQL Server 2012 SP2 CU12 / SP3 CU3

Microsoft has just released a new round of cumulative updates for SQL Server 2012, and the release notes indicate that a fix to the sys.dm_db_index_usage_stats dynamic management view has been included in these updates. This is good news for index tuners using SQL Server 2012.

As of SP2 + CU12 and SP3 + CU3, SQL Server 2012 will persist index usage information even when ALTER INDEX REBUILD is run.

How to Script Out Indexes from SQL Server

How to Script Out Indexes from SQL Server

Sometimes you need to script out all the indexes in a database. Maybe you’re concerned something has changed since they were last checked in. Or maybe the indexes aren’t checked into source control, and you’re working on fixing that. (Important!)

Either way, sometimes you need to do it, and it’s not fun through the GUI. I needed to write some fresh demo code for this recently, and I needed it to give the details for partitioned tables using data compression, and I thought I’d share.

Index Usage Statistics with ColumnList and Index Size

Index Usage Statistics with ColumnList and Index Size

As an add on to my last post, here is what I currently do use to track index usage. This shows usage, columns in the index, and index size on disk. The size can be quite useful to know when evaluating how much an index is worth– typically if this index is large then you’re paying a fair amount on the inserts.

Everything About Your Indexes (well, almost)

Everything About Your Indexes (well, almost)

I am going to post my monstrously big index query.

Why? Because it’s AWESOME. No really, it actually is awesome. At least, if you like that sort of thing. I use some variant of this almost daily, and I tweak it fairly regularly to suit the needs of whatever I’m working on. So it’s a work in progress, but I find it constantly valuable.