Recent content in Blogs on KendraLittle.com
I increasingly hear or read the advice to solve problems by querying MCP servers on production databases. Meanwhile, I’m reading output from an AI agent that tried to use an MCP server of some sort. And the output is full of things that are obviously wrong/false/dangerous. It’s expensive and dangerous to trust an AI Agent to query production freely, even with read-only access. You have…
I sometimes joke that I’m a Junior Developer and a Principal Database Administrator, which is why I have a Staff level title. I’m not sure it’s a joke anymore, though. I’m still an OLTP database specialist, but increasingly I’m also a programmer who commits code in multiple languages. I own pull requests that I ship in a release process, following all the same rules…
Adaptive joins let the optimizer choose between a Hash Join and a Nested Loop join at runtime, which can be fantastic for performance when row count estimates are variable. Recently, when Erik Darling taught two days on TSQL at PASS Community Data Summit, a student asked why a query plan where an adaptive join used a Nested Loop at runtime ended up with a large memory grant anyway. I didn’t…
I see this pattern repeatedly: a “wide” query that returns many columns and less than 100k rows runs slowly. SQL Server gets slow when it drags large amounts of baggage through the entire query plan, like a solo traveler struggling with massive suitcases in an airport instead of picking them up close to their destination. SQL Server often minimizes data access by grabbing all the…
SQL Server 2025 introduces a new Resource Governor capability to manage tempdb usage, along with making Resource Governor available in Standard Edition . I wondered: can Resource Governor’s new tempdb feature help contain queries that don’t use temporary tables, but which spill massive amounts of data to tempdb? The docs say yes , but I always like to get hands-on experience when I…
Triggers can be tricky to observe in SQL Server. When you need to understand exactly what a trigger is doing and what it impacts, Query Store and Extended Events both have something to offer, but using them effectively requires navigating some confusing nuances. Query Store tracks query-level executions aggregated by query_id, but has a habit of generating a lot of query_ids with different context…
I’ve standardized design, upgraded components, added client-side search, and configured browser caching for this site over the last few weeks. I’ve also adopted a workflow that helps me capture ideas, test changes, and get posts published more easily. I’m not a web developer, I’m a database nerd. I’m don’t want to be great at CSS. I got this all done with a…
The rpc_completed event in Extended Events is useful when troubleshooting SQL Server performance. It captures detailed information about Remote Procedure Calls: that means stored procedure executions, including the calls to sp_executesql often used by applications (including Entity Framework) to run parameterized queries against SQL Server. The output for rpc_completed includes the parameters that…
Erik Darling joins me on the Dear SQL DBA Podcast to chat about AI tools, why they work better for Python and PowerShell than SQL, and what’s exciting (and what’s not) in SQL Server 2025. Along the way we describe the ONE THING we always want to configure in Resource Governor, why at least one SQL Server 2025 feature should be backported to 2022, and whether the universe is text files…
Microsoft recently announced that Azure SQL Managed Instance Next-gen General Purpose (GPv2) is generally available . GPv2 brings significant storage performance improvements over GPv1. If you’re using GPv1, you should plan to upgrade. But GPv2 still has the same memory-to-core ratio problem that makes Managed Instance a rough deal for running SQL Server. SQL Server is engineered to use lots…
Index bloat in Postgres can cause problems, but it’s easy to miss. I’ve written about how vacuum problems can prevent PostgreSQL from using covering indexes , and index bloat is one of the things that can make vacuum struggle. Here’s what you need to know about index bloat, how to find it, and how to fix it. What Is Index Bloat? “Index bloat” describes an index that…
I frequently need to see rowcounts and execution time for queries while they’re running. Maybe I’m troubleshooting a slow query that’s still executing, or I want to understand which operators are causing the slowdown before the query completes. Last week at the PASS Summit I learned some little nuances about how this works that I’d missed. Tldr: Quick Method For application…
While every managed database service has high points and low points, there are three things that make RDS for SQL Server shine in comparison to Azure SQL Managed Instance: options for a higher memory:vCPU ratio, a well documented API that works beautifully with python, and fast and effective customer support that isn’t painful to use. It’s just three things, but they make a huge…
Dear Postgres, Why won’t you use my covering index? Lately I’ve been learning to tune queries running against PostgreSQL, and it’s pretty delightful. One fun question that I worked through struck me something that most Postgres users probably encounter one way or another: sometimes you may create the perfect index that covers a given query, but the query planner will choose to…
Forcing plans with Query Store can be a powerful tool— until it mysteriously fails. In real production systems, plan forcing sometimes just… doesn’t work. One common culprit is the cryptic OPTIMIZATION_REPLAY_FAILED error. If you’re hitting OPTIMIZATION_REPLAY_FAILED, try re-forcing the plan using @disable_optimized_plan_forcing=1 . The Syntax You May Be Looking For How to force a query plan…
Statistics in SQL Server are simple in theory: they help the optimizer estimate how many rows a query might return. In practice? Things get weird fast. Especially when you start filtering on multiple columns, or wondering why the optimizer thinks millions of rows are coming back when you know it’s more like a few hundred thousand. In this post, I’ll walk through examples using single-column,…
🔥 UPDATE : Microsoft has announced the general availability of the Next-gen General Purpose service tier for Azure SQL Managed Instance, which includes improvements to I/O latency, IOPS, and transaction log throughput. This post describes the original General Purpose blob storage. You don't want that. The biggest lesson I’ve learned from helping folks manage data in Azure is this: if…
Views let you do dumb things by accident in SQL Server. Then they make you have to think way too hard to fix them. Most of the time when people create views, they start by refining a SELECT query, then turn it into a view. People also often create multiple views that pull different slices of data and UNION the results together. Combined, these two things easily lead to undeclared datatypes in…
‘Is it just me, or is SQL Server quality slipping?’ I asked myself that question for couple/few years until I faced up to it: SQL Server is well into a period where Microsoft investment is waning, and Microsoft regularly isn’t able to deliver the features they promise. Feature Announcements ≠ Usable Features Remember when folks waited for Service Pack 1 for SQL Server releases…
I listened to ‘Surviving the A.I. Endgame’ this weekend and realized: I’ve become one of the believers that AI advances are very likely to completely change tech and knowledge roles as we know them over the next 10 years. This is going to dramatically shrink the workforce across MANY roles (and many of those impacted will be outside of the tech sector). It isn’t that people…
SQL Server’s free state-based version control tooling was introduced under the ‘Data Dude’ brand, then became known as ‘SQL Server Data Tools’ (SSDT). Its extension for the (now dying) Azure Data Studio IDE is called ‘SQL Database Projects’. If you need to find documentation, you often need to know to search for specific component names like SQLPackage.exe…
🔥 UPDATE : Microsoft has announced the general availability of the Next-gen General Purpose service tier for Azure SQL Managed Instance, which includes improvements to I/O latency, IOPS, and transaction log throughput. This post describes the original General Purpose blob storage. You don't want that. What are your stories of unbelievably bad performance from cloud vendors? I’ll go first.…
This is the worst bug I’ve found in SQL Server to date. Previously, my top find was SQL Server Online Index Rebuild sometimes happens offline without warning . This one has taken top slot because it makes my life more difficult on a daily basis. Background: SQL Server generates a query_hash for each query. This is stored in sys.query_store_query and it’s one of the primary ways you can…
I use Datadog on a regular basis, and I’m a pretty huge fan. The monitoring pack for SQL Server (and its PAAS variants) is still pretty rudimentary, but it evolves regularly. That’s NOT what I’m a fan of, though. What makes me a raving fan is the flexibility of Datadog’s notebooks and dashboards, combined with the ability to create all sorts of custom metrics and monitors.…
Asking Microsoft for support for SQL Server or Azure SQL is a lousy experience these days, whether you’re using a cheaper service tier or the more expensive support tier formerly known as “Premiere Support.” You need to know a lot about the root cause of your problem and how to solve it, or your request will be dismissed with misinformation. You’ll need data and metrics to…
Every time I share a recommendation to use data compression in SQL Server to reduce physical IO and keep frequently accessed data pages in memory , I hear the same concern from multiple people: won’t this increase CPU usage for inserts, updates, and deletes? DBAs have been trained to ask this question by many trainings and a lot of online content – I used to mention this as a tradeoff…
tldr; I’ve published a script to loop through all databases on an instance, identify if there are any query plans in a problematic ‘failed" forced state (which can hurt query performance), and un-force them if found. Get the dbo.dba_QueryStoreUnforceFailed stored procedure on GitHub . This script is designed to work on SQL Server on-prem, in a VM, or in Azure SQL Managed Instance or…
Shrinking databases in SQL Server isn’t fun – it’s slow, it causes blocking if you forget to use the WAIT_AT_LOW_PRIORITY option , and sometimes it persistently fails and refuses to budge until you restart the instance . You only want to shrink a SQL Server database when you’ve got a good reason and a lot of patience. If you’re using Azure SQL Managed Instance and you…
At times when shrinking a data file in a SQL Server or Azure SQL Managed Instance/Database, shrink operations may persistently fail with the error: Msg 1119, Level 16, State 1, Line 11 Removing IAM page ([filenumber]:[pagenumber]]) failed because someone else is using the object that this IAM page belongs to. DBCC execution completed. If DBCC printed error messages, contact your system…
🔥 UPDATE : Microsoft has announced the general availability of the Next-gen General Purpose service tier for Azure SQL Managed Instance, which includes improvements to I/O latency, IOPS, and transaction log throughput. This post describes the original General Purpose blob storage. You don't want that. This post demonstrates two related bugs with plan forcing in Query Store which increase the…
Up till now, I’ve thought of compilation time in SQL Server as being dependent only on CPU resources– not something that requires fast storage to be speedy. But that’s not quite right. Slow storage can result in periodic long compile time in SQL Server. And long compile time not only extends the runtime for the query, it can also result in blocking with waits for compile locks.…
What’s it like to be a Database Administrator for managed databases in Azure? Sometimes it’s a painful guessing game when a routine, core operation– restoring a database – fails with a most unhelpful error. In this case, if the restore is run via PowerShell, following Microsoft guidance , the error message is: Restore-AzSqlInstanceDatabase: Long running operation failed…
Erik Darling joins Kendra Little to rate different SQL Server Performance Tuning Techniques in episode 81 of the Dear SQL DBA podcast. We share our opinions of… (deep breath) Recompile hints, Query Store hints and plan forcing, CTEs, Resource Governor, the legacy cardinality estimator, Table Variables, Automatic Plan Correction, Batch Mode, index rebuilds, Hekaton, NOLOCK, page compression,…
I’ve spent a bit of time with Microsoft’s new database watcher tool for Azure SQL recently. There are a lot of things I like about database watcher– which is currently in preview and which refuses to Capitalize Its Name– but it does one big thing that I really, really like: it collects data from Query Store. You can access that Query Store data from built-in database…
I’m teaming up with Erik Darling to teach you SQL Server Performance Tuning in two days at the PASS Data Community Summit in Seattle. Erik and I are co-teaching both days of training to give you a strong strategic background on the internals you need to know, along with critical tactical performance tuning techniques. Join us to level up your perf tuning skills! About Erik Darling and Kendra…
I’m a huge fan of SQL Server’s Query Store feature. Query Store collects query execution plans and aggregate query performance metrics, including wait stats. Having Query Store enabled makes troubleshooting performance issues such as bad parameter sniffing, much, much easier. Because Query Store is integrated into SQL Server itself, it also can catch query plans in a lightweight way…
I’ve been an employee at small, medium, and large companies, and I’ve also been a short-term consultant working with a new company in any given week. I’ve worked with hundreds of tech companies remotely and have visited companies onsite on multiple continents. Air-dropping into company cultures to work through problems with teams and present recommendations to their leadership…
Back in my 20’s, I was lucky enough to go to graduate school. I had a work-study job in the Dean’s office where I got to develop and administer their Access databases, which helped me get by. One day, the Dean said to me: ‘Kendra, when you talk about your work on our databases, you light up. When you talk about your coursework, that doesn’t happen. Have you thought about…
Last November, a puzzle was really bothering me. Some queries from an application were timing out frequently after running for 30 seconds, but they were halfway invisible in the SQL Server. I say ‘halfway invisible’ because I could see the queries while they were running in SQL Server’s dynamic management views using free tools ( sp_WhoIsActive and sp_BlitzWho ). But the queries…
It took me more than half hour to figure out how to start an XEvents trace on a read-scale out instance of Azure SQL Managed Instance. It’s hard to monitor read scale-out instances, so tracing is desirable! I started with a simple trace of sql_statement_completed . Hopefully this saves other folks some time. Architecture Review: What are Read Scale-out Instances in Managed Instance? Read…
If you use readable secondaries in Availability Groups or Read-Scale out instances in Azure SQL Managed Instance, you may have queries fail repeatedly if there is a glitch and statistics are not successfully ‘refreshed’ on the secondary replica. Those queries may keep failing until you manually intervene. It’s unclear if Microsoft will ever fix this. There is a well established…
🔥 UPDATE : This issue has now been documented. A note has been added to the Perform index operations online documentation page, stating: "Index rebuild commands might hold exclusive locks on clustered indexes after a large object column is dropped from a table, even when performed online." I found a nasty bug in SQL Server and Azure SQL Managed Instance recently: sometimes an ‘online’…
The Business Critical service tier in Azure SQL Managed Instance is a lot more expensive than General Purpose. For the extra money, you get a different architecture. Is it worth the extra cost? Spoiler: your mileage will vary, but probably not. Let’s talk about why. Business Critical Architecture In the Business Critical service tier, all databases are on local SSD on each node (instead of…
🔥 UPDATE : Microsoft has announced the general availability of the Next-gen General Purpose service tier for Azure SQL Managed Instance, which includes improvements to I/O latency, IOPS, and transaction log throughput. This post describes the original General Purpose blob storage. You don't want that. Whether or not you use Azure SQL Managed Instance, you will likely be asked for an opinion on it…
🔥 UPDATE : The sys.sp_configure_automatic_tuning stored procedure is now documented and supported by Microsoft. Thanks to the SQL Server Product team for this improvement. I’ve written a bit about SQL Server’s Automatic Plan Correction feature before– I have an hour long free course with demos on Automatic Plan Correction here on the site. Today I’m updating that course…
Eugene Meidinger stops by the Dear SQL DBA Podcast to chat about Power BI Performance Tuning. We talk about the various engines and languages used in Power BI and big-picture strategies for getting performance from the start. Eugene then talks about the community of tools and techniques that can be used to dig in and solve performance problems in Power BI. Pick Your Favorite Way to Listen to the…
According to Microsoft’s documentation , ‘Database compatibility level … allow[s] the SQL Server Database Engine to be upgraded while keeping the same functional status for connecting applications by maintaining the same pre-upgrade database compatibility level." But these days, the “functional status” of a database at a given compatibility level differs depending on…
One feature I’ve not appreciated enough in the past in SQL Server is Resource Governor. Resource Governor allows you to fix problems with Memory Grants in a simple way, as Erik Darling recommends . It also lets you classify sessions into groups and limit the maximum number of simultaneous requests , and /or limit the degree of parallelism if you need more CPU for other workloads. While this…
🔥 UPDATE (November 2025) : Since this post was written, most Intelligent Query Processing features are now available. According to Microsoft documentation , most Intelligent Query Processing features are now available in Azure SQL Managed Instance, including Cardinality Estimation Feedback, Memory Grant Feedback (percentile), and Parameter Sensitivity Plan Optimization. Some features require…
I shared an image on social media this week that describes how I feel about isolation levels in SQL Server (and its various flavors): the more concurrent sessions you have in a database reading and writing data at the same time, the more attractive it is to use version-based optimistic locking for scalability reasons. There are two isolation levels in SQL Server that use optimistic locking for…