Thinking of using sql server partition switching to facilitate reloading tables during operational hours. If a query is running when a partition switch is performed, what happens regarding data integrity? Does the query continue against the partition it started with, or does it switch to the new partition?
Edit: Looks like it waits for the query to finish before doing the switch.
craiggsmith (BOB member since 2009-06-01)
Anyone out there do this kind of thing in sql server? Another way of doing essentially the same thing is by using two tables and a synonym.
I know a lot of people who do it in Oracle, which seems a little more robust. I understand why they do this, but if it has to wait for the query to complete it might never do it; there could be queries running all day.
craiggsmith (BOB member since 2009-06-01)
In Oracle you can perform the partition exchange and if there is an outstanding query when that happens then Oracle continues to allow the query to use the data that was exchanged. The query does NOT start using the new data unless the query was executed AFTER the exchange was completed.
As you said, Oracle is quite sophisticated in this area.
If your process has to clean up after itself then you have a challenge. In Oracle we would normally truncate the exchange table after the partition exchange is complete. But if some process is still using the data in the exchange table then the truncate fails. So I wrap the truncate in a PL/SQL block. Within the block it attempts the truncate and catches the exception if it fails. In the case of failure the block sleeps for 10 seconds and then tries again. It gets 10 attempts and if still fails then it raises an exception back to Data Services. I don’t let the exception fail the job, but a warning is printed to the log.
eganjp 
(BOB member since 2007-09-12)
Thanks, that’s how I thought Oracle handled it. Sounds like you have a good method. Yes, we would have to truncate the table/partition for the next load, and in sql server that operation would also wait until any queries are complete (rather than fail).
But since it doesn’t even let you do the exchange while a query is running that doesn’t come into play. What I noticed is that it appears subsequent queries queue up behind the exchange command, so that the exchange command does execute immediately after the blocking query is done. That part is good, but the fact that all other queries must wait isn’t so good.
I guess there’s always a catch. But regardless of the method or issue, it appears I need to work on speeding up queries.
craiggsmith (BOB member since 2009-06-01)
In my design with partition exchange there was some consideration given to not even trying to truncate the exchange table after the exchange was complete. But we were dealing with a table that had rows numbering in the billions. Disk space was not a small issue. So we had to try to free up the space.
Have you tried the exchange operation with the NO_WAIT option?
eganjp (BOB member since 2007-09-12)
That’s a lot of data! We are not dealing with a lot of data, so I was hoping it would behave like Oracle and I was exploring the option of using multiple tables to allow for longer query times.
I asked our DBA’s about the option to force it to execute immediately and they said it wasn’t an option, but none of them have ever actually done this. So I will experiment with it. I’m worried about data consistency though.
craiggsmith (BOB member since 2009-06-01)
That’s a bummer than you can’t get the exchange to happen while queries are active. Can you make the queries use a different isolation level?
When you’re ready to switch to Oracle give me a call. 
eganjp (BOB member since 2007-09-12)
Sure enough it doesn’t appear that no_wait is an option for this… We do need to look at the query side of things though, thanks for the reminder; we have had other blocking issues that don’t make any sense. I’ve bugged the DBA’s before but never got anywhere. Unfortunately I don’t have much access to see what’s going on in the database so it’s been difficult to debug.
I actually come from an Oracle background. Never did partitioning though, but we did a similar thing using two tables and synonyms. There were some frustrating things with Oracle and there are some things about sql server that are nice, but for the most part it’s reversed; there are so many things I miss. But I don’t want to start a database war here … I just wish they could all learn from each other.
craiggsmith (BOB member since 2009-06-01)
You won’t get a database war out of me. I use them all. 
eganjp (BOB member since 2007-09-12)
Glad there’s no prejudice here.
Tried snapshot isolation level but it still didn’t let me do the switch. Rats. I’ll keep testing but I’m not overly optimistic.
craiggsmith (BOB member since 2009-06-01)