Performance of SQL Monitor on 1500+ databases on SQL Server

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Performance of SQL Monitor on 1500+ databases on SQL Server

Postby markcw » Wed Jun 20, 2012 11:41 pm

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)
markcw
 
Posts: 10
Joined: Tue Dec 13, 2011 10:34 pm

Postby peter.peart » Fri Jun 22, 2012 12:57 pm

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
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids

Postby markcw » Fri Jun 22, 2012 11:34 pm

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
 
Posts: 10
Joined: Tue Dec 13, 2011 10:34 pm

Postby markcw » Thu Jun 28, 2012 4:52 pm

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.
markcw
 
Posts: 10
Joined: Tue Dec 13, 2011 10:34 pm

Postby peter.peart » Thu Jun 28, 2012 8:32 pm

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
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids

Postby markcw » Thu Jul 05, 2012 9:57 pm

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>
markcw
 
Posts: 10
Joined: Tue Dec 13, 2011 10:34 pm

Postby peter.peart » Mon Jul 09, 2012 10:23 pm

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
peter.peart
Site Admin
 
Posts: 362
Joined: Tue Sep 02, 2008 9:58 am
Location: Top floor, RG towers with the cool kids

Postby markcw » Tue Jul 17, 2012 8:04 pm

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
markcw
 
Posts: 10
Joined: Tue Dec 13, 2011 10:34 pm

Postby chriskelly » Mon Jul 23, 2012 7:49 pm

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
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK

SQL monitor slow

Postby chefi_d » Tue Aug 28, 2012 7:49 pm

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
chefi_d
 
Posts: 6
Joined: Thu Apr 12, 2012 6:16 pm

Postby Baguck » Wed Mar 20, 2013 10:17 pm

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
Baguck
 
Posts: 4
Joined: Thu Jul 26, 2012 7:47 pm
Location: USA - North Carolina


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests

cron