I'm being challenged since we have customers that are seeing different types of errors while running what is supposed to be online maintenance. errors like timeouts, scan stopped due to data movement and more.
Some of our customers purchased SQL server enterprise edition to be able to get the rebuild index online feature.
To replicate the issues, I've been testing on a big database the dbcc checkdb, reorganize and rebuild of indexes and the update of statistics while I bombard the server with transactions on a database that is almost 1TB.
My first test was with the checkdb with maxdop=1 while the sqlServer was processing 124000 small transactions... I received a timeout from my application which has a the timeout set to 5 minutes. Research on the way the check db works , is that is creates a snapshot, uses tempdb intensively and nolocks are created to create the snapshot... So how can one of my small transactions be blocked if it doesn't lock tables?
My second test was to reorganize all indexes (which is also supposed to be online) while processing 124000 transactions, This time I got a deadlock...
My third test was to update all statistics with maxdop=1 while running 124000 transactions. The error that was received in this case is: Could not continue scan with no lock due to data movement
my forth test to rebuild all indexes online while running 124000 transactions will be running soon and I will update my post with the results.
The maxdop=1 that I used where it can be used was to eliminate contention of resources.
I've read multiple articles inclusing Paul Randal's explanation of online vs offline of the rebuild of indexes and understand the difference... but as I said, I'm being challenged to explain why it isn't really online although Microsoft claims it's online.
Any input would be much appreciated.
What am I missing