SQLServerPedia

The Microsoft SQL Server Data Mining team is looking for ways to help DBAs with data mining, and they came up with an interesting idea: data mine the DMVs to find interesting information. I’m going to start by data mining index fragmentation statistics, and I need your help.

I’ve got a DMV query to gather information about indexes and index fragmentation. It dumps out a list of objects (without names) and their fragmentation percentages. I need you, buddy old pal, to run this query on your databases, export the results to CSV, and send it to me. If you could, include a note about whether or not you use any index maintenance jobs, like SQL’s built-in index rebuild maintenance plans.

Your submitted data will be completely confidential – I’ll put it into my data warehouse without any information to tie it back to you. It won’t even go to the Quest or Microsoft offices – actually, you’ll be emailing it to brento@brentozar.com because I don’t have a big enough inbox at Quest anyway, hahaha.

I’ll draw names this Monday, January 26, and 5 random winners will get a SQLServerPedia t-shirt. To enter, run the below script on as many databases as you want, store each db’s results in a CSV file, and email it to me. You get one entry per database, and yes, you can win more than once. Now would be a good time to grab your nearest developer, tell them you want to train them to be a DBA, and get them to do all the querying for you!

The overall findings will be published in blog entries at SQLServerPedia.

Here’s the script. This will grab some locks while it does the analysis, so I wouldn’t run this on your high-volume production boxes except after hours. It only produces data for the current database, so the easiest way to run it is to set SSMS to export results to file, and then run it in each database. Yes, this is a hassle. No, I don’t expect you to do it in every database you have. Don’t complain – if it was painless, I wouldn’t be offering t-shirts, hahaha.

SELECT ix.[type]
,ix.[type_desc]
,ix.[is_unique]
,ix.[data_space_id]
,ix.[ignore_dup_key]
,ix.[is_primary_key]
,ix.[is_unique_constraint]
,ix.[fill_factor]
,ix.[is_padded]
,ix.[is_disabled]
,ix.[is_hypothetical]
,ix.[allow_row_locks]
,ix.[allow_page_locks]
,ps.*
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N’Limited’) ps
INNER JOIN sys.indexes ix ON ps.object_id = ix.object_id AND ps.index_id = ix.index_id