Performance of SQL Monitor on 1500+ databases on SQL Server
markcw
Posts: 13
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)
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)
Comments
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
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
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.
It *will* be located on the machine that is running the Base Monitor service, not the machines you are looking to monitor.
HTH!
Pete
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
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>
Pete
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
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
Once I get you reply, I will post here.
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
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