CREATE DATABASE 3 seconds for one server, 72 for the other???

Question

CREATE DATABASE 3 seconds for one server, 72 for the other???

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Conor.Lillis SSC Veteran Points: 222 More actions · Answer 1

I wonder are the SQL Services on the different servers running under accounts with different privileges?

If the [SQL Server] Service account has the privilege "Perform Volume Maintenance Tasks" it will create the MDF (and ndf) files v quickly. This doesn't apply to the log file(s) though as they have to be zeroed out.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 2

Oh, that's good, Conor. That would be interesting to know.

Maxer SSCrazy Eights Points: 8964 More actions · Answer 3

Conor Lillis (12/5/2014)
I wonder are the SQL Services on the different servers running under accounts with different privileges?
If the [SQL Server] Service account has the privilege "Perform Volume Maintenance Tasks" it will create the MDF (and ndf) files v quickly. This doesn't apply to the log file(s) though as they have to be zeroed out.

Each server has the same SQL service account with same permissions.

I also verified instant file initialization was working on both (so it has Perform Volume Maint Tasks and I verified in the database log that it was zeroing the log ONLY but that the database files were instant init).

Is there a good TSQL command I could run to dump EVERY SQL config to a grid/result set?

I could do a text compare between the two servers and verify that they match.

I was going to use:

SELECT *

FROM sys.configurations

ORDER BY name ;

GO

unless there is something better/more complete to use?

When I compared the results of that:

Database mail isn't enabled on the faster server --don't care, shouldn't matter.

faster (enterprise edition)

Has:

common criteria compliance --not enabled

EKM provider enabled --not enabled

I assume that is because it is Enterprise edition while standard doesn't offer those features.

So on the SQL server level... identical configs...

Maxer SSCrazy Eights Points: 8964 More actions · Answer 4

Just for fun, I DISABLED Instant File Init on both servers and ran the same create statements:

fast server - 5 seconds, 7 seconds one time

slow server- 80 seconds (so basically same, maybe a tiny tiny bit slower by 3 or 5 seconds), tends to be 72 or 75 seconds WITH Instant File Init.

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 5

something is seriously baked on that server.

It's pretty abnormal. I've been working with VMs in Azure for years. And, because I'm doing lots and lots of testing and development, not much production stuff, I create them and drop them willy nilly. I have never run into this situation before, or anything like it. I'd push on Microsoft to get them to check it out. You might find the cause, or they might say to drop & recreate it. But it really does sound odd.

"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

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 6

Maxer (12/5/2014)
Just for fun, I DISABLED Instant File Init on both servers and ran the same create statements:
fast server - 5 seconds, 7 seconds one time
slow server- 80 seconds (so basically same, maybe a tiny tiny bit slower by 3 or 5 seconds), tends to be 72 or 75 seconds WITH Instant File Init.

Grrrr, if you need this, build a new one and test.

At this point, I'd use this one to experiment and try to determine why it's slow, but I wouldn't waste time on it. My guess is that the underlying Azure host is broken somehow. Maybe a bad cable, incorrect setting on a switch port or something else.

As Grant said, burn it. Unless you have time and curiosity.

SQLPowerDoc does a great job of grabbing setting and getting them into Excel, but not sure it's easy to compare things then.

https://sqlpowerdoc.codeplex.com/

Maxer SSCrazy Eights Points: 8964 More actions · Answer 7

Yeah, sadly I think I am going to have to drop and re-create.

I'm going to try just migrating data disks to new VM first and see if it just "goes away".

That being said, I'm still pushing them to give me a root cause analysis because...it's just really odd.

(Granted I likely will never again encounter this issue, but if this were our in-house environment we would track it down to find and fix the root cause)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

Have you done any IO performance testing on the two servers with SQLIO or any other IO performance testing utility?

I would start with that.