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 |
|
jhermiz
3564 Posts |
 Posted - 2008-01-24 : 10:08:22
|
| I dont have much details on this db as it is not mine. Nor do I have access to this db which makes it all the more difficult. The only thing I can do is once in a while a project manager will open up a webex conference to a database at one of our customer plants. The customer then remotes to the sql server 2005 box to show us the issues. So I'm sorry I don't have a lot of details on this issue...but basically I was dragged into a meeting about issues with a database that one of our customers uses for pulling assembly parts off a plant.The original issues dealt with deadlocking issues and performance. Well I already went over those issues with them regarding the performance problems. First off there was way too much dynamic sql and using sp_exec to execute the SQL. No sprocs..apparently they want the code to work for both oracle and sql server..no comment on that! Anyhow the other issue regarding performance was the table design..nope cant change though...Fine..the last issue I do not understand.The customer states that only one record can be updated / changed or inserted before the next one is available. As I said I dont have much details, there setup is weird they've got this db attached to some sort of assembly line. Basically a part has an RFID tag it is scanned and put into a db. This routine used to work fine (so they state). Now they are stating that the entire assembly line is at a stand still because the record / records are inserted / updated one at a time.I thought about row level locking or something of that nature but Im not really certain of the issue. They state absolutely nothing was changed, yet they continue to say this is a problem on the sql server side. Is there anything I can advise them to check to ensure some sort of switch that should be off has been turned on?Thanks,JonWeblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-24 : 10:12:42
|
Check the SPIDS to see which application that are connected?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
jhermiz
3564 Posts |
Posted - 2008-01-24 : 10:15:40
|
quote:
Originally posted by Peso
Check the SPIDS to see which application that are connected?
E 12°55'05.25"N 56°04'39.16"
Not sure what you mean, if you are referring to SQL Profiler, I did profile that db and all seems ok. Well it displays a lot of sp_execute crap but thats all their dynamic sql issues...That doesnt solve all of these record locking issues ?Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-24 : 10:26:11
|
Not long ago I solved a similar problem with an application that was written in Access.It seems Access introduces a locking mechanism of it's own, as long as you are in "edit mode" in the grid editor.When you commit the changes by either changing row or pressing tab or enter, the lock were gone.I discovered this by checking which SPIDS that were active and I could trace back the locking spid to the machine and application that was creating the lock.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
DB Locking?