December 3, 2014 at 2:30 pm
We have two IaaS VMs in Azure and a CREATE DATABASE statement takes 3 seconds in one and 72 seconds or even longer, on the other. (SQL 2014)
Both same VM configs, only differences is SQL Standard vs Enterprise.
However, to my knowledge, that should not have any impact on CREATE DATABASE nor on instant file init.
Instant File Initialization is enabled for both and I verified by using trace flag and checking SQL log to see that log file was zero'd but database was not:
Message
Zeroing completed on F:\SQLData\DummyTestDB_log.ldf --Log YES, database file, not mentioned and therefor not.
Here is database create statement:
CREATE DATABASE [test]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'test', FILENAME = N'F:\SQLDATA\test.mdf' , SIZE = 262144KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'test_log', FILENAME = N'H:\SQLDATA\test_log.ldf' , SIZE = 128000KB , FILEGROWTH = 10%)
[/
So at this point I am at a loss.
Other than disk I/O issues.
Anything else on the SQL server config side that I should be looking at?
Thanks!
December 3, 2014 at 2:51 pm
It's so hard to know this. It's entirely possible at DB create time you had a noisy neighbor. If you create another db on the slow VM, is it still 70+s?
December 3, 2014 at 3:02 pm
Apologies, I should have provided more context.
This issue has been repeated nearly 50 times, and I've been able to repeat it on demand over the course of about 21 days.
The duration for each server is also very similar, within a few seconds for each attempt. Always 3 to 4 seconds, and always 72 to 80 seconds. 72 to 300 seconds or even 140 might imply some sort of random I/O flux... but I'm at a loss.
That tells me that it isn't an issue of "jitter/throttling/random fluctuations".*
Also turned on throttling monitor in Azure, didn't see any instances of that happening.
I would completely agree with you, and had the same thought so I repeated the CREATE DATABASE several times in a row that first day, every time was the same.
Now, I suppose I could have a PERPETUALLY noisy neighbor on one of the regions and that is what is happening.
I ended up running crystal disk mark (I wanted GUI and stupid simple, fast) 🙂
I/O was nearly identical on both machines, except on the faster database create machine, the C:\ read was much faster than on the other.
That is very odd, BUT..I wouldn't expect that to really make much difference?
Don't think SQL would be doing much related to reading the C:\ as it has 56 GB or RAM and even with a pagefile, it isn't using all that RAM for anything.
Memory usage is below 20% on both machines.
December 3, 2014 at 4:05 pm
Well that is strange. I haven't built a lot of databases on Azure IaaS, but never seen that, especially not that consistently.
Have you checked IFI?
December 3, 2014 at 6:26 pm
bah! browser crashed and ate my reply...
Anyway:
Sounds like a job for... the DBA-Team 🙂
Yep, verified instant file init was working, trace flag and reviewed logs to confirm.
The drive I/O from the benchmark was basically the same on both machines except the c:\ was faster on the one that gets it done in 3 seconds, but that doesn't really track as I don't know what C:\ activity would have to do with it...page file? but again RAM usage less than 20%.
No A/V software, etc..
When that 72 seconds ticks off, it just says I/O wait in activity monitor for the create database statement...
not sure what else to even look at?
Maybe I should watch disk I/O in resource monitor and see exactly what is using I/O but the max I/O during that time is only 7 MB / sec...so not REALLY that much going on there either...
December 4, 2014 at 4:02 am
That's a weird one. And it's Enterprise that's causing the problem?
I haven't seen it, but I'll go spin up a VM and see what happens.
"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
December 4, 2014 at 4:46 am
OK. I'm back.
I spun up an Enterprise server taking all the defaults and then created a database. It only took about 3 seconds (if that). Maybe this is on Standard? Not that it should take less time on Standard, but I'm trying to replicate the results.
"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
December 4, 2014 at 5:05 am
Standard is the 72 second one, enterprise is 3 seconds.
Though, shouldn't make a difference.
I have a ticket with Microsoft support but they haven't found and answer yet either.
Just making a new VM and reinstalling everything was suggested but I would really rather find out the actual cause.
It seems like it is an "Azure host" issue not and SQL server setup or config issue.
If we figure it out I will post back. It is most fascinating 🙂
December 4, 2014 at 7:03 am
To double check a few more things:
On the "fast" server - I created the database and log on each of the different drives (instead of just log on the drive for logs and database mdf on the drive I had for data files).
Same results, about 4 seconds to create on each of the attached drives, including C:
On the "slow" performing server (again, these are identical VMs)-
Same, regardless of which drive I create the database on, all 72 to 100 seconds EXCEPT:
D:
Which in Azure is an SSD temp drive if I recall properly.
That was 38 seconds. Still... not a proper 4 seconds like the other server.
----------
On the "slow" standard server, when creating the database and watching activity monitor:
It just bounces between (for the CREATE DATABASE command):
IO_COMPLETION and WRITE_COMPLETION
which, indicates it is well... waiting on the storage system, correct?
December 4, 2014 at 7:18 am
Weird.
I tried to replicate it. Standard worked just as fast as Enterprise on the VM I created.
Have you tried capturing wait stats before and after running the create database command?
"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
December 4, 2014 at 7:47 am
Wait stats is good. It's got to be I/O, unless there's some contention in work (CPU) on the slow server. However I can't guess what that would be.
Is all I/O slow on the slow server? If you load AdventureWorks to both servers, do queries perform similarly, or is the slow one slow?
I'm almost leaning towards a broken machine. I might scrap the slow server and rebuild it as a another VM elsewhere. I doubt you'll get much support from MS Azure on this.
December 4, 2014 at 7:55 am
No I have not.
Probably wouldn't be a bad idea by any means.
Any suggested ones to look at for this?
December 4, 2014 at 7:57 am
Not even a good guess. Now, frankly, I'm curious. I'm with Steve. Set this one fire and move on... after you capture the wait stats.
"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
December 4, 2014 at 8:29 am
Yeah, I'll look at loading up adventure works and running a few queries.
Issue is going to be though, that once it caches adventure works I would expect normal performance, only the I/O issues being the bottleneck.
I would normally burn and rebuild, but with this being in Azure I'm concerned about root cause because I don't want to standup another VM in a month and find this same problem the "hard way".
Maybe I'm being too much of a stickler?
If this was in-house on VMWare I'd ask my infrastructure guys(and gals) to migrate the VM around to a new host or play with a few of those options first to start eliminating things.
December 4, 2014 at 9:24 am
Hard to know. I suspect you're too worried about this particular VM. It's likely (I think) and Azure thing, but perhaps you did something? Do you have completely scripted setup for the VM?
I'd try ADW, clearing cache and running a few things. That gives you a bit of an IO benchmark. I'd be curious if wait stats change or if you see slower performance elsewhere (maybe unexplained delays in CPU processing that don't show up.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply