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.

Happy Logging!

Buy an SSD Using Data

05 June 2012

I decided to buy a solid state disk (SSD) for my home computer, and want the best deal possible. I require three things: low price, fast performance, and good reliability. Which drive has the best mix of the three? Let's find out using my favorite tool: data!

Performance

Most of us do not have a collection of SSDs and a test rig. Luckily, there are great websites that do have these things and publish their results. I used two of the most popular: AnandTech.com and TomsHardware.com. A quick check shows that there are 12 SSDs that were recently reviewed by both sites.

Price

I chose a reputable website (Newegg.com) and looked up prices. I don't use mail-in rebate prices because rebates are notoriously hard to redeem. This part was very, very easy.

Reliability

Reliability data is also available online, sort of. I like to crowd-source my reliability data from feedback consumers leave on large sites. We'll use Amazon.com and Newegg.com 'star' ratings. Let's consider both the scores and the number of reviews posted. The reason to look at counts is because a large enough number of reviews implies the wisdom of the crowds is accurate (statistically significant is the technical term).

Analysis

To start with, I put everything into an Excel file. Then I uploaded the data into a common visualization tool, Tableau. To start, let's look at price versus performance.

The drives we are interested in are in the upper-left corner; they have the best performance and are also cheap. 6 SSDs qualify:

• Plextor M3
• Samsung SSD 830
• OCZ Vertex 3
• Corsair Performance Series Pro
• Intel SSD 520
• Kingston HyperX

The color represents the ratings given to each SSD by customers. One of them (in red) has bad ratings: the OCZ Vertex 3. We therefore eliminate it from our list. If we look a little closer at the data, 2 SSDs are pricier and slower than others: the Intel SSD 520 and Corsair Performance Series Pro. We therefore eliminate them as well, leaving us with 3 drives: the Kingston HyperX, Samsung SSD 830, and Plextor M3. They are all fast, and all under $280. Time to look at some different data.

Here we see performance scores vs. customer ratings for 3 different performance tests. The SSDs trend (roughly) from bottom-left to upper-right in each graph, indicating that review scores get better as the performance improves. That makes sense; we expect to see faster drives create happier customers. This time the interesting SSDs are in the upper-right corner; they have good customer reviews and good performance.

Three SSDs fit this criteria:

• Samsung SSD 830
• Kingston HyperX
• Intel SSD 520

We have already eliminated the Intel drive because it is pricey, which leaves us with 2 that pass our price filter and our ratings filter. I would be confident getting either drive. Success!

Conclusion: Samsung SSD 830 and Kingston HyperX