July 7, 2006 at 12:46 pm
Hi,
I'm having a problem which I hope someone can help me with. The developers have put in a new application to update tables in a database. Without my knowledge, they've been testing this for a month with only one user. Now they've moved all their users to it and the table is locking up. It's using SQL Server authentication and everyone is using the same login ID.
Please understand that this is changing a data warehouse database to an OLTP database. I won't comment on the management who okayed this change without our knowledge or consent.
I have no idea where to start looking for the cause. Could it be because they're all using the same login ID? Would it help to change it to Windows authentication? Where do I look to find out what causing the table lock? I have no access to the application, only to the database.
Thank you in advance.
July 10, 2006 at 8:00 am
This was removed by the editor as SPAM
July 10, 2006 at 8:56 am
I'd either sack the lead developer or find a new job! ( probably both )
the single user will have little impact one way or another ( it may indicate poor design but that's another issue unrelated ) contention will occur due to locking for the updates, make sure the search arguments used to select the updates have indexes to avoid table scans. In multi user apps correct indexes for deletes and updates can be critical.
There are various scripts etc. to isolate lockign but I guess you know what that is anyway!!! Try indexes first, job websites second.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 10, 2006 at 8:57 am
You might want to consider partitioning if the table is large.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 10, 2006 at 11:07 am
I was mainly concerned about whether the single user login was contributing to the problem, since I couldn't find anything on that one way or the other.
There are many clustered or unique indexes, but no primary keys. I believe this was imported from Informix as is.
I asked the developer for some sample queries so I could see if the indexes were being used. His answer was that they were embedded in the application. I'll just have to keep trying. Thank you for your help.
July 10, 2006 at 11:51 am
That's a nonsense answer from the developer. He's just being lazy. Even if the code is "embedded" he can provide examples. (I'm speaking as a former software developer turned DB developer / admin.)
IN your defense, I would highly reccomend that you get familiar with SQL Profiler and use it (assuming you ahven't already). That can trap the SQL that is being sent. It can also give you all sorts of information regarding the performance and locking.
Oh, and embedded SQL is just one more reason to fire that developer and/or look for a new job...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply