High CPU usage % on master database

Question

High CPU usage % on master database

Viewing 4 posts - 16 through 18 (of 18 total)

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

x SSC-Insane Points: 23660 More actions · Answer 1

Jeff Moden - Thursday, April 12, 2018 7:17 AM
PiManÃ© - Thursday, April 12, 2018 6:00 AM
Grant Fritchey - Thursday, April 12, 2018 5:49 AM
I'm trying to help, I just don't have data.
I know and sorry for not having much more data.
I was told they are going to install a SP tonight and restart the server and by next week the users will start using the application on a regular basis.
This will "clean" the data added by the database attachments and our script
EXEC sp_MSforeachdb 'USE [master]; IF ''?'' <> ''master'' AND ''?'' <> ''tempdb'' EXEC ('' ALTER DATABASE [?] SET PARAMETERIZATION SIMPLE WITH NO_WAIT; ALTER DATABASE [?] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT; ALTER DATABASE [?] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT; ALTER DATABASE [?] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT; ALTER DATABASE [?] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT; ALTER DATABASE [?] SET AUTO_SHRINK OFF WITH NO_WAIT; ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT; ALTER DATABASE [?] SET COMPATIBILITY_LEVEL = 130; ALTER DATABASE [?] SET RECOVERY SIMPLE WITH NO_WAIT; '');'
If this keeps happening I'll post more info here.
Thanks,
Pedro
Recovery model being set to SIMPLE for the master database would indicate a problem. Is there no RPO requirement for backups/restores for the master database???? In other words, you're happy with possibly losing all data since the last full backup?
What does your backup/restore plan actually look like?

Also with closer inspection of the script I'm thinking it doesn't even do an alter on master or tempdb.

PiMané SSChampion Points: 13965 More actions · Answer 2

It doesn't... But the master stores all info on all databases properties so any alter on the properties will make a change to the master

If you need to work better, try working less...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

patrickmcginnis59 10839 - Thursday, April 12, 2018 11:39 AM
Also with closer inspection of the script I'm thinking it doesn't even do an alter on master or tempdb.

It doesn't, but both are always in simple recovery as it is (master behaves that way no matter what, and an attempt to set recovery model on tempDB to anything other than simple will fail)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 4

GilaMonster - Thursday, April 12, 2018 11:33 AM
Jeff Moden - Thursday, April 12, 2018 7:17 AM
Recovery model being set to SIMPLE for the master database would indicate a problem.
NO!!!!!!
Master is always in simple recovery. Even if you set it to full or bulk logged, it's still in simple. Because you cannot restore master WITH NORECOVERY (SQL restarts immediately after a restore of master completes), and so there's no way to apply log backups even if you had them (which you won't, because they can't be taken)
Master being in simple recovery model indicates as much as finding that 1 = 1.

I've apparently lost it. I even went back to my backup scripts to check. Thanks for the correction, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)