Fragmentation on Replicated Tables

I’ve heard more than one person state that they do not need to defrag their tables, because they’re replicated and the source tables are defragged regularly. Let’s set this myth straight. Repeat after me: defragmentation processes are not replicated. Good. Thank you. For those non-believers, let me prove it: SET NOCOUNT ON;   /* Create publication db */ CREATE DATABASE sandbox_publisher; GO   /* Create subscription db */ CREATE DATABASE sandbox_subscriber; GO   /* Navigate to publisher db */ USE sandbox_publisher; GO   /* Create a table to play with */ CREATE TABLE myTable ( myID UNIQUEIDENTIFIER , myColumn VARCHAR(2000)   CONSTRAINT PK_myTable PRIMARY KEY CLUSTERED (myID) ); /* NOTE: please never, ever use this type of clustered index in real-life. The whole purpose is to generate substantial fragmentation for illustrative purposes. */   /* Populate it with some non-sequential records to generate fragmentation */ DECLARE @iteration INT; SET @iteration = 0;   WHILE @iteration < 1000 BEGIN INSERT INTO myTable SELECT NewID(), REPLICATE(’A', 2000);   SET @iteration = @iteration + 1; END   /* Now let’s verify that we really did make a mess of our clustered index */ SELECT avg_fragmentation_in_percent , page_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N’MyTable’), NULL , … Continue reading

Creating a 60 GB Index

Recently, I needed to create an index on a 1.5 billion row table. I’ve created some large indexes before, but this was the largest, so I thought I’d share my experience in case anyone was interested. The plan was to create the following index: Create NonClustered Index IX_indexName_unpartitioned On databaseName.dbo.tableName(columnList) Include (includedColumnList) With (MaxDop = 1, Online = On, Sort_In_TempDB = On) On [Primary]; This is an unpartitioned, non-clustered index being built on a partitioned table. Normally, when you build an aligned partitioned index, the index creation process requires less memory and has less noticeable impact on the system, because each partition is handled individually, one at a time. But as this is an unpartitioned (unaligned) index, each partition was built concurrently, requiring more memory and causing a greater impact on performance. Because of this, I needed to restrict the process to MaxDop 1; otherwise, the server would suffer because of too much memory pressure. I chose Sort_In_TempDB = On because: I’m building this index online on a very busy table and cannot afford to impact normal oeprations. By using Sort_In_TempDB = On, index transactions are separated from user transactions, allowing the user transaction log to be truncated. * TempDB … Continue reading

Index Clean-Up Scripts

I’ve been spending a lot of time lately looking at indexing in my production environments… dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this. Here’s the script I use to find any un-used indexes. This relies heavily on the sys.dm_db_index_usage_stats DMV (2005+). This query will also return the SQL statements needed to drop the indexes for convenience. This does NOT mean you should necessarily drop the index. This is only a guide and a starting point; only you know how your application is used and whether SQL Server’s recommendations make sense. Un-Used Indexes Script Declare @dbid int , @dbName varchar(100);   Select @dbid = DB_ID() , @dbName = DB_Name();   With partitionCTE (object_id, index_id, row_count, partition_count) As ( Select [object_id] , index_id , Sum([rows]) As ‘row_count’ , Count(partition_id) As ‘partition_count’ From sys.partitions Group By [object_id] , index_id )   Select Object_Name(i.[object_id]) as objectName , , Case When i.is_unique = 1 Then ‘UNIQUE ‘ Else ” End + i.type_desc As ‘indexType’ , ddius.user_seeks , ddius.user_scans , ddius.user_lookups , ddius.user_updates , cte.row_count , Case When partition_count > 1 Then ‘yes’ Else ‘no’ End … Continue reading

Back from Vacation!

I’m back from Europe! We had a fantastic time, and saw some incredible sights, but I must say I am glad to be back home. A couple of quick items to mention. First, we now have an official PASS Chapter for the Cedar Rapids area, called I380 Corridor PASS! A special thanks to the folks at PASS for all of their help. If you’re in the Eastern Iowa region and interested in attending or volunteering, please e-mail me at michelle @ sqlfool dot com. Also, I’ve received a couple of comments about my latest codebox plugin. It appears that in an attempt to fix one issue, I’ve caused another. I plan to work on a solution this weekend, but in the mean-time, if you try to copy code from the codeboxes, you’ll need to do a find/replace on the single quotation marks. I apologize for the hassle and hope to have this resolved soon. Finally, the very knowledgeable and prolific Mr. Denny wrote a post regarding Wordle and challenged me to post the results of my blog. I’m just now getting caught up from vacation, so I’m a little late in responding, but here it is: Pretty neat, huh? Thanks, … Continue reading

Indexing for Partitioned Tables

So you’ve partitioned your table, and now you’re ready to performance tune. As with any table, indexing is a great place to start. And if you’re like most people new to partitioning, you probably created all of your indexes on the partitioned scheme, either by design or unintentionally. Let’s take a step back here and discuss what a partitioned index is. A partitioned index, like a partitioned table, separates data into different physical structures (partitions) under one logical name. Specifically, each partition in a nonclustered index contains its own B-tree structure with a subset of rows, based upon the partitioning scheme. By default, an unpartitioned nonclustered index has just one partition. Keep in mind, when you create an index on a partitioned table, i.e. Create NonClustered Index IX_myIndex On dbo.myTable(myColumn); … you are creating the index on the partitioned scheme by default. In order to create a NON-partitioned index on that same table, you would need to explicitly declare “On [FileGroup]“, i.e. Create NonClustered Index IX_myIndex On dbo.myTable(myColumn) On [Primary];   But should you partition your index? That depends on how you use it. In fact, most environments will probably want to use a mix of partitioned and non-partitioned indexes. … Continue reading

Error Handling in T-SQL

Error handling is one of those things in SQL Server that just doesn’t get the attention it deserves. Even a properly constructed stored procedure can still result in error, such as primary key or unique constraint errors. Why should you care? Consider this real-world example: You’re a DBA monitoring a well-performing environment. You deploy a new application to production. Suddenly, performance degrades but you do not know why. You look in your error log and see a whole mess of primary key errors. Digging into your newly deployed application, you find that you are now making an extra (and unnecessary) insert to the database, which is resulting in error and causing your performance issues. This is just one example of many. Fortunately, SQL 2005 has really simplified the error handling process with features such as the Try/Catch block. The basic components of error handling are: Try…Catch block (2005/2008) Error identification Transaction handling Error logging (optional) Error notification As an early holiday gift, here’s a generic error handling process to get you started: If ObjectProperty(Object_ID(’dbo.dba_logError_sp’), N’IsProcedure’) = 1 Begin Drop Procedure dbo.dba_logError_sp; Print ‘Procedure dba_logError_sp dropped’; End; Go   If ObjectProperty(Object_ID(’dbo.dba_errorLog’), N’IsTable’) Is Null Begin   Create Table dbo.dba_errorLog ( errorLog_id … Continue reading

SQL Quiz, Part 2: Toughest Challenges

Chris Shaw posted a new SQL Quiz yesterday, asking “What are the largest challenges that you have faced in your career and how did you overcome those?” Brent Ozar tagged me when he responded to Chris’ challenge. Can I super-size my SQL database? When I left my position at a small logistics company in Indianapolis, I went from a couple of relatively small databases to a pretty large environment. I’m talking 10mm row tables to billion row tables, with comparable increases in transactional volumes. I quickly found that developing in a small environment can be very different from developing in a large one. To improve my skills, I hit the internet. I discovered the value of community resources like and the joys of reading technical blogs. I also became intimately acquainted with Books Online. Perhaps most helpful of all, I discovered how knowledgeable and talented some of my new colleagues were; if I didn’t know the answer, I sought out their advice. It’s like a promotion but with longer hours and no pay increase. A few years back, I was given responsibility for a pricey software deployment. I knew I was not qualified to lead the project, and even … Continue reading

SQL Fool Happenings

It’s not plagiarism if I copy myself I’m now a content contributor for the SQLServerPedia wiki!  So you may now begin to see some of my content on the wiki as well. If you’ve not heard of SQLServerPedia, you should check it out and consider contributing. It’s like a party, but for geeks I’m trying to start a PASS Chapter for the Eastern Iowa region. So if you’re in Cedar Rapids, Iowa City, or Waterloo, and are interested in attending or assisting with PASS, e-mail me at michelle @ for more information. I didn’t realize people still used IE Thanks to Peter for informing me of the image distortion issue. Apparently Internet Explorer is inferior to doesn’t handle image resizing as well as FireFox. The only solution I’ve found is to make 2 versions of each image: a smaller thumbnail and a larger “pop-up” image. I’ll work on updating past posts this week. The more people that follow me, the better I feel about myself Jason Massie rocked my world with his post, SQL Twitter n00bs #SQLPASS. I’ve now been using it for a couple of weeks and have found a lot of benefit in it. It helps that … Continue reading

SQL Tweaks and Tools That Make My Life Easier

It still surprises me how many people don’t know about some of the very things that make my job so much easier. So this next post is dedicated to sharing some of the tweaks and tools I’ve run across that will help anyone who works with SQL:   Indexes Anyone who uses included columns is probably well aware of the frustrations that can come from having to look up information on which columns are included. I wrote a stored procedure, dba_indexLookup_sp, to help me with this, before discovering sp_helpindex2. If you haven’t heard of sp_helpindex2, it’s a re-write of sp_helpindex by Kimberly Tripp. You can find it on Kimberly’s blog. The main difference is Kimberly’s is a system stored procedure (mine is not) and my version returns partitioning information (Kimberly’s does not). Check both out and use whichever one meets your needs best.   KeyBoard ShortCuts In SQL Server Management Studio (SSMS), click on:     Tools –> Options… –> Environment –> Keyboard For your copying convenience: Ctrl+3   Select Top 100 * From Ctrl+4   sp_tables @table_owner = ‘dbo’ Ctrl+5   sp_columns Ctrl+6   sp_stored_procedures @sp_owner = ‘dbo’ Ctrl+7   sp_spaceused Ctrl+8   sp_helptext Ctrl+9   dba_indexLookup_sp or sp_helpindex2 Please note that these settings will not take effect until you open a new query window. … Continue reading