Category Archives: SQL Server Administration

Execution Plan of Frequent Queries

Posted on May 29, 2009 by Clay Lenhart

Bill Galashan, DBA of bet365 sent over the following query that lists the execution plan of the 10 most frequently executed queries. Continue reading →

Posted in SQL Server Administration | Tagged , , , , , | 28 Comments

Renaming a SQL Server machine

Posted on August 8, 2008 by Clay Lenhart

Below are a collection of links on how to rename a SQL Server server. Rename the SQL Server engine. This describes how to run Rename SQL Server Reporting Services server. This describes how to update a config file with the … Continue reading →

Posted in SQL Server Administration | Tagged | Leave a comment

Cached Execution Plans in SQL Server

Posted on April 13, 2008 by Clay Lenhart

This blog entry describes how you can get the SQL Server execution plan of any running statement and display the graphical representation in Enterprise Manager. Continue reading →

Posted in SQL Server Administration | Tagged , , , , , , | 37 Comments

Table Size Query

Posted on March 8, 2008 by Clay Lenhart

The following query lists the tables and the space they use. This query is much faster (sub-second) than a standard SELECT COUNT(*) query since it uses the dynamic management views in SQL Server rather than scanning your data. SELECT sum … Continue reading →

Posted in SQL Server Administration | Tagged , | 16 Comments

Finding Used Databases

Posted on January 31, 2008 by Clay Lenhart

It can be difficult to determine which databases you are administering are actually being used. Below is a query that determines the last time an index was used since the last time SQL Server started to help narrow down these … Continue reading →

Posted in SQL Server Administration | 7 Comments

SQL Server RAM Usage

Posted on January 30, 2008 by Clay Lenhart

SQL Server has difficultly managing memory for systems with a large amount of RAM (say 8+ GB), typically found in 64-bit installations. You might see log messages such as: A significant part of sql server process memory has been paged … Continue reading →

Posted in SQL Server Administration | 5 Comments

Defragging Indexes in SQL Server 2005

Posted on January 29, 2008 by Clay Lenhart

There are two ways to optimize your indexes in SQL Server 2005: ALTER INDEX … REORGANIZE ALTER INDEX … REBUILD Which one should you choose? According to BOL, it depends on how fragmented they are. The avg_fragmentation_in_percent column from the … Continue reading →

Posted in SQL Server Administration | 9 Comments

Backing up to a NTFS compressed folder

Posted on January 28, 2008 by Clay Lenhart

It seems like a good idea to backup SQL Server 2005 databases to a compressed folder, however if your database is larger than roughly 30 GB, you will get the following error: BackupMedium::ReportIoError: write failure on backup device ‘G:\Backups\200501220402.BAK’. Operating … Continue reading →

Posted in SQL Server Administration | 32 Comments

SQL Server 2008 Nov CTP Released

Posted on November 26, 2007 by Clay Lenhart

The November CTP for SQL Server 2008 has been released: http://www.microsoft.com/downloads/details.aspx?FamilyId=3BF4C5CA-B905-4EBC-8901-1D4C1D1DA884&displaylang=en

Posted in SQL Server Administration | 9 Comments

List statistics and date updated

Posted on November 25, 2007 by Clay Lenhart

The following query will list all the statistics in the current database, and the date they were lasted updated. SELECT object_name(object_id) as tablename, name AS index_name, STATS_DATE(object_id, stats_id) AS statistics_update_date FROM sys.stats WHERE object_id > 1000 ORDER BY object_name(object_id), STATS_DATE(object_id, … Continue reading →

Posted in SQL Server Administration | 6 Comments