1

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)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
cfreak
  • 82
  • 1
  • 8
  • Post your report query or at least relevant portions of it. Also do you have NOLOCK hint in the report query? – amit_g Dec 01 '11 at 19:42
  • I smell row/table locking and a hint of deadlocks. –  Dec 01 '11 at 22:51

2 Answers2

2

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.

Community
  • 1
  • 1
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • Thanks. On development server, I added several indexes and now the query ran in matter of few seconds. wow!. Do you have any idea what the cause of timeout problem would be? – cfreak Dec 01 '11 at 20:58
  • Hmm well table scans vs index seeks do seem to make a HUGE difference. – Conrad Frix Dec 01 '11 at 21:01
  • @cfreak, a full-table scan is **slow** compared to an index scan. I refer you to [this question](http://stackoverflow.com/questions/4810804/sql-indexes-vs-full-table-scan) for more information. And pretty much google "full table scan vs index" –  Dec 01 '11 at 22:52
1

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.

Chris E
  • 973
  • 13
  • 26