March 12, 2014 at 11:36 am
Hi I really need help with something strange that's happening when I am creating a test database.
Just some background. I have set up a Virtual Machine on azure and assigned it 8 data disks. I created a new database on one of the drives. I then created a table with only one column in it called column1 that is NVARCHAR(1000). I filled this table up with a couple of hundred thousand records to get some data to run selects against.
When I run the following query: select column1 from table, the query runs in 4 seconds.
This is where the confusing part comes. I run a backup to disk of the database and then backup the log file. I do the EXACT same select statement and it take twice as long to run, even longer sometimes. This happens also if I do not perform a log backup as well.
Just FYI disk caching is turned off for all these drives.
I can rinse and repeat this all day long and get the same results I've tried it over and over again. What could be causing the excessive query times after a backup is taken?
Also I am creating the exact same set up in another new database, however this has its primary file group spread out over all 8 drives. The exact same select statement as above is taking longer on the multiple file database than it is on the single file database?
If I could get some help that would be great !
March 12, 2014 at 1:15 pm
If after your backup you run the select query TWICE or 3 or 4 times, does it return back to the original 4 second run time? If so, then it is simply the time it is taking to get the data back up into RAM after some/all of it got flushed out during the backup.
If it doesn't, we will need to dig deeper.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 12, 2014 at 4:26 pm
Hi Sorry, one crucial piece of information that I left out was the fact that I am running DBCC DROPCLEANBUFFERS before I run the query every time. This will mean that the results are pulled from Disk.
I was speaking to somebody I know about this earlier this evening and he came up with the suggestion that it could be that the statistics are created or changed for the table after the backup is performed. Does this sound likely?
March 12, 2014 at 4:46 pm
No, statistics aren't modified by backups.
I've been using SQL Server on Azure in VMs a ton. I haven't seen this problem crop up before, so I'm seriously stumped on what to do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2014 at 4:48 pm
Well, thinking about it some more, you can turn on auditing on the blob stores where the VM files are located. Set that to verbose and see what the heck is happening. That's where I'd start.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2014 at 4:52 pm
At what point does the query start returning to the normal 4s run speed?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2014 at 5:06 pm
I'm not entirely sure this is an Azure problem because I ran through the same steps on my local machine with similar results.
March 12, 2014 at 5:09 pm
It doesn't. It stays at the new speed no matter how many times I run it after the backup
March 12, 2014 at 5:11 pm
Can you provide the entire statement being run with the backup?
A backup by itself should not cause what you are explaining. The stats don't get reset when the backup is run. And you are clearing the cleanbuffers manually each run of the script.
But even on your local machine, at which point is it that your script start to run at the expected time again? Does it ever? Or does it only start running faster again when you rebuild the test case?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2014 at 5:13 pm
william.rees.howells (3/12/2014)
It doesn't. It stays at the new speed no matter how many times I run it after the backup
Intriguing.
You must have been responding while I was typing out my response.
Can you post a script to build out this test case that reproduces the problem so others can try to find the cause? This would mean scripting out the query being run, the test data, the database and the backup scripts that are being run (and any other scripts being run in conjunction with your backup).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2014 at 5:19 pm
william.rees.howells (3/12/2014)
Hi Sorry, one crucial piece of information that I left out was the fact that I am running DBCC DROPCLEANBUFFERS before I run the query every time. This will mean that the results are pulled from Disk.I was speaking to somebody I know about this earlier this evening and he came up with the suggestion that it could be that the statistics are created or changed for the table after the backup is performed. Does this sound likely?
Let me get this straight, prior to running your query you also run DBCC DROPCLEANBUFFERS, correct?
March 12, 2014 at 5:21 pm
March 12, 2014 at 5:48 pm
OK no problem. I will grab the script off my machine at work tomorrow and post it up for all to see.
March 12, 2014 at 5:50 pm
That is correct. I run it every time I run the query. The query speeds only go back to normal once I drop and recreate the database, table and days.
March 12, 2014 at 5:56 pm
william.rees.howells (3/12/2014)
That is correct. I run it every time I run the query. The query speeds only go back to normal once I drop and recreate the database, table and days.
That means you are clearing the buffer cache before running the query. This means the data has to be read from disk every time the query is run.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply