[RESOLVED] Locking in .net

[FunkyDexter]

We're getting a problem here where out application occassionally slows to a crawl and we start getting loads of time out issues. Anecdotally it looks like this might be assosciated with a user doing a large export. I want to eliminate this as a possibility.

The code to get the data to be exported uses the SQLHelper.FillDataset method to populate a dataset. It then loops through the dataset and writes the records out to a file. That's fine if the recordset's detached but I can imagine it would be a major problem if it's attached. How can I check this in .net? How can I specify it as detached? (I used to be happy with the ado parameters to specifiy this but I can't find how to do it in .net)

Thanks as always,
Dec

[jmcilhinney]

ADO.NET is inherently disconnected. There's no such thing as a connected DataSet. The tables and records retrieved will only be locked if the code explicitly locks them.

[kevchadders]

If its SQL Server your using put a trace, use SQL Profiler on the Server to mointor what SQL is being run on there. Then you maybe be able to identify which piece of SQL is causing the problem when the slowdown occurs.

ps. Using SQL Profiler can cause a minor slowdown as it will take up some resource to monitor SQL Server. Just something to bear in mind if you use it.

[FunkyDexter]

Thank you both for your replies.

jmcilhinney, that's exactly what I wanted to hear and lets me eliminate the export from my list of possible culprits.

Kev, I generally avoid using the profiler when I'm dealing with timeout issues because I generally find that the slow down is enough to push the performance over the edge. This is a really busy production system and that extra bit of slowdown is enopugh to get me hung . Thanks for the suggestion, though.

I probably should have said on my original post that it's always the same query that times out. The slightly confusing thing is that it seems to start timing out in bursts. It runs along fine all day then suddenly we get a rash of time outs from it for a period of maybe half an hour that then clears up again. Anyway, I spent most of yesterday afternoon going through all the indexes that support it with a fine tooth comb and setting up covering indexes on all the tables it references. I also noticed that our overnight job which rebuilds and reorganises the indexes has been failing so I rebuilt all the indexes manually and that was enough to drop the execution time from about 18 seconds to 8 or 9 (the query has changed alot since the indexes were originally set up and they weren't particularly suitable any more). I've also rewritten the query to remove a lot of old cruft and that drops the query time to about 2 to 4 seconds in the dev enviroment. I'm hoping that will be enough so that it doesn't timeout even when the wierd slowdown occurs.

[kevchadders]

Thank you both for your replies.

Kev, I generally avoid using the profiler when I'm dealing with timeout issues because I generally find that the slow down is enough to push the performance over the edge. This is a really busy production system and that extra bit of slowdown is enopugh to get me hung . Thanks for the suggestion, though.

Aye, in my old company we had a similar system, where the profiler would dramatically slow down a nightly batch run, which we where trying to monitor as it was taking far to long and impacting on working hours.

Of course, if you had the space spare i would recommed running it on a test server, but as you already know the problem SQL, its pointless. Plus i'm sure finding another testserver to run it might be a problem!

Another point to consider is if its a locking issue, you could use a table locking hint if your problem SQL involves SELECT statements.

Again, there is a chance of the users getting dirty data if one is selecteing while another is updating but that would be a call to make based on how your app works, and the impact of that.

Example select using WITH (NO LOCK).

SELECT field1, field2 etc..
FROM tablename WITH (NO LOCK)
WHERE some criteria

ps. If you want, attach the execution plan for the SQL and we will see if we can spot anything.