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 until

  2015, 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