November 2007 Blog Posts
I've received a few comments in the past about people modifying my code so that it loops through the databases rather than having to call my code for each database. One such example is with my isp_ALTER_INDEX stored procedure. In my version, you provide the database name as an input parameter. I recently received a comment that someone had modified my code so that it did not have this input parameter and instead had code added to it that looped through the databases to be defragmented. The disadvantage to this is that I update my code occassionally to fix bugs...
UPDATE: This stored procedure has been updated.
I have fixed a bug in my isp_ALTER_INDEX stored procedure. My old stored procedure was not handling the online option correctly for clustered indexes when there were LOB data types anywhere in the table. Use this new version instead of the old one.
This stored procedure defragments indexes in SQL Server 2005. It utilizes sys.dm_db_index_physical_stats, a dynamic management function, to retrieve the fragmentation levels.
If you plan to use DETAILED for @statsMode, you may want to consider running the query that builds the #FragIndex table on a database snapshot. Kalen Delaney mentions this in the May 2006 edition...
In a previous blog, I mentioned a performance problem that I've been working on for a few weeks. I can finally say that this issue has been resolved. The problem was that the database server (SQL Server 2005) was running at 95%-100% CPU utilization at all times.
Here is the query that was causing our problem (object names have been changed):
WHERE Unit = ?
We were receiving a bad execution plan for this query due to nvarchar being used for the Unit parameter. The Unit column is defined as varchar(50) in the table. See the below demonstration for the...
Recently we purged millions of rows from a database as it was determined that we did not need to retain it. Since we were low on free disk space, we decided to shrink the primary data file (MDF). Usually I wouldn't recommend shrinking the database, but this was a situation where it met the exception to the rule.
Since the amount of space to be reclaimed was around 50GB, I decided to run the shrink in smaller batches (1 GB each pass). After about an hour of shrinking the database down, I started to receive the following error:
File ID 1...
For a few weeks, I've been battling performance problems of an application whose database is hosted on SQL Server 2005. The database server has been running at very high CPU utilization for a few months now and we've been slowly ruling things out as the culprit. We haven't gotten to the bottom of it, but we are getting close.
Here's a great article that I stumbled upon recently that goes into great detail on how to troubleshoot performance problems in SQL Server 2005.