Search Results for: trace flag

Plan Metrics Without the Plan: Trace Flag 7412

I place a lot of emphasis on capturing actual execution plans because of the runtime metrics, but with Trace Flag 7412, we don't need the plan. This is great news, because capturing execution plans, even using extended events, is an expensive proposition. However, using either the query_thread_profile event, or, Trace Flag 7412, we can get the runtime metrics without the plan. Trace Flag 7412 Here's how it works.You can either be running the extended event, query_thread_profile (a debug event, but one documented and supported by Microsoft) or, enable the Trace Flag 7412. I like to use the extended event in a targeted fashion to easily see behaviors on a query without having to capture the plan. You can even capture the information and then combine it with an estimated plan…
Read More

Trace Flags in Azure SQL Database

One of the ways that you take more direct control over your SQL Server instances is through the use of trace flags. There are a number that people recommend you enable by default. Prior to Extended Events for example, I'd say you should turn on trace flag 1222 in order to capture deadlock information on your server (now I just recommend you use the system_health session). I absolutely think you should turn on trace flag 2371 to get better behavior out of your automated statistics updates. There are others that I'll leave to all the systems experts to advise you on. What about Azure SQL Database? I doubt you'll be shocked, but if I try this: DBCC TRACEON (2371,-1); I get the following error: Msg 2571, Level 14, State 3,…
Read More

Youtube Channel Update: 3 August 2018

I've posted a number of new videos to the Youtube channel that might be of interest if you're a data professional. First up, I've started migrating my Database Fundamentals posts over to Youtube as SQL Server Fundamentals. The first one is available: [embedyt] https://www.youtube.com/watch?v=NHkzj9ZRhbk[/embedyt] Speaking of DBAs, the job is not going away, but it is changing. Find out how to survive the change: [embedyt] https://www.youtube.com/watch?v=loUwmKQ_Eg4[/embedyt] Would you like to know how the Query Store works? Here's a getting started video: [embedyt] https://www.youtube.com/watch?v=XK8rBO9R43c[/embedyt] I also have a good video on how to combine capturing query metrics along with wait statistics using Extended Events: [embedyt]https://www.youtube.com/watch?v=nYyTSxry03A&t=97s[/embedyt] Wait until you see what Trace Flag 7412 can do for you. It's very cool. [embedyt]https://www.youtube.com/watch?v=ei2zJwZyRic&t=79s[/embedyt] Finally, if you are interested in attending one of my…
Read More

Statistics Use, Extended Events and Execution Plans

Query tuning ain't easy. Figuring out which index is getting used is one step, and generally simple, look at the execution plan to see which index is in use and whether it's being used in a SEEK or a SCAN. Done. However, when your index isn't being used, how do you tell how or why something else is being done? Well, that's largely down to row counts which brings us to statistics. Which Statistics are Used Years ago I was of the opinion that it wasn't really possible to see the statistics used in the generation of a query plan. If you read the comments here, I was corrected of that notion. However, I've never been a fan of using undocumented trace flags. Yeah, super heroes like Fabiano Amorim and…
Read More

Same Query, Different Servers, Different Performance. Now What?

Based on the number of times I see this question on forums, it must be occurring all the time. You have two different servers that, as far as you know, are identical in terms of their options and setup (although not necessarily in terms of power, think a test or pre-production system versus production). On these servers you have a database on each that, as far as you know, is the same as the other in terms of options, objects, maybe even data (although, this does mean that you have unmasked production information in your QA environment, which potentially means you're going to jail, might want to address this, especially now that I've told you about it, mens rea, you're welcome). On each database you run, as far as you know, the exact same query (whether…
Read More

Expert? Ha!

How do you define an expert? My personal definition: An expert is the person that is a chapter ahead of you in the book. Why am I talking about this? Just that I’m feeling more stupid than usual lately. In the last two weeks I’ve had people bring up through various discussions, documents, what have you, four different SQL Server trace flags that will affect how SQL Server builds execution plans and I’ve never heard of any of them before. I’ve never, ever, thought of myself as an expert in execution plans, despite having written a book about them. I just thought I had a good grasp on how they worked and I was willing to share. I didn’t know everything and never pretended to, but I thought I knew…
Read More

Make the Optimizer Work Harder

One of my favorite indicators for whether or not you have a good execution plan is when you see the “Reason for Early Termination” property in the TSQL operator like this: The optimizer considered this particular plan “Good Enough.” which is what you want to see. When you see “Timeout” as the reason, that’s an indication that the plan you have may be sub-optimal. The question is, can you make the optimizer spend more time on your queries. Well, actually, the question is, should you make the optimizer spend more time on queries. During my session on SQL Cruise I answered the original phrasing of that question, no. As usual when I present in front of people smarter than I am, I was wrong. Brent Ozar (blog|twitter) pointed out that…
Read More

Can You Force A Parallel Plan in Query Store?

I love the questions I get when presenting: Can You Force a Parallel Plan in Query Store. I haven't a clue. The trick I think is going to be in setting up the test. Let's try it out. Reliably Getting a Parallel Plan Because this is for testing, rather than try to build some crazy query that may or may not go parallel, I've decided to cheat. I'll take advantage of a little functionality that ensures I see a parallel plan when I want to. Here's my code: DBCC TRACEON(8649); GO SELECT soh.OrderDate, soh.ShipDate, sod.OrderQty FROM Sales.SalesOrderHeader AS soh JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.SalesOrderID = 43705; GO DBCC TRACEOFF(8649); Traceflag 8649 will force all plans to go parallel by effectively making the Cost Threshold for…
Read More

OPTIMIZE FOR Hints When Parameter Sniffing is Turned Off

While presenting recently and talking about dealing with bad Parameter Sniffing, I got the question; what happens to OPTIMIZE FOR hints when parameter sniffing is disabled? This is my favorite kind of question because the answer is simple: I don't know. Parameter Sniffing For those who don't know, parameter sniffing is when SQL Server uses the precise values passed into a query as a parameter (this means stored procedures or prepared statements) to generate an execution plan from the statistics using the value from the parameter. Most of the time, parameter sniffing is either helping you, or is not hurting you. Sometimes, parameter sniffing turns bad and hurts you quite severely. Usually, but not always, this is because you either have severely skewed data (some data is very different than the rest, lots…
Read More

Parallelism and Columnstore Indexes

Columnstore indexes are fascinating and really cool. Unfortunately, they're adding an interesting new wrinkle to an old problem. What's the Cost Threshold for Parallelism set to on your server? If you just said "The whatsis of whositz?" then the value is 5. The cost threshold is the point at which the estimated cost of an execution plan goes from definitely serial to possibly parallel. This default was set for SQL Server 2000 and hasn't been changed since. I've long argued, loudly, that it's too low. I've suggested changing it to a much higher value. My advice has gone from 35 to 50 and several places in between. You could just look at the median or the mode of costs on your system and use the higher of those values as…
Read More