A Fine Slice Of SQL Server
Erik Darling is less than enthused:
The results from my Extended Event session, and… I’ll talk through my disappointment in a moment, was not terribly spectacular.
There are some promising concepts in there but as a whole, it does seem like we’ll need fingers crossed that this improves for RTM.
Comments closedTom Zika checks out some extended events:
It has been announced today (2022-05-24) during the MS Build event. The blog post includes a download link. Unfortunately, the Docker container is not quite ready yet.
Anyway, because I’m a #TeamXE, I had to check out if there are any new goodies there. So, I took an XE event list from Microsoft SQL Server 2019 (RTM-CU16) and the new one from Microsoft SQL Server 2022 (CTP2.0) and compared them.
There are a lot of new events—click through to see how many.
Comments closedGrant Fritchey tries out extended events in Amazon’s RDS:
AWS has posted the documentation on what you have to do in order to enable the collection of Extended Events within RDS. Normallly, I’d follow along with the documentation. However, I’m going to approach this like I knew that Extended Events support was there, but I wasn’t aware of the docs. So, I’m starting in SSMS and I’m just going to try plugging in the Extended Events GUI to see what happens. Further, I’m going to use the simplest method for launching Extended Events, XEvent Profiler.
Read on for Grant’s findings.
Comments closedDimitri Furman shows off a DMV:
As part of our efforts to improve database engine supportability, we have added a new dynamic management view (DMV), sys.dm_os_out_of_memory_events. It is now available in Azure SQL Database and Azure SQL Managed Instance and will become available in a future version of SQL Server. If an out-of-memory (OOM) event occurs in the database engine, this view will provide details to help you troubleshoot the problem, including the OOM cause, memory consumption by the database engine components at the time of event, potential memory leaks (if any), and other relevant information.
Read on to learn more about it, as well as a corresponding Extended Event.
Comments closedTomas Zika answers a question:
I’ve helped answer another question that appeared on the SQL Server Slack:
Are timestamps in XE event files you view in SSMS local or server time?
To test this, I need a server in a different timezone than the client (SSMS). I find the quickest and most easy tool for that to be containers – more specifically, Docker.
Click through for the answer, as well as a few Docker-related incidentals.
Comments closedGianluca Sartori’s speaking my language:
The TIG software stack (Telegraf, InfluxDB, Grafana) is a very powerful combination of software tools that can help you collect, store and analyze data that has a time attribute. In particular, InfluxDB is a time series database, built with sharding, partitioning and retention policies in mind. It is absolutely fantastic for storing telemetry data, like performance counters from SQL Server or other software products.
In order to store data in InfluxDB, you can use Telegraf, a data collection agent that takes care of extracting telemetry data from the object to observe and upload it to the InfluxDB database. Telegraf is built with the concept of plugins: each object to observe has its own plugin and it’s not surprising at all to find a specialized plugin for SQL Server.
Click through for more details and how to set it up.
Comments closedGianluca Sartori continues a series on XESmartTarget. First up, let’s analyze a workload:
The idea comes from a blog post by Brent Ozar about “How to Find Out Whose Queries are Using The Most CPU“. Brent uses the Resource Governor to detect who’s using the CPU. That’s an interesting approach, but you can do the same more efficiently with XESmartTarget.
Analyzing a workload means capturing all the queries on a server, categorize them by application name, database name and login name, and creating samples at regular intervals, in order to describe the behavior of the workload over time, let’s say every one minute.
From there, we’ll capture the queries running on our server:
In the previous recipe we used XESmartTarget to analyze a workload and we could characterize it by application/login/database. Now let’s take one more step: let’s capture all the queries and their plans and add query_hash and query_plan_hash to the analysis table. We will also capture plans and query text on separate tables.
It is fun getting to see all of the versatility in XESmartTarget.
Comments closedGianluca Sartori continues a series on XESmartTarget:
For this post, the problem to solve is this: a session has an open transaction, is blocking other sessions, it’s been sleeping for a long time and it’s probably a good idea to kill it. This usually happens when there’s a problem in the application, that doesn’t handle transactions properly and leaves open transactions for a long time, maybe because it displays an error dialog, waiting for user input. There is very little that you can do in these cases: the blocked processes continue to pile up and the only thing left to do is kill the offending session.
Let’s see how to do that with XESmartTarget.
Let’s, shall we?
Comments closedGianluca Sartori continues a series on XESmartTarget:
In the previous recipe, you learned how to combine multiple Responses together and how to control their output, using filters and Expression Columns. Armed with this knowledge, you can now tackle the most complex problems, using all the available Response types. In this post you learn how to notify when specific events occur, using the EmailResponse.
This might be a “You can, but should you?” type of target.
Comments closedGianluca Sartori continues a series on XESmartTarget:
In the previous recipe, we wrote event data to a table in the database and each event used field and action names to map to the column names in the table. The same information (the text of the command) was stored in two separate columns, depending on the event type:
– batch_text for sql_batch_completed events
– statement for rpc_completed events
SSMS has a nice feature that allows you to create a merged column using data from several columns. Here is how you do it:
Click through for that explanation.
Comments closed