1. Prerequisites and Security Requirements
Before initiating a monitoring job, ensure that the environment meets the necessary security standards.
Administrative Privileges: To capture deep-level diagnostics, the account used by SQL Planner must have DBA (Database Administrator OR Server View State) privileges.
Access to DMVs: High-level permissions are required because the tool executes complex T-SQL code and queries Dynamic Management Views (DMVs). These views provide vital metadata about server health, memory pressure, and query execution plans that standard user accounts cannot access.
2. Initiating a New Monitoring Job
The monitoring process in SQL Planner is “job-based,” allowing you to customize tracking for different environments (Production, UAT, or Development).
Access the Wizard: Navigate to the primary navigation menu and select “Create Monitoring or Health Analysis Job.”
Define the Instance: You will be prompted to enter the Server Name or IP address of the SQL Server instance you wish to monitor.
Authentication: Select your connection method (Windows Authentication or SQL Server Authentication) and provide the DBA-level credentials mentioned in the prerequisites.
(Generating the Monitoring User : Click the “Create Monitoring Login” link to initiate the automated user-provisioning wizard. This tool creates a restricted SQL Server login tailored specifically for SQL Planner’s diagnostic requirements. After the account is generated, you can view the full breakdown of granted permissions to verify that the account operates under a “view-only” or “limited access” scope.)
3. Configuring Advanced Alert Thresholds
One of the most critical steps in the setup is defining what constitutes a “Critical” or “Warning” state. SQL Planner monitors three core hardware pillars:
A. CPU Usage
Set a percentage limit (e.g., 80% for 5 minutes) to identify sustained spikes. High CPU usage often indicates poorly optimized queries, missing indexes, or excessive compilation.
B. Disk Drive Monitoring
Database growth can be unpredictable. By setting disk thresholds, you ensure that you receive an alert long before a drive reaches 100% capacity, which would otherwise cause the SQL Server service to halt.
C. RAM (Memory) Pressure
SQL Server is designed to use a significant amount of memory. Monitoring RAM helps you distinguish between normal “Internal Memory Pressure” (where SQL Server manages its cache) and “External Memory Pressure” (where other applications are starving the database of resources).
50+ critical and warning level of alerts available which can be configured : https://mssqlplanner.com/alerts-for-sql-server/
4. Automation: Notification & Job Identification
Once the thresholds are set, you must define how the system communicates issues to your team.
Job Naming Convention: Use a clear, descriptive name (e.g.,
PROD_FINANCE_DB_MONITOR) to ensure that when an alert arrives, you immediately know which server is affected.Email Integration: Input the primary email address for notifications. In an enterprise environment, this is typically a distribution list for the DBA team or a ticketing system’s intake email.
5. Post-Configuration: Dashboards and Analysis
After saving the job, SQL Planner begins its data collection cycle. There are two primary ways to interact with this data:
The Server Monitoring Dashboard
This is your “bird’s eye view.” It displays a real-time status of all configured servers. If a server goes offline or a threshold is breached, the status indicator will change color, allowing for immediate visual recognition of issues.
Health Analysis Reports
For deep-dive investigations, navigate to the Analyze section. Unlike the dashboard, which shows “right now,” the Analysis section provides:
Historical Trends: See if memory pressure occurs every Monday morning or during month-end processing.
Live Performance Snapshots: View active sessions, blocked queries, and current resource consumption in a single, unified report.
Summary Checklist for Administrators
[ ] Verify DBA permissions for the monitoring account.
[ ] Define custom CPU, Disk, and RAM thresholds based on server hardware.
[ ] Test the notification email to ensure alerts aren’t caught in spam filters.
[ ] Review the “Analyze” section after 24 hours to establish a performance baseline.
Need further assistance?
If you encounter issues connecting to your SQL Instance, ensure that Remote Connections are enabled in SQL Server Surface Area Configuration and that your firewall allows traffic on the designated SQL port (Default: 1433).
