Scripts for SQL Server Magazine Web Seminar, October 1, 2002

Questions and Answers:

Chuck Boyce Asked: I'm sorry for the ambiguity of the question, but can you speak generally about the (apparent) unique behavior of locking observed when applications use MS Access as a front end with SQL server as the back end? I have seen an entire SQL Server table locked when a user had an Access form open and then the user went on break. Strange.

Answered: Unfortunately, I haven't use ACCESS as a front end, and really don't know what it is doing when it accesses a table. I would start a trace using the Profiler and then see what actual commands are being sent to SQL Server when ACCESS opens a form.

Jeff Mergler Asked: We sometimes see an error message: Connection is busy with results from another hstmt. This seems to occur when two connections are accessing the database from a single applcation. One connection is fetching rows and another connection can be doing anthying, such as a select. As long as the first connection is fetching and is not completed that process (of fetching rows), the second connection's select statement will throw that error message. Is there a specific locking strategy that can improve this situation?

Answered: This really sounds like the kind of message you would get if you really were using the same connection to run different queries. Make sure they really are defined as two different connection objects and not just two names for the same one. You can also use Profiler to trace New Connections, to make sure they are separate, and also to see what each connection is really doing.

Judy Shoop Asked: So you've inherited this canned software package, where do you start to investigate tuning? Profiler?

Answered: You guessed it! Profiler is the perfect tool to watch what a canned app is doing behind the scenes, and is definitely where I would start.

Janice Parkinson Asked: Can you review what resources (pages, indexes, etc) are locked during inserts? In particular to avoid blocking where there are different transactions that are inserting or updating the same set of tables.

Answered: Unfortunately there is not just a simple answer for this. It really depends on the kinds of indexes you have, and any pattern that the data inserts are following. You can try figuring out for yourself what locks are acquired by creating a simple table, and then inside a transaction perform an insert and then look at sp_lock to see locks for your connection only. (Note that sp_lock can take a spid value as an argument, and the function @@spid returns your current process id. So, EXEC sp_lock @@spid will show you only the locks that your session has acquired. This is a good thing if there is a lot of other activity on the server, and you don't want to have to wade through date on other users' locks.) A short answer, that sometimes is useful to help prevent blocking during updates, is to make sure you have good indexes to find the data to update. Use showplan to make sure the index is being used and use sp_lock to make sure EXCLUSIVE (X) KEY locks are acquired and not PAGE locks or table locks. (Intent page and table locks (IX) are ok.) For INSERTS which are blocking, you might want to make sure that your clustered index is NOT on a column that determines the order that the new rows are arriving. For example, don't put your clustered index on OrderDate or OrderNumber, which will always be increasing.

Mary LaFrance Asked: Is there any history on deadlocks?

Answered: Deadlock history is not kept by default. There are two ways that you can configure SQL Server to keep deadlock history. You can start your SQL Server with traceflag 1204 enabled, in which case all information about deadlocks in written to your SQL Server errorlog. You will need to make sure you save these errorlogs containing deadlock information to another location, because SQL Server only keeps the last 6 errorlogs, by default. Although you can change the number that is kept, you still might eventually overwrite information you want to keep, so I would make sure that you periodically write your errorlogs to an archive location. The second method to save deadlock information is to define a trace, either using Profiler or using the trace stored procedures. The trace should capture statements, transactions, deadlocks and deadlock chains.

You can refer to Microsoft Knowledgebase article Q271509 --INF: How to Monitor SQL Server 2000 Blocking, for general information about troubleshooting locking. SQL Server Books Online has an excellent section on troubleshooting deadlocks.

Rick Passaglia Asked: Do multi-segmented cols in a clustered index increase potential for locking/blocking?

Answered: I'm not sure what you mean by multi-segmented. Do you mean indexes on multiple columns? In general, indexes are good for reducing blocking problems, because it means you can find the data faster and release your locks sooner. However, an index on a lot of columns will need to be updated every time any of those columns is modified, and this maintenance overhead can cause blocking problems during data modification operations.

Rick Passaglia Asked: Are BMC Software's "Patrol" or NET IQ "App Manager" good tools to trouble shoot problems?

Answered: Unfortunately, I haven't used either of these tools. I would suggest you post this question on a public help forum, to find out what other SQL Server users think of these tools. You can access SQL Server Magazine's web based help forums from the web site at www.sqlmag.com. If you prefer to use a newsreader, you can access Microsoft's public forums on the server msnews.microsoft.com and look for sections with 'sql' in the name.

Shirley Landers Asked: I would be interested in a webinar series on Profiler. I know you covered some already, but am interested in a more in depth and complete review.

Answered: I'll keep it in mind. SQL Server Magazine and I have discussed an advanced DBA series, or even a whole class on Performance Tuning, and there would definitely be more information on Profiler if we did this.

Rick Passaglia Asked: Does running DBCC INDEXDEFRAG frequently help?

Answered: I have never known this to help reduce contention specifically, but it can help in other ways to speed up performance, and that is good for everybody. The less time your tasks take, the less time that locks will be held. Note that defragging only helps speed up queries that are scanning a table or a range of data in the order defined by an index.

Jeff Mergler Asked: Yes! A webinar on advanced Profiler tricks/topics would be great!

Answered: I think so too! I love profiler, and would love to talk more about it. :)

Sheila Acker Asked: What is error msg 1203, "attempting to unlock unowned resource "?

Answered: This error indicates a bug in SQL Server. Several bugs that caused this error were fixed in SQL Server 2000, service pack 1. If you can create a script that will allow anyone to reproduce this problem, you should send it in to Microsoft SQL Server support.

Rick Passaglia Asked: IF clustered indexes prevent row locking, what advantages are there to a clustered index?

Answered: When I said that you wouldn't see row locks in the sp_lock output if you had a clustered index, I didn't that there wouldn't BE any row locks, and I apologize for the confusion. If you have a clustered index, the leaf level of the index contains the actual the data rows, so when a row is locked, it is reported as a KEY lock. But it is the same as a row lock; it is just going by a different name.

Jeff Mergler Asked: what is the best strategy to prevent lost updates, from a locking and isolation level perspective? but still allow reasonable read access to other users?

Answered: True lost updates can only happen when you read and change the data in two separate operations. If your updates are always a single operation, such as UPDATE inventory SET quantity = quantity + @newquantity WHERE part_no = @recently_received_part_no, this update may get blocked or may block others temporarily, but it cannot get 'lost'. The best strategy is to keep your transactions absolutely as short as possible, so the locks are never held longer than necessary. You can consider using READ UNCOMMITTED isolation level if your readers don't have to get absolutely current data.

Shirley Landers Asked: For SQLMAG - need a way to better inform people about your seminars - I did not find out about Moran's profiler session

Answered: To be answered by SQL Server Magazine

lihui gao Asked: where can we get the scripts?

Answered: To be answered by SQL Server Magazine

Judy Shoop Asked: How about a step by step seminar for the new dba?

Answered: This is a great idea. For now, I will be presenting a seminar called TOP 10 DBA Tasks, Tips and Tricks, at the SQL Server Magazine LIVE conference in Orlando, Oct 27 - 30. It will include many things that a new DBA needs to know, but frequently overlooks.

Rick Passaglia Asked: when you have been chosen as a deadlock victim is your update lost?

Answered: Yes, the transaction that the victim's process is running is rolled back and any partially completely work is lost. It is important that your APPLICATIONS test specifically for error 1205 and if they receive it, they should rerun the same transaction that failed.

Judy Shoop Asked: After the 3 months pass, is there a way to get the other downloads?

Answered: To be answered by SQL Server Magazine