T-SQL Tuesday 31: Logging
12 June 2012
It is T-SQL Tuesday once again, hosted by Aaron Nelson (blog / @SqlVariant) . The theme for this T-SQL Tuesday is logging.
Why Logs?
Databases encounter performance problems. All software does. Logs are a good way to isolate why servers, applications, and queries have issues. The life of a DBA (and developer) is easier with logs.
However, getting the logs can be a pain. Worse, we never have enough data when we need it. It can be hard justifying an expensive monitoring app like SCOM or Foglight. So I built a utility using Powershell that does most of the work, for free.
Save-SqlHistory
Save-SqlHistory.ps1 is a single Powershell script with some examples and documentation. It is open-source and free on GitHub. It works very simply:
PS > Save-SqlHistory
-ServerListCsv <a csv file of servers/databases to query>
-QueryListCsv <a csv file of script files to run, and the list of .csv files to save the results to>If you gave the Save-SqlHistory function a list of 1000 databases to query, and 40 monitoring queries, it would run all 40 queries against all 1000 databases. It logs the results to 40 csv files, one per query.
Run Save-SqlHistory in a scheduled task or SQL Agent job, and you have an instant monitoring tool.
Getting Started
The best way to start is to use queries that are return useful information. I like to use the fabulous DMV queries by Glenn Berry (blog / @GlennAlanBerry). The example queries in Save-SqlHistory are based largely on his work, and work with SQL 2012.
To get started, downloade the code. The two CSV files to change are Queries.csv and Servers.csv, and they are intuitive. Run the script against a test database somewhere and look at the results. Next, think about what data to collect.
What information do you wish you had? Stats about index fragmentation? Top queries by CPU usage? Page life expectancy? The runtime of each backup? All you need to do is find/write queries that return this and add their locations to the Queries.csv file. Along with Glenn’s work, I would highly recommend Brent Ozar’s (b / t) sp_Blitz script and Adam Machanic’s (b / t) sp_WhoIsActive script.
Run these for a week or three, analyze the CSV files in a handy tool like Excel or Tableau, and you will have a far better understanding of what your databases are doing.
What’s Next?
I will be refining and adding features to Save-SqlHistory. First on the list is multithreading, so querying 1,000 databases doesn’t take too long. Next on the list is whatever you suggest, just contact me if you have an idea.