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 |
|
PeterG
Posting Yak Master
156 Posts |
 Posted - 2004-01-19 : 17:22:19
|
| When using the recordset.open method, the lock type can be set. But when using the command object to execute a select stored procedure, is there a default locktype being used or is it using one? We have an ASP web app that's working fine, but lately a client has complained that they've been experiencing performance degradation. We investigated and found out that this particular client has multiple users accessing the site using the same id and password and interacting with the database at the same time. We're thinking this causes the tables/records to get locked up; hence the slow performance. Now we're curious about locking on SQL server, thus the question.I hope this makes sense. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-19 : 17:28:16
|
| I can't answer your question, but...It doesn't have to do with the users using the same id and password. If someone is updating a row, then someone wants to read that row, the second person has to wait until the update has completed (unless you want a dirty read).Have you run SQL Profiler to determine what the slowest performing queries are? Have you narrowed down the performance problem to missing indexes, poorly written queries, etc...? If you've run SQL Profiler, have you sent the trace results into the index tuning wizard? Have you run Performance Monitor to see if you have a hardware bottleneck?Tara |
 |
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2004-01-19 : 17:33:21
|
| I haven't done any of these. Do you have any links that I can read up on using these things? I am using SQL Server 7.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-19 : 17:38:24
|
| sql-server-performance.com has a lot of performance information related to SQL Server.From BOL:LockingMicrosoft® SQL Server™ 2000 uses locking to ensure transactional integrity and database consistency. Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time. If locking is not used, data within the database may become logically incorrect, and queries executed against that data may produce unexpected results.Although SQL Server enforces locking automatically, you can design applications that are more efficient by understanding and customizing locking in your applications.Tara |
|
|
|
tinks
Starting Member
34 Posts |
Posted - 2004-01-20 : 11:28:53
|
| you can also use with (nolock) as an option to your query if you are reading data only... ie... select blah from blahdidah with (nolock) where rec = @valthis will prevent the table from being locked at all... however updates will still lock tables/pages... remember that this will also cause a 'dirty' read as this will return the uncommited data.Taryn-Vee@>-'-,--- |
|
|
|
|
|
|
|
|
LockType