Performance Monitoring and Server Tuning Archives - Page 8 of 21

T-SQL & Development

[Video] The Top 10 Developer Mistakes That Won’t Scale on Microsoft SQL Server

August 3, 2020

You're a C# or Java developer building an application that stores data in Microsoft SQL Server, and you want to avoid common pitfalls that will cause you scalability problems down the road.

In this session from my live stream, you'll learn why you probably want to avoid triggers, SELECT *, user-defined functions, dynamic SQL, big datatypes, heaps, storing binaries in the database, old indexes, TempDB, and SQL Server's v1 features:

Bad Idea Jeans: Building Big Query Plans

June 23, 2020

When you build a monitoring tool that sends advice via email, you discover two things:

Some people have really, really big execution plans
Email servers have reasonable limits on file attachment sizes

Page Life Expectancy Doesn’t Mean Jack, and You Should Stop Looking At It.

June 17, 2020

Page Life Expectancy is a Perfmon counter that's supposed to track how long pages stay in memory before they get flushed out to make room for other pages that are needed instead.

Paul Randal has blogged this a few times, and rather than rehash 'em, I'd rather point you to a couple of his roundups:

Performance Tuning

Parallelism Can Make Queries Perform Worse.

May 20, 2020

While I was building lab queries for my all-new Fundamentals of Parameter Sniffing course - first live one is next week, still time to get in - I ran across a query with delightfully terrible behavior.

I'm always torn when I build the hands-on labs. I want to make the challenges easy enough that you can accomplish 'em in the span of an hour, but hard enough that they'll ... well, challenge you.

Production DBA

[Video] How Can I Measure If My Database Does More Reads Than Writes?

April 7, 2020

When someone asks you, "Is this database read-intensive or write-intensive?" you probably look at sys.dm_io_virtual_file_stats to measure the file activity - but that isn't really correct. After all, your users run a lot of queries all day that are simply satisfied from the buffer pool. SQL Server caches your data so it doesn't have to hit the disks, but ... those are reads, right? (Write? Get it?)

[Video] Watch Brent Work on sp_Blitz

April 2, 2020

During the quarantines, I'm looking for new ways to help you level up for free.

I decided to start live-streaming whenever I'm working on things I can share with you publicly. I wanted to add a new check to sp_Blitz, so I figured I'd do it live on camera while you watch, and talk you through my thought process.

Why Database Monitoring Tools Are So Hard to Interpret

January 27, 2020

SentryOne's Greg Gonzalez recently published a post about "Why Wait Stats Alone Are Not Enough." Sure, Greg's obviously biased towards his own monitoring tool, but his post is really well-written and fair. I want to explain the same story, but from a different (and simplified) angle.

Say we've got a really simple database server, and we start out with just one query that runs each day:

Performance Tuning Means 3 Things

January 15, 2020

Managing what requests are made
Managing the efficiency of each request
Managing the hardware capacity available to satisfy requests

And that's it.

Performance Tuning

Can SELECT * Make a Query Go…Faster?!?

January 14, 2020

Most of the time, you'll hear advice that you should select as few columns as possible so that SQL Server can do less work.

However, one of my clients hit a neat edge case scenario where SELECT * queries actually ran faster than picking a subset of columns.

Three Reasons to (Temporarily) Change Max Worker Threads

January 8, 2020

When you go to the deli, there's a "take a number" dispenser where you tear off a number and wait for your number to be called.

If you walked into a deli and you saw 50 people lined up all waiting their turn, and you went to take a number and the dispenser was completely empty, you'd say to yourself, "I think I'll come back later."

SQL Server 2019 Standard Edition Feature Limitations Are Out

October 17, 2019

The features list by edition is out, and there's a disclaimer at the top:

This content is being updated for SQL Server 2019. The information in the article is not final.

Production DBA

DBA Training Plan 20: Planning Your Next Server

September 12, 2019

When the business decides it's time to give up fixing the old server because it's not fast enough or reliable enough, stop. The worst thing you can do is jump directly to the hardware vendor's site, check a few boxes, and order a new shiny server that ends up with exactly the same problems as the last server.

Performance Tuning

DBA Training Plan 18: Managing Concurrency

September 4, 2019

Locking isn't a problem - queries are gonna lock data. As long as nobody else wants the lock, you can take your sweet time locking it, and it won't show up as a problem. (That's one of the many reasons queries work so well in development, and then hit performance issues in production.) Blocking, now…

Performance Tuning

DBA Training Plan 15: What’s Your SQL Server Waiting On?

August 29, 2019

You're here, dear reader, because you weren't "classically trained" as a database administrator. You didn't graduate from the University of Sciencing Computerses with a Master's of Transactional Processing.

You probably got your start as a developer or sysadmin, and gradually fumbled your way here. You're used to monitoring stuff from the OUTSIDE using things like perfmon counters or CPU graphs.

Production DBA

DBA Training Plan 14: POP QUIZ!

August 28, 2019

AH-HA! You didn't know there would be one of these, did you, hotshot? You've been reading along in the series, just smiling and nodding, pretending you're keeping up, but not really doing the homework. BUSTED! Put your books away and get your pencils out.

Backup & Recovery Questions:

Production DBA

DBA Training Plan 6: How to Monitor Drive Space

July 30, 2019

Don't.

It's not your job.

Are your CPU-intensive queries running slowly? Check your VM type.

May 2, 2019

When you build a VM for SQL Server in Amazon EC2, Azure VMs, or Google Compute Engine, it's all too easy to overlook the CPU speeds that you're getting. Most of the time, it doesn't really matter because SQL Server isn't usually CPU-bottlenecked: your queries are doing table scans with 16GB RAM, starving for IO throughput.

Free SQL Server Load Testing Tools

April 22, 2019

So you wanna run a load test against SQL Server. One thing I need you to understand first: you have to provide the database and the queries. Almost all of the tools in this post, except the last one, are designed to help you run queries, but they don't include the queries. The whole idea…

Performance Tuning

15 Reasons Your Query Was Fast Yesterday, But Slow Today

April 18, 2019

In rough order of how frequently I see 'em: There are different workloads running on the server (like a backup is running right now.) You have a different query plan due to parameter sniffing. The query changed, like someone did a deployment or added a field to the select list. You have a different query…

Which Database is Causing the Problem?

April 17, 2019

You've got one SQL Server hosting a bunch of databases, and performance is bad.

Which database should you focus on first?