Experiencing substantial delay in reflecting updates to a table

I am experiencing an unusual problem in which a stored procedure that updates a table with but a single update statement executes in but 13ms per SQL Profiler but a following stored procedure doesn't display this change for upwards of five seconds.

The first stored procedure executes normally without error. Again, per the profiler it completes in 13ms.

A series of other stored procedures execute followed by a final SP which displays data that should reflect the change from the first SP executed at the beginning of the process. However, I find the value is not updated immediately but rather takes a few seconds to be reflected. The value changed is not a column on which a clustered index is based.

Some of the stored procedures that execute between these two SPs of interest do involved INSERT INTO statements. But, in all cases they are inserting into a table variable. I had read somewhere the INSERT INTO could introduce some locking but wasn't sure if it applied in my case.

I could really use some fresh ideas on what to look at - such as locking performance monitors or the like. Any ideas/suggestions?

Thanks!

What isolation level are you using?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Neither stored procedure explicitly declares an isolation level. So, I'm assuming it is using the default isolation level. Is it possible to change the default isolation level for a database? If so - how? If it isn't possible to change the default isolation level then the stored procedures should be using Read Committed.

Is it possible for SQL Profiler to display the isolation level used on a stored procedure?

Thanks!

|||

Hi,

You cannot set a default isolation level for a database (only read committed or read committed snapshot can be changed).

The default is read committed for SQL Server. If you are accessing your database through COM+ however the default is SERIALIZABLE.

I see now reason however why your updates would have a delay. Can you give some more background about the application?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

The application is a ASP.Net v1.1 application. I've searched for usage of a transaction initiated from the .Net code and have not found that transactions are not used in this situation.

Again, just to clarify the delay - the stored procedure executing the INSERT completes in approximately 200ms (it calls a scalar UDF which I've not researched yet). However, sometimes, querying for that changed data via a stored procedure executed later in the process does not reflect those changes but rather the original data. If I query multiple times it eventually shows up.

I'm at a loss on this one - any ideas?

|||

Very odd, I can not think of a good reason why there would be a delay.

The microsecond that your transaction gets committed it should be reflected in your select queries.

When you talk about your 'select' statement are you executing them from Query Analyzer or are you using the ASP.NET page?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com