As
I was waiting for the next TSQL
Tuesday topic to be announced, we all know that when SQL Server performance is
slow, and becomes a big issue in your company, sometimes we need to throw an
army at the problem to resolve it.
Other times, we can employ an army-of-one, a soldier who can
troubleshoot almost any performance bottleneck in his sleep! His name, Robert L. Davis, aka SQLSoldier, is the man with the
plan, and the #sqlhelp hashtag on twitter, who is the host of today’s TSQL Tuesday topic #49 – Wait For It….well, that’s it, what are you all waiting
for? Yes, the topic is Wait Stats, a topic
near and dear to my SQL Soul, as I have written and presented on this topic for
some time now.
Therefore, art tho SQL readers, Mr.
Davis wants us to write about our
most interesting post involving waits (wait types, queues, DMV queries, etc). Now, there’s quite a lot out there, including
my own blog posts, as well as an entire chapter, written by yours truly, in SQL
Server Deep Dives, Volume 2, (Chapter 34), aptly entitled, “What are
you waiting for? An introduction to wait and queues” In my chapter, I
introduced the reader to one of the most underutilized performance
troubleshooting methodologies at the time, as well as about the SQL Server
execution model, running and runnable processes, signal vs. resource waits, and
information on finding and resolving IO, CPU, and/or memory bottlenecks.
Now the
secret is out, and if your SQL Server is experiencing technical difficulties,
the first place you should be looking, is the SQL Server wait stats. By
examining the wait statistics we can understand where SQL Server is waiting,
and what resource it’s waiting for to become available. Once identified, you
can set up relevant performance counters to further analyze the cause of
resource contention, responsible for slowing down the server.
One early on blog that I started discussing this
topic, was a precursor to my chapter and verse, was simply doing my part to
evangelize Waits & Queues, and how I first utilized this performance tuning
method at a client, for whom I did a performance review. You can see this here.
So,
where does one find these wait stats that we speak of? The primary system views that expose these
metrics are sys.dm_os_wait_stats,
and sys.dm_os_waiting_tasks. The wait_stats DMV is historical aggregated
data across all session ids since, SQL Server last started (or statistics cleared),
and shows the time for waits that have completed.
The
waiting_tasks dmv, shows the current waits at the moment in time it is queried,
and shows which resources it is waiting on (the suspended queue) for all active
sessions. It is what is happening on the
server right now.
Just
back from SQLSaturday#233 DC Circuit, I presented my HealthySQL™
session, and part of my roll your own health check strategy was to employ
waits & queues. I recently blogged
about SQLSaturday
HealthySQL, (which you can read by clicking on the highlighted link)
So,
glad I can participate in this December 2013 edition of T-SQL Tuesday. Don’t keep your end-users waiting, if SQL
does, find out why using wait statistics.
I look forward to reading the many other wonderful writings on waits.
Thanks again to Robert Davis for hosting, and for picking such a great topic,
and look forward to continuing news
from the frontlines blog
And now, the obligatory credits and
rules of the road, Started by our friend SQL MVP Adam Machanic blog|twitter, who came up with the idea of
improving community involvement via blogging where bloggers around the world
post their views on a same topic chosen by the host on the 2nd Tuesday of every
month. If you are
interested in participating in T-SQL Tuesday, please follow these guidelines.
- Your
post must be published between 00:00 GMT Tuesday, October 8th, 2013, and 00:00
GMT Wednesday, October 9th, 2013
- Your
post must contain the T-SQL Tuesday logo from above and the image should
link back to this blog post.
- Trackbacks
should work, but if you don’t see one please link to your post in the comments
section below so everyone can see your work
And
some optional (and highly encouraged) things to also do:
- Include
a reference to T-SQL Tuesday in the title of your post
- Tweet
about your post using the hash tag #TSQL2sDay
- Consider
hosting T-SQL Tuesday yourself. Adam Machanic
keeps the list.
(Sign up now, because, you’ll be waiting until2015, since it is booked from now until then!)
Please follow me on Twitter @Pearlknows,
and check out our web-site for all our available services at http://www.pearlknows.com.
Take our HealthySQL Challenge! Are you SQL Servers
healthy? How do you know for sure? Please contact us about our 15-point
Health Check report, which will identify areas for improvement, and allow for
best practice recommendations for your SQL Server(s). If we find NOTHING
wrong with your SQL Server, the report is FREE! Contact us as
pearlknows@yahoo.com