February 16, 2014 at 7:51 am
Hi! I inherited a SQL Serve 2008 R2 sp2 database maintenance (db reindex,checkdb etc) where the first step is to set the database to single user mode. I know, I know... I asked all the questions you are probably thinking about now... why would they set it to single user mode etc..
My question is.. is there any performance hindering considerations when setting a user db to single user mode? They have 2 numa nodes on 2 cpus, 8 cores each. I wonder if parallelism will take a hit while running some resource intensive maintenance tasks.
Anything? Anyone?
Thanks!!
February 16, 2014 at 9:29 am
No. You shouldn't hit any performance hits while doing it, but, the bigger concern for me is that between setting it to single user mode and continuing on with the next step, whatever it might be, you may see connections get in ahead of you. I've seen that. In general, instead of single user mode, I'd prefer restricted user mode. Although, that assumes that every login isn't already an 'sa' which seems to be the case over and over again.
"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
February 16, 2014 at 11:51 am
Thanks so much for the reply!
Is there a way to restrict a specific login for a specific amount of time? You were right about the loose number of users with sa privilege.
Paula
February 17, 2014 at 3:51 am
You can disable logins. As far as a certain amount of time, I don't think there's a disable for a time period, but you could set up two jobs, one that disables and one that enables and have them trigger each other or schedule them.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply