Clay Lenhart’s Blog

I have been working on a performance problem recently, so you might see several blog entries with information that help me. Hopefully they will help you.

Getting the SQL Server execution plan from a production can be difficult, since you are not running the code within Enterprise Manager. You can still get the execution plan of any running statement and display the graphical representation in Enterprise Manager. Read more »

Tags: execution plan, performance, SQL Server Engine

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 ( used_page_count ) * 8 AS SizeKB,
  sum(row_count) AS [RowCount], object_name ( object_id ) AS TableName
FROM sys.dm_db_partition_stats
WHERE index_id=0 OR index_id=1
GROUP BY object_id
ORDER BY sum ( used_page_count ) DESC;

Tags: dynamic management views, SQL Server Engine

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 databases:

 
SELECT db.name, i.name, S.*, i.*
 FROM sys.dm_db_Index_Usage_Stats S
      INNER JOIN sys.DATABASES db ON db.database_id = s.database_id
      INNER JOIN sys.Indexes I
              ON S.Index_Id = I.Index_Id
              AND S.Object_Id = I.Object_Id
              AND S.DataBase_Id = DB_ID()
WHERE I.Object_Id > 1000
ORDER BY db.name

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

Or worse, SSAS may fail to process a cube with the only message that it was cancelled. To see if you have this problem, open Performance Monitor and view "Private Bytes" under the Process category. The value should be less than the amount of physical RAM.

I found this following article useful in dealing with this issue: http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx

You want to configure SQL Server and SSAS to use a specific amount of RAM, but be sure to leave plenty of RAM for OS and other processes, such as SSIS packages. Monitor Private Bytes while you run a typical workload to see how much RAM you can allocate to SQL Server and SSAS. It won't be surprising if you allocate 2/3 of the RAM to SQL Server and SSAS, and leave 1/3 for the OS and other processes.

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 sys.dm_db_index_physical_stats() system function lets you know how fragmented your indexes are. If the value is below 5.0, then there is no need to defragment the index. If the value is between 5.0 and 30.0, then "ALTER INDEX ... REORGANIZE", is the best option. If the fragmentation is greater than 30.0, then "ALTER INDEX ... REBUILD" is the best option.

REORGANIZE has a couple of benefits. REORGANIZE will log only the pages that changed, while REBUILD will log the whole index. REORGANIZE can be done online, while REBUILD can't (unless you add the ONLINE=ON option for Enterprise Edition installations).

However REBUILD will update the statistics of the index which lead to better execution plans, while REORGANIZE does not.

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 system

error 33(The process cannot access the file because

another process has locked a portion of the file.).

One way to get around this is to stripe the backup to multiple files, for instance:

BACKUP DATABASE AdventureWorks TO
DISK='G:\Backups\200501220402.BAK1',
DISK='G:\Backups\200501220402.BAK2',
DISK='G:\Backups\200501220402.BAK3'
WITH FORMAT, CHECKSUM;

Not ideal, but there are two options:

1. Buy LiteSpeed Backup which includes compressed backups
2. Wait for SQL Server 2008, which includes compressed backups

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

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, stats_id);