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'...

https://www.red-gate.com/simple-talk/sql/database-administration/great-sql-server-debates-buffer-cache-hit-ratio/

Memory-Optimized TempDB Metadata in SQL Server 2019

'Problem:
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).

Solution:
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.'...

https://www.mssqltips.com/sqlservertip/6230/memoryoptimized-tempdb-metadata-in-sql-server-2019/

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.'...

https://www.sqlskills.com/blogs/paul/delayed-durability-sql-server-2014/

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.'...

https://sqljana.wordpress.com/2017/09/12/powershell-backup-all-ssas-databases-to-a-network-share/

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.'...

https://www.sqlshack.com/interpreting-execution-plans-of-t-sql-queries/

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.'...

https://www.sqlshack.com/snapshot-isolation-in-sql-server/