OK, I did some testing and this is not the magic I could have dreamed of.

While I can SWITCH to a table that does not have matching indexes, I cannot...

That actually has not been my experience, hence my question.

I did encounter the CLUSTER index requirement, but in practice the regular indexes have not seemed to matter.

My test was something...

Thanks for your comments Brian

I will be doing additional investigation around these suggestions.  My initial thought was just making sure I was not missing anything obvious to more a experienced...

Thanks Brian

Yes this has been a brain drain for me.  The process basically creates a new database from scratch and adds the "demo" data.

It does this in the same pattern...

Ok, Yes I did not add that detail.

When it fails it fails because of timeout.  A procedure that normally takes < 1 second suddenly starts taking 30 seconds.  When the...

Thank you John

You have been very helpful.  I think I have some ideas based on your suggestions that will help me to proceed.

I did run some comparisons in execution plans...

• This reply was modified 4 years, 6 months ago by  Steve.

I am not using a transaction, as I do not need the whole operation to be atomic.

The question I have about yours is that there is no native ordering in...

I guess that the more specific question is, If I run this delete in a Loop is is possible that successive delete statements in the same session could block themselves...

Thanks Mike and Grant.

I was looking for tricks for actively running when I am troubleshooting. I agree a 24/7 eyeballs on monitor process is rather inefficient.

Grant, I TOTALLY forgot about...

Right, I get what you are saying, but the point of my questions would be that there is no "end" event because the process is still running.  The SQL reports...

SELECT * FROM (

SELECT NTILE(4) OVER ( PARTITION BY gt.SubGroup ORDER BY gt.RowID ) nt, *

FROM dbo.SomeRandomTable gt)id

WHERE id.nt=2

This is an example where you have a...

I asked a similar question some time ago.

Maybe this thread will help.

http://www.sqlservercentral.com/Forums/Topic935472-392-1.aspx

From an alerting perspective you may have to start a job that checks the default trace for these events and sends an email when they occur.

It may be a little...

While I am not entirely certain I understand the use case, I do have some information that may help.

The SQL96 RFC declares trailing space as irrelevant. Therefore 'a<space>'='a'='a<space><space><space>'=....

You can use...

Thanks for all the comments.

I moved the database to one of our less utilized servers and the problem disappeared. The procedure now runs between 16 and 200ms all the...