 |
| I'm on a Book! |
Hello Dear Reader, early last year I was asked if I would
like to contribute to a book. The
concept was get a lot of really great SQL People together and let them write a
chapter on a subject that they were passionate about. Eighteen different top SQL professional’s, at
least two MCM’s, many MVP’s, Shake Stir and you get our book, Pro
SQL Server 2012 Practices.
Having only written once chapter I hadn’t read the
others. I had an idea I’d read the
chapters and then blog reviews. I shared
this idea with Mr. Grant Fritchey (@GrantFritchey|Blog), check out his review of Chapter 12 yesterday, and he suggested that we get all of the authors to blog reviews. A lot of people signed on and we’re releasing
our reviews one at a time.
My first review is on Ben Debow’s (@BBQSQL | Website)
chapter Tuning for Peak Load. Ben is a
co-founder of SQLHA with MVP Alan Hirt(@SQLHA | Blog). Ben is a speaker, very active in the SQL
Community, and an all around expert.
“So Balls,” you say, “Get to the review all ready!”
Alright Dear Reader, away we go!
TUNING FOR PEAK
LOAD
Ben does an amazing job of stepping through many different
tools that you can use to assess your environment. First Ben identifies what Peak Load is. He talks about the people that should be
involved in the process of identifying and tuning for this period, and really
steps through the business logic of why these different people should be
involved. Your mileage may vary based on
how large or small your shop is, but in bigger shops he is spot on. He goes on to talk about how you identify
where you are today.
This is an important concept. You cannot measure improvements, or if
changes were detrimental, without first knowing as much as possible about your
current environment. We start at a
10,000 foot view of a setting up a topology diagram, Ben also lists a detailed
table of Attributes to gather on your servers.
Next up we begin doing a performance assessment.
Ben weaves his way through Perfmon, gives you counters to
monitor, and reasons for why you would want to collect them. We move into a discussion of how to gather
profiler data and recommendations what counters you would want to collect.
We move next into Observations. Ben walks through metric’s he has collected
and what they tell him. This is
invaluable to a DBA. You often hear professionals
say “Collect this data” if you’re lucky you hear them say “You want these
numbers”, in this case he tells you how he interprets the numbers and what they
could mean. I’m stressing could, because this will help
you in diagnosing your server, but each environment will be different.
We move into using PAL, http://pal.codeplex.com,
to interpret and report on our Perfmon counters we’ve been collecting. A quick aside if you want to set up PAL and
get it working read the documentation,
there are two add ins. One is tools for Office
2013 Web Components, another is Microsoft
Log Parser, not listed but required as well is Microsoft
Chart Controls for Microsoft.NET Framework 3.5. If you do not have that last one you’ll get a
nice little .NET error when trying to generate the report.
From there we move onto DMV’s and gathering index
statistics. Ben discusses gathering
Index Usage Statistics from sys.dm_db_index_operational_stats, no script is
listed to verify, but the columns discussed are from
sys.dm_db_index_usage_stats. As long as
you get the right DMV the content is solid and I found the Costly Indexes
description very interesting!
Finally Ben helps you devise a plan to actually implement the
analysis into a plan you can implement.
The thing I love is that you can ask 20 brilliant SQL minds to do the
same thing, and you’ll get 20 different variations of the same thing. Ben looks for some things that I had not
considered. I enjoyed the chapter immensely
and look forward to implementing what I’ve learned.
If you pick up the book please feel free to drop me a line
and tell me what you think!
As always Thank You for stopping by!
Thanks,
Brad
