Look into the past with SQL CoPilot Time Machine.
SQL CoPilot has always been the first place you turn to when you want to see what is happening right now on your SQL Servers. You can even see a little bit into the past via the logs and ring buffers - useful for checking out connection failures or deadlocks.
But now the next step in the evolution of SQL CoPilot is here - Time Machine. Enable it on as many of your instances as you like, wait a few minutes for it to start collecting data, and you are ready anytime someone asks those awkward questions:
"Hey DBA, everything seemed a bit slow just after 8 this morning. Can you find out the cause?"
"Hey DBA, we just released some new code. Can you compare today's activity with the same day 2 weeks ago?"
"Hey DBA, a user is complaining about a report that took 3 hours to run yesterday afternoon. Can you send me the SQL that she ran?"
So What Can You See?
Quite a lot already, and the next couple of releases will add a whole lot more.
The Time Machine Overview screen displays 9 charts showing data captured over the last few hours. You can very quickly change the time range to view charts back as far as 28 days (once the data collection has been running for that long).
Data currently available in this screen includes:
- CPU utilization
- Batch requests/sec with (re)compilations/sec
- Page memory (database memory, stolen memory and free memory)
- Non-page memory (connection memory, lock memory, cache memory, granted workspace memory)
- Page life expectancy
- I/O throughput
- Data read and log write latency
- Top waits
Hover over any chart to see a tooltip with a legend for the area under the mouse pointer.
Click on any chart to see a the queries running at that time. Click on one of those queries to see the SQL that was running, with a few other details such as host, login, wait type and time, I/O metrics, etc.
How To Enable It
Visit the Time Machine pages for an instance that doesn't have it enabled and you will be prompted to enable it.
The Configuration page checks that you have sufficient permission and, if so, the button to enable it can be clicked.Time Machine stores the historical data in a database called SQLCoPilot on each server for which it is enabled, and creates a SQL Agent job that runs once per minute to capture activity and peformance counters.
How It Works
Time Machine is very simple.
The most sensible place to keep historical performance and activity is on the instance itself. This puts the minimum load on the server and avoids the need for another SQL Server, with the associated hardware and licensing costs that go with it.
SQL CoPilot Time Machine only captures data once per minute. You might think that is not often enough, but actually it collects plenty of data. The only downside is that only queries running at collection time are captured, but you will find that most long running queries will be included and can be examined using the SQL CoPilot application.
Data is kept for 28 days, which is only a few 100MB for most servers. The largest amount of stored data we have seen was 12GB, and that was on a server averaging 1nearly 20,000 requests per second! A future release may allow retention time and collection frequency to be modified, if there is enough demand.
Time Machine data is stored in a database called SQLCoPilot, which is created if it doesn't already exist. If you have multiple users, I suggest you create an AD security group and grant it read-only access to the SQLCoPilot database on all servers you have enabled it on.
Time Machine is a new feature. More data will be added in a future release, but if you have any issues or suggestions for anything you would like captured or changes to the charts, please let me know via the contact form.