A Fine Slice Of SQL Server
Grant Fritchey answers an interesting question:
Strictly speaking, a recompile isn’t really a performance tuning problem. In fact, a lot of time, recompiles are desired because they reflect changes in statistics which are likely to need a new plan. However, you can get really excessive levels of recompiles because of a variety of different issues. So, identifying the causes can be a pain. Here’s one way to get it done.
Click through for an extended event which does the job.
Comments closedJess Pomfret breaks out the lab coat and safety goggles:
I found myself needing to clear out a large amount of data from a table this week as part of a clean up job. In order to avoid the transaction log catching fire from a long running, massive delete, I wrote the following T-SQL to chunk through the rows that needed to be deleted in batches. The question is though, what’s the optimal batch size?
I usually go with a rule of thumb: 1K for wide tables (in terms of columns and row size) or when there are foreign key constraints, 10K for medium-width tables, and about 25K for narrow tables. But if this is an operation you run frequently, it’s worth experimenting a bit.
Comments closedEd Pollack continues a series on extended events:
While using Extended Events is not overly complex, building a reliable system to collect, parse, and store events over time without any data loss can be challenging.
This article dives into one method for collecting and retaining all event data for a specific set of events. A system like this is highly customizable and provides a solid starting point for Extended Events development. This should be viewed as a collection of puzzle pieces; individual pieces can be adjusted as needed to produce a monitoring solution that fits the needs of a given situation, even if it is vastly different from what is demonstrated here.
Read on for the process. Shredding XML isn’t pretty, but the good news is that with a setup like this, you only need to do it once…unless you need to change it later, so get it right the first time and bam, problem solved.
Comments closedGrant Fritchey shows us another use of extended events:
I was recently approached at work about a company that was seeing tons of timeouts in SQL Server. I was asked how to troubleshoot this. There are lots of posts by people on this topic, but I found something I didn’t see anywhere else, let me share it with you.
Read on to see how, though as Grant notes, it’s not as easy as searching for “login_timeout” or something named similarly.
Comments closedJack Vamvas shows how to track SQL Server logins:
I have to capture logon information details for a specific logon on a SQL Server. Specifically – the client_hostname, nt_username & username. What i’m looking for is a log recording a successful connection made to the server. The event should be triggered a) when a connection is made & b) from a connection pool.
Click through to see how.
Comments closedEd Pollack takes us through the basics of extended events in SQL Server:
Extended Events are an excellent way to collect data about a SQL Server that provides a vast array of events that can be used for performance monitoring, troubleshooting, or auditing a server. In this article, I’ll explain the building blocks of Extended Events data collection.
While using Extended Events is not overly complex, building a reliable system to collect, parse, and store events over time without any data loss can be challenging.
This article walks through the steps to create, configure, and implement Extended Events in SQL Server, providing the prerequisite code and concepts to build an automated collection process.
Read the whole thing.
Comments closedDavid Fowler sets us straight on query timeouts:
Not very helpful, right? And unless you’ve got some sort of monitoring in place, for example you can find these in SQL Sentry by looking for aborted queries, or you’re picking these up in your applicaiton logging, you’re going to have a hard time tracking down what query actually tripped the timeout. As you’re reading this post, I’m going to assume that you haven’t got monitoring.
So how do we go about figuring this out?
Click through for the answer.
Comments closedGrant Fritchey gives us a technique to filter extended events:
Did you know, you can use Actions to Filter Extended Events? Well, you can. Filtering is one of the greatest ways in which Extended Events differentiates itself from other mechanisms of gathering information about the behavior of SQL Server. You can put Actions to work in your filtering. Best of all, the Actions don’t have to be collected in order to put them to work filtering your Extend Events.
Read on to see how.
Comments closedIf you want to troubleshoot a slow SQL Server Restore or just find out more about all the restore steps , use these techniques to give you an insight
Click through for the extended event session as well as a script to shred the resultant XML and a sample output.
Comments closedGrant Fritchey is doing it live:
Once you’ve created an Extended Events Session that is output to Azure Storage, you’ve done most of the work. The trick is really simple. Get the Azure Storage account set up with a Container. Create a Shared Access Signature (SAS) with the right permissions (rwl, read, write, list). Get the token from the SAS (it’s a long string). Use it, along with the path to the container to create a Database Scoped Credential. Create the session using the same path and container that you defined in the Credential. Done. You’ve got an Azure Extended Events session gathering data for you and outputting to a file in Azure Storage.
Now, what I’d like to tell you is that you can open up the Live Data window from SSMS. You can’t.
Grant does give us a workaround which kind of does the trick, but this is an obvious place where some additional developer care would be valuable.
Comments closed