It could be compile time or it could be a question of data caching or it could be a combination of both.

Usually compile times are pretty benign unless the query...

If you don't have your clients running regular database backups... you should get on that right away, for all your clients.

Mostly, believe it or not, these are technical questions, they're business questions. Ask your business how much data loss they're willing to put up with? That will begin to fill...

Tuning Advisor, assuming it works correctly, and that's a huge assumption, will only suggest indexes for you. Indexes are not necessarily the problem. You need to gather performance metrics to...

I'm not so sure it's that bad. I like to think of myself as reasonable successful in this field and competent at my job. I would make the claim to...

You have a lot of stuff in the query that's going to lead to table scans. For example:

AND PAYCODENAME LIKE '%' + @PAYCODE + '%'

Will only allow for table scans....

tvantonder-992012 (2/3/2010)

---

Thanks for all the help so far. When I try to get an estimate execution plan on just the code it gives me this error:

Msg 208, Level 16, State...

I didn't think to ask that.

If it's doing no work at all, but using up all the memory on the system... I'd contact Microsoft. Open a PSS call or at least put it in Connect...

Don't use a staging table, instead use SSIS to do the import and that way you can break down the records on the fly.

MySQL has pretty similar concepts too. I think I'll just start answering every question with a MySQL answer. After all, what's the difference really?

tvantonder-992012 (2/3/2010)

---

I did create a new sp under new name with recompile. This did exactly the same. Even trying to get estimated execution plan on the sp runs more than...

No, I'm still not sure what's up.

One difference is I ran it on 2008. But there's little reason it should be different.

I think you need to further clarify what types of options you're looking for. Do you mean three different ways to rebuild an index? Do you mean three parameters you...

Maybe I missed a step or something, but I just tested your script against a test database using SQL Agent and it ran without errors.