Browsing the archives for the SysAdmin category

Size of Filegroups and Files

In recent posts I looked at queries that show us data at the instance, the database, backups and now we will look at filegroups and the individual physical files. Too many times disk space is not an issue, it is THE issue.  This query will show several key attributes of our database at the file […]

SQL Server Backup Compression Default

In SQL Server 2008 Microsoft added the long overdue backup compression feature.  You may have noticed in the backup dialog box the pull down menu to use the DEFAULT SERVER SETTING for compression. To determine what the DEFAULT value actually is: Out of the box, SQL Server 2008 backup compression is turned OFF.  To avoid […]


How to Drop a TempDB Database File

Now The SQL Server tempDB is arguably the most important database in any installed instance.  A poorly configured tempdb will trump all the best efforts for design of good OLTP or datawarehouse systems. The standard recommendation is to have one tempdb database file for each CPU.  It’s also generally recognized that this rule of thumb […]


DBA Query: Instance Information

As part of my weekly routine I like to take a holistic view of the production servers I am responsible for.  After backups and maintenance plans, the next most important role of the DBA is to keep an eye on the overall health of the production databases. I have several queries that I use to […]


Monitor Transaction Isolation Levels

As part of a performance improvement effort we decided to make sure our enterprise reporting tools are operating under the READ UNCOMMITTED transaction isolation level (ie., allow dirty reads).  While our reporting application experts were certain the tools were at the desired isolation level we decided to monitor the actual transactions just to be sure. […]

Maintaining Accurate SQL Server Statistics in Small Maintenance Windows

The single most important component of the SQL Server product is the query optimizer. The SQL Server storage engine utilizes a cost-based optimizer to determine optimal query plans. The job of the optimizer is to determine an optimal plan that will result in exceptional performance. Note: An “optimal plan” is not necessarily the same as […]

Move Tables to New Filegroup

About a year ago I showed how to move data in a table to a new partition by modifying the clustered index.  Since the clustered index contains the data for the table, moving the clustered index is equivalent to moving the table. This post is a simpler example of using the clustered index to move […]


Set up Dedicated Administrator Connection (DAC) before you need it!

When I was in elementary school we often had planned “fire drills”.  We would be told of the impending alarms, the alarms would go off, and we’d all exit the school in an orderly fashion using the prescribed path and exits. Fast forward a hundred years and I’ve discovered why fire drills are important…  So […]

Upgrading SQL Server 2008 Trial Version to Developer Edition

After six months of using SQL Server 2008 Trial, the party is finally over; my free trial has expired.  Unlike most trial software applications Microsoft does not present the user with a dialog box indicating that there are only X number of days remaining with the trial version.  The SQL Server service will just shutdown […]

Charlotte SQL Server Users Group, Guest Speaker Kevin Kline

Just in time for the Holidays…  Kevin Kline has come to town. Kevin’s topic at the Charlotte SQL Server Users Group weighed the pros and cons of SQL Server virtualization.  In this post I will note some of the many points that I learned. Before I dive into the technology details I would like to […]

Using Database Snapshots To Test Loads

When testing data warehouse ETL routines it’s often necessary to be able to reload the same data several times before you get the code dialed in and ready for production work.  One way to be able to do this is to simply TRUNCATE the target table, assuming there are no foreign keys on it.  If […]