1

2008 R2(SP1) - 10.50.2500.0 (X64)

Hi,

I am having the following problem intermittently on my development machine. I am running some T-SQL in a FOR loop. During each loop iteration, first we delete all rows in a table and then we add some rows to it (we are talking thousands of rows so nothing extraordinary). Then we query the rows in this table a few times and store the results in a table variable.

Sometimes (quite often) the process appears to completely hang. I cannot kill the sp_id. DBCC OPENTRAN indicates that we are stuck on an INSERT. After much research I now see the following in sys.sysprocesses:

Two different spids both attached to the database I am working on.

One of these is my T-SQL statement, waiting on 'WRITELOG', status is 'suspended' and cmd is 'INSERT'. The other is waiting on 'WRITELOG', status is 'background' and cmd is 'GHOST CLEANUP'.

As I requery sys.sysprocesses the wattime value is changing so I am not sure if things are actually being written to disk or not.

I am no DBA, but this seems to be quite an advanced topic and has had me frustrated for a few days now. It is interesting to note that I am only seeing this on my development machine at the moment so I am wondering if this has something to do with my hardware and the fact that we are inserting/deleting so quickly...

Any help appreciated...

-UPDATE-- So I realised that as the problem table is really just for staging that I could use some TVF and table variables. This seems to have stopped the problem occurring and has a nice performance boost side effect. I am still intrigued as to what the problem is though and wonder if it could reoccur anyway if SQL Server starts to make use of tempdb (as I said, I am no DBA, but http://msdn.microsoft.com/en-us/library/ms190768.aspx seems to indicate that tempdb could be used for table variables)

MT.
  • 121
  • 4

1 Answers1

0

After suffering a couple more days of this, updating my BIOS and also the firmware for the hard drive (it is a Crucial M4 SSD - I have had a few issues with it since purchase so maybe it is not quite right), I was still seeing strange disk I/O related waittypes 'hanging' the server, even just for a simple deletes of a few thousand rows.

I did have a look at some of the disk counters in performance monitor, but was not really sure what I was looking at. Maybe they were high compared to the optimum, but hard to say with no baseline to compare to.

As a last resort I added another harddrive to the system and moved my offending databases there (note: I had to start SSMS as Administrator to attach the database) and it would seem that the problem has gone away.

So the problem is more than likely disk related. I will leave this question here for reference just in case anyone else has the same problem.

MT.
  • 121
  • 4