[Video] Office Hours: Ask Me Anything in the Reykjavik Harbor
You posted and upvoted questions at https://pollgab.com/room/brento to pick what you'd like to see covered in a Q&A session:
https://www.youtube.com/watch?v=gRwLZERyORk
Instance Resource Pressure and Tempdb
CPU, memory, tempdb, and related instance-level bottlenecks.
53 associated posts51 primary posts
[Video] Office Hours: Ask Me Anything About SQL Server
Erika and I are on the road in Iceland, roaming around the countryside, and I'm taking you with us. This week, we're in Siglufjörður, a beautiful little oceanfront fishing village in the north. In the mornings, before Erika wakes up, I'm filming Office Hours videos to answer your questions that you post here. The first video comes to you from a park bench next to the harbor:
Page Life Expectancy Doesn’t Mean Jack, and You Should Stop Looking At It.
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:
Three Reasons to (Temporarily) Change Max Worker Threads
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."
[Video] Office Hours 2018/12/5 (With Transcriptions)
This week, Tara and Richie discuss deletes on heaps, Docker, using containers in production environment, advice for new DBA, downgrading server hardware, agent jobs, setting SQL Memory Min and Max to the same value, how to prove CPU pressure to management using the Power BI report using sp_Blitz, corruption, patching, backups, sargability, and more.
[Video] Office Hours 2018/8/8 (With Transcriptions)
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!
[Video] Office Hours 2018/7/18 (With Transcriptions)
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.
A New tempdb Trace Flag Appears!
Coming Around
Microsoft has been doing a good job of taking stuff that used to be Wizard Hat and making it part of out of the box functionality.
Starting with SQL Server 2016, you didn't need to keep these in your setup checklist:
[Video] Office Hours 2018/6/6 (With Transcriptions)
This week, Brent, Erik, Tara, and Richie discuss index maintenance, stats updates, the acquisition of GitHub, using linked servers to move tables across, talking to developers as a DBA, the best cloud option, setting the number of tempdb files, and their favorite SQL Server features.
Here’s the video on YouTube:
T-SQL & Development
Concurrency Week: How Entity Framework and NHibernate Can Cause Poison RESOURCE_SEMAPHORE Waits
I've already blogged about my dislike for ORMs from a production DBA performance tuning standpoint only. I get that they're useful to developers. I get it. But I'm focused on performance.
A quick recap of what I don't like about ORMs from that other blog post:
[Video] Office Hours 2018/1/31 (With Transcriptions)
This week, Brent, Tara, and Richie discuss a simple way to fill tempdb, tools for retrieving data from transaction logs, tools for diagraming server architecture, their favorite counters for checking if SQL server on VMWare has sufficient memory, SSRS subscription issues, change requests, thread pool issues, best practices for SELECT * into, failover SQL Server vs failover SAN, backups, and more.
[Video] Office Hours 2017/12/13 (With Transcriptions)
This week Tara, Erik, and Richie discuss virtualization, replication & mirroring, switching SQL service logins to an AD account, temporal tables, tempdb issues, migrating from SQL 2015, and Richie's outdoor adventures in "Breath of the Wild."
Here’s the video on YouTube:
[Video] Office Hours 2017/05/24 (With Transcriptions)
This week, Brent, Erik, and Richie discuss disabling telemetry in SQL Server 2016, proper place for log files, Availability Groups, measuring data returned from a single query, whether CHECKDB should be done on tempdb, limiting tempdb usage for each session, transferring large amounts of data from on-prem to the cloud, and Iron Chef spoilers.
Here’s the video on YouTube:
[Video] Office Hours 2017/05/10 (With Transcriptions)
This week, Tara and Richie discuss increasing the size of tempdb, splitting database across several files, tracking DML changes to a table, upgrading different versions of service packs, recommendations for improving T-SQL skills, trace flags, replication, Always On Availability Groups, compatibility level of databases, and more...
Here’s the video on YouTube:
Max Worker Threads: Don’t Touch That
More isn't faster
I've had people give me all sorts of janky reasons for changing Max Worker Threads. I've even had people tell me that someone from Microsoft told them to do it.
The thing is, all changing that setting does is help you not run out of worker threads. It doesn't make queries run any better or faster. In fact, under load, performance will most likely be downgraded to Absolute Rubbish© either way.
[Video] Office Hours 2017/02/15 (With Transcriptions)
This week, Erik, Tara, and Richie discuss tempdb etiquette for developers, elastic search vs full-text search, server CPU spiking issues, source control in a database project, querying transaction logs, and more.
Here’s the video on YouTube:
DBA Days: Scripts from Downtime Train
ostress is so much fun It's totally free to download and use as part of Microsoft's RML Utilities. What else is in there? Stuff I've never used! I hear you can read trace files or something else perfectly adequate. Even though it's a CLI, it's still a bit less insane and confusing than HammerDB. Plus…
News & Opinion
Bad Idea Jeans: Dynamically Generating Ugly Queries and Task Manager Graffiti
Say you're at Dell DBA Days, and you want to build a really ugly query, fast. You want to generate a StackOverflow database query that will take a long time to compile, and maybe demand a huge amount of memory to run, but not actually take any time to execute.
You might start by building a numbers table with Method #7 from this StackOverflow answer, and then:
[crayon-6ae69f01470f0182298260/]
Which gives you a 10,000 join query, but I'm only showing the first 10 lines here:
SQL Interview Question: “Talk me through this screenshot.”
After writing about "For Technical Interviews, Don't Ask Questions, Show Screenshots", lots of folks asked what kinds of screenshots I'd show. Here's this week's example.
I show each screenshot on a projector (or shared desktop) to the candidate and say:
Performance Tuning
When Shrinking Tempdb Just Won’t Shrink
Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.
I am not a proponent of shrinking databases, but sometimes you have to because your momma said to. Sometimes that database is tempdb.
