OS Hang or Out of Memory due to SQL Ser… No Wait, it’s SQL Analysis Services (SSAS)

Recently, we have observed a number of cases where DBAs or application developers are complaining about out-of-memory errors or even machine not responding (hangs) despite the fact that there is plenty of available memory on the system. Frequently this is on systems where SQL Server is running and the issue occurs after increasing the ‘max server…

0

How to create a Deadlocked Scheduler Scenario at will

  First, please do not try this on your production server, because it will actually work. Second, this example is to illustrate how Deadlock scheduler issues can and do occur with the goal of increasing your understanding of the problem. Over the years of working with SQL Server, I learned that to understand a technology…

3

Choosing what SQLIO tests to Run and Automating the Tests

Many companies rely on IO stress-testing tools like SQLIO Disk Subsystem Benchmark Tool (aka SQLIO) to perform an I/O subsystem validation before they deploy SQL Server in production. Over the years, I have seen a variety of SQLIO tests being used and many of them are not really necessary because they do not mimic SQL…

9

Memory Grants Meditation: The mysterious SQL Server memory consumer with Many Names

The Memory Consumer with Many Names Have you ever wondered what Memory grants are? What about QE Reservations? And Query Execution Memory? Workspace memory? How about Memory Reservations? As with most things in life, complex concepts often reduce to a simple one: all these names refer to the same memory consumer in SQL Server: memory…

1

Recompile Meditation: Is a plan always cached?

I wanted to make this post because I think the term “recompile” is used to describe two slightly different concepts in SQL Server. Here are the two uses of the term “recompile”:   1. WITH RECOMPILE or OPTION (RECOMPILE) – in this context a new plan is compiled but not cached when a query runs….

0

Meditation on SQL Trace performance Impact and Wait types

  Is SQL Trace,  or SQL Profiler,  or any other 3rd party tracing tool impacting SQL Server performance? The DBAs often contemplate this issue when they want to get insight from their SQL Server. Essentially the tracing mechanisms in SQL Server will present themselves as a bottlenecks (waits). See, when a SQL worker thread is…

2

"Waits, Waits, Do Tell Me"* – how to analyze bottlenecks in SQL Server

“Waits” in SQL Server are often a major source of performance issues. “Waits” as they are known in the SQL Server world essentially indicate that a worker thread inside SQL Server is waiting for a resource to become available before it can proceed with executing. See, a thread is tasked with executing code, it blossoms…

1

Meditation on Slow Inserts in SQL Server

This SQL Meditation contemplates on some common reasons why INSERT statements are taking a long time (slow)     1. Each Insert in a large batch is causing a log flush thus causing waiting   Is each insert a separate transaction or are those grouped in a transaction? If you review KB 230785 you will notice the…

0

Slow I/O – SQL Server and disk I/O performance

This SQL meditation provides a general approach on how to troubleshoot SQL Server I/O related issues. My belief is that if you truly understand this, you are empowered to solve the disk I/O challenges yourself. I offer to teach you how to fish. What kind of fish you catch, and where you catch it and…

0