Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links



Search this Blog


Post Categories


November 2007 Blog Posts

Running my code against multiple databases

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...

posted @ Wednesday, November 28, 2007 11:53 AM | Feedback (2) | Filed Under [ SQL Server - Database Administration ]

Defragment Indexes stored procedure - new version

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...

posted @ Tuesday, November 27, 2007 12:09 PM | Feedback (6) | Filed Under [ SQL Server - Database Administration ]

Data Types of Parameters and Execution Plans

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):  SELECT Table1Id FROM Table1 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...

posted @ Friday, November 16, 2007 11:56 AM | Feedback (6) | Filed Under [ SQL Server - General ]

Shrinking a Database and Transaction Log Backups problem

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...

posted @ Thursday, November 08, 2007 2:31 PM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

Troubleshooting Performance Problems in SQL Server 2005

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.

posted @ Monday, November 05, 2007 11:41 AM | Feedback (1) | Filed Under [ SQL Server - Database Administration ]

Powered by:
Powered By Subtext Powered By ASP.NET