This post is a follow-up to Thomas LaRock’s excellent article here, so first go read that. I’ll wait.

You back? Good.

As most of you know tables missing clustered indexes (CI) are fairly bad performance-wise, but why? Even disregarding the fact that SQL Azure actually requires a CI for each and every table in the cloud database, and hence you may be “tripped up” on this in the future. More importantly, SQL Server sequentially reads the data in a CI one extent at a time. Brad McGehee states, “This makes it very easy for the disk subsystem to read the data quickly from disk, especially if there is a lot of data to be retrieved.” Heaps cause the server to do so much extra work due to unordered nature of NCI’s or having no indexes at all, that performance is bound to suffer.

My goal here is to extend the functionality of Thomas’ little script to do two additional things which I find extremely helpful. One, have the script work all at once for every database on a particular server. Certainly this is not an issue for those of you in environments with one database per server, but I’d bet that’s more the exception than the rule for most of us.

Secondly, I find it very useful to have a row count included to assist me with deciding on exactly how to proceed with the data provided. While I don’t disagree with the need to potentially trash any tables you find with 0 rows, that cleanup project is for another day. I’m concerned today with missing CI’s on tables that truly need them. Unfortunately, row count is not included in either of the joined tables in Thomas’ script. I solve that problem by creating a temp table filled from sp_spaceused and join it to the original resultset.

A couple caveats for you before checking out this script. Firstly, being that I work in an environment that’s 95% SQL Server 2000, this script was written specifically for 8.0. Now, I did indeed test it on 2005 and it does work, but I’d bet a lot of coin there are much easier ways of doing this using DMV’s and other new features. Secondly, the script has a hard time with alternate schemas besides ‘dbo’. While I’m fully aware the script can probably be changed to take current schema into account (perhaps from the ‘information_schema.tables’ system view), I don’t have the need to in my environment so honestly, I just never took the time to change the script to handle alternate schemas. Third, don’t hassle me for using cursors. I don’t use them in production, but am not averse to using them carefully in maintenance scripts that get run “occasionally.” I know all about the performance implications but not really an issue here for a script that takes less than a second to run…and on an ad-hoc basis at that.

Thanks again to the SQLRockstar for inspiring me to clean this up and post it out there for all.

Have a Grateful day…Troy

USE master
DECLARE cur1 CURSOR FOR
  SELECT [name] FROM sysdatabases
  WHERE [name] NOT IN ('tempdb','master','msdb','model')
        
DECLARE @db VARCHAR(75)
DECLARE @sql1 NVARCHAR(2000)
OPEN cur1
FETCH NEXT FROM cur1 INTO @db
WHILE @@FETCH_STATUS <> -1
  BEGIN
    SET @sql1 = N'SET NOCOUNT ON

      -- CREATE TEMP TABLE TO HOLD ALL TABLE NAMES
      CREATE TABLE #so (dbName VARCHAR(75),
        tableName VARCHAR(75),
        tableRows INT)
    
      -- FILL FROM sysobjects
      INSERT INTO #so
      SELECT ''' + @db + ''' [db], name [table], 0
      FROM ' + @db + '.dbo.sysobjects
        INNER JOIN ' + @db + '.information_schema.tables ist ON ist.table_name = ' + @db + '.dbo.sysobjects.name
      WHERE xtype = ''u''
        AND id NOT IN
          (SELECT id FROM ' + @db + '.dbo.sysindexes WHERE indid = 1)
        AND ist.table_schema = ''dbo''
        AND ' + @db + '.dbo.sysobjects.name NOT IN (''cdata'',''cglobal'')

      -- CREATE CURSOR TO ITERATE TABLE NAMES AND RUN sp_spaceused AGAINST THEM
      DECLARE cur2 cursor for
        SELECT tableName FROM #so
      OPEN cur2
      DECLARE @tn VARCHAR(75)
      DECLARE @sql2 NVARCHAR(1000)
      FETCH NEXT FROM cur2 INTO @tn
      WHILE @@FETCH_STATUS <> -1
        BEGIN
          -- CREATE TEMP TABLE TO HOLD ROW AMOUNTS (AND OTHER STUFF WE DON'T CARE ABOUT NOW)
          CREATE TABLE #su
            (tableName varchar(100),
                NOR varchar(100),
                RS varchar(50),
                DS varchar(50),
                IxS varchar(50),
                US varchar(50))
          
          -- FILL THE NEW TEMP TABLE
          SET @sql2 = N''INSERT INTO #su EXEC ' + @db + '.dbo.sp_spaceused '''''' + @tn + ''''''''
          EXEC sp_EXECUTESQL @sql2
            
          -- JOIN THE 2 TEMP TABLES TOGETHER TO UPDATE THE FIRST WITH THE ROW NUMBER COUNT
          UPDATE #so
          SET tableRows = su.NOR
          FROM #su su
            INNER JOIN #so so ON so.tableName = su.tableName
          WHERE ISNULL(su.NOR,0) <> 0
          
          -- CLEAN UP
          DROP TABLE #su

          -- ITERATE        
          FETCH NEXT FROM cur2 INTO @tn
        END
  
      -- CLEAN UP
      CLOSE cur2        
      DEALLOCATE cur2

      -- OUR OUTPUT!!
      SELECT * FROM #so
      WHERE tableRows > 0

      -- CLEAN UP
      DROP TABLE #so'
    
    -- EXECUTE THE ENTIRE BLOCK OF DYNAMIC SQL
    EXEC SP_EXECUTESQL @sql1  
    
    -- ITERATE
    FETCH NEXT FROM cur1 INTO @db
  END

-- CLEAN UP
CLOSE cur1
DEALLOCATE cur1

Had a relatively simple maintenance to perform this past weekend; the kind so simple, it’s the fodder of DBA dreams everywhere. I was assigned the task to move the data and log files for four separate databases to another drive on the same server. The server designation itself for these four database wasn’t to change; simply the file path to the mdf and ldf. To make it even easier, none of these four were involved with replication; actually quite a rarity in our environment. Our network operations team provided me with a new SAN share on this box, which afforded me the opportunity to ease some of the drive space issues I ran into occasionally.

At first glance, a simple detach-move-reattach was all that appeared necessary for this straightforward operation. But a deeper look prior to the maintenance window’s arrival indicated all four of these databases were full text enabled and populating six separate catalogs each. Ok, no need for panic, this doesn’t turn it from a half hour operation into a six-hour one, but dropping the 24 catalogs then recreating and repopulating them did indeed add a substantial amount of time to the project. It got me wondering just how efficient my approach really was.

Detaching and reattaching a database does by default disable full-text indexing on that database. After the files have been properly moved, the task at hand is to move the full-text catalogs to folder alongside the new mdf/ldf location. For example, the “Users” table is FTI’d and the original catalog in stored on the M drive, same as the data and t-log files. After the move, the mdf and ldf are now on the N drive but right-clicking the “Users” table shows the catalogs still mapped to M. Not only this, but they are grayed out in the full-text editing dialog box, disallowing a simple remapping of the catalog location. My solution was to carefully document the fields/names/primary keys/etc. used for each table’s FTIs, then remove all catalogs and re-add them individually with them mapped to N as needed. Not a mind-numbingly horrible operation as some DBA stuff goes, but complicated enough in that one truly has to concentrate and not miss ANY details whatsoever.

While I accomplished the whole thing with hours still to spare in the maintenance window, I told myself there just has to be an easier way to accomplish the FTI portion. I suppose I could script out the whole process from dropping of the original catalogs to the recreation at the new location in T-SQL. Now while that would indeed save me mountains of time during the window, just how long would it take to create those very scripts? Have I really saved myself work? I don’t think so with that option.

It occurred to me that this catalog location must of course be stored in one of the system tables somewhere. What if I simply ran an update against that row then repopulated the index? Or maybe there is some non-documented-super-duper-secret hidden system stored procedure I could fire off? Could it be that simple?

Turns out that it isn’t that simple after all.

Sudarshan offers a SQL Server 2005 solution towards the bottom of this forum post which seems to indicate the magic “Keep Full Text Indexes” checkbox included with the detach dialog takes care of the issue very easily. There are a couple gotchas according to Sudarshan, but his solution appears quick and elegant. Lest it be noted I haven’t actually tested it because I currently work in a predominantly SQL Server 2000 environment. This looks viable for ’05 and most likely ’08, but proceed cautiously due to me not knowing this author personally.

Microsoft offers up two support pages that address this very issue; here and here. As your typical MSDN pages do, these offer tremendous detail into the process; more than most humans need. They recommend not just a simple “point the catalogs somewhere else” solution, but registry changes as well. Now, well I’ve been around a bit and registry changes don’t scare me as much as they used to, I still try and avoid them whenever possible. Too many horror stories have surfaced over the years of even the slightest of registry changes wreaking total havoc across multiple systems, that I’m ALWAYS going to be hesitant to manually make those changes. If that’s what goes on behind the scene when I complete a wizard or close a dialog box, well then fine, that I trust. If I have a choice that’s slightly more laborious yet means I don’t have to visit regedit, then I’m all for it.

While my “manual” solution appears to possibly be the quickest methodology for a small number of databases, obviously one’s going to have major issues when it comes to scaling and trying to accomplish this over 20, 30 or more databases simultaneously. Like so so many other SQL Server topics, this comes down to an “It depends” answer once again. Take everything into account. How many databases? How long is your maintenance window? Your ability for producing similar scripts to run at a later time? Your tolerance for making direct registry changes? Take all these factors into account and I’m sure the solution you end up with with, will much more appropriate for your situation.

I’m very interested in your feedback on this topic. Have I over-complicated a relatively simple issue? Or have I stumbled upon one of those timeless aspects of SQL Server that simply needs dealing with on a manual basis? How have you accomplished this in the past? I look forward to your responses.

Have a Grateful day…Troy

Most DBAs are aware that when a SQL Server database is set in simple recovery mode, then the transaction logs of it’s member databases are shrunk automatically upon a CHECKPOINT. This is all fair and good, but how many times have you viewed the folder containing your mdf’s and ldf’s and see that many of the ldf’s are countless times bigger than the minimum size?

In a production environment, there are generally so many active processes happening simultaneously that it’s usually best to leave well enough alone. But it’s a different story in a development, staging, or R&D environment. Some mornings I navigate to the ‘mssql/data’ folder on my dev server and see 90% of the ldf’s taking up huge amounts of room. In some of these cases, there is active development work going on and thusly the fluctuation in transaction log size is expected. But more often than not 75% of these 90% haven’t been touched for days or longer!

Now, I don’t know about you, but drive space is always at a premium in our environment. A client requests a refresh of the development database from production and you barely have enough room on the drive to place the backup file, let alone keep the old one just in case. Yes, you can pick away at the contents of the drive and perhaps find an old file or two you could 86, but i usually go right to the jugular and try shrinking all the transaction logs and see if that frees up enough space for your needs.

I’ll reiterate myself at the risk of redundancy, but again this is not something I would do in production. As stated earlier though, in a development or staging environment, there is no harm whatsoever in shrinking the transaction logs. I was using the tried and true method of right-clicking the database > ‘All Tasks’ > ‘Shrink the Database’ > ‘Files’ > choosing the log file from the dropdown > ‘Ok’ > ‘Cancel’. That worked well enough but was laborious on some of my dev servers which literally have hundreds of databases on them. I threw together a simple script that does this automatically for all databases on the server and all it takes is a simple ‘execute’ and a minute or two and you can regain all that potential space back. The script is nothing mind-numbingly complicated, but nevertheless I wanted to share it with you all.

Ran this on one my dev servers just this morning and freed up almost 10% of the drive’s space in a matter of seconds. No digging through the file structure to examine ldf sizes. No manual shrinking. Just a couple seconds and I all the sudden found myself with enough room to accomplish the refresh I needed to do. Absolutely perfect.

Would love any feedback or comments you may have.

In the meantime, be sure to have a grateful day…Troy

SET NOCOUNT ON;

DECLARE cur CURSOR FOR
SELECT [name]
FROM master.dbo.sysdatabases

DECLARE @db VARCHAR(50), @sql NVARCHAR(1000)

OPEN cur
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS <> -1
BEGIN
     SET @sql = N'DECLARE @logname VARCHAR(100) '
     SET @sql = @sql + 'USE [' + @db + ']
          SELECT @logname = RTRIM(LTRIM([name]))
          FROM sysfiles WHERE name LIKE ''%log%'' '
     SET @sql = @sql + 'DECLARE @sf NVARCHAR(300) '
     SET @sql = @sql + 'SET @sf = ''DBCC SHRINKFILE
          ('' + @logname + '') WITH NO_INFOMSGS '' '
     SET @sql = @sql + 'PRINT @sf EXEC SP_EXECUTESQL @sf '
     EXEC SP_EXECUTESQL @sql
     FETCH NEXT FROM cur INTO @db
END

CLOSE cur
DEALLOCATE cur

My company uses Quest Software’s LiteSpeed for SQL Server for both nightly and ad-hoc backups.  The DBA’s here before me set everything up using a compression ratio of 1 and we get actual compression and backup times that are more than adequate for our needs.  I’ve often wondered exactly what effect raising that ratio in the LiteSpeed settings would make in both how long the backup took, but in the actual compression itself.  What trade-off between size and efficiency do I have to make should I decide to raise that setting up the scale?

Let me first make it VERY clear.  I am not an employee of Quest.  I have nothing whatsoever to gain or lose by this examination.  I happen to be an extremely big fan of LiteSpeed and have found it by far to be the best backup solution I’ve had the pleasure to work with during my short professional career.  I have tremendous respect for everyone from Quest I’ve had the pleasure to come in contact with; that includes Kevin Kline, Brent Ozar & others.  This is simply a non-scientific, ground-level look at LiteSpeed’s compression ratios.  Please by all means visit Quest’s website to get all the product details and info you need.   (http://www.quest.com/)  Take nothing I say here as gospel.  We clear folks??   I don’t wanna wake up to a cease-and-desist order from Quest tomorrow, ok?

The results graph first, then a discussion on my conclusions…

The blue line and left x-axis represent actual compression for the ratios along the bottom.  The red line and right x-axis likewise represent the time taken vs. a native non-LS backup.  So for example, when I did my sample on compression level 3, it was compressed almost 81% and it took just about half the time of a full native backup.

Ok, so what observations can I make from these results?  As far as compression goes, there seems to be a significant jump from level one to two; that 6% could make a tremendous difference when extending these results to an entire company’s worth of backups.  From there, the compression indeed inches up, but never more than another 1/4 or 1/2 percent at a time.

Looking at the red line (time taken for the backup to complete vs a native one), we see time is even quicker than native all the way through level four.  Even five and six completed in about the same time as a native backup.  From there though, total time taken ramps up pretty quickly; specially at the highest compression ratio which took over five times as long as a native backup.

What compression ratio is best for your situation?  Like Paul Randal is so fond of saying, “It depends.”  This falls along with the dozens of other decisions we make as DBA’s on a daily basis; a trade-off between factors.  Are we severely limited in backup drive space yet have plenty of time to accomplish the backup?  If this is the case then I might choose level 7 or 8.  Is drive space never ever an issue, yet your evening backup window is?  Perhaps level 1 would be the optimal choice for you.

I think 90% of us fall somewhere between those two extremes.  Yes, storage is cheap, but rarely ever unlimited.  While some of us may have multi-terabyte server farms to backup to; it’s still irresponsible to waste it needlessly.  I wouldn’t be awfully surprised though if many of us fall into the “not-enough-hours-in-a-night” caveat where backup time is much more a significant factor.  From my testing and actual experience, I’ve found a compression factor of two or three brings the right balance into a variety of environments.

I would love some feedback and/or some opposing viewpoints from anyone; particularly if someone from Quest happens upon this.

Have a Grateful day…Troy

Ok, I know “SQL Server Replication Error 3726″ isn’t the most imaginative of headlines for a blog article.  What little I know about SEO tells me that titles carry more weight when the search engines do their crawls.  So, I had to make the decision that this particular title may not actually bring sexy back.  I can live with that.

Error 3726: “Could not drop object ‘myTable’ because it is referenced by a FOREIGN KEY constraint.”  This is thrown predominantly upon the initial setup of a replication publication.  When the snapshot agent is run, one of the first things that is done is the engine verifying whether the articles already exist at the subscriber. If so, one has a choice of either dropping the article entirely (schema included), just deleting the data (closer to a TRUNCATE than a delete because I’m fairly sure it’s not logged), filter based on predetermined criteria, or simply doing nothing.   Whichever of the options you choose; if an article you wish to publish has a foreign key constraint that ties it to an object on the publisher that is not published as well, then this error will occur.

Fair enough, but what options do we have for resolving the error?  Several actually.  The easiest is to simply add the related table to the list of published articles and rerun the snapshot agent.  This time around, the agent will recognize both related tables are being published and proceed as expected.

If for some reason you don’t have the flexibility to be able to alter the article list, then your best bet would be to drop and re-add the constraint.  This can be done easily with the traditional ALTER TABLE/DROP CONSTRAINT & ALTER TABLE/ADD CONSTRAINT syntax (link) in all versions.  Here is an excellent article on dropping and creating FK constraints by Pinal Dave (link).  As well, if you’re creating the publication (transactional or snapshot) using sp_addpublication, then there is an optional “@pre_snapshot” parameter wherein you can add T-SQL to drop the constraint (link).

The issue of foreign keys on the subscribers in transactional or snapshot replication is actually quite debated.  I personally am of the opinion they are a waste of resources and that if referential integrity is being maintained on the publishers PRIOR to replicating, then there is no need whatsoever for those keys on the subscribers.  It gets a bit more complicated when dealing with merge replication due to the 2-way nature of the beast.  What do you say we leave that conversation for another day, eh?

Thanks and have a Grateful day…Troy