Performance of SQL Monitor on 1500+ databases on SQL Server

[markcw]

We have a SQL server 2008 SP3 with 1500+ databases being monitored by SQL Monitor version 3.1.0.118.

Any suggestions on how to reduce the query times for some of the SQL Monitor queries or turn off some features like the database status or top queries, etc.

Turning off all database stats would be the best solution for us.

These are a couple of the statements causing massive blocking and wait time on the SQL Server.

SELECT DB_NAME() AS [DB_NAME()], CONVERT(DATETIME, [Value], 121) AS [CONVERT(DATETIME, [Value]], 121)] FROM #DBINFO WITH ( NOLOCK ) WHERE [Field] = 'dbi_dbccLastKnownGood';

WITH query_stats AS
(
SELECT sql_handle ,
plan_handle ,
statement_start_offset ,
statement_end_offset ,
MIN(creation_time) AS creation_time ,
MAX(last_execution_time) AS last_execution_time ,
SUM(execution_count) AS execution_count ,
SUM(total_worker_time_ms) AS total_worker_time_ms ,
SUM(total_logical_writes) AS total_logical_writes ,
SUM(total_logical_reads) AS total_logical_reads ,
SUM(total_physical_reads) AS total_physical_reads ,
SUM(total_elapsed_time_ms) AS total_elapsed_time_ms
FROM #query_plan_stats
GROUP BY sql_handle ,
plan_handle ,
statement_start_offset ,
statement_end_offset
)
..........................(much more)

[peter.peart]

Thanks for your post. There's a config file called RedGate.Response.Engine.Alerting.Base.Service.exe.settings in C:\ProgramData\Red Gate\SQL Monitor 3 on the machine hosting the base monitor service.

You can make modifications to this file to alter the frequency of polling for alerts, as well as disabling counters. There's instructions contained in the file, however we strongly recommend making a copy of this file before making any modifications.

HTH!

Pete
_________________
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569

[markcw]

Engineers could not find that file on the SQL monitor server in that folder. Is the file on the SQL monitor server or the monitored server? Sorry, I do not have direct access to servers.

[markcw]

I worked with the engineers and the config file does not exist on the monitor or the server that is monitored in any directory. File: RedGate.Response.Engine.Alerting.Base.Service.exe.settings

SQL Monitor is installed on a Windows 2008 R2 Server with the latest service packs.

We had to turn off SQL Monitor until we can disable the database functions.

[peter.peart]

Are you sure you're looking in ProgramData? The folder is hidden by default; you'll need to go into Folder Options to show hidden files and folders.

It *will* be located on the machine that is running the Base Monitor service, not the machines you are looking to monitor.

HTH!

Pete
_________________
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569

[markcw]

Found the file in the hidden folder.

Can you confirm all schedules in the XML file should be commented out except cluster.machine.process at 5 minutes?


<collectionSettings maxTraceFileAge="00:15:00" maxTraceFileDiskUsage="1024" reconnectAfterAuthorizationError="false">
<collectionSchedules>

<schedule qualifiedChannelName="[Cluster].[Machine].[Process]">
<intervalSchedule interval="00:05:00"/>
</schedule>

</collectionSchedules>
</collectionSettings>

[peter.peart]

Yes, that's correct. Please do remember though that this will make the analysis page look a bit odd, in that you won't have machine stats etc. there.

Pete
_________________
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569

[markcw]

Changes were made and retested without success.

Is there anything else we can do to stop all database queries and stats from occurring?

We really just need server level monitoring along with SQL Jobs and deadlocks for that server only. I do not recall this happening on SQL Monitor until 3.0+.

The database related commands that still jump to the top of query execution time due to 1500+ databases.

------commands below are the top offenders
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#DBINFO')) DROP TABLE #DBINFO
CREATE TABLE #DBINFO (
[ParentObject] NVARCHAR(255),
[Object] NVARCHAR(255),
[Field] NVARCHAR(255),
[Value] NVARCHAR(255)
);

IF IS_SRVROLEMEMBER('sysadmin') = 1 INSERT INTO #DBINFO EXECUTE('DBCC DBINFO WITH TABLERESULTS');
SET NOCOUNT ON;
SELECT DB_NAME() AS [DB_NAME()], CONVERT(DATETIME, [Value], 121) AS [CONVERT(DATETIME, [Value]], 121)] FROM #DBINFO WITH ( NOLOCK ) WHERE [Field] = 'dbi_dbccLastKnownGood';
DROP TABLE #DBINFO;



WITH query_stats AS
(
SELECT sql_handle ,
plan_handle ,
statement_start_offset ,
statement_end_offset ,
MIN(creation_time) AS creation_time ,
MAX(last_execution_time) AS last_execution_time ,
SUM(execution_count) AS execution_count ,
SUM(total_worker_time_ms) AS total_worker_time_ms ,
SUM(total_logical_writes) AS total_logical_writes ,
SUM(total_logical_reads) AS total_logical_reads ,
SUM(total_physical_reads) AS total_physical_reads ,
SUM(total_elapsed_time_ms) AS total_elapsed_time_ms
FROM #query_plan_stats
GROUP BY sql_handle ,
plan_handle ,
statement_start_offset ,
statement_end_offset
)
,
filtered_query_stats AS


DBCC DBINFO WITH TABLERESULTS

[chriskelly]

I have sent you an email from one of the support email addresses detailing something that should help you. Please try it and reply to that email with information about how it goes.

Once I get you reply, I will post here.
_________________
Chris Kelly
Technical Support Engineer

[chefi_d]

Hello,
I am currently on SQL Monitor version 3.2.0.219. All services are hosted on one machine. It is currently monitoring around 800 databases. The SQL monitor web page is really slow and unusable most of the time. I notice lots of deadlocks on the SQL Monitor's database server. I also see very high IO response times on this server. What would you recommend in order to improve performance? Can I increase the web page refresh period as well as server/database collection periods as a short term solution?

Thanks,
Stefka

[Baguck]

Sorry for reviving such an old thread, but I happened upon it through Google.

Can you provide additional information on qualified channel names. I understand the concept but I'm not sure on the naming of each channel. For example if I wanted to change the interval by which SQL monitor polls for avg write time, or even disk stats all together, what would be the name of that channel?

If there's a complete list of channels or if there is a way to identify the qualified name please let me know.

Thanks!
_________________
___________

Matt Laffoon