15Aug 2022 by Grant Fritchey 1 Comment

Distributed Replay: The Little Engine That Almost Could

SQL Server
Honestly, sincerely, no kidding, I love Distributed Replay. Yes, I get it. Proof positive I'm an idiot. As we needed proof. To be a little fair to me, I love what Distributed Replay could have been, with a little more love. However, fact is, it's on the deprecation list for 2022. Which means, what minimal amount of love, if any, that Microsoft was giving to it, it's all gone, forever. Unlike the Little Engine That Could, turns out that Distributed Replay was the Little Engine That Almost Could, But Didn't. Really Didn't. Let's discuss it a bit. Distributed Replay The concept is wonderful. Capture a bunch of queries from your production system. Replay them on a non-production system for testing. Add in the idea of being able to chain together…
Read More
09Feb 2022 by Grant Fritchey 6 Comments

SQL Server, PostgreSQL and the Future

PostgreSQL, SQL Server
This is just a quick note to talk about the future, mine, yours and this blog. First, I'm not abandoning SQL Server. I'm actively working on a revision of the query performance tuning book (something I should be working on instead of writing this post). SQL Server is my first, and best, database love. We're not going anywhere, SQL Server and I. You can expect more posts on SQL Server, query tuning, execution plans, DevOps, community, #sqlfamily, and everything I've blogged about for the last, ooh, 14 years next month. Second, our family is growing. Just like when a family brings home a new baby, well, I'm adding another database. It doesn't mean I don't love my first database. I still do. We're just going to ALSO be talking about…
Read More
03Jan 2022 by Grant Fritchey 4 Comments

Identifying Recompile Causes

SQL Server
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. Extended Events for Recompile Causes You knew I was going there. Seriously though, we know that, since SQL Server 2005, all recompiles are at the statement level (unless you call for a recompile of a procedure or create that proc with a RECOMPILE hint). So, capturing the recompiles and getting grotty on the details means capturing statements. Understand that this can be expensive, especially in terms…
Read More
29Nov 2021 by Grant Fritchey No Comments

Get SQL Server 2022 Now: It’s Called Azure

Azure, SQL Server
If you're like me, your palms went into a cold sweat to get your grubby fingers on the bits for SQL Server 2022 a few nanoseconds after you heard the announcement. Well, you might be waiting a while. As will I. However, I really do want to start experimenting with some of the functionality right now. If you do to, then join me at Azure SQL Database. Azure SQL Database as a Preview for SQL Server 2022 Yeah, I know it's not the same. I get it. You have a need to run this stuff on big iron, not virtual, and certainly not in the cloud. Still, you want to learn, right? Tons of the new functionality is either already in Azure or incorporates Azure functionality. You can get started…
Read More
06Jul 2021 by Grant Fritchey No Comments

Permissions Needed To Force Plans in Query Store

SQL Server
I was recently asked what permissions were needed to force plans in query store. I'm sure I knew at one point, but at the moment I was asked, I couldn't remember to save my life. So, I went and looked it up. In the interest of sharing, especially for the poor person who I left hanging, here's what I found. Permissions in Query Store Look through the blog, you'll find I'm pretty enamored with Query Store. I even contributed to a book on the topic (a little, it was almost all Tracy's work on that book, I just helped out). I haven't addressed security and Query Store. You do need to think about security in Query Store. For example, should you give read access to Query Store to your dev…
Read More
23Sep 2020 by Grant Fritchey 7 Comments

Review: Stellar Repair for SQL Server

SQL Server
I was contacted by Stellar Info quite a while ago. They asked me to try out their software. I said yes, but I was really bad about getting it done. Well, I finally got off my bottom and did the job. So, let's talk about Stellar Repair for SQL Server. Stellar Repair Let's start with the most important piece of information you need: it works. The software itself is really simple to use and just does what you need, repairs your corrupted SQL Server instance. On that alone, I can recommend the tool. However, there are a few gotchas I ran into along the way. Mostly, little stuff. It's things a little polish in the UI and some clean up around language could help out. Don't get me wrong, I'm…
Read More
08Jun 2020 by Grant Fritchey 3 Comments

AWS RDS and SQL Server Deadlocks

AWS, RDS
What's the story with AWS RDS and SQL Server deadlocks? I'm approaching AWS RDS like I was taking on a new role at a new organization. Do we have backups in place? Yes, great. Can I test them? Yes. Do they meet our RTO & RPO? Yes. Moving on. What have we got for monitoring? AWS RDS has a good percentage of the fundamentals. Now, it's laid out a little oddly. You have the stuff going through CloudWatch which is largely OS oriented. Then you have enhanced monitoring, which you have to turn on, which covers eight key metrics for SQL Server. Finally, you can enable Performance Insights which gives you metrics on query behaviors (and yeah, any or all of these may be the subject of upcoming blog posts).…
Read More
01Jun 2020 by Grant Fritchey 4 Comments

Getting Started on AWS RDS

AWS, RDS
I'm expanding my skill set into AWS in a big way. So, one of the things I do when I'm learning a technology is to write blog posts about that tech. So, prepare yourself for a bunch of info on AWS. I'll be working with RDS, which will include SQL Server & PostgreSQL for certain. I'll also be posting quite a bit on AWS DevOps as I get into it. The usual stuff, including SQL Server, query tuning, Extended Events, Azure, Azure DevOps, and all the rest is not going away. I'm just adding another topic. Today, we're getting started on AWS RDS. Creating an RDS Instance The thing is, there's a lot of documentation available on getting started on AWS RDS, directly from Amazon. It's embedded neatly into the…
Read More
04May 2020 by Grant Fritchey No Comments

Database Fundamentals #24: More Filtering Data

Database Fundamentals, SQL Server
In this Database Fundamentals post we continue discussing the functionality of the WHERE clause. We started with the basics of the logic using things like AND, OR and LIKE or '='. Now, we'll expand into some other areas. Functions in the WHERE clause SQL Server provides you with all sorts of functions that can be used to manipulate strings, modify dates or times or perform arcane mathematical equations. The problem with these is that if you do them on columns in tables it can lead to performance issues. The trick then, is to not perform functions on the columns in the tables. We’ll cover this in more detail when we get to indexing, variables, and parameters. Just don’t get into the habit of putting functions on the columns in your…
Read More
30Mar 2020 by Grant Fritchey 2 Comments

Extended Events: Queries and Waits

SQL Server, You Can't Do That In Profiler
Wouldn't it be great to be able to put together queries and waits at the same time? You all capture query metrics using some method. Most of us query sys.dm_os_wait_stats or sys.dm_db_wait_stats. Combining them is hard. You could query the wait stats. Store the results in a table variable. Run the query in question. Then query the wait stats again into a different table variable. Join the two table variables together to find the differences. Ta-da, you have query waits. Well. Probably. If you're the only one running queries on the system. Also, you're not seeing system waits or other noise caused by activity on the system. Or, we could put Extended Events to work. Queries and Waits Just like Profiler/Trace, you can capture stored procedures, batches, and individual statements…
Read More