Sign up to our newsletter...
...and be the first to learn about new releases
What Is SQL CoPilot?
Instant information at your fingertips
SQL CoPilot is your central source of all information about your SQL Servers
Add or import all SQL Server instances.
Add tags to group instances by business area, application name, whether production or development, etc. Use these tags along with name search, version and edition to quickly find an instance. Essential for companies with a lot of servers.
- Import from file
Quickly populate the list of instances by importing from a text file or from a Registered Servers export file. Or just add individual instances manually.
- Add/edit tags
Give each tag a different color for quick identification. Assign multiple tags to each instance.
- Modify thresholds
SQL CoPilot is supplied with default values for highlighting issues. For example, a database is highlighted if a log backup is more than 1 hour overdue. These thresholds can be altered to suit your own environment.
- Generate Excel reports
Perform a scan of all, or selected, instances generating an Excel spreadsheet of results. Currently instance details, database details and a best practise report, with more planned soon.
The Instance Summary dashboard is the first view of a SQL Server instance after it has been clicked on, showing a few important properties such as version, edition, number of databases, etc. Also displayed are current metrics including number of waiting tasks, number of blocked processes, CPU%, page life expectancy, and more. Under this is a list of databases showing more important properties.
Other dashboards are:
- Big Picture
A grid of important data about the instance, color-coded to highlight issues.
- Database Check
A more indepth check of all databases, checking backups are up to date, CHECKDB has been performed, whether any suspect pages, database status, etc
- Real-Time Activity
A real-time view of CPU, memory usage, I/O, waits, important performance metrics and all requests currently running, updated continuously. Essential first step when investigating performance issues.
All high level performance metrics in one place. CPU history, memory utilization, other memory metrics including page life expectancy, average I/O latency, current waits (important ones only), and tempdb usage. Click on any section to drill down to a more detailed view.
A high level view of the SQL Agent Job system, showing status of all jobs and all errors and warnings in the Agent error log.
Selecting a database from one of the dashboards displays a database overview with much more information and issues and warnings highlighted.
Drilldown from here into a huge amount of information:
- Data Files
A list of all data and log files for the selected database, with issues highlighted.
A list of all databases tables showing number of rows, index size and reserved space, and columns indicating if it is clustered, has a primary key, is compressed, and more
- More database objects
Similar lists of views, stored procedures, functions, data types, assemblies, etc.
- Missing Indexes
Indexes that may improve query performance.
- Unused Indexes
Indexes that have not been used since the last service restart.
- Redundant Indexes
Indexes that are covered totally by one or more other indexes.
- Duplicate Indexes
Identical indexes, which may add unnecessary overhead to updates.
Indexes whose statistics are most out of date.
The most fragmented indexes in the database.
- Lock Stats
Indexes that have the most lock requests and lock waits.
- Latch Stats
Indexes that have the most page latch requests and latch waits.
- Scans and Lookups
Indexes that have the most range and table scans, and singleton lookups.
- DML Stats
Number of cumulative inserts, updates and deletes on each index since the last service restart.
Performance high-level dashboard and further details showing metrics and charts for CPU, I/O and memory. More will be added soon.
Current and Historical Activity
Active requests and current sessions, highlighting long-running requests and blocked processes. Other information
includes login, host, program, current wait type, reads, writes, etc.
Clicking on a request or session shows more detail, with a mass of information, including the currently
running SQL, query options and connection details. The SQL can be copied to the clipboard with just one click.
Also in this section. Clicking on any item shows more detail.:
- Current Locks
Lists all locks currently granted and waiting.
- Current Transactions
Lists all open transactions, both user and system.
- Blocking Chains
Shows all sessions involved in a blocking chain, displayed in a hierarchical view so that the root of each chain can be easily identified.
- Waiting Tasks
Shows all waiting tasks, including the objects and indexes involved. So, for example, if you see PAGEIOLATCH waits you can tell which table(s) and index(es) are being read from disk.
- Current Connections
Lists all user connections.
- Most Expensive Queries By CPU
Lists the top 50 most expensive queries based on CPU usage.
- Most Expensive Queries By Duration
Lists the top 50 most expensive queries based on duration.
- Most Expensive Queries By Reads
Lists the top 50 most expensive queries based on number of reads.
- Most Expensive Queries By Writes
Lists the top 50 most expensive queries based on number of writes.
The SQL Server Operating System (SOS or SQL OS for short) is very sophisticated and exposes numerous metrics so we can see what is going on. SQL CoPilot is able to capture and display this information:
View all wait types, split according to whether they are useful (such as PAGEIOLATCH_XX waits) and ignorable (such as LAZYWRITER_SLEEP). This allows you to see exactly where your bottlenecks are without having to sift through the unimportant waits.
A similar view to wait types, showing latch wait times, number of waits, etc.
All schedulers, highlighting the ones with pending I/Os or with a large runnable queue.
- Performance Counters
All SQL Server performance counters, with average values and ratios pre-calculated.
A view of tempdb showing the breakdown by data file and the amount of tempdb being allocated and deallocated by each session.
There's a load of logging in SQL Server. View the error log, displaying errors and CHECKDB errors, but excluding a lot of the "noise" such as confirmation of log backup or application generated messages.
Also view all recent deadlocks - click on a deadlock to display more detail.
Here are the other logs that SQL CoPilot can display:
- Default Trace
View the default trace, filtered by category - Security Audit, Errors and Warnings, Objects, Database, Full Text, and Server.
- System Health
All events recorded in the system_health extended event session
- Ring Buffers
Five views of the events in the ring buffers. These are Connectivity, Exceptions, Resource Monitor, Security, and Memory Broker.
SQL Agent Jobs
SQL Agent related views:
All jobs with last run-status highlighted so you can easily spot failures. A single click on a job shows the history in an easy to view screen.
- Error Log
The SQL Agent error log with errors and warnings highlighted.
A gantt-style view of all jobs allowing you to compare run-times and statuses of jobs in relation to each other.
Download SQL CoPilot free. Get started today