Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Chris H
Starting Member
4 Posts |
 Posted - 2010-05-01 : 15:47:01
|
| I have a very simple database comprised of a table with 7 columns. All are VARCHAR type.Within this database I created a few stored procedures. One of them is an UPDATE procedure. My VB2005 application calls this stored procedure along with the other 2, many thousands of times in succession. I am having a problem to where the UPDATE stored procedure will randomly "hang up" and take more than 30 seconds to complete. This causes the command to timeout using the default 30 second timeout. Again this is a very small table with fewer than 30,000 rows. I can see it happen while watching my logging scroll through the debug window.This is what my very simple stored procedure looks likeset ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_UpdateFileActiveTime]@FileActiveTime VARCHAR(50),@FileID VARCHAR(50)ASUPDATE FileListSET FileActiveTime = @FileActiveTimeWHERE FileID = @FileIDThe FileID column in my table is the primary key. I don't understand why this is happening. Is it possible that the SQL server is trying to run some sort of optimization during this time? Can it be disabled?Appreciate any ideas or suggestions. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-01 : 17:04:05
|
| You need to check for blocking. You can do that with sp_who/sp_who2/sysprocesses. You should not prefix your stored procedure with sp_ as that will take a performance hit, since SQL Server will try to call it in the master database first. Use a different prefix. We use usp_ to signify a user stored procedure as opposed to a system stored procedure. Another possible culprit is a bad execution plan. You can check what plan it gets in SQL Profiler. What's likely happening though is blocking.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Chris H
Starting Member
4 Posts |
Posted - 2010-05-03 : 13:43:15
|
| Perhaps having the name start with sp_ was my problem. I changed the names to usp_ and I'm not seeing the queries get hung up anymore. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-03 : 13:48:54
|
| The prefix would have added a very small performance impact. If it seems to have fixed your problem, then my bet is that you had a bad execution plan in cache. By changing the name, you would have gotten a new plan.If the issue happens again, view the execution plan in SQL Profiler. Compare it to what the plan usually is. If they are different, you need to consider recompiling the stored procedure occasionally or each time it runs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
"Update" Stored procedure hangs