Yeah, I'd make sure that you're using WITH MOVE. It could just be that it's overwriting the files... which would only be possible if the database is offline.

I think Jason's approach is better, but you could just disable the login from a SQL Agent job and then have another at the scheduled interval to disable the login....

It really depends on how and where those 70 columns are coming from. You say it's a query to a linked server, are you joining a bunch of other tables,...

You're taking out locks on both queueitem and handrule. It could be that the differences in order between those locks and the ones needed by the index rebuild could be...

You can disable logins. As far as a certain amount of time, I don't think there's a disable for a time period, but you could set up two jobs, one...

And for anyone who searches up your question in the future and wants to know how you solved it, what was it?

No. You shouldn't hit any performance hits while doing it, but, the bigger concern for me is that between setting it to single user mode and continuing on with the...

The details are going to vary for your environment, but here's roughly how we used to do it:

1. Get a copy of the production backup

2. Restore it to a test...

It means you have more than one BEGIN statement and only one COMMIT or END statements. You need to have matching pairs for the query to work correctly.

TheGreenShepherd (2/14/2014)

---

Yeah, I know, it's non-SARGable. That was really my last line of defense for shooting this idea down, because his next suggestion is to make that column non-nullable, and...

It's all about speed. When you need really fast OLTP processing, you go with the in-memory tables and the compiled procedures. The limitations are too severe for any small or...

Michael Valentine Jones (2/14/2014)

---

I am not sure what "overnormalized" even means, but anytime...

Yeah, it could, although you might be better off creating the index on the table after you load it.

It sure sounds to me like the query could be tuned, but as to the long running stuff, I'd assume contention on resources. While it's running, have you collected wait...

Everything Kevin said, then check out the execution plan to understand where the indexes are getting used and where they aren't. Just tossing them vaguely towards a query never works...