SQL Planner uses sysadmin credential to show greater insight about SQL Server root-cause of slowness , issues , Errors without this it would be very much limited to certain alerts only and its basic metric report.
Here is details, Where SQL Server SysAdmin user Credential are used within SQL Planner :
Retrieve SQL Serve Agent Job Execution Details : A procedure in SQL Server under msdb database “sp_help_jobhistory_full” is executed to fetch Agent Job Duration , execution status and is also used in Alert when any Job Fails – The job could be of following but not limited :
- Backup Job,
- Index maintenance,
- Application Related etc.
Retrieve SQL Server Error Log information: A procedure in SQL Server under mssqlsystemresource database “xp_readerrorlog” is executed to know
- several critical errors ,
- warnings ,
- errors ,
- disk latency IO errors,
- cluster failover events etc…. which is used in dashboard with detailed information and used in sending Alerts.
Retrieve OS Disk space details : SQL Planner hit a procedure xp_cmdshell used to to run Powershell script to extract Disk storage details and is used in Disk Storage Dashboard and used to Alert when low space is left.
Deadlock, Blocking, Expensive Queries, Waits related Extended Events : SQL Planner creates 3 Extended event to capture Deadlock, Blocking , Expensive Queries & Procedure , Waits (Finding Where Session are waiting). This gives user exact reason about the issues and slowness root-cause with most granular level including parameter details.
SQL Server Auditing : SQL server Audit event created altered and read its data