Query To Find SQL Audit Details

I've been deploying a SQL Server auditing solution for our new SQL 2014 estate. I know, we're a few years behind. But if you saw some of the legacy systems I work with you'd understand that migrating to SQL 2014 is quite a coup!

Anyway, I found a really great audit solution that I have used as a base on Colleen Morrow's blog. I will detail what I have added to her solution in a later post. But, for now, I would like to share a query that displays some basic information about existing audits, both at the server and database level.

I put this query together for the purposes of documentation. Our 2014 estate is getting large fast. And with so many installations, we need to keep track of what is installed and configured on all our servers.

Tracking Query Plan Changes

I was reading a blog post from Brent Ozar (SQL Server Query Store), which detailed Microsoft's announced future feature in SQL Server that aims to store a history of cached execution plans. The main purpose, it seems, of such a feature is to aid in performance troubleshooting and tuning. If something has changed recently and a query or store procedure begins behaving badly, there will be another tool in the DBA toolbelt to aid in finding what happened. Beyond that I'll let others explain it further.

Full List of SQL Server 2014 DMVs

I'm not sure how useful this will be to others, but I keep searching for a single, comprehensive list of all the SQL Server DMVs with short descriptions. I am looking to complete a certification and this should prove handy for studying. This is certainly no replacement for the full descriptions and examples on MSDN, but I thought it worth putting together.

Get table and row size data

It often happens that you need to know how big a table is. There are many reasons for this: finding which tables have the most rows; understanding which tables tend to grow the fastest; find which table is using the most space.

I found a nice query on stackexchange.com that listed the tables, row counts and data sizes in a database:

DMV Starter Pack

A while back I discovered the DMV Starter Pack, a really useful set of queries exploiting the very powerful Dynamic Management Views and Functions in SQL Server 2005 and 2008.

What I've done is add a few bits to the queries I use most often. Mostly the index queries. In the missing and bad indexes queries I added CREATE and DROP statement columns. Obviously, the CREATE statements conform to the naming conventions of my company [IX_tablename_column1name_column2name...], but this is quite easily modified. I also use a fillfactor of 90 by default. I hope this is helpful.