Ramblings of a DBA

Multiple “clustered” indexes on a SQL Server table

Tara Kizer — Mon, 09 Nov 2009 19:23:05 GMT

You can only have one clustered index on each SQL Server table, however there are two ways to create pseudo clustered indexes on a table:

Create clustered indexed on a view (indexed view) that covers the table
Create covering index on the entire table

Let's look at the following table:

CREATE TABLE t1 
(
    c1 int, 
    c2 varchar(5), 
    c3 bigint, 
    c4 datetime, 
    CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED (c1, c2, c3, c4)
)
GO

For option 1 above, here's the indexed view and clustered index:

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE VIEW v1 WITH SCHEMABINDING
AS 
SELECT c1, c2, c3, c4 FROM dbo.t1
GO
CREATE UNIQUE CLUSTERED INDEX cidx_v1 ON dbo.v1(c2, c3, c4, c1)
GO

For option 2 above, here’s the covering index:

CREATE INDEX idx_PseudoClust ON t1(c3, c4) INCLUDE (c2, c1)
GO

So why would you want to do this? I'm sure that there are some very good reasons, but I've never had the need to do it. I recently had the discussion with a Microsoft engineer and thought it was interesting enough to share.

Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

Tara Kizer — Tue, 03 Nov 2009 19:04:36 GMT

I have modified isp_ALTER_INDEX, which is the stored procedure that I wrote and use to defragment/rebuild indexes in SQL Server 2005 and SQL Server 2008. The code change has just one bug fix. The bug would only have been encountered on SQL Server 2008 systems that have XML indexes that need to be rebuilt based upon the input parameters.

In SQL Server 2005, the sys.dm_db_index_physical_stats data management function outputted “XML INDEX” for the index_type_desc column when the index contained a column with the XML data type. In SQL Server 2008, the DMF was modified so that you could have either “XML INDEX” or “PRIMARY XML INDEX” for XML indexes in the index_type_desc column.

I don’t have any systems that use the XML data type, so it wasn’t until we had the SQL Server 2008 AdventureWorks database on a development system that we found the bug.

Here’s the code change in case you are interested:

SET @online = 
        CASE
            WHEN @indexType IN ('XML INDEX', 'PRIMARY XML INDEX') THEN 0
            WHEN @indexType = 'NONCLUSTERED INDEX' AND @allocUnitType = 'LOB_DATA' THEN 0
            WHEN @lobData = 1 THEN 0
            WHEN @disabled = 1 THEN 0
            WHEN @partitionCount > 1 THEN 0
            ELSE 1
        END

You can download the new version of the stored procedure here.

SQL Server Magazine web article about a tool I wrote

Tara Kizer — Thu, 08 Oct 2009 18:20:51 GMT

My Database Growth Tracker tool is featured in a SQL Server Magazine web article that Kevin Kline wrote.

Check out the web article here!

Fix for CLR object execution error after attach/restore in SQL Server 2005

Tara Kizer — Mon, 21 Sep 2009 17:55:25 GMT

After attaching or restoring a database to SQL Server 2005, you may encounter an error when executing a CLR object.

Error:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65538. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.

This KB article describes the issue and shows the fix, however it says the bug occurs when the database is attached/restored to a different SQL instance. I’ve encountered the error three times now. In at least one occurrence of the error, it has happened when I’ve restored the database to the same SQL instance.

Fix:

USE master
GO
ALTER DATABASE dbNameGoesHere SET TRUSTWORTHY ON

USE dbNameGoesHere
GO
EXEC sp_changedbowner 'sa'

Fast and Reliable Backup and Restore of VLDBs in SQL Server 2008

Tara Kizer — Thu, 10 Sep 2009 23:03:22 GMT

The SQLCAT team released a very informative whitepaper regarding backup and recovery of very large databases in SQL Server 2008. It is a must read if you are a SQL Server DBA. Check it out here.

If you are not already following the SQLCAT team’s blogs and you are a SQL Server DBA, you need to get with the times and start following one of the best online resources for SQL Server.

Backup SQL Server 2005 and 2008 Databases

Tara Kizer — Tue, 08 Sep 2009 18:26:53 GMT

I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server databases. This new version includes one bug fix and three new features:

Bug Fix – removed ReportServerTempdb from exclusion list
Bug Fix – fixed file retention code to handle database names with spaces
Feature – support for SQL Server 2008 including compression
Feature – archive bit option
Feature – COPY_ONLY option

I decided that excluding the ReportServerTempdb from the backups was unnecessary. This database, used by Reporting Services, is in FULL recovery model by default, so this exclusion could have caused you to run out of space in its transaction log.

Bryan Conlon, one of my blog readers, noticed that the file retention code was not working when a database name had spaces in it. It took me a while to come up with a solution for this one, but I came up with a solution that involves an additional temporary table that stores just the file names and then synchronizes with the original temporary table that handles the dates. If you are wondering why I don’t just use the FORFILES command along with the DEL command which probably wouldn’t have this bug, it is because you can’t use UNC paths with FORFILES. I didn’t want to limit storage of backups to local or mapped drives. To see a file retention solution that uses FORFILES with DEL, check out this blog.

The archive bit option was added in case you don’t want to delete files that haven’t been backed up to tape. If you aren’t backing up your files to tape, then do not use this feature unless you are setting this option to true on the files. The default value of this new input parameter is false, so you don’t need to do anything if you don’t want to use it.

The COPY_ONLY option was added due to a feature request from one of my blog readers, “The Baking DBA”. This option allows you to perform a backup without impacting the normal sequence of backups. For more details on this, please check out the COPY_ONLY option in the BACKUP DATABASE topic in SQL Server Books Online.

I also dropped support for SQL Server 2000, so this new version only supports SQL Server 2005 and SQL Server 2008. I decided to drop SQL Server 2000 support from this new version, and all future versions, as keeping it in there was making it harder to release new versions. If you still need SQL Server 2000 support, like I do on a legacy system, then do not deploy this new version to those systems, simply use the last version.

You can download the new version here.

Thank you to Bryan Conlon for also testing and finding bugs in the “beta” version of the stored procedure. All bugs that were found have been fixed. Thanks Bryan!

How to remove a SQL Server distribution database if other methods fail

Tara Kizer — Wed, 02 Sep 2009 20:59:26 GMT

For various reasons, you may need to manually remove SQL Server replication. Microsoft has a good KB article which shows how to do it. It has always worked in the past, but I ran into a situation a couple of weeks ago where it couldn’t remove the distributor via sp_dropdistributor. I don’t recall what error we were getting, but I didn’t have time to figure out what was wrong so with the help of a Microsoft engineer we were able to setup another distributor and then get replication working again. Later I tried “drop database” command on the original distribution database, but it said it couldn’t be dropped since it was being used for replication. It no longer was being used for replication, so it seemed to be orphaned.

Yesterday, I opened a case with Microsoft to see how I could get rid of the orphaned distribution database. It’s a simple fix:

use master
go
alter database distribution set offline;
drop database distribution;

Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

Tara Kizer — Mon, 31 Aug 2009 16:50:58 GMT

I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment/rebuild indexes in SQL Server 2005 and 2008. The new version has a minor bug fix.

Recently I was comparing the fragmentation results between the DETAILED and SAMPLED modes of sys.dm_db_index_physical_stats and noticed that the DETAILED mode had “duplicate” entries for indexes. Due to this, my old code would have ran ALTER INDEX against the same index multiple times if you used DETAILED for @statsMode. To fix this, I simply added “index_level = 0” to the WHERE clause of the sys.dm_db_index_physical_stats query.

I consider this to be a minor bug as most people can not afford to run sys.dm_db_index_physical_stats in DETAILED mode.

You can download the new version of the stored procedure here. Let me know if you run into any issues with it.

Defragmenting/Rebuilding Indexes in SQL Server 2005

Tara Kizer — Tue, 23 Jun 2009 21:48:22 GMT

UPDATE: A new version of this stored procedure is available. Please check it out here.

I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment/rebuild indexes in SQL Server 2005. The changes include two bug fixes and one feature request.

The first bug fix was reported by Fedor Baydarov. He found that @lobData was not being re-initialized to zero after a LOB data type was encountered. This meant that the rest of the indexes to be processed were being done offline even if the online option was available. D’oh!

The second bug fix is that it didn’t check for LOB data types of the included columns in a non-clustered index. The online option is not available for this type of index, so the bug caused the stored procedure to fail when such a condition was encountered. This bug fix might have been found by a blog reader, but I’m unable to find an email regarding it. I came across the bug recently on a system that had such an index.

The feature request was to add the option to do the sort operation in the tempdb database. This is recommended if your tempdb is optimized according to best practices, such as by having a tempdb data file for each of the CPUs. See ALTER INDEX topic in SQL Server Books Online for more details.

You can download the new version of the stored procedure here.

Let me know if you run into any issues with it. I’d also be interested to hear if it works on SQL Server 2008.

SQL Server Script to Display Job History

Tara Kizer — Wed, 10 Jun 2009 18:29:17 GMT

I was going through my scripts today and found one that I’d like to share.

This SQL Server script will display job history. The benefit of this script over displaying it from the GUI is that you get to see the job durations quickly.

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

I know I stole the code from the inner-most derived table from someone, but I didn’t make a note of the source. I did a quick search for the source, but I came up with too many possibilities.

Backup SQL Server Databases

Tara Kizer — Tue, 07 Apr 2009 20:00:06 GMT

EDIT: new version of stored procedure is located here. Use the new version for 2005/2008. Use the below version for 2000.

I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server databases. In this version, I fixed two bug and added a feature. Here are the changes:

Fixed "2000 backupset" bug by qualifying the object to msdb.dbo.backupset
Fixed the full backup check for SIMPLE recovery model databases (bug found by Eugene from PerfSpot.com)
Added Red Gate SQL Backup functionality

If you have scheduled jobs that are using any of my previous versions, you might need to modify the job step so that it uses the new @bkpSwType input parameter rather than the @liteSpeed parameter.

You can download the revised stored procedure here.

In the download, I have provided a script, ModifyJobStep.sql, to update the job step by replacing @liteSpeed with @bkpSwType. This only needs to be run if the job step includes @liteSpeed. You may not have this if you were using native backups since "@liteSpeed = N" was the default. If the script fails, run it one more time. There is some sort of SQL Server bug that is encountered on some systems, but it doesn't appear if you run it a second time. It is not a bug with the script though.

Let me know if you encounter any issues with this version so that I can get them fixed.

In the next version, I will add support for SQL Server 2008. It is a rather quick modification, but I need to do some additional testing of it before I release it to the Internet. If you'd like the next version before I finish my testing, just send me an email.

Defragmenting Indexes in SQL Server 2005

Tara Kizer — Fri, 27 Mar 2009 19:30:51 GMT

I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment indexes in SQL Server 2005. The changes include one bug fix and one feature request.

The bug fix was simply to add "SET QUOTED_IDENTIFIER ON" to the script. You'd only see the bug in special circumstances, such as if you were using indexed views.

The feature request was to add the ability to log the fragmentation data. The logged data could be used to determine if you want to rearrange the index or perhaps add/remove columns to reduce fragmentation. The logged data is stored in a table called DefragmentIndexes. If you don't want to log the data, then you'll still need to create the table if you want to use this new version, otherwise the CREATE PROC statement would fail due to a missing object. To configure the stored procedure to log the data, simply pass 1 to the new input parameter, @logHistory. The default for this new input parameter is 0, so you do not need to modify your job if you don't care about this new feature.

You can download the new version of the stored procedure and the required table here. I put them in the same script to make it easier to deploy the new version.

Let me know if you run into any issues with it.

FastCopy - fastest Windows copy product?

Tara Kizer — Tue, 03 Mar 2009 23:35:18 GMT

If you've ever had to copy large files on a Windows platform using the Windows copy method (copy/paste in Windows Explorer or copy/xcopy commands), then you know how slow it is. Recently I had to setup database mirroring for a largish database, so I needed to copy the full backup to the mirror server. The database is about 110 GB in size, but since we use Quest's LiteSpeed product, the full backup is just 35.8 GB in size. I had heard about FastCopy, which claims to be the fastest Windows copy product, so I decided to do comparison tests.

Using the Windows copy method, the 35.8 GB file was successfully copied across the WAN in 2 hours and 15 minutes. Using FastCopy, it copied in 1 hour and 53 minutes. FastCopy was about 16% faster than the Windows copy method.

I then tried copying an 11.7 GB file in a different environment, one where file copies seem to take forever over the WAN. Using the normal Windows copy method, the 11.7 GB file copied across the WAN in 55 minutes. Using FastCopy, it copied in 51 minutes. FastCopy was only about 7% faster.

Both of the above tests were done on compressed files. Since I wasn't seeing much of a performance boost with FastCopy, I then tested with uncompressed files.

The first uncompressed file was 51.6 GB in size. That file took 4 hours and 3 minutes using Windows copy method and 3 hours and 16 minutes using FastCopy. That's a 19% improvement.

The second uncompressed file was 15.6 GB in size. That file took 2 hours and 32 minutes using Windows copy method and 2 hours and 23 minutes using FastCopy. That's a 6% improvement. By the way, I was getting about 1.85 MB per second for the transfer rate on this system, so that's why these times are so bad. On the other ones, I was getting around 4.75 MB per second.

FastCopy provides a command-line interface as well, so it can be used in batch files and scheduled jobs, such as those that refresh your development/test databases with production data.

If you don't mind the cluttered GUI and the poorly translated to English documentation, then I'd recommend this product. You can download and read more about FastCopy here.

There are other products out there that claim to be faster than the Windows copy method, such as Robocopy and TeraCopy, but I did not compare them to FastCopy. If you already performed these comparisons, please let me know.

SQL Server 2008 System Views Map

Tara Kizer — Fri, 27 Feb 2009 18:53:41 GMT

Microsoft published the ERD for the system views about a month ago, but I'm just now seeing it. It is available in PDF or XPS format.

You can download them here.

How to refresh a SQL Server database automatically

Tara Kizer — Wed, 25 Feb 2009 20:32:16 GMT

As a DBA, we are often asked to refresh a database, which means to overwrite an existing database using a different database's backup. If you are rarely asked to do this, you may decide to do it manually. If you are asked to do this on a regular and perhaps scheduled basis, then you'd want to automate it. Developers often want to write and debug code against a copy of the production database, so it makes sense to refresh their databases on a daily or weekly basis.

If you were to do the refresh manually, you would probably follow these steps more or less:

Copy the backup file
Restore the database
Unorphan the accounts if the SIDs aren't in sync
Provide db_owner or similar permissions to the development staff
Run a script for anything else not handled above

Depending upon the size of the backup, you may want to compress it after the first step from above using tools like WinRAR, PKZIP, or WinZip. These three tools have command line versions, so it can be easily added to the refresh process.

To automate this process, we need to write code that does the above steps and then to schedule that code to run via the SQL Server Agent. To make this easier to understand, I will be referring to the source server as Server1 and the destination server as Server2. An example of a source server is production, and an example of a destination server is development.

Here's how I have the refresh automated in a couple of my environments:

On Server1, a job exists that compresses the latest full backup
On Server2, there's a job that performs the following steps:
- Copies the compressed file to a local drive
- Decompresses the file to make the full backup available
- Kills all sessions to the database that is about to be restored
- Restores the database
- Sets the recovery model to Simple
- Grants db_owner privileges to the developers

You'll need to schedule the job on Server2 so that it doesn't run until the job on Server1 completes. I just watch how long the job on Server1 takes and then schedule the job on Server2 to run a few hours afterwards.

You can download all of my code here. The file for Server1 is named RefreshServer1.sql, and the file for Server2 is named RefreshServer2.sql. I perform all of the steps on Server2 in a single stored procedure, which means I only have one job step.

In the code, you'll notice that I am using rar.exe and CPAU.exe. Your refresh process may not need these.

We use WinRAR as our compression utility. WinRAR's command line version is rar.exe. CPAU.exe is used to provide credentials to authenticate to Server1 as Server1 and Server2 are in different domains in my environment, and unfortunately we don't have a trust relationship setup between the two domains. For more information on CPAU, see this blog post. In the comments, I included the restore command if you are using Quest's LiteSpeed. We use it for our backups, so we use their xp_restore_database extended stored procedure to restore them.

I don't have any code to unorphan the accounts in my refresh process as I ensure that the SIDs are identical between the two environments. For more information on that, see this blog post. In case you need a script to unorphan the accounts, I've included it in the download as UnorphanAccounts.sql.

You can easily rewrite the code to handle multiple databases that need to be refreshed on the same Server1 and Server2. In fact, that's what I am doing in my environments as some of the applications that I support use multiple databases. To handle multiple databases on the same instances, you'll need to loop through them in isp_RefreshServer1. My looping code is in there, but it is commented out. You'll also need to perform multiple restores in isp_RefreshServer2. I left the code for multiple databases in that stored procedure too, but I commented it out.

If you have a lot of databases to refresh and they all follow the same code, I would suggest parameterizing the stored procedures rather than hard-coding the various things.