Query Timeout when running t-sql save or update statements compiled in vba and run in SQL Server

Question

My company is currently running SQL Server 2000, and about to make the jump to SQL Server 2012, but before we move the upgrade, we want to try and clear up a performance issue that has recently cropped up and is plaguing our ability to do business.

A few months ago we decided that we wanted to add a text field to one of our sql server tables to try and capture a specific event. I thought I’d try and add the column during the day, which in retrospect was probably the first mistake, but I figured if people were using the table, SQL Server wouldn’t allow me to add the column. On this attempt, the extra column was accepted and added to the table. I then coded our Access front end to write to this new column.

Soon after, users started experiencing the Access GUI “locking up”, and eventually throwing a query time out error.

Here's how the error occurs. After clicking a save button in an Access front end, which would then build a T-SQL statement in VBA to update or save a new record, users would not see an Access message box saying that the save completed successfully or that an error had occurred. Instead they would get a Access system dialogue box that said there was a query time out. At this time I deleted the column that I added, hoping that would fix the issue.

I started analysis by having everyone close their Access program, then have the person who was experiencing problems re-open and try to save again. This worked for that user, and I would allow everyone else back into the system at this time. Of course I wasn’t sure why this worked at the time, so I tried looking more into the issue. Further analysis led me to sp_who2. Running this would give me all the users and the SPID of who was blocking. This gave me a quicker way of getting things working again because I could kill the offending SPID and allow everyone else to continue working. Of course the kill results in a rollback, so not all data is being saved and I have to tell the owner of the killed process that they must re-enter all their data a second time. To date, I continuously monitor this database in this way, and if I find something blocked, this is how I fix it.

I know the problem is with a single table in this database, first because trouble only started after I tried to enter a new column, but also because every time a save or update is performed, it locks when writing to this particular table. At the time, I diagnosed this because I asked my users what action they were trying to perform, and in all cases, they were trying to do something to this particular table. In fact, changes to other tables in this same Db execute with no issues. So I felt the problem may be related to corruption to this table.

First thing I did was run a CHECKDB and CHECKTABLE, but neither of these returned an issue with the table. So we decided to rebuild the table. I rebuilt the table by hand, then copied the data over from the original table to the new table. I believe I used a sql tool for this, but I may have run an insert into. (It’s been a while and I didn’t document.) Either way, it didn’t work. The organization continued to experience blocking. So then I re-indexed the table, and that seemed to work, for about a day, and then the error “came back.” Since then I found INPUTBUFFER, which I was running on the SPID of the blocking user every time for a while. Almost every time, the INPUTBUFFER would return a SELECT statement, though I don’t think that’s what was throwing the block. I don’t think the SELECT was throwing the block because in the Access GUI what happens is you hit the save button, it runs a save or update, then it runs a select to refresh the Access screen. I would think that if the SELECT was throwing the block that the save would have happened, but when recalling the record after the process starts blocking, and the process is killed, the user changes are never saved to the record. I’m not entirely sure how INPUTBUFFER works. I know it supposedly returns the last statement sent to SQL Server, but if the save didn’t commit, could Access still be sending SQL to SQL Server?

I will also add that I never seem to throw a block when I’m working in SQL Server Management Studio itself. I have obviously felt the repercussions of an existing block which will hang my sql statement, but I’ve never thrown a block to my knowledge. To that end I’ve also updated the ODBC connectors from every user to 10.0. This doesn’t seem to have a negative or positive impact on the issue.

So I'm not sure what to do next. Any suggestions would be appreciated.

Answer 1

When working with linked tables, make sure your SQL Server table has a unique identifier.

Check out my article on how to link tables.

http://craftydba.com/?p=1893

The second question is what type of lock are you getting?

Below is some code from a presentation I do from time to time. Change the adventureWorks2012 to your database name.

This should show you what locks are occuring. Pick the correct hobt_id or object_id to find out details.

--
-- Locked object details
-- 

-- Old school technique
EXEC sp_lock
GO

-- Lock details
SELECT
    resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description 
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('AdventureWorks2012')
GO

-- Page/Key details
SELECT object_name(object_id) as object_nm, *
FROM sys.partitions
WHERE hobt_id = 72057594047037440
GO

-- Object details
SELECT object_name(1266103551)
GO

Blocking is part of how SQL server allows cooperative execution between sessions. The problem is when it turns into a deadlock or SQL server does not detect a deadlock.

Some solutions are to change the isolation level. But this has its own issues - phantom reads, dirty reads, unrepeatable reads, and low concurrency. The first place to look is the lock sequence. Please post an image.

Since you are using SQL Server 2000, look at sp_indexoption.

Overview:

http://technet.microsoft.com/en-us/library/aa213036(v=sql.80).aspx

"For example, when a table is known to be a point of contention, it can be beneficial to disallow page-level locks, thereby allowing only row-level locks. Or, if table scans are always used to access an index or table, disallowing page-level and row-level locks can help by allowing only table-level locks."

Details:

http://technet.microsoft.com/en-us/library/aa238800(v=sql.80).aspx

Try stopping the MS Access program from using page locks.

Sample call from BOL:

USE Northwind
GO

EXEC sp_indexoption 'Customers.City', 
   'disallowpagelocks', 
   TRUE
GO

Use sp_who2 to get the two SPIDS that are blocking.

Use DBCC INPUTBUFFER to get the actual SQL statement for both SPIDS.