Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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)

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.