Category Archives: SQL Server Administration

Execution Plan of Frequent Queries

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 , , , , , | 27 Comments

Renaming a SQL Server machine

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

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 , , , , , , | 34 Comments

Table Size Query

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

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 | 6 Comments

SQL Server RAM Usage

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 | 4 Comments

Defragging Indexes in SQL Server 2005

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 | 8 Comments

Backing up to a NTFS compressed folder

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 | 29 Comments

SQL Server 2008 Nov CTP Released

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 | 5 Comments

List statistics and date updated

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 | 4 Comments