The DMV Project 2010.1.1 sys.dm_os_wait_stats

I made a resolution that I was going to learn and use 2 new Dynamic Management Objects (DMOs aka Dynamic Management Views and Dynamic Management Functions aka DMVs and DMFs) each month in 2010.  Well, here it is the end of the first week in February so I guess I should report in.  The first DMV I’ll be covering is one that is getting a great deal of attention as of late: sys.dm_os_wait_stats.

SQL Server has some things in common with ex-girlfriends of mine.  It keeps track of every time it needs to wait for something – anything.  Parallelism causing delays?  Record a CX_PACKET wait.  The application doing row-by-row processing of a batch result returned from SQL Server and not releasing?  Write down “ASYNC_NETWORK_IO”.  The DBA lose track of what he was doing?  That’s a SOMETHING_SHINY wait folks.  Point is that unlike my exes, this is good for me – and for you.

By analyzing what waits are being recorded you can drill-down into specific resource areas to determine what is your bottleneck/performance issue on the SQL Server instance.  Specific waits can point you to CPU, Memory, or I/O issues to resolve.  By further consulting additional DMVs (which we will eventually cover here in subsequent months) you can resolve your performance issues much more quickly than the days when you would stare at Perfmon and Profiler traces.

What does the output of a call from sys.dm_os_wait_stats look like?  Here is a sampling:

SELECT * FROM sys.dm_os_wait_stats;

 

Glenn Berry, in his chapter on DMVs in the MVP Deep Dive Book had a great query that utilizes sys.dm_os_wait_stats.  I’ve taken that query and altered it slightly for my use to get a snapshot of waits in percentage form at the current point in time.  By creating a common table expression to build a Waits table you can filter out irrelevant wait types and then look at a listing of just those waits that comprise the top N% (in this case 95%) of the waits on the SQL Server instance.

WITH Waits AS
 (
 SELECT 
   wait_type, 
   wait_time_ms / 1000. AS wait_time_s,
   100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
   ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
 FROM sys.dm_os_wait_stats
 WHERE wait_type 
   NOT IN
     ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
   'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
   'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
   ) -- filter out additional irrelevant waits

SELECT W1.wait_type,
 CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
 CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
 CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn, 
 W1.wait_type, 
 W1.wait_time_s, 
 W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;

 

The important thing to remember is that the metrics for the columns returned when querying sys.dm_os_wait_stats is cummulative.  Like all Dynamic Management Object information these values accumulate over time and are wiped-clean at a SQL Server service restart.  This is why I advocate persisting the information in a static table on a dedicated administrative database located on each managed SQL Server instance.  You can then query the information just for the period between the last two stats collections.  I use a SQL Agent job that runs hourly to persist the information to the static table.  You could either run the code manually when desired or schedule as works best in your organization.  The query is lightweight, not really too noticeable on a decently-sized server.  The templated script below will allow you to do just that.  Note that the remaining scripts in this post use template notation.  CNTL+SHIFT+M in SQL Server Management Studio will provide a form to enter parameter values that are correct for your environment

USE [<database_name,,Foo>];
--Create table to persist wait stats information: 

CREATE TABLE <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
(
 [wait_type] [nvarchar](60) NOT NULL,
 [waiting_tasks_count] [bigint] NOT NULL,
 [wait_time_ms] [bigint] NOT NULL,
 [max_wait_time_ms] [bigint] NOT NULL,
 [signal_wait_time_ms] [bigint] NOT NULL,
 [capture_time] [datetime] NOT NULL,
 [increment_id] [int] NOT NULL
); 

ALTER TABLE <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats> 
 ADD  DEFAULT (GETDATE()) FOR [capture_time]; 

--Insert wait stats info in a datestamped format for later querying:
DECLARE @DT DATETIME ;
SET @DT = GETDATE() ;
DECLARE @increment_id INT; 

SELECT @increment_id = MAX(increment_id) + 1 FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>;
SELECT @increment_id = ISNULL(@increment_id)

INSERT INTO <database_name,,Foo>.<table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
 ([wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms],
 [signal_wait_time_ms], [capture_time], [increment_id])
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], 
 [signal_wait_time_ms], @DT, @increment_id
FROM sys.dm_os_wait_stats;

The following information would be persisted to the physical table of your choosing.  If using the defaults in the template it would be Foo.dbo.dm_os_wait_stats.  Note how it creates and increment_id and datestamp for the data collected:

 

Then you can run a query similar to this and only view count resulting from recent activity – not activity over days, months, weeks, etc (trailing columns removed from screen shot for space concerns.):

--Return persisted information from table
 USE [<database_name,,Foo>]; 

DECLARE @max_increment_id INT 

------------------------------------------------------------------
--Determine most-recent increment_id
------------------------------------------------------------------
SELECT @max_increment_id = MAX(increment_id)
FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>

------------------------------------------------------------------
--Present Waits results for period
------------------------------------------------------------------
SELECT DOWS1.wait_type, 
 (DOWS1.waiting_tasks_count - DOWS2.waiting_tasks_count) AS [waiting_tasks_count],
 (DOWS1.wait_time_ms - DOWS2.wait_time_ms) AS [wait_time_ms],
 DOWS1.max_wait_time_ms, 
 (DOWS1.signal_wait_time_ms - DOWS2.signal_wait_time_ms) AS [signal_wait_time_ms],
 DATEDIFF(ms, DOWS2.capture_time, DOWS1.capture_time) AS [elapsed_time_ms],
 DOWS1.capture_time AS [last_time_stamp], DOWS2.capture_time AS [previous_time_stamp]
FROM 
 (
 SELECT  wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
         signal_wait_time_ms, capture_time, increment_id
 FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
 WHERE increment_id = @max_increment_id
 )AS DOWS1 
 INNER JOIN 
 (
 SELECT  wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
         signal_wait_time_ms, capture_time, increment_id
 FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
 WHERE increment_id = (@max_increment_id - 1)
 )AS DOWS2 ON DOWS1.wait_type = DOWS2.wait_type
WHERE (DOWS1.wait_time_ms - DOWS2.wait_time_ms) > 0 
 /*
 This can technically be eliminated because we're not persisting these waits:
 AND DOWS1.wait_type NOT IN 
   ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
   'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
   'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT')
 */
ORDER BY (DOWS1.wait_time_ms - DOWS2.wait_time_ms) DESC;
 

Finally, you could re-tool the earlier top N% waits query to examine just the most recent wait stats activity from the persisted table as demonstrated below.  You can utilize this process (building a CTE comparing deltas for just the activity collected since the last two polling periods) to retrofit any query you find online for analyzing results from sys.dm_os_wait_stats.

USE [<database_name,,Foo>];
DECLARE @max_increment_id INT 

------------------------------------------------------------------
--Determine most-recent increment_id
------------------------------------------------------------------
SELECT @max_increment_id = MAX(increment_id)
FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>;

------------------------------------------------------------------
--Present Waits results for period
------------------------------------------------------------------
WITH Waits AS
 (
 SELECT DOWS1.wait_type, 
   ((DOWS1.wait_time_ms - DOWS2.wait_time_ms)/1000) AS [wait_time_s],
   100. * (DOWS1.wait_time_ms - DOWS2.wait_time_ms) / SUM(DOWS1.wait_time_ms - DOWS2.wait_time_ms) OVER() AS pct,
     ROW_NUMBER() OVER(ORDER BY (DOWS1.wait_time_ms - DOWS2.wait_time_ms) DESC) AS rn
 FROM 
   (
   SELECT  wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
      signal_wait_time_ms, capture_time, increment_id
   FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
   WHERE increment_id = @max_increment_id
   )AS DOWS1 
   INNER JOIN 
   (
   SELECT  wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
      signal_wait_time_ms, capture_time, increment_id
   FROM <table_schema_name,,dbo>.<table_name,,dm_os_wait_stats>
   WHERE increment_id = (@max_increment_id - 1)
   )AS DOWS2 ON DOWS1.wait_type = DOWS2.wait_type
 WHERE (DOWS1.wait_time_ms - DOWS2.wait_time_ms) > 0
 )

SELECT W1.wait_type,
 CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
 CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
 CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn, 
 W1.wait_type, 
 W1.wait_time_s, 
 W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;

Tags: , ,

Thing 1 Thing 2 and Thing 3. (or No Farm Animals Were Injured In The Making of This Post.)

Paul Randal (web|twitter) created another one of these chain blog memes recently when posting about the three things/events that brought him to his current circumstances on this little blue-green planet.  The meme eventually made its way to Jack Corbett (web|twitter) and then onto me.  As per normal processes when asked to make a list of nitems of anything I end up lamenting over an un-trimmable n+1 items.  However, there are rules to be obeyed so I shall bend (but never break, my friends – never break.)

Thing 1.

First and foremost in my life, as is the case with most men of any age, is the impact their father(s) have on their development, their sensibilities or lack thereof, their compassion, and how they in-turn parent their own children.  Some who know or follow me are aware of the fact that my father died shortly after my 4th birthday back in 1972.  Most do not know the circumstances, but the circumstances of my father’s death, more-so than the impact my father’s life, has formed the foundation of the man I am today.  It is the death I’ve had to live with longer than the life that precluded it.  I’m sure he was formative in those first 4 years of life, but those days, while critical to physical and psychological development, are lost over the years.  There are flashes of the time we spent together, but it’s hard to say if those come from actual events, or photographs I’ve seen of us together.  Yes, it’s sad.  I’m not looking for or expecting pity though, as I suppose growing up without a father is in some ways quite similar to growing up blind or missing a limb.  You (of the blind, limbless, fatherless sort) don’t have a firm understanding of what life would be like without the circumstances you’ve been dealt, so it is sadder for those looking in than the individuals dealing with the circumstances.  If anything I feel that sadness, but towards my Mom.  Here she was, 24 years old with a 4 year old and a high school education in the early 1970s when a small percentage of households were single-earner maternal incomes. 

My father was a pipe-fitter.  That’s really all I know about his profession other than I know on that day in September he was working on a scaffold on the exterior of a building.  There was a second scaffold several floors higher than his.  Up until five years ago I only knew that there was an accident and the man on the scaffold above his fell.  My father leaned out to attempt to catch him and was knocked off his scaffold in turn, falling to his death.  (Apparently you weren’t clipped into any safety devices back in the ’70s.)  Five years ago I found out more details from his best friend at the time.  Apparently someone driving a forklift caught ropes dangling from the upper scaffold and triggered the fall.  Part of me wanted to look up the driver and see if I could get more information.  However, this man, involved in a tragic accident that took the lives of two men has hopefully put this behind him.  He doesn’t need to hear from a 41 year old reminder of that accident.  I am not Inigo Montoya and I must suspect that this forklift driver from Kalamazoo, Michigan does not have extra digits.

What this event has done to me is make me an extremely cautious individual.  It’s made me a compassionate individual.  It gave me a drive to be the best father I could possibly be – but it did not give me the role model to be that father.  I grasp so often at the right thing to say or do when dealing with my amazing sons.  Yes, I know, all men should be proud of their children.  I feel mind are extraordinary: voracious readers, one an outstanding athelete, the other preparing to take the SATs and college honors classes at the ripe old age of 11.  I’m told so often by teachers and adults they interact with that they are polite, charming, compassionate, and intelligent.  It is the way we’ve hoped to raise them, but I also feel that I’ve pushed and cajoled my sons along the way though to get them to this point.  My desire to be a great dad and have great sons that turn into great men was countered with the lack of any formal training through the guise of a father of my own to learn from.  I had a step father eventually.  I resented him greatly for most of my formative years though – he being forceful, bullying, and intractable in his own right (hmm, a trend?) – until I was a father.  He was much older than my Mom and unfortunately he passed a couple years back.  My resentment towards him taking the role of father from a man I didn’t even remember well must have been hard for him; probably adding a few harsh brush strokes to the picture I describe now.  He was also the one who would side with giving me more freedoms than my Mom would in my teen years.  (Thanks Pop – those were some fun years as a result!)

My father’s death made me fear for losing anything close to me, be it possessions, family, friends, accomplishments.  It has given me an insatiable drive to push myself and succeed, perhaps as a way to fill the hole in this world left behind by the loss of my father.  I’m sure it is all the times I’ve heard from my Mom, Grandparents, and other loved ones that “your Father would be so proud of you.”  You’d be amazed at how much of an impact that has on a child, or as a 40-something-year-old man.  This is why when I jump into something: professions, projects, fitness, hobbies – I jump in to them full-force.

Thing 2.

I was accepted to Florida Institute of Technology as an Applied Mathematics major in my senior year in high school.  While at summer camp the month before school was to start though I had a conversation with a girl whose name and face has faded into the fog of time who admonished me for going out-of-state before completing my general education classes.  I was paying for my own schooling from funds invested from the life insurance payouts for my father’s death and that made perfect sense.  I cancelled my enrollment, got back my dorm and tuition deposits (which I promptly used to buy a ten-speed bike and skiis) and enrolled at Western Michigan University.  I ended up changing my major when I hit calculus 3.  I made friends, joined a fraternity, and settled into an accounting degree (it was still numbers, and my puzzling mind liked the Left = Right aspect of financial accounting.)  I eventually graduated with a degree I hated, but with no more money to stay in school, but landed a job working as a pre-press graphic designer thanks to my creative abilities, love of computers, and the fact that my Mom got me an interview at the company. NEPOTISM R-O-C-K-S when you’re the nepotiser.  The summer after I graduated I ended up dating my (now) wife after running into her in a convenience store parking lot on campus.  I had known her for a couple years already through the fraternity, dated some of her roommates and friends (she, likewise with my friends and fraternity brothers.)  We always seemed to be in eachother’s social circle but nothing ever developed until that meeting in a parking lot in 1991.  To this day I can still remember so many aspects of that encounter it frightens me a bit.

We bought a house in 1993, got married in 1994, had kids in 1998 and 2000.  None of which would have happened if I had gone to Florida as planned.

The position as a pre-press designer turned into a production estimator because of my accounting background and my color blindness (that’s not conducive to doing color correction!)  The owner of the company was the most belligerent son-of-a-bitch you’d ever work for and I struggled through it for eight years of being called “stupid”, “an idiot”, “worthless, with an eighth grade education” all with pepperings of F-bombs.  It drove me to look for new alternatives and professions and I took up Visual Basic design and Transact SQL language because of the elegance of its simplicity.  Again, without that off-hand comment about taking general education classes locally I may be a professional cat groomer, pretzel salter at an airport food stand, or (forbid) an accountant.

Thing 3.

I recently celebrated my tenth year as an employee at Spectrum Health.  I’ve spent the entire scope of my professional database career at this company and I have no desire to work elsewhere.  The people I work with are, for the most part, an amazingly-talented set of individuals who are dedicated to using technology to make people healthier, heal the sick, and comfort the dying.  I would have never been hired if not for the recommendation of the (now) Oracle DBA who interviewed me.  I was applying for the open Microsoft Access Developer position.  (Why do I feel like I just admitted to enjoying the company of farm animals?)  It was the waning days of Y2K-1 and he and one other person at the time were responsible for converting non-Y2K-compliant Access 2.0 apps into Access 97.  Thousands of them.  (Now feeling like I just tried to plead my case for enjoying the companionship of farm animals: they were of a legal age!  They had nice eyes!  They asked for it!)  He asked a specific question that sealed the deal with him and that I now use to this day in interviews:  “You’ve been asked to do a task.  What happens if you get stuck?  What do you do?”  My response was along the line of “crack a book and dig in.  Research it, learn it, and move on.”  He liked the fact that I would not be satisfied until I figured out the tasks and accomplished the goal.  To this day we talk about that being the reason why I was hired. 

I could have added other events, I am compelled to itemize them here, but I won’t.  It would violate the intent of the post.  But suffice to say that it is the cumulative events of our lives that drive us to where we are.  Each one of these three events were set in motion by events that preceded them.  The important thing is to make the most out of the events we find ourselves a part of and roll with it.  Life is a series of doors you open or walk past.  So you going to open the door with the lady or the tiger? 

Remember if you end up with the tiger they like pepper; hate cinnamon.

A Thank You To New England

Thank you Grant Fritchey, Adam Mechanic, David Waugh, and all the others behind SQL Saturday all the way up to Andy Warren!  I had a great time presenting at SQL Saturday #34 (Boston) and learned some nice things to take back to the office first thing Monday morning.

About to board a flight to Chicago then onto Grand Rapids here in a few moments and just wanted to thank you for myself and for my company, Spectrum Health, who (besides being the most-awesome of companies to work for) allow me to keep up my MVP-ness and contribute to the community when possible.  It was worth spending all this time in airports this weekend.  Now it’s time to bring some  value-added back to the staff and patients I serve back home.

- Tim

PS. Logan has free WiFi?  How much does that rock?

Tags:

Speaking at SQL Saturday RicXXXXmond, er Boston. Or Mother Nature’s Reverse Cleveland Steamer.

Looks like Mother Nature is into giving certain IT professionals the old Reverse-Cleveland Steamer.  Yep, she dropped a big ol’ pile o’ cold poo on this SQL Server Presenter’s (and other’s) chests yesterday, causing the folks behind SQL Saturday #30 in Richmond to postpone until April 10, 2010.  This unfortunately coincides with Spring Break here in the Midwest and I’ll be sunning my whiteness along the Eastern shores of the Gulf of Mexico at that time.

Thanks to a phone call from Jeremiah Peschka (web|twitter) I was notified of this fact or else I would have been speaking to an empty hotel room tomorrow.  (or better yet, crashing at Andy Leonard’s (web|twitter) domicile and dining the best that any surprise house guest ever dined thanks to his lovely wife, whom I’m destined to never meet, Christy Leonard (web|twitter)).

What’s a guy to do?  His company paid for their lone MVP to keep up his MVP’ness by speaking at SQL Saturday and the airfare was non-refundable.  Well, this cunning DBA has parlayed this into two speaking sessions for up-coming SQL Saturdays:  tomorrow’s in Boston (same time, different place than the one scheduled for Richmond) and May in Portland, OR. 

I’ll be taking over Adam Machanic’s (web|twitter) slot, and will be presenting my Insight into Indexes with DMVs presentation that I had planned on presenting to Richmond.  The slide deck is the same as the one I just used for the West Michigan SQL Server User’s Group (WMSSUG) and is available here for downloading.

So Boston, as I sit here in O’Hare passing my time on a 3 hour layover (travel on the cheap) I say “see you soon!”

Tags:

CROSS APPLY

On Twitter this morning I saw a plea for an explanation on what CROSS APPLY is in Transact-SQL.  I answered the question via email and then realized that it may make for a post here on The SQL Agent Man…

When used, CROSS APPLY will execute the function listed in the CROSS APPLY sub-clause of the FROM statement in your T/SQL command for each row being returned in the query.  An example is probably easier to see the behavior:

This query will return a row for each active request on your SQL instance:

SELECT [session_id], [request_id], [user_id], [sql_handle] 
FROM sys.dm_exec_requests 
WHERE [session_id] > 50

Great, but what the heck is the sql_handle?  Well, it’s a unique identifier for the T/SQL text executing on a SQL Server.  It’s a bit cryptic, don’t you think?  Well, you can return the plain text of a sql_handle via the sys.dm_exec_sql_text() DMF by passing in the sql_handle as follows:

 SELECT * 
FROM sys.dm_exec_sql_text(0x0200000036FF532C48B2041F90C3E1269468AE086BDC1727);

But why would you want to cut/paste the sql_handle?  What if there are 3000 active requests, not just 1?  Well, by using CROSS APPLY it will run for each row in the result set and give you the following (I am just interested in the [text] column so you’ll see that I only ask to return that row from sys.dm_exec_sql_text (as DEST) in my SELECT statement:

SELECT DER.[session_id], DER.[request_id], DER.[user_id], DER.[sql_handle], DEST.TEXT 
FROM sys.dm_exec_requests DER
     CROSS APPLY sys.dm_exec_sql_text(DER.[sql_handle]) DEST
WHERE [session_id] > 50

As you can see, the CROSS APPLY results in the function call to the sys.dm_exec_sql_text being executed against each row in sys.dm_exec_requests by passing in the sql_handle from that Dynamic Management View.

Tags: ,