0

I recently had a web application migrated to a different data center. That included moving the database from SQL 2000 to 2005, which shouldn't be a problem per se (I run other instances of the same app with SQL 2000 to 2008 R2).

The problem is, after the migration some operations, especially UPDATEs, became extremely slow (timing out after whatever limit I define in my app, and I tried up to 6 minutes).

I tried running the same query on Management Studio, but the results were inconsistent. Sometimes it just runs instantly, but sometimes it takes forever and I have to cancel the query. On my app, it always time out (Classic ASP app).

It's happening on more than one query, but most frequently on one that looks like this:

UPDATE mytable SET timestampcol = GETDATE() WHERE record_id = 12345 /* record_id is the PK */

What I tried already:

  • First, I suspected of the table size (~2M rows). So I deleted old records, leaving only ~40,000 rows, but the problem persisted.
  • Then I tried recreating my indexes, and it didn't work.
  • I also tried running both sp_updatestats and sp_updatestats 'resample', but no luck.

So, I'm stuck. Does anyone has a clue of what might be happening, and how could I fix it?

UPDATE

Looking at sys.dm_tran_locks, I can see that two locks are in place while the query is running from my app:

request_mode        request_type    request_session_id
S                   LOCK            65
IX                  LOCK            67

I also realized the S lock appears before I try to run the query that gets stuck, on an SP call. I'm now revising the SP to try and understand why the lock persists after the SP finishes running.

UPDATE 2 - Answers to Aaron Bertrand questions (see comments)

  • Does the procedure use transactions? No, there are no transactions in the whole app

  • Are there any code paths where the transaction might not be committed? N/A

  • Who is calling this other stored procedure? The same script is calling it, a few lines before the problematic query

  • Are you using some kind of transaction context in .NET? No, and it's not .NET, it's classic asp (old legacy code I have to maintain)

  • Why isn't the app calling stored procedures? You means instead of running the problematic query directly from (ADO) connection.execute? Well, also tried it, same result.

  • Why does calling the other stored procedure not affect you when you run the problematic query in SSMS? I have no idea! But the update is currently running just ok from SSMS, whether I call the sp before that or not.

Guys, thank you for all your help, but I'm about to give up on this. Things just worked before someone decided to move the server. The problem was just dropped on my hands, I'll try to pass it along to someone else.

bfavaretto
  • 71,580
  • 16
  • 111
  • 150
  • If this is happening to a single row update easily found with an index I would suspect blocking. Have a look at `sys.dm_os_waiting_tasks` whilst it is waiting. – Martin Smith Sep 23 '11 at 19:14
  • Is this a dedicated server, or one where the provider has 100's of clients on it? – E.J. Brennan Sep 23 '11 at 19:14
  • @MartinSmith, I looked at that table, but don't know how to interpret it. After running the query from my app, 2 new rows showed up, with `wait_type` = OLEDB and ASYNC_NETWORK_ID. – bfavaretto Sep 23 '11 at 19:23
  • @E.J.Brennan, not 100's, but not dedicated also. – bfavaretto Sep 23 '11 at 19:23
  • When you run the query in Management Studio and it takes a long time, see if it's being blocked by looking at `sys.dm_exec_requests` in a different window. You might have some other background operations that are preventing the updates from happening quickly. – Aaron Bertrand Sep 23 '11 at 19:42
  • @AaronBertrand, Currently, I cannot reproduce the problem in Management Studio, UPDATEs are running instantly from there (connecting with same user as my app). – bfavaretto Sep 23 '11 at 19:49
  • It may be due to different `SET` settings. Some background questions to research: http://stackoverflow.com/questions/2736638/sql-query-slow-in-net-application-but-instantaneous-in-sql-server-management-stu http://stackoverflow.com/questions/808914/sql-server-2005-stored-procedure-fast-in-ssms-slow-from-vba http://stackoverflow.com/questions/2886592/sql-server-query-slow-from-php-but-fast-from-sql-mgt-studio-why – Aaron Bertrand Sep 23 '11 at 19:54
  • You can compare set settings also using `sys.dm_exec_sessions` you're looking for differences in columns like `arithabort` and `quoted_identifier` - then change the SSMS session to use the same ones as the app, it might be quite easy to reproduce if that's the culprit. – Aaron Bertrand Sep 23 '11 at 19:56
  • I set both `ARITHABORT` and `QUOTED_IDENTIFIER` to `OFF` on SSMS to match the other connection, but the UPDATE still runs instantly... – bfavaretto Sep 23 '11 at 20:15
  • Is this code being called from a stored procedure? – E.J. Brennan Sep 23 '11 at 20:26
  • Erland has written probably the best resource on this topic, ever. It's going to take some time but I suggest you grab a coffee and read through it. http://www.sommarskog.se/query-plan-mysteries.html While reading this you'll probably have very handy answers to a lot of the questions we might ask in order to guide you through troubleshooting steps, and you'll be able to test things quite easily without waiting for our responses. Not trying to shoo you away, just trying to help you learn how to fish by studying a fantastic resource. – Aaron Bertrand Sep 23 '11 at 20:31
  • Also my suggestion about checking for blocking can work also if you run the diagnostic query while the app is slow - you just have to identify which session_id belongs to the app. – Aaron Bertrand Sep 23 '11 at 20:33
  • @E.J.Brennan, no the update query itself is not inside a procedure. But I realized a previously invoked procedure is leaving a lock behind (just updated the question with this info). – bfavaretto Sep 23 '11 at 20:33
  • @AaronBertrand, thanks, I already took a quick look at it, but you are right, will need to grab a coffee and read more carefully! – bfavaretto Sep 23 '11 at 20:34
  • 1
    Does the procedure use transactions? Are there any code paths where the transaction might not be committed? Who is calling this other stored procedure? Are you using some kind of transaction context in .NET? Why isn't the app calling stored procedures? Why does calling the other stored procedure not affect you when you run the problematic query in SSMS? – Aaron Bertrand Sep 23 '11 at 20:34

2 Answers2

0

This turned out to be a lock on the table. I still don't understand why it was getting locked, but I solved it by adding the WITH(NOLOCK) hint to my stored procedure.

bfavaretto
  • 71,580
  • 16
  • 111
  • 150
0

sp_updatestats gives nothing if there are no suitable statistics already created (both tables and indexes). Review actual execution plans and look for missing statistics (explicit warnings and mismatches between actual and estimated rows says that statistics are probably wrong)

pkmiec
  • 2,594
  • 18
  • 16