A similar question has been asked in a different thread, but didn't get a conclusive answer. Is there a way to set process priority on a specific process (alternatively - processes started by a specific user), similar to what the Task Manager allows in Windows?
I need to archive some data from an OLTP database. Unfortunately, some of the tables are growing rather large, and copying the data, and especially removing it based on record age, is a resource-intensive operation. My database must be available 24/7, and I can't have queries timing out, or I start losing business real fast. I was thinking of firing off the archive script and setting the process priority on it to something lower than normal, so it won't interfere with my OLTP processes.
Maybe someone can suggest a better solution?
Rather than trying to change the process priority, you might want to write a T-SQL cursor that selects a subset of the data that you want to archive, then loops through the cursor, possible with a one second delay each time, to move or delete the data. This will allow you to better control the impact on your main system.|||Glenn, good point. I was actually thinking of doing both, if that was possible. I won't be able to run a copy-and-delete operation on something like 40 million rows in one shot anyhow - I'll run out of RAM. SO a cursow was going to happen regardless. But it chews up CPU. The RAM I can release by using explicit transactions for each cursor iteration, but I don't see what I can do about the CPU. The delay would help, but I have constant "write" access to the very tables that need to be archived, and I am afraid that I'll still have service interruptions unless I can lower the process priority.
No comments:
Post a Comment