Press "Enter" to skip to content

Category: Query Tuning

How Do Natively Compiled UDFs Perform?

Published 2016-01-14 by Kevin Feasel

Gail Shaw investigates natively compiled user-defined functions in SQL Server 2016:

When I saw that, the first question that came to mind is whether natively compiling a scalar function reduces the overhead when calling that function within another query. I’m not talking about data-accessing scalar UDFs, since natively compiled functions can only access in-memory tables, but functions that do simple manipulation of the parameters passed in. String formatting, for example, or date manipulation.

While not as harmful as data-accessing scalar UDFs, there’s still overhead as these are not inline functions, they’re called for each row in the resultset (as a look at the Stored Procedure Completed XE event would show), and the call to the function takes time. Admittedly not a lot of time, but when it’s on each row of a large resultset the total can be noticeable.

Read the whole thing and check out Gail’s method and conclusions.

Comments closed

Starting Query Tuning

Published 2016-01-08 by Kevin Feasel

Tim Radney has in introduction on how he tunes a SQL Server instance:

Typically the common compliant when someone’s stating they need to tune a SQL Server is that it’s running slow. What does slow mean? Is it a certain report, a specific application, or everything? Did it just start happening, or has it been getting worse over time? I start by asking the usual triage questions of what the memory, CPU, and disk utilization is compared to when things are normal, did the problem just start happening, and what recently changed. Unless the client is capturing a baseline, they don’t have metrics to compare against to know if current stats are abnormal.

Tuning is about method and tools (in that order).  I like the way Tim does both.

Comments closed

Parallel Horizontal

Published 2016-01-06 by Kevin Feasel

Erik Darling looks at operators which result in serial plans:

In the past, there were a number of things that caused entire plans, or sections of plans, to be serial. Scalar UDFs are probably the first one everyone thinks of. They’re bad. Really bad. They’re so bad that if you define a computed column with a scalar UDF, every query that hits the table will run serially even if you don’t select that column. So, like, don’t do that.

What else causes perfectly parallel plan performance plotzing?

Commenting on one of his comments, I can name at least one good reason to use a table variable.

Comments closed

Tuning SQL Server Backups

Published 2015-12-23 by Kevin Feasel

Derik Hammer has a post on tuning SQL Server backups:

Finally, do not forget about your memory. To backup or restore a database you have to load data pages into memory. We will talk more about memory below and how the internal buffer pool comes into play and can cause operating system paging or out of memory conditions.

Derik shows the various knobs and switches available, and I want to emphasize one thing:  optimizing backup statements involves testing different scenarios.  You can make good guesses as to the appropriate MAX_TRANSFER_SIZE or BUFFERCOUNT, but even then, test different combinations and find what works best for each database.

Comments closed

Optimizing Update Queries

Published 2015-12-22 by Kevin Feasel

Paul White has an article.  Read it:

The point is that there is an awful lot more going on inside SQL Server than is exposed in execution plans. Hopefully some of the details discussed in this rather long article will be interesting or even useful to some people.

It is good to have expectations of performance, and to know what plan shapes and properties are generally beneficial. That sort of experience and knowledge will serve you well for 99% or more of the queries you will ever be asked to tune. Sometimes, though, it is good try something a little weird or unusual just to see what happens, and to validate those expectations.

Optimizing update queries seems trivial at first, but as Paul shows, we have a few more tools at our disposal than is apparent at first glance.

Comments closed

Beware Certain Extended Events

Published 2015-12-14 by Kevin Feasel

Jonathan Kehayias warns us about using query_post_execution_showplan in production:

During a recent client engagement to look at the performance problems of a production SQL Server I ran into something I hoped to never see in real life. For a long time I’ve been a proponent of Extended Events in SQL Server, and I’ve really looked forward to seeing them used more and more inside of SQL Server for diagnostics data collection, especially by third party software vendors. I’ve done a lot of performance tests comparing Extended Events to SQL Trace and Extended Events generally has lower performance impact to SQL Server performance. However, I have also found and demonstrated how specific events like collecting the actual execution plan with Extended Events can severely impact a server’s performance. Any time Erin or I talk about Extended Events, whether it is at a conference or in our IEPT02 – Performance Tuning class, one of the key things we both repeat to people is that if you see an completely unexplainable drop in server performance and throughput, check for Extended Events sessions and Traces running on the server and look at what events are being collected. For Extended Events we always demo the impact of the query_post_execution_showplan event live to demonstrate why this event should not be used in production environments ever. Yesterday I was incredibly surprised to not only find the event in an Event Session on the affected server, but also to find that it came from Idera Diagnostic Manager’s Query Monitor feature.

If you’re using Diagnostic Manager version 9, check to make sure this event is not turned on, as it’s a performance killer.

Comments closed

Entity Framework Performance Problems

Published 2015-12-10 by Kevin Feasel

Cristian Satnic points out potential performance issues with using Entity Framework:

Entity Framework will not exactly issue SELECT * FROM commands – what it will do though is have explicit SELECT statements that include ALL columns in a particular table. If you see that most SQL queries are selecting all columns this way (especially from large tables when it appears that the UI is not using all that data) then you know that developers got a little sloppy with their code. It’s very easy in Entity Framework to bring back all columns – it takes more work and thought to build a custom LINQ projection query to select only the needed columns.

It is taking all of my strength not to say “A tell-tale sign of an Entity Framework performance problem is seeing Entity Framework in your environment.”

Satnic’s advice isn’t EF-specific, but his link to Microsoft guidance is.

Comments closed

Tuning With BlitzIndex

Published 2015-12-08 by Kevin Feasel

Koen Verbeeck on using sp_BlitzIndex:

What’s great is that this script also provides you with the URLs to knowledge articles on the Brent Ozar website. If you don’t understand one of the results, you can immediately look it up and read about it.

By focusing on the results of sp_BlitzIndex script, I could boost performance in just a few hours of work. This near real-time data warehouse is the source for a reporting application used by dozens of people in the field, and you could immediately tell it worked a lot faster. Awesomesauce.

There are several interesting scripts in the suite; check them out if you’ve not already.

Comments closed

Actual Number Of Rows Read

Published 2015-12-07 by Kevin Feasel

Kendra Little shows us something new, the Number of Rows Read tag in an execution plan:

Execution plans got a cool new piece of diagnostic information in SQL Server 2012 SP3, and we believe we’ll soon have this change in SQL Server 2014 and the coming SQL Server 2016: “Number of Rows Read”.

In fancy language, this is “better diagnostics” when a query plan has “residual predicate pushdown” (KB 3107397).

In human language, SQL Server will now tell you “How many rows did I really have to read, even if I have a hidden filter in here?”

This appears in actual execution plans only. Sorry, there is no such thing as “Estimated Number of Rows Read” that I can find.

This is another piece of useful information now available in execution plans.  Hopefully it’ll be supported in SQL Server 2014 and 2016 soon enough.

Comments closed

Removing Bad Execution Plans

Published 2015-12-07 by Kevin Feasel

Andrea Allred shows one way of removing a bad query plan:

If you click on the query_plan link, you can see what the plan looks like.  After you have reviewed it and determined the plan is bad then you can paste your plan handle over the one below to remove it from the proc cache.

DBCC FREEPROCCACHE normally is something you don’t want to play with in production, but this is narrowly focused enough not to harm you down the line.

Comments closed