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.

Instances

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.

Dashboards

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.
  • Performance
    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.
  • Jobs
    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.

Databases

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.
  • Tables
    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.
  • Statistics
    Indexes whose statistics are most out of date.
  • Fragmentation
    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

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.

SQL OS

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:

  • Waits
    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.
  • Latches
    A similar view to wait types, showing latch wait times, number of waits, etc.
  • Schedulers
    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.
  • tempdb
    A view of tempdb showing the breakdown by data file and the amount of tempdb being allocated and deallocated by each session.

Logs

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:

  • Jobs
    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.
  • Timeline
    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