Sign up to our newsletter...

...and be the first to learn about new releases

SQL CoPilot Version 1.5.1
Four more useful reports, plus performance improvements

By Richard Fryar

The latest update to SQL CoPilot has just been released.

In this release we have added some of the most asked for features. The ability to easily see blocked and blocking sessions in a blocking chain, a breakdown of waiting tasks with the resources and objects involved, and detail of which objects are suffering from lock and latch contention.

Also added, is a report showing all current user connections, plus performance improvements throughout.

Read on for more details.

Blocking Chains

When faced with a mass of blocking and blocked sessions it is often difficult to find the root of the chain. It involves stepping back and forward through a list until you get to the one that's not being blocked.

The Activity | Current | Blocking Chains report solves that problem. It displays a tree view of all sessions in the chain(s), making it quick to spot the culprit.

Waiting Tasks

All DBAs know the importance of wait stats for performance tuning, but often the waits are listed in isolation. You can see which waits are most prevalent but not which objects, indexes or databases are affected.

The Activity | Current | Waiting Tasks report shows all waiting tasks, along with the wait type they are waiting on, and as well as the resource description (which is never easy to interpret) it also extracts the tables and indexes affected.

This will quickly become one of your most used reports from SQL CoPilot!

Connections

The previous two reports are very clever in their implementation and undoubtedly will be referred to often.

The Activity | Current | Connections report isn't clever at all (you could select * from sys.dm_exec_connections to get the same data) but it is useful to have it available at the click of a mouse.

Operational Statistics

This was going to be called "Lock and Latch Waits" but that wasn't specific enough.

The Activity | 10 Seconds | Operational Stats report looks at the SQL Server for 10 seconds, and then shows all tables and indexes in all databases for which there were waits on row locks, page locks, page latches or page I/O latches during those 10 seconds.

Knowledge of where there is contention on locks or page latches can help identify potential schema or application changes.

Knowledge of where there are significant page I/O latches can help identify queries performing large scans.

Performance Improvements

How many of you have noticed how much slower Management Studio for 2012 or 2014 is than 2008?

Microsoft chose to base the later versions on Visual Studio - and some functions can now take 2 or 3 times longer than before.

This also affected SQL CoPilot. Whereas most reports render instantly in SSMS 2008, some can now take 3 or 4 seconds in 2014.

So in the latest release we have fine tuned some of the reports to squeeze as much performance as possible from Management Studio.

Summary

With the latest release, SQL CoPilot has just become even more useful, and deserves to become an essential daily tool in the armory of every DBA.