CODEWITHSUNDEEP
sql-serverselecttimeoutreport
cfreak
cfreak

Reputation: 82

SQL Server Query(s) Timeout (both Read/Write) when Report Query is Running

I have a SQL Server 2005 database which has about 30-40 connections to the same database during business hours.

When I executed a report query (this query takes about 30mins to 1 hour), the other connections started to get timeout when doing select/write on some particular tables. This report query does SELECT with two or three level of sub-queries and joins. I looked at SQL Server Log and I couldn't find any error at all. Looking at Activity Monitor doesn't show any process running except tempdb (which shows Running). Checking to see if there is any locks on tables, show only shared lock.

I went further and check tempdb had enough space (500MB grow-able to 10GB).

Do you know what might cause this problem? Where should I start to look at? (I am looking at optimizing to report query right now)

Upvotes: 1

Views: 1294

Answers (2)

Chris E
Chris E

Reputation: 983

You need to run a profile on the database when you're running the query. at least that's the fastest and easiest way to do it. Obviously, do it offhours when so you don't burden your users with your query from hell. Don't take the recommendations as gospel, but they'll certainly help you move in the right direction.

Also check the query plan. I believe 2005 allows you to generate the query plan right there. If not, you can get the Management Studio/Analyzer for SQL Express 2008 and it'll work fine on a 2005 database.

Upvotes: 1

Conrad Frix
Conrad Frix

Reputation: 52645

You definitely need to try and fix that query. In the mean time you can as a band aid measure set the isolation level on the Report query to READ UNCOMMITTED

This can cause inaccuracies in your report (because it can read a transaction that ends up being rolled back) but it should lower the contention this query causes.

Upvotes: 2

Related Questions