Comments
Sort by recent activity
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 / comments
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 Jo...
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. / comments
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 ...
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> / comments
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...
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. / comments
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.
Done / comments
Done
I have emailed a screenshot of the results from your suggestions which did not resolve.
Perhaps this feature could be added to future ideas. / comments
I have emailed a screenshot of the results from your suggestions which did not resolve.
Perhaps this feature could be added to future ideas.
I was able to get the results I wanted by using the Options button on the 'Add objects to Project' window.
Thanks for pointing that out to me. / comments
I was able to get the results I wanted by using the Options button on the 'Add objects to Project' window.
Thanks for pointing that out to me.