Creating a database is slow based on role held, what am I missing?

Question

Post reply

Creating a database is slow based on role held, what am I missing?

ChubbyBuddy

SSC-Addicted

Points: 424
More actions
November 18, 2016 at 5:47 pm

#337400

I've got two new installs of SQL 2016 Standard, with SP1 installed as of 2 days ago. I have had this issue since I've created added my login (windows group) with the 'dbcreator' role. Users can login without an issue however, when they go to create a db within SSMS they get the 'donut of death' for anywhere from 30-60 seconds before they are prompted for the table information. When I or my other SQL Admin create tables (sysadmin role) within SSMS we're prompted immediately for the table information.
I'm not a full time DBA so I'm grasping at straws at this point. Would this be associated with SSPI/SPN's? I'm at a loss...
Thank you for your time!
V/R,
Chubby

Viewing 12 posts - 1 through 11 (of 11 total)

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

Are some of them connecting locally and some remotely, or anything like that?

Lowell SSC Guru Points: 323504 More actions · Answer 2

maybe the databases they are creating have large space assignments?

i know if you have not set up instant file initialization, you can see some delays, right?

https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/12/22/how-and-why-to-enable-instant-file-initialization/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChubbyBuddy SSC-Addicted Points: 424 More actions · Answer 3

Beatrix Kiddo (11/21/2016)
Are some of them connecting locally and some remotely, or anything like that?

Beatrix,

All users will be accessing the servers via the latest build of SSMS from their desktop computers. Those of us with the 'SysAdmin' role do NOT have this slowness, and it doesn't matter what size db/logs we create because we don't get the initial prompt to build for 30-60 seconds if we're holding the 'dbcreator' role.

ChubbyBuddy SSC-Addicted Points: 424 More actions · Answer 4

Lowell (11/21/2016)
maybe the databases they are creating have large space assignments?
i know if you have not set up instant file initialization, you can see some delays, right?
https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/12/22/how-and-why-to-enable-instant-file-initialization/

Lowell,

The users aren't even being prompted for db/log file sizes at this point. They're still waiting on the New db dialog box to be presented to them.

Eric M Russell SSC Guru Points: 125522 More actions · Answer 5

When users create a new database, are they specifying a non-default file location or growth size?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

ChubbyBuddy SSC-Addicted Points: 424 More actions · Answer 6

Eric M Russell (11/21/2016)
When users create a new database, are they specifying a non-default file location or growth size?

Eric,

From the Object Explorer in SSMS 2016 the users with the 'dbcreator' role are Right Clicking on Databases, selecting New Database, and then the donut of death takes over for 30-60 seconds BEFORE they're even prompted for db properties. i.e. name, location, size, etc. When I do this same thing with my 'sysadmin' role there is no delay whatsoever.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 7

Just to eliminate the obvious, have you tried doing this on each other's machines to rule out any hardware discrepancies?

Lowell SSC Guru Points: 323504 More actions · Answer 8

i googled a bit to find reasons SSMS is slow sometimes;

i see SSMS can sometimes validate some .net packages;

https://www.virtualobjectives.com.au/sqlserver/ssms_slow.htm

this one implies a few basics: virus scanners, the default connection type it might try to connect(named pipes vs tcp/ip )

http://dba.stackexchange.com/questions/20725/sql-server-management-studio-slow-opening-new-windows

this one from SSC shows an expired third party plug in added additional overhead,and took ten seconds for anything to open:

http://www.sqlservercentral.com/Forums/Topic702461-149-1.aspx

is the SSMS version and the SQLversion exactly the same? ie are they using SSMS2014 to connect to SQL2016?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eric M Russell SSC Guru Points: 125522 More actions · Answer 9

I just started a SQL Profiler trace on my (local) instance and then did the "New Database.." dialog. There are quite a few things that SSMS does in the background, like confirm role membership, query server properties, query available drives and space allocation, etc. I your case there are non-admin users who have been granted permission to create databases by inclusion in the dbcreator role, so maybe there is some extra security delegation going on as well that doesn't apply with a DBA who has local admin membership on the Windows server.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

ChubbyBuddy SSC-Addicted Points: 424 More actions · Answer 10

Lowell (11/22/2016)
i googled a bit to find reasons SSMS is slow sometimes;
i see SSMS can sometimes validate some .net packages;
https://www.virtualobjectives.com.au/sqlserver/ssms_slow.htm
this one implies a few basics: virus scanners, the default connection type it might try to connect(named pipes vs tcp/ip )
http://dba.stackexchange.com/questions/20725/sql-server-management-studio-slow-opening-new-windows
this one from SSC shows an expired third party plug in added additional overhead,and took ten seconds for anything to open:
http://www.sqlservercentral.com/Forums/Topic702461-149-1.aspx
is the SSMS version and the SQLversion exactly the same? ie are they using SSMS2014 to connect to SQL2016?

Lowell,

They are using SSMS 2016 and they are connecting to two separate SQL 2016 servers. I would assume these events above would apply to the SysAdmin role as well as the dbCreator role as well. To me it seems to be about role held and not by the underlying infrastructure/software. I could very well be wrong but the ONLY difference is when I use my DBA account it takes me 30-60 seconds to receive the new db property window. When I use my SysAdmin account I'm prompted immediately for the db properties.

Thank you for your input and ideas!!

V/R

Bill

ChubbyBuddy SSC-Addicted Points: 424 More actions · Answer 11

Eric M Russell (11/22/2016)
I just started a SQL Profiler trace on my (local) instance and then did the "New Database.." dialog. There are quite a few things that SSMS does in the background, like confirm role membership, query server properties, query available drives and space allocation, etc. I your case there are non-admin users who have been granted permission to create databases by inclusion in the dbcreator role, so maybe there is some extra security delegation going on as well that doesn't apply with a DBA who has local admin membership on the Windows server.

Eric,

THANKS for the input! You motivated me to use SQL Profiler for the first time. I have ZERO idea what I'm looking for but I did find this by limiting my events to Audit events. This pattern goes on for the 30-60 seconds, firing every 2-3 seconds, before I'm prompted for a db name and properties: