April 11, 2018 at 3:58 am
Hi,
I have a server with over 1.000 databases and when I checked the most consuming CPU database I got a surprise...
master has over 56% of database usage... Is it because of the high number of databases?
Thanks,
Pedro
April 11, 2018 at 4:57 am
Impossible to know without more information on what queries are causing the CPU usage and how you're measuring it.
"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
April 11, 2018 at 5:05 am
Grant Fritchey - Wednesday, April 11, 2018 4:57 AMImpossible to know without more information on what queries are causing the CPU usage and how you're measuring it.
Probably the problem is that the instance hasn't been used a lot since it restarted (the server has 3 other instances and they are mostly working on the other 2).
Any use of SSMS by expanding the databases makes the % increase and the query is:SELECT dtb.is_ansi_null_default_on AS [AnsiNullDefault], dtb.is_ansi_nulls_on AS [AnsiNullsEnabled], dtb.is_ansi_padding_on AS [AnsiPaddingEnabled], dtb.is_ansi_warnings_on AS [AnsiWarningsEnabled], dtb.is_arithabort_on AS [ArithmeticAbortEnabled], dtb.is_auto_close_on AS [AutoClose], dtb.is_auto_shrink_on AS [AutoShrink], dtb.is_cursor_close_on_commit_on AS [CloseCursorsOnCommitEnabled], dtb.is_concat_null_yields_null_on AS [ConcatenateNullYieldsNull], dtb.is_numeric_roundabort_on AS [NumericRoundAbortEnabled], dtb.is_quoted_identifier_on AS [QuotedIdentifiersEnabled], dtb.is_read_only AS [ReadOnly], dtb.is_recursive_triggers_on AS [RecursiveTriggersEnabled], dtb.is_local_cursor_default AS [LocalCursorsDefault], dtb.page_verify_option AS [PageVerify], dtb.recovery_model AS [RecoveryModel], dtb.user_access AS [UserAccess], dtb.snapshot_isolation_state AS [SnapshotIsolationState], dtb.is_read_committed_snapshot_on AS [IsReadCommittedSnapshotOn], dtb.is_db_chaining_on AS [DatabaseOwnershipChaining], dtb.is_auto_update_stats_async_on AS [AutoUpdateStatisticsAsync], dtb.is_date_correlation_on AS [DateCorrelationOptimization], dtb.is_broker_enabled AS [BrokerEnabled], dtb.is_trustworthy_on AS [Trustworthy], dtb.name AS [Name], dtb.database_id AS [ID], dtb.create_date AS [CreateDate], dtb.is_fulltext_enabled AS [IsFullTextEnabled], CAST(isnull(dtb.source_database_id, 0) AS bit) AS [IsDatabaseSnapshot], ISNULL(DB_NAME(dtb.source_database_id), N'') AS [DatabaseSnapshotBaseName], dtb.is_auto_create_stats_on AS [AutoCreateStatisticsEnabled], dtb.is_auto_update_stats_on AS [AutoUpdateStatisticsEnabled], dtb.is_parameterization_forced AS [IsParameterizationForced], (dtb.is_published*1+dtb.is_subscribed*2+dtb.is_merge_published*4) AS [ReplicationOptions], dtb.service_broker_guid AS [ServiceBrokerGuid], ISNULL((case dmi.mirroring_redo_queue_type when N'UNLIMITED' then 0 else dmi.mirroring_redo_queue end),0) AS [MirroringRedoQueueMaxSize], ISNULL(dmi.mirroring_connection_timeout,0) AS [MirroringTimeout], suser_sname(dtb.owner_sid) AS [Owner], ISNULL(dmi.mirroring_partner_name,'') AS [MirroringPartner], ISNULL(dmi.mirroring_partner_instance,'') AS [MirroringPartnerInstance], ISNULL(dmi.mirroring_role,0) AS [MirroringRole], ISNULL(dmi.mirroring_safety_level + 1, 0) AS [MirroringSafetyLevel], ISNULL(dmi.mirroring_state + 1, 0) AS [MirroringStatus], ISNULL(dmi.mirroring_witness_name,'') AS [MirroringWitness], ISNULL(dmi.mirroring_witness_state + 1, 0) AS [MirroringWitnessStatus], CAST(case when dmi.mirroring_partner_name is null then 0 else 1 end AS bit) AS [IsMirroringEnabled], ISNULL(dmi.mirroring_guid,'00000000-0000-0000-0000-0000000000000000') AS [MirroringID], ISNULL(dmi.mirroring_role_sequence,0) AS [MirroringRoleSequence], ISNULL(dmi.mirroring_safety_sequence,0) AS [MirroringSafetySequence], ISNULL(dmi.mirroring_failover_lsn,0) AS [MirroringFailoverLogSequenceNumber], case when dtb.collation_name is null then 0x200 else 0 end | case when 1 = dtb.is_in_standby then 0x40 else 0 end | case dtb.state when 1 then 0x2 when 2 then 0x8 when 3 then 0x4 when 4 then 0x10 when 5 then 0x100 when 6 then 0x20 else 1 end AS [Status], drs.recovery_fork_guid AS [RecoveryForkGuid], drs.database_guid AS [DatabaseGuid], CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit) AS [IsSystemObject], ISNULL(dtb.log_reuse_wait,0) AS [LogReuseWaitStatus], CAST(case when ctb.database_id is null then 0 else 1 end AS bit) AS [ChangeTrackingEnabled], CAST(ISNULL(ctb.is_auto_cleanup_on,0) AS bit) AS [ChangeTrackingAutoCleanUp], ISNULL(ctb.retention_period,0) AS [ChangeTrackingRetentionPeriod], CAST(ISNULL(ctb.retention_period_units,0) AS tinyint) AS [ChangeTrackingRetentionPeriodUnits], CAST(( case LOWER(convert( nvarchar(128), DATABASEPROPERTYEX(dtb.name, 'Updateability'))) when 'read_write' then 1 else 0 end) AS bit) AS [IsUpdateable], CAST(dtb.is_encrypted AS bit) AS [EncryptionEnabled], CAST(dtb.is_honor_broker_priority_on AS bit) AS [HonorBrokerPriority], CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible] FROM master.sys.databases AS dtb LEFT OUTER JOIN sys.database_mirroring AS dmi ON dmi.database_id = dtb.database_id LEFT OUTER JOIN sys.database_recovery_status AS drs ON drs.database_id = dtb.database_id LEFT OUTER JOIN sys.change_tracking_databases AS ctb ON ctb.database_id = dtb.database_id WHERE (dtb.name=@_msparam_0)
Seems like SQL is getting the information for all databases.. probably SSMS..
Thanks
April 11, 2018 at 7:12 pm
PiMané - Wednesday, April 11, 2018 3:58 AMmaster has over 56% of database usage...
Hi,
Which method have you used to get this information?
Have you run a profiler trace/ XE to profile current database activity?
April 12, 2018 at 12:09 am
Evgeny Garaev - Wednesday, April 11, 2018 7:12 PMPiMané - Wednesday, April 11, 2018 3:58 AMmaster has over 56% of database usage...Hi,
Which method have you used to get this information?
Have you run a profiler trace/ XE to profile current database activity?
No. DMVs to get the most CPU queries.
April 12, 2018 at 5:07 am
PiMané - Thursday, April 12, 2018 12:09 AMEvgeny Garaev - Wednesday, April 11, 2018 7:12 PMPiMané - Wednesday, April 11, 2018 3:58 AMmaster has over 56% of database usage...Hi,
Which method have you used to get this information?
Have you run a profiler trace/ XE to profile current database activity?
No. DMVs to get the most CPU queries.
So that's the most for the queries currently in cache? Does it stay consistent? How many SSMS instances are running against this server retrieving database lists frequently? I'm still very unclear as to the issue because the parameters around it haven't been defined.
"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
April 12, 2018 at 5:21 am
Grant Fritchey - Thursday, April 12, 2018 5:07 AMPiMané - Thursday, April 12, 2018 12:09 AMEvgeny Garaev - Wednesday, April 11, 2018 7:12 PMPiMané - Wednesday, April 11, 2018 3:58 AMmaster has over 56% of database usage...Hi,
Which method have you used to get this information?
Have you run a profiler trace/ XE to profile current database activity?
No. DMVs to get the most CPU queries.
So that's the most for the queries currently in cache? Does it stay consistent? How many SSMS instances are running against this server retrieving database lists frequently? I'm still very unclear as to the issue because the parameters around it haven't been defined.
We attached all the 1000 databases with a powershell script and then ran a sql script to change all databases properties (compatibility mode, page checksum, auto shrink off, ....).
After that only a couple of users used the application that connects to the databases, they logged in once and exited the application.
Only a SSMS instance is opened but it's been opened for almost a month.
April 12, 2018 at 5:49 am
PiMané - Thursday, April 12, 2018 5:21 AMGrant Fritchey - Thursday, April 12, 2018 5:07 AMPiMané - Thursday, April 12, 2018 12:09 AMEvgeny Garaev - Wednesday, April 11, 2018 7:12 PMPiMané - Wednesday, April 11, 2018 3:58 AMmaster has over 56% of database usage...Hi,
Which method have you used to get this information?
Have you run a profiler trace/ XE to profile current database activity?
No. DMVs to get the most CPU queries.
So that's the most for the queries currently in cache? Does it stay consistent? How many SSMS instances are running against this server retrieving database lists frequently? I'm still very unclear as to the issue because the parameters around it haven't been defined.
We attached all the 1000 databases with a powershell script and then ran a sql script to change all databases properties (compatibility mode, page checksum, auto shrink off, ....).
After that only a couple of users used the application that connects to the databases, they logged in once and exited the application.
Only a SSMS instance is opened but it's been opened for almost a month.
So, your query does what, it adds up all the CPU usage recorded in the cache and then groups the queries by percentages of CPU usage? Guessing. So, someone has SSMS up and somehow it's banging away at the server. Kill that process I guess. But, are you also experiencing waits on CPU? Is it anything other than the query against the cache? How volatile is your cache? How many queries might be hitting the system that aren't in cache either due to aging out or because they're ad hoc and have RECOMPILE hints or because you have 'Optimize for ad hoc' enabled? What's the last execution time for this query? Is it still actively banging away at the server or was it a one time thing, it's still in cache, but not actually causing an active problem.
What I'm getting at is, you've given three pieces of information. A query is 56% of your CPU usage. You have 1000 databases. The query came from SSMS. From that, you want to know if having a lot of databases is... causing problems? Are there problems? I can't tell from the three pieces of information you gave me. I still don't understand where we're going. Are you experiencing issues beyond the aggregation query (assuming I'm right) that you ran against the cache?
I'm trying to help, I just don't have data.
"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
April 12, 2018 at 6:00 am
Grant Fritchey - Thursday, April 12, 2018 5:49 AMI'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
April 12, 2018 at 6:44 am
OK. Not sure what that script has to do with things.
You're saying that on a server with zero actual load, a query constitutes 56% of the perceived CPU usage, but we don't actually know if the CPU is 2% in use or 100% in use. Based on the fact that there is no load, it's probably 2% (or less). So, our query is using 1% of the CPU. Is that a concern?
At this point, no. Not at all. Let's see what things look like under load and let's use more than one metric to arrive at whether or not we have an issue.
"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
April 12, 2018 at 7:17 am
PiMané - Thursday, April 12, 2018 6:00 AMGrant Fritchey - Thursday, April 12, 2018 5:49 AMI'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?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2018 at 7:32 am
Jeff Moden - Thursday, April 12, 2018 7:17 AMRecovery 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?
Doesn't the master database normally use the simple recovery model?
<clickyclicky>
Ah yes. Technet says that "BACKUP LOG" is not supported on the master database, so even if master is set to use FULL or BULK LOGGED, it still only behaves as if it's using the SIMPLE recovery model...
Thomas Rushton
blog: https://thelonedba.wordpress.com
April 12, 2018 at 7:39 am
PiMané - Wednesday, April 11, 2018 3:58 AMHi,I have a server with over 1.000 databases and when I checked the most consuming CPU database I got a surprise...
master has over 56% of database usage... Is it because of the high number of databases?Thanks,
Pedro
We will need ALL the details for us to help you! Can you please elaborate further?
😎
Suggest you run Glenn Berry's diagnostic queries and post the results here.
April 12, 2018 at 11:27 am
PiMané - Thursday, April 12, 2018 12:09 AMEvgeny Garaev - Wednesday, April 11, 2018 7:12 PMPiMané - Wednesday, April 11, 2018 3:58 AMmaster has over 56% of database usage...Hi,
Which method have you used to get this information?
Have you run a profiler trace/ XE to profile current database activity?
No. DMVs to get the most CPU queries.
If you're using sys.dm_exec_requests, then master WILL have the highest CPU, as the system processes typically register as using master, and they're connected from the time SQL starts, and that CPU is cumulative.
In other words, looking at exec requests and just taking the highest CPU is pretty useless.
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
April 12, 2018 at 11:33 am
Jeff Moden - Thursday, April 12, 2018 7:17 AMRecovery 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.
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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply