Reporting – SQL Studies

Default SQL Agent Reports – Top Jobs

Kenneth Fisher — Mon, 24 Jul 2017 13:00:38 +0000

Looking at what a job has been doing is important. Particularly if something is going wrong. Now there are several ways to do this depending on your preference. You can use the system views.

Honestly, though, as much as I love writing queries for things these views are more than a bit difficult to work with.

So let’s look at the GUI. If you need to look at the error for a job or it’s output, in detail, the best way is to use the View History option.

But sometimes what you want is an overall history. Maybe you want to see your longest running jobs? Or the most frequent jobs? Or jobs that are failing? There is a great pre-built report just for that.

Now, be warned. The report and the history view both pull from the system views and they only hold so much history. You need to make sure that you are aware of what your job history system settings are.

And of course, all of this is to view the job information across a single server. If you want to view aggregate data across multiple instances you’ll have to either roll your own solution using SSIS, Powershell, etc, or use a paid product like Idera’s SQL Server Job Manager, Redgate’s SQL Monitor or Minion Enterprise.

Default Database Reports – Disk Usage by Table

Kenneth Fisher — Wed, 19 Jul 2017 13:00:01 +0000

Every now and again you need to know how big a table is. Or several tables. Or all of the tables. Number of rows is frequently handy when you’re going to create a new index or otherwise modify the table. The amount of space used by the indexes can be helpful in deciding how much space you need to do a re-index. The tables with the most unused space is nice to know if you have a problem with ever growing heaps.

In the past my go to solution here was sp_spaceused. It’s a really handy procedure.

USE AdventureWorks2014;
GO
EXEC sp_spaceused 'Person.Person';
GO

Great information but it has a few problems. You can only run it for one table at a time (sp_msforeachtable is a workaround, if undocumented), the file sizes aren’t consistent (sometimes KB, sometimes MB or even GB), and it only returns the name of the object but not the schema. So if there is the same table name under multiple schemas it can get tricky.

A much better solution is the default database report “Disk Usage by Table”. Right click on the database -> Reports -> Standard Reports -> Disk Usage by Table.

And you get a very nice report with all of the tables listed, with schema names and consistent sizes.

It’s even sortable! Unfortunately, the arrows next to the columns always start with ascending but if you click on the arrow next to the Unused column twice you will get the tables with the most unused space.

You can ignore the orange/gray lines. For whatever reason each line doesn’t change from it’s initial color when you sort the report.

Default Instance Reports – Configuration Changes History

Kenneth Fisher — Mon, 17 Jul 2017 13:00:10 +0000

Hey, who changed the max memory setting for the xyz instance? The good news is that this information is captured in the default trace. Even better is the fact that there is a report that will pull the data out of the default trace and make it nice and easy to see.

You get to see the old value as well as the new, who did it, and when. The big downside is the fact that the default trace is only so big so the more active your instance is the less time you have before the information goes away. Because of this, you might consider creating a daily job to pull the information into a logging table. You won’t be able to use the report but at least you’ll have the data.