SQL DM causing excess load on Production OLTP server

Currently when I take SQLDM out of Maintenance mode the CPU utilization o the OLTP database it is monitoring goes up to between 75 and 95% and stays there for over 12 hours.

We are running SQL Server 2012 enterprise and SQLDM version

Is there a standard configuration we should be looking at for Alerts and Monitoring

Thanks in Advance!

  • Check if you have Query Monitor enabled. If so, check if you are collecting Query Plans. If so, uncheck the box to collect Query Plans, either actual or estimated Query Plans.

    See also the description for the *Showplan* Events, e.g.query_pre_execution_showplan:
    Occurs after a SQL statement is compiled. This event returns an XML representation of the estimated query plan that is generated when the query is optimized. Using this event can have a significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time.

    May check also with the query:

    SELECT InstanceName, QueryMonitorEnabled, QueryMonitorCollectQueryPlan, QueryMonitorCollectEstimatedQueryPlan
    FROM dbo.MonitoredSQLServers
    WHERE Active= 1
    AND QueryMonitorEnabled = 1

    May this could be the Problem, but you have to check it out.