Red Gate forums :: View topic - Performance of SQL Monitor on 1500+ databases on SQL Server
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

Performance of SQL Monitor on 1500+ databases on SQL Server

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
markcw



Joined: 13 Dec 2011
Posts: 10

PostPosted: Wed Jun 20, 2012 11:41 pm    Post subject: Performance of SQL Monitor on 1500+ databases on SQL Server Reply with quote

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)
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Fri Jun 22, 2012 12:57 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
markcw



Joined: 13 Dec 2011
Posts: 10

PostPosted: Fri Jun 22, 2012 11:34 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
markcw



Joined: 13 Dec 2011
Posts: 10

PostPosted: Thu Jun 28, 2012 4:52 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Thu Jun 28, 2012 8:32 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
markcw



Joined: 13 Dec 2011
Posts: 10

PostPosted: Thu Jul 05, 2012 9:57 pm    Post subject: Reply with quote

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>
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Mon Jul 09, 2012 10:23 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
markcw



Joined: 13 Dec 2011
Posts: 10

PostPosted: Tue Jul 17, 2012 8:04 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
chriskelly



Joined: 19 Apr 2010
Posts: 328
Location: Cambridge, UK

PostPosted: Mon Jul 23, 2012 7:49 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
chefi_d



Joined: 12 Apr 2012
Posts: 6

PostPosted: Tue Aug 28, 2012 7:49 pm    Post subject: SQL monitor slow Reply with quote

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
Back to top
View user's profile Send private message
Baguck



Joined: 26 Jul 2012
Posts: 4
Location: USA - North Carolina

PostPosted: Wed Mar 20, 2013 10:17 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group