| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2011-04-14 : 16:14:38
|
| Hi,We had a very odd problem today. A server was non-responsive for a while and we identified that there was a job hanging for a few hours.This job causes millions of writes to the database within a short period of time. We killed the job and waited for a while for things to come down. The server was still not very responsive. Looking at "sp_who2" results we identified that there was a lot of blocking going on and all related with DBName = 'tempdb'We finally narrowed the culprits down to 2 stored procedures which create temporary variable tables. Once the logic was changed not to use temporary variable tables, all the blocking stopped and the server came back to normal.It seems that simply creating the temporary variable tables (not even inserting any data into them) within the stored procedure, and then executing the stored procedure would make the procedure run forever, as if something is not allowing for the creation of the temp variable table to happen.Could someone please suggest what could be the reason for this?Could it be the issue with tempdb or something else? How to find out?Thanks a lot! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2011-04-14 : 16:58:36
|
| SELECT @@VERSION shows: Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) May 13 2005 18:33:17 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) After some time the stored procedures are working with temp variables in them. Looks like it was a temporary problem. Could it be that it reached a limit on how many temp variables it could create?Thanks! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2011-04-14 : 17:18:16
|
| typically you would want to use table variables if the dataset is very small like a couple of hundred rows... temp tables perform better for anything higher..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2011-04-14 : 17:47:21
|
| Tara,Do you remember what was the problem the temp variables caused?I always thought it was good to use them when size is small (in our case 5-25 rows).Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2011-04-18 : 15:49:42
|
| ITs true that table variables dont have statistics not can you create any other indexes except PK.However, for a few rows of data SQL Server may end up scanning the table anyway.. Tara: Have you looked into creating a PK on the table variable to see if that gave any improvement? #Temp tables work but the downside is depending on the workload of your system, if you have thousands of users calling the same proc/code.. you would be creating/dropping temp tables in large numbers putting a load on GAM pages constantly allocating/deallocating pages for objects..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2011-04-18 : 17:26:14
|
| well.. one of those "unexplainable" things SQL Server does.. :)Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
Can't create temporary table variables anymore