A Fine Slice Of SQL Server
Monica Rathbun looks at tempdb:
Tempdb is always a topic for me whether it’s in my sessions or blogs I have written. However, I’ve never been so excited about it then I am when it comes to the dramatic performance changes introduced in SQL Server 2022. THEY HAVE SOLVED ONE OF OUR BIGGEST PERFORMANCE BOTTLE NECKS, System page latch concurrency.
Click through for the details.
Comments closedJared Poche looks at the recent past:
Tempdb contention has long been an issue in SQL Server, and there are many blogs on the issue already. But I wanted to add one more mainly to highlight the improvements in recent versions of SQL Server
Tempdb contention is most often discussed in as relating to the creation of temp tables (and other objects) in tempdb. If you are experiencing this you will see PAGELATCH_EX or PAGELATCH_SH waits, frequently with wait resources like 2:1:1 or 2:1:3. This indicates contention in database 2 (tempdb), page 1 (the first data file in tempdb), and one of the PFS, GAM, or SGAM pages (which are pages 1, 2, and 3 respectively). Tempdb files of sufficient size will have additional PFS, GAM, and SGAM pages at higher page numbers, but 1 and 3 are the pages most often referenced.
Read on to see what the SQL Server team has done over three of the past four versions of SQL Server to limit tempdb contention.
Comments closedMatthew McGiffen searches for the root of the problem:
In this post we look at a method using Extended Events (XE) to identify what parent objects are calling a given SQL function and how often.
The background is that I was working with a team where we identified that a certain scalar function was being executed billions of time a day and – although lightweight for a single execution – overall it was consuming significant CPU on the server. We discussed a way of improving things but it required changing the code that called it. The problem was that the function was used in about 700 different places across the database code – both in stored procedures and views – though the views themselves would then be referenced by other stored procedures. Rather than update all the code they’d like to target the objects first that execute the function the most times.
Read on to see how Matthew did it, as well as some caveats along the way.
Comments closedRecently I had a situation where I was looking at a SQL Instance due to a contractor who controls the system wanting to drop some indexes and they couldn’t because they couldn’t get a lock request. Sadly, they were using the GUI to try to accomplish this. Always makes me sad when we aren’t using scripts. So, I took a deep drive into what was actually running on this system. I had them send over scripts for what they were trying to do. Meanwhile, I looked at the system and discovered some things. First, we had really had RESOURCE_SEMAPHORE wait types for two days which indicates it waiting on memory. So, I took a look at the memory clerks. The top memory clerk was for lock manager at 32 GBs and the system only has 64 GBs, which is abnormal.
Read on for the full story. This particular fix worked, I presume, because the ORM was sending all parameters as Unicode, whereas none of the strings in the database were Unicode—they were all VARCHAR rather than NVARCHAR.
Erik Darling has a video for us. There’s no graf for me to use as a lead-in quotation, which is why I haven’t been linking to a lot of the videos. Erik’s putting out a lot of great video content and this is no exception, so check it out.
Comments closedEtienne Lopes troubleshoots a strange issue:
After the downsizing (to GeneralPurpose: Standard-series (Gen5), 2 vCores) occasionally there were timeouts in the application for a very specific task (the command timeout property in the application was set to 30 seconds). Other times the very same task would execute immediately, as it should always, since the underlying query was actually quite simple: a SELECT to a single, although large table (58 GB) but with a predicate that would always result in a perfect index seek to return never more than 300 rows. Furthermore each time there were timeouts, there were also momentary I/O spikes up to 100%:
Read on to learn more about what caused this problem and how Etienne was able to resolve it.
Comments closedShalu Ganotra Chadra, et al, explain what Synapse Genie is:
The Genie framework is a metadata driven utility written in Python. It is implemented using threading (ThreadPoolExecutor module) and directed acyclic graph (Networkx library). It consists of a wrapper notebook, that reads metadata of notebooks and executes them within a single Spark session. Each notebook is invoked on a thread with MSSparkutils.run() command based on the available resources in the Spark pool. The dependencies between notebooks are understood and tracked through a directed acyclic graph.
Read on for more information about how you can use it and what the setup process looks like.
Comments closedAjay Dwiveldi does some digging:
In the above dashboards, I could clearly notice PAGELATCH_** wait at the top along with SOS_SCHEDULER_YIELD. The presence of the above 2 waits is indicative of high CPU issues due to contention on the access of data file pages. I validated and found that this PAGELATCH_** wait is present almost all the time on the server. So decided to check the data of dbo.WhoIsActive that stores captured data of sp_WhoIsActive in SQLMonitor tool.
Read on for the outcome.
Comments closedAmit Kumar shares a few tips with us:
RDD does serialisation and de-serialisation of data whenever it distributes the data across clusters such as during repartition and shuffle, and we all know that serialisation and de-serialisation are very expensive operations in spark.
On the other hand, DataFrame stores the data as binary using off-heap storage, no need for deserialization and serialization of data when it distributes to clusters. We see a big performance improvement in DataFrame over RDD
Click through for several additional tips.
Comments closedRyan Lambert gets a lot of explanation:
Postgres has a handy module called
auto_explain. Theauto_explainmodule lives up to its name: it runsEXPLAINautomatically for you. The intent for this module is to automatically provide information useful for troubleshooting about your slow queries as they happen. This post outlines a pitfall I recently discovered withauto_explain. Luckily for us, it’s an easy thing to avoid.I discovered this by running
CREATE EXTENSION postgis;and watching it run for quite a while before failing with an out of disk space error. That is not my typical experience with a simpleCREATE EXTENSIONcommand!
Read on to learn what happened and how you can prevent making a similar mistake.
Comments closed