SQL Server and Data Life Musings.

This month’s T-SQL Tuesday is hosted by Mike Fal (Blog | Twitter).  For this edition, the invitation was to show a cool trick you developed in SQL Server and explain how it helped you learn about how SQL Server works.  What I particularly like about this challenge is the fact that the trick developed does not need to be directly used in production; it could be something that was just developed in your free time.  Just as Google’s GMail service was developed during innovation time, so was my featured trick.

About half way into my first year of being a full-time DBA, I began to obsess over table indexes.  From strategy to design, best-practice to implementation, I just could not soak up enough knowledge to satisfy my thirst for what it takes to build a good index.  This lead to the creation of a few stored procedures that I used to begin analyzing and cleansing the indexes I had implemented.

One of the best stored procedures I created, and that helped me further my knowledge of SQL Server and Microsoft best practices, was an Index Defragmenting procedure called sp_IndexCleanup.  (You can freely download this and other stored procedures directly from my Downloads page.)  In a nutshell, this procedure first takes an inventory of all the indexes in the current database and, based on if you want to use Microsoft defaults or not, begins to analyze and tag which indexes need to be defragmented.  Using careful dynamic SQL, each tagged index is then defragmented and a final output of which indexes were effected is displayed.

I am particularly proud of this procedure given the number of SQL Server topics I learned about while creating it.  First and foremost is the built-in feature for identifying index fragmentation levels that adhere to Microsoft’s standards.  Using MSDN, I found that if the index incurs less than 5% fragmentation no action should be taken.  However, if index fragmentation levels range between 5% and 30%, the index should be reorganized/defragmented.  For anything over 30% fragmentation, the index should be rebuilt or possibly reviewed for inaccuracy (bad fill factor, poorly included columns, etc.).  Originally when I wrote this procedure it was against a SQL Server 2005 instance.  Since then, Microsoft has not changed this recommendation.  What’s more is that I allowed the user to specify whether or not to use the defaults.  If a user decided not to, then the procedure would accept a ‘maxFrag’ value and then do its comparisons accordingly.

CREATE PROCEDURE dbo.sp_IndexCleanup
(
    @useDefault char(1) = 'N'
    ,@maxFrag int = NULL
)

Another nifty topic I was reading about at the time I was creating this procedure was the new SQL Server 2005 Dynamic Management Views (DMV) feature.  These simple yet powerful views gave us admins a clear glimpse into the inner workings of SQL Server in real-time.  As someone who was also reading Kalen Delaney’s (Blog | Twitter) book on SQL Server Internals, these views provided an excellent real-world approach to what I was trying to learn and accomplish on my own.

SELECT ''?'' AS DatabaseName
    ,f.name AS SchemaName
    ,t.name AS TableName
    ,i.name AS IndexName
    ,s.Index_ID
    ,s.Index_Type_Desc
    ,s.Index_Depth
    ,s.Index_Level
    ,CAST(s.Avg_Fragmentation_In_Percent AS float(2)) AS Avg_Fragmentation_In_Percent
    ,s.Fragment_Count
    ,s.Avg_Fragment_Size_In_Pages
    ,s.Page_Count
FROM [?].sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL) AS s
INNER JOIN [?].sys.indexes AS i
    ON (i.object_id = s.object_id)
    AND (i.index_id = s.index_id)
INNER JOIN [?].sys.tables AS t
    ON (t.object_id = s.object_id)
INNER JOIN [?].sys.schemas AS f
    ON (t.schema_id = f.schema_id)
WHERE s.index_id != 0
    AND s.database_id > 4;

In the procedure, I ended up using the DMV dm_db_index_physical_stats to base my comparisons on either the default fragmentation values or the values supplied by the user.  The ease of using the DMV coupled with the clear results returned cut down the number of queries and amount of T-SQL code I needed to make accurate comparisons.  Plus the fact that the DMV was up-to-date at the time of running the procedure made it especially accurate.  Needless to say DMV’s have definitely improved the quality of a DBAs professional life (at least this one anyways)!

Another notable function used in this procedure was the newly introduced ROW_NUMBER() for SQL Server 2005 (which is still in use today).  Having “grown up” on SQL Server 2000 where identity columns were the norm, the addition of ROW_NUMBER() was a welcome sight!  Although I used it for a menial operation in the procedure, it did give me insight into how and when to use it.  It is for this reason that I have continued to use it to this day, albeit for different reasons.

SELECT ROW_NUMBER() OVER (ORDER BY b.Avg_Fragmentation_In_Percent)
    ,b.*
FROM #_indexinfo AS b;

Independent ventures into unexplored SQL terrain are typical activities that I try to accomplish a few times a week.  Not only does it keep me fresh with SQL Server as a product, but I believe it also makes me a stronger DBA while keeping my thirst for database knowledge in check.  On more than one occasion I have used what I learned from one of these ventures to create some really creative solutions.  Journeying down the path less traveled is always a pleasant change.