Get the Source of PREEMPTIVE_OS_PIPEOPS Waits

A client reached out to me for help about a wait_type they were seeing. That’s not terribly uncommon, but the specific wait_type was something that was fairly obscure.

Every day, they could see the delta on this wait_type continue to climb higher and higher. The problem was not in their ability to capture the deltas for the biggest wait_types. The problem as it were, was in figuring out what exactly was the root cause of their biggest wait_type.

What was the wait_type? Well, the obscure wait_type was called PREEMPTIVE_OS_PIPEOPS. What causes this wait? As it turns out, this is a generic wait that is caused by SQL pipe related activities such as xp_cmdshell.

Knowing this much information however does not get us to the root cause of this particular problem for this client. How do we get there? This is another case for Extended Events (XEvents).

Tracking Pipe Ops

Knowing which wait_type needs attention is most of the battle in figuring out what precisely is causing the specific wait. The second piece of the puzzle is to understand whether the wait is an internal or external. This is essential because a different event is triggered depending on whether the wait is internal or external. There will be more on that in a future article.

Since I happen to know already that the PREEMPTIVE_OS_PIPEOPS wait is an external wait, then I also know that I will want to use the sqlos.wait_info_external. The downside to this method is that conventional wisdom teaches us that we must use the integer value for the wait in order to monitor for it. To find that integer value, one can query the sys.dm_xe_map_values dmv. Since the values of many of the waits change from CU to CU, then one should validate this value on every server instead of using the same value on all servers without regard.

Depending on which server I run that code on, I may receive different results.

As you can see, I have a small sample showing five different values for this specific wait. Using the wrong value on the wrong server could result in a bit of hair tugging since nothing may be trapped.

Due to this potential for change, writing a script to monitor for the session gets a little bit trickier and a lot more dynamic – for now.

After executing that script, the session will be created with the appropriate wait_type as well as the session will be started. To verify it is working, then I can execute a query that will use xp_cmdshell.

With that information in hand, we now have a tool that will help us identify the root cause of this wait climbing to the top of the waits list. Once I am able to identify the root cause, then I can start applying an appropriate fix (or do nothing if the situation deems that necessary).

But what about this map_key that keeps on changing with updates to SQL Server? Surely there is a better method than figuring out the integer value. As luck would have it, conventional wisdom is wrong in this case. There is a much easier method of creating this event session. So much easier in fact that I don’t need to worry about the map_key value. All I need to know is the wait_type I wish to monitor. So let’s look at a better version of the script.

This will yield the same results as the first script, but without the dynamic sql and with a more friendly script for the DBA to understand. This method is actually employed in the system_health system default session if you ever decide to peruse that session.

Wrapping it Up

Extended Events is a powerful tool with plenty of ease of use and flexibility. This flexibility allows the DBA to capably monitor the server for any issue be it small or large. This article demonstrated how to use Extended Events to monitor for a specific wait_type and the same principles can be applied to any of the waits you may need to investigate. If you are in need of a little tune-up for your XE skiils, I recommend reading a bit on Extended Events to get up to date. For other basics related articles, feel free to read here.

 

Note to Self

TSQL2sDay150x150TSQL Tuesday

The second Tuesday of the month is upon us once again. That means it is time for another group blog party called TSQLTuesday. This party that was started by Adam Machanic has now been going for long enough that changes have happened (such as Steve Jones (b | t) managing it now). For a nice long read, you can find a nice roundup of all TSQLTuesdays over here.

Sands of Time

This month, MOHAMMAD DARAB (b | t) invites us to do the 40 yr. old self reflection. You know that age that is almost as dreadful to reach as the righteous age of 50? From there age only gets better – or at least I have been told that. I have done a couple of articles similar to this in past TSQLTuesdays. Similar yet still different just enough. Check out my article on passion,  or this on one influence.

The problem with this particular challenge is the request to write a note to your 20 yr. old self. Well, maybe not an extreme problem other than the time travel part. As luck would have it, I recently discussed the topic with my grandmother. There was a lot of wisdom in what she taught me as we discussed the topic and I must admit, I was in agreement prior to even broaching the topic with her.

Passing Notes

Here is basically what my note to myself would say: “Self, you are doing good. Keep it up!”

That is even if I chose to send myself a note. You see, I would not want to change how things have happened. Life experience is good – both the good and the bad. All of it is necessary to grow and develop. Just like Mater’s dents, each experience is valuable and shouldn’t be changed.

Now, what if I were to create a note of advice for other more junior level professionals that I might be mentoring? That is a different story. My note to them would be simple – “Listen to me.” Maybe not so literally “me” and maybe more precisely as advice to listen to anybody who is giving their time to present, mentor, blog, or write (articles, books, etc). They are imparting of their experience and time and you should respect that and take advantage of it.

Not only should you pay respect to those giving of their time, but you should spend a little time to also give back to the community. Why? Well, I covered that pretty well in this article here. I will say it again, the more you give of yourself to help others learn your trade, the more you will personally learn and grow in your professional and personal self. It is an investment well worth taking.

 

Wrapping it Up

I don’t believe in trying to alter who I have become with any sort of visit back to my past self. The changes could cause me to change my path and not end up where I have reached due to personal growth and experience. Trial and error is good for growth. Everybody needs to learn that for themselves and cannot be forced into it. Just learn to grow and be patient with anybody you may mentor.

Easy Open Event Log Files

One of the beauties of using a tool such as Extended Events (XEvents) is the versatility and ease of use. XEvents can be used for so many things to help an aspiring DBA or Developer do their job better.

There has long been a sticking point about allowing various people access to production servers. Part of the sticking point is the developer who believes that access to read and evaluate trace files is mandatory on prod (yes I have heard many times where this has been used effectively by developers to gain prod access). But is that prod access truly necessary? Without an adequate method to provide the developer access to the trace data, the DBA loses confidence (from management) and standing ground for their side of the argument. How can a middle ground be met?

XEvents to the Rescue

With the use of XEvent tracing, a DBA is given a new realm of possibilities. This particular realm of possibilities becomes available when the XEvent trace (session) is created using a file target. Through the use of a file target, and the flexibility of XEvents, I have shown the beginnings of how a developer might be able to access the trace data easily.

That is merely the beginning though. There are a few more layers to making life easier for both the developer and DBA in regards to fetching event trace data. This article will show a few methods that will help make life just a bit easier.

Let’s start with the basics. After an XEvent session is created, it is important to know where the event file is being stored. If you don’t happen to know where it is, that is not a problem. The file source is easy enough to find and I have detailed it in this article.

Method 1

As shown in the previous article, the first quick method to open an XEvent trace file is from within SSMS as shown here.

After clicking on “Merge Extended Event Files…”, a new window titled “Merge Extended Event Files” will open. From the new window, follow the following steps.

Method 2

Now, that is an extremely simple method to open and view a trace file for XEvents. That said, would you believe there are other equally easy methods? Let’s look at the next method that is very simple as well.

As was previously mentioned, you will need to know the file location first and then navigate to that location. So let’s do that. My common location is C:\Database\XE. Once navigated to the folder, I need to choose my file to be opened and follow the prompts as shown here.

A new window will pop up. By default, SSMS will be showing. If not, you can select SSMS and enable the option to always use SSMS for this file type by clicking the check box before clicking OK.

Method 3

If the prior two methods feel unreasonably easy, then there is this next method that will spice things up a bit. It does require a bit of coordination. Once you have identified the file to be opened, you can elect to perform a drag and drop operation of the file onto SSMS. Once done properly, the file will open in SSMS and you can start evaluating the data.

Method 4

This is as equally simplistic as the previous method but requires just a scoch less coordination. This method involves a double-click method. A double-click on the file will open the file up in SSMS.

Method 5

If you paid close attention to the screenshots in method 2, you will have noticed that there was a menu option called “Open” that I did not discuss. If you select open from the context menu at that point, then the xel file will open in SSMS just like any of the other methods mentioned thus far.

Wrapping it Up

Extended Events is a powerful tool with plenty of ease of use and flexibility. This flexibility allows the DBA to better service the needs of the developers when the developers need access to the trace data. This article demonstrated another use for Extended Events. If you are in need of a little tune-up for your XE skiils, I recommend reading a bit on Extended Events to get up to date. For other basics related articles, feel free to read here.

Implicit Conversion Insights with XE

Implicit

In a recent article, I covered a little bit about implicit conversions and an error that may crop up from time to time. It is one thing to understand what an error means, it is an entirely different problem to understand what is causing the error in the first place.

Seldom does a DBA have the opportunity to get out in front of infrequent or random errors such as implicit conversions. More often than not, it is the privilege of the DBA to find out about the problem after the fact from a developer or, worse yet, an end-user.

What if there was an easy method to track when these errors occurred? Even better, it would be ideal to understand what query caused the error as well as which types of queries are merely causing implicit conversion warnings. Even better, what if you had a way to understand which type of conversion problem is at hand.

Implicit Playground

To start this playground, let’s begin with a simple query based on the AdventureWorks database.

This query will yield a plan similar to the following.

If I hover the select operator, I will see a little window like the following.

I have a few items highlighted in this window. In the larger red box, I am outlining much of the type conversion warning. Inside of that, there is a blue box highlighting the “convert_implicit” warning. If I look further into the error message, there is text about query plan choice and “CardinalityEstimate” or “SeekPlan” for the different convert_implicit warnings. Another way of looking at it is as follows.

This seems a lot more friendly than that little context window. Here we have an easy to follow layout of what is happening. As I drill down into the warnings, I can see there are three convert_implicit warnings and they all trigger a “planaffectingconvert” type of warning. As we look closer at this, I can see there are two distinct types of convert issues.

There is one for the conversion from nvarchar to int in the HumanResources.Employee table for the NationalIDNumber field (green).

Two warnings that state that an index seek has been disabled due to the conversions ( orange and purple).

Curious to see what all of the possible “ConvertIssue” values could be for these implicit conversions? That is easy enough with the following query.

Which shows us the following results.

That is a grand total of three. Having covered the cause of two of these already, all that is left is the type called “unknown”. I think we can safely presume that the cause of this one is something else that is undocumented and hence “unknown”.

So far, so good. We have seen how these warnings can crop up in execution plans. We have also seen how to query the for the “ConvertIssue” types or “plan_affecting_convert_type” from the Extended Events (XE) maps system DMV.

Right about now, the light bulb should be popping up for you. If we can query the XE metadata for the different convert issue types, then does that mean there is a way we can use XE to track these things and review them at our own convenience? Yes there is!

XE

Without further adieu, let’s look at an XE session that can be useful in discovering more information in regards to implicit conversions in the database environment.

This session is pretty comprehensive with the ability to be very much like a fire-hose session if the commented out events are uncommented prior to session creation. Running this on a busy server ( less than 1 minute) for a client with a third party vendor application produces results that look like the following.

As we can see here, the session is trapping when I have any of those pesky implicit conversion errors (red box outline) as mentioned in the previous article, as well as the convert_implicit warnings (purple box outline) that were demonstrated in the execution plans in this article.

How am I trapping the implicit conversion error? That is done via the following piece of the event session shown above.

Error number 257 is the message id for the implicit conversion error. When filtering on that ID for this event, then I will receive only the implicit errors that match that number.

As for the implicit conversion warnings, we have the following that accomplishes that requirement.

The event, plan_affecting_convert should seem eerily familiar at this point. We have seen terminology like that in the execution plans as well as in the query for the map values.

The combination of these events will provide great insight into the issues revolving around any implicit conversions you may be having in your database environment.

Wrapping it Up

Implicit conversions are a fail-safe for when bad design or code (or both) crops up in your environment. When this fail-safe is being used in your environment, it is helpful to gain further insights via Extended Events. This article demonstrated another use for Extended Events. If you are in need of a little tune-up for your XE skiils, I recommend reading a bit on Extended Events to get up to date. For other basics related articles, feel free to read here.

 

Implicit Conversion Fail

Implicit

Every now and again, you may run into an error about implicit conversions in your queries. Implicit conversions are not uncommon in the computing world and can be viewed as kind of a fail-safe for when we don’t quite follow decent practices when designing the database or when writing queries or both.

Despite this little fail-safe, there are times when a nasty little error will pop up and cause a bit of consternation.

Implicit conversion from data type %ls to %ls is not allowed. Use the CONVERT function to run this query.

What Went Wrong

Unlike many other errors in SQL Server, this error message makes some sense. The major components of what is wrong are present and you are given a decent idea of what the conversion attempt is that failed. When this particular error happens, you can bet that there are issues with some TSQL code somewhere for sure. In addition, you can bet there is likely a problem with the database design as well. Yay! More work for your back burner.

First, this error comes with an error id of 257 and we can see the message text via the following query. This id is important for when we want to monitor for this problem in the future.

Let’s see how we can recreate this problem.

Which will produce the following.

This is a prime example of a bad query producing an error. Obviously, I am using the wrong data type to try and query the temp table. The ImplicitID column is an integer and I am trying to query it using a date. The quick fix, would be to query the appropriate date column if I must use a date in my query, or i can use an integer to query the ImplicitID column.

After this minor tweak, now the query will work and I start to see results. Given the random nature of the data in this query, the results will vary from batch to batch.

Wrapping it Up

Implicit conversions are a fail-safe for when bad design or code (or both) crops up in your environment. Sometimes, this fail-safe is inadequate and needs further assistance. Sometimes, that may be an explicit conversion and sometimes that means an appropriate rewrite of the query to use the appropriate columns and data types in the queries. This was an introductory article into the world of implicit conversions. There will be a follow-up or two about implicit conversions and monitoring for them. In preparation for the upcoming articles, I recommend reading a bit on Extended Events to get up to date. For other basics related articles, feel free to read here.

 

«page 3 of 132»

Calendar
August 2019
M T W T F S S
« Jul    
 1234
567891011
12131415161718
19202122232425
262728293031  

Welcome , today is Wednesday, August 21, 2019