This document has not been formally reviewed for accuracy and is provided "as is" for your convenience.
Summary
Error
When page fragmentation is present in the index(es) of a database table; the error "Query timeout expired" can be seen within an event written by any Connected service which issues a query -- typically, a query which involves multiple large tables.
Example 1 - Single large table queried by Compactor:
Type : Error
Event : 7537
Source : Compactor Maint
Description:
Failed to compact account nnnnnnnnn. An exception was caught while processing the account:
An error occurred while executing a Select statement while connected to
database Directory on SQL Server SERVERNAME as user DDOMAIN\USERNAME on ThreadId 7420 and Spid 168.
The SQL statement is: SELECT TOP 1 * FROM PoolIndex WITH (NOLOCK) WHERE Account = ? AND Owner = 0
The parameters, if any, are:
[1] nnnnnnnnn
The error reported is: [HYT00][0][Microsoft][ODBC SQL Server Driver]Query timeout expired
[HYT00][0][Microsoft][ODBC SQL Server Driver]Query timeout expired
[HYT00][0][Microsoft][ODBC SQL Server Driver]Query timeout expired
[HYT00][0][Microsoft][ODBC SQL Server Driver]Query timeout expired
Example 2 - Two large tables queried by Compactor:
Type: Error
Source: Compactor Maint
Details:
Failed to compact account nnnnnnnnn. An C_CException error was detected while processing the account:
An error occurred while executing a Select statement while connected to
database Directory on SQL Server SERVERNAME as user DOMAIN\USERNAME on ThreadId 23340 and Spid 102.
The SQL statement is: SELECT TOP 1 * FROM FileIndex WITH (NOLOCK) WHERE EXISTS ( SELECT * FROM PoolIndex WITH (NOLOCK) WHERE PoolIndex.TrueName = FileIndex.TrueName AND PoolIndex.Size = FileIndex.OrigSize AND Account = ? AND Owner = 0)AND Account = ? AND Type = 5 AND Kind IN (2, 6, 16)
The parameters, if any, are:
[1] nnnnnnnnn
[2] nnnnnnnnn
The error reported is: [HYT00][0][Microsoft][ODBC SQL Server Driver]Query timeout expired
[HYT00][0][Microsoft][ODBC SQL Server Driver]Query timeout expired
[HYT00][0][Microsoft][ODBC SQL Server Driver]Query timeout expired
[HYT00][0][Microsoft][ODBC SQL Server Driver]Query timeout expired
Cause
When SQL timeout expires during a running query, some performance issue is the root cause. Most commonly it is caused by fragmentation in the indexes of the database tables queried. However, if after investigation, no significant fragmentation can be found, then other factors which can cause poor query performance must be investigated -- preferably by engaging a Database Administrator's analysis and guidance.
Fix
Microsoft has published articles in MSDN and TechNet to guide both the detecting of and correction for page fragmentation. The resolution method is based upon the severity of the fragmentation.
To check the fragmentation of an index using SQL Server Management Studio:
- In Object Explorer, expand the database you want to check.
- Expand Tables
- Expand the table to check
- Expand the Indexes
- Right-click the index to check and select Properties, then select Fragmentation.
When fragmentation is between 5 - 30%, Microsoft recommends reorganizing an index. Reorganizing an index is always executed online.
When fragmentation is greater than 30%, Microsoft recoomends rebuilding an index, which can be executed online or offline
Source: As of this writing, the MSDN article which details all this is located here: msdn.microsoft.com/en-US/library/ms189858(SQL.120).aspx. Read it carefully before beginning diagnosis or taking corrective action.