A Fine Slice Of SQL Server
Michael Swart notes that cursor performance can be hidden from monitoring tools:
The
OPENstatement is missing fromsys.dm_exec_query_stats. I want to demonstrate that.
There are a few workarounds, and Michael even provides us a handy table, so read the whole thing.
Comments closedKevin Eckart shows us how to get table-valued function execution plan details:
While the estimated gives us all kinds of information, the actual plan keeps the underlying operations hidden in favor of a Clustered Index Scan and a TVF operator. This isn’t very useful when it comes to troubleshooting performance issues especially if your query has multi-table joins to the TVF.
Thankfully, this is where Extended Events (EE) comes into play. By using EE, we can capture the Post Execution Showplan that will give us the actual full plan behind the Clustered Index Scan and TVF operators.
As Kevin notes, this extended event runs the risk of degrading performance, so don’t do this in a busy production environment.
Comments closedGail 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 closedTim 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 closedErik 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 closedDerik 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 closedPaul 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 closedJonathan 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 closedCristian 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 closedKoen 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