Skip to content

Great SQL Server Debates: Buffer Cache Hit Ratio

'One of the more popular counters used by DBAs to monitor the performance, the Buffer Cache Hit Ratio, is useless as a predictor of imminent performance problems. Worse, it can be misleading. Jonathan Kehayias demonstrates this convincingly with some simple tests.

Many years ago, when I first started working with SQL Server, there were a number of performance counters that were on the radar of all conscientious DBAs, and were used to track SQL Server performance and assess the general health of a server. One of those counters was SQLServer:Buffer Manager\Buffer Cache Hit Ratio, described as follows in the Books Online topic for the SQL Server:Buffer Manager Object'...

Memory-Optimized TempDB Metadata in SQL Server 2019

For as long as I have worked with SQL Server, and on almost every system I've ever managed, tempdb has been a bottleneck in one way or another. Microsoft has made a lot of improvements over the years to help resolve bottlenecks with access to PFS, GAM, SGAM, and other resources in tempdb. In no particular order, a few of these enhancements include:

  • Optimized number of data files during setup
  • Eliminated Trace Flag 1117 and 1118 to help with object allocation contention
  • Eager writes to reduce impact of bulk operations
  • Multiple optimizations to improve metadata contention
  • Accelerated Database Recovery, which shifts version store overhead to user databases

While they have removed some of these bottlenecks, and generally this makes tempdb less of a performance drain, certain workloads may introduce bottlenecks elsewhere. One area where contention is still common, even after all of these improvements, is with tempdb system table metadata, where too many sessions are trying to write to system tables (like a heavy workload that creates, alters, and drops a lot of #temp tables).

Memory-Optimized TempDB Metadata is a new feature in SQL Server 2019, as part of the "In-Memory Database" feature umbrella. Much like "Always On" isn't a specific feature, In-Memory Database is a term describing the set of capabilities that allow you to take advantage of memory to help make databases faster.'...

Delayed Durability in SQL Server 2014

'One of the cool new features in SQL Server 2014 is delayed durability (available in all Editions), which is described in detail in Books Online here.

I think I’m going to see a lot of people turn this on, as you can get a profound increase in transaction throughput with the right workload. However, I also think a lot of people are going to turn this on without realizing the potential for data loss and making the appropriate trade off.'...

PowerShell: Backup All SSAS Databases To A Network Share

'I am working on downgrading SQL Server Enterprise Edition (EE) s/w to Standard Edition (SE) where EE features are not used (to save money on licensing). We have a big list of these to go through. That required all SSAS databases to be backed-up to the network and restored after the edition downgrade. I will share the bigger script to help with the complete downgrade that includes SQL Server, SSIS, SSRS and SSAS at a later point in time.

For now, you can use the below snippet to backup all SSAS databases in an instance. Sorry, I am still using the SQLPS module instead of the newer SQLServer module but the code should be no different.'...

Interpreting execution plans of T-SQL queries

'In this article, we will analyze a simple T-SQL query execution plan with different aspects. This will help us to improve our practical skills instead of discussing theoretical knowledge.

The execution plan is a very significant point to figure out what is going on behind the query execution process. For this reason, if we want to boost the performance of a query in which we experience poor performance, we need to understand clearly what this guide tells us. In fact, execution plans are an output of the query optimizer, so we’ll try to understand how the query optimizer behaves. Firstly, let’s go over some of the main concepts that will be used in this article.'...

Snapshot Isolation in SQL Server

'In this article, I’ll explore the use of snapshot isolation using row versioning as an alternative to the classic ANSI SQL transaction levels.

Modern database systems excel at using the system resources available to them. Those include the CPU, RAM, Network and persistent storage of some sort. Busy systems can have thousands of active sessions, yet each session has the illusion of exclusive access. This is called concurrency and it is fundamental to the operation of most computing systems — even your smartphone!

In relational database systems, transactional isolation – an ANSI SQL standard – is often used to try to maximize concurrency while maintaining the ACID principle. Sometimes that is called pessimistic concurrency, since it assumes that something may go wrong and tries to prevent that from happening. Some database vendors have developed alternative approaches to managing concurrency and SQL Server is no exception. This article looks at snapshot isolation using row versioning as an example of just such an approach. Row versioning can be called optimistic concurrency since it assumes that things will usually go right.'...

Query Store for Workload Replays

'Today, I wanted to talk about my least favorite part of replaying workloads. It’s having an extended event or server-side trace running during a workload replay only so we can compare the results at a query-level when the replay is finished. Now, this might seem like a trivial thing but when you have workloads over 10k batch requests/sec this can consume terabytes of data quickly. The worst part is waiting to read all the data, slice and dice the data for analysis.

Starting with SQL Server 2016 there is a better and faster way to go! You can replace your extended event or server-side trace with Query Store captured data. Today, I will show you how to use the Query Store for the same purpose.'...

Different Ways to Monitor SQL Server AlwaysOn Availability Groups

"In my previous articles, I have explained the step-by-step process of deploying an AlwaysOn Availability group on SQL Server 2017. In this article, I am going to explain how to monitor AlwaysOn availability groups.

First, let’s review the configuration of the availability group we had deployed previously. To do that, open SQL Server Management Studio  Expand database engine from the object explorer Expand “AlwaysOn High Availability”  Expand “Availability Groups.” You can see the availability group named SQLAAG. Under this availability group (SQLAAG), you can see the list of availability replicas, availability databases, and availability group listeners."...

SSMS tips to make your day easier

'I thank Jes Pomfret (blog|twitter) for hosting T-SQL Tuesday #123. The topic for this month is Life hacks to make your day easier. I also want to acknowledge Adam Machanic’s vision behind this project. I learned a lot from some of the #TSQL Tuesday’s and participated in a few. Thank you, Steve Jones (blog|twitter), for coordinating this program. If you are interested to see previous topics, visit here.'...

How to Find I/O Usage Per Database in SQL Server

'If you’re looking at performance and trying to understand I/O on a per database level in SQL Server, sadly there is not a report in Management Studio that you can run from those available.

It will need some T-SQL to pull out this information from an existing DMV – sys.dm_io_virtual_file_stats.

The information returned by the DMV is data which has been recorded since the SQL Server was last restarted and so it is therefore cumulative data.

It helps the DBA to understand where the I/O distribution is across databases at both the data and log file level.'...