Black Friday prices end in 28d 12h 31m — See the sale

Performance Monitoring and Server Tuning

Diagnosing instance performance through waits, resources, monitoring, and baselines.

405 associated posts180 primary posts

Performance Tuning

Your SQL Server is Bored: What Low Wait Times Mean

September 10, 2018

Let's say you have an assistant. (I know, unlikely, but bear with me.)

And say you give your assistant a task - hey, go fetch me a coffee. Your assistant would nod obediently, go head out to the neighborhood coffee shop, get your preferred Americano, and bring it back to you. It might take them 10 minutes to go achieve that task - and then when they return, they'll sit patiently waiting for their next assignment.

Read more about Your SQL Server is Bored: What Low Wait Times Mean 4 comments — Join the discussion

[Video] Office Hours 2018/9/5 (With Transcriptions)

September 8, 2018

This week, Brent, Tara, Erik, and Richie discuss whether you should keep autoshrink on, AG multisite failovers, next version of SQL Server, SQL Server 2017 Vulnerability Assessment, SELECT INTO vs INSERT INTO, using Node.js with SQL Server, Using PowerShell for DBA tasks, the future of SQL Server for Linux, memory gateway query compile, VB.NET, patching, and more!

Read more about [Video] Office Hours 2018/9/5 (With Transcriptions) 2 comments — Join the discussion

[Video] Office Hours 2018/8/8 (With Transcriptions)

August 12, 2018

This week, Brent, Tara, Erik, and Richie discuss troubleshooting port blocking, page life expectancy issues, problems with turning off CPU schedulers, coordinating two jobs across servers, adding additional log files to an almost-full partition, tips for getting a new SQL Server DBA job, using alias names for SQL Servers, database going into suspect mode during disaster recovery, SQL Constant Care "Too Much Memory" warning, index operational statistics, running newer versions of SQL Server with databases in older version compat mode, and more!

Read more about [Video] Office Hours 2018/8/8 (With Transcriptions) Be the first to comment
Performance Tuning

Is the CXCONSUMER Wait Type Harmless? Not So Fast, Tiger.

July 24, 2018

Let's say you've got a query, and the point of that query is to take your largest customer/user/whatever and compare their activity to smaller whatevers. If SQL Server doesn't balance that work evenly across multiple threads, you can experience the CXCONSUMER and/or CXPACKET wait types. To show how SQL Server ends up waiting, let's write…

Read more about Is the CXCONSUMER Wait Type Harmless? Not So Fast, Tiger. 8 comments — Join the discussion

[Video] Office Hours 2018/7/18 (With Transcriptions)

July 22, 2018

This week, Brent, Erik, and Tara discuss their SQL server specialization, CPU-ready time, making SSIS changes globally rather than one at a time, creating an Always On AG farm, Azure managed instances, going from log shipping to Availability Groups, tracking folks that executed queries, moving databases into Availability Groups, an exciting update to SQL Server 2016, and Brent's upcoming move across country.

Read more about [Video] Office Hours 2018/7/18 (With Transcriptions) Be the first to comment
T-SQL & Development

[Video] Why Performance Tuning Wisdom Needs Expiration Dates

June 26, 2018

Several months ago, Erik said something like, "We should do a performance art piece where one person does the talking, while another person does demos that contradicts everything they say."

I thought it was a brilliant idea. We just had to find the right place and time to try it, and find the right topic. When we got the chance to speak at the 24 Hours of PASS 2018, we figured it was a perfect fit.

Read more about [Video] Why Performance Tuning Wisdom Needs Expiration Dates 1 comment — Join the discussion

Nodes Offline: Why Your SQL Server VM Can’t Use All Its Virtual Hardware

June 13, 2018

When you install SQL Server Standard or Web Edition in a virtual machine with more than 4 cores, things can look like they're just fine - when in reality, you're not using anywhere near your server's potential. See, these editions have a tricky limitation - let's look at the documentation: Here's the catch: the lesser of 4…

Read more about Nodes Offline: Why Your SQL Server VM Can’t Use All Its Virtual Hardware 31 comments — Join the discussion
T-SQL & Development

Demoing Latch Waits with Stupid Tricks

June 8, 2018

Say you've got PAGELATCH_UP or PAGELATCH_EX waits, and you're wondering what might be causing 'em.

I'm going to keep the brutally simple approach of building a stored procedure to simply dump hard-coded values into a table at high speed. I don't want to select from other tables since they might introduce some other types of waits, especially when I'm demoing concurrency quickly.

Read more about Demoing Latch Waits with Stupid Tricks Be the first to comment

[Video] Office Hours 2018/5/23 (With Transcriptions)

May 26, 2018

This week, Brent, Tara, and Richie discuss relaying SQL Server performance updates to upper management, differential backups in AlwaysOn Availability environment, replication, Troubleshooting THREADPOOL Waits, an acceptable amount of preemptive waits, Postgres, and the technologies Richie is working with these days.

Here’s the video on YouTube:

Read more about [Video] Office Hours 2018/5/23 (With Transcriptions) Be the first to comment

[Video] Office Hours 2018/5/16 (With Transcriptions)

May 20, 2018

This week, Brent, Erik, Tara, and Richie are back in action again to and are discussing sys.dm_db usage stats, Availability Group troubleshooting, ASYNC Network IO issue, reasons for using a contained database, whether you need to add more CPUs if facing THREADPOOL (or is it Deadpool?) waits, auto-tiering tempdb, and query tuning.

Here’s the video on YouTube:

Read more about [Video] Office Hours 2018/5/16 (With Transcriptions) 1 comment — Join the discussion