SQL Server

Execution Plans, Memory Grants, SQL Server

SQL Server needs to have a reasonably correct estimate of how many rows your query is going to handle. If it doesn’t, things can go terribly, terribly wrong. Most often, the problem is that SQL Server under estimates its work. In this case, the query optimizer thinks it has just a little bit of work…

What is the CXPACKET Wait Type, and How Do You Reduce It?

Last Updated August 13, 2021

SQL Server, Wait Stats

121

When you query sys.dm_os_wait_stats, or check your waits with sp_Blitz® or sp_BlitzFirst®, one of your biggest wait types is probably CXPACKET. Let’s talk about what’s really going on. Our SQL Server Stand-In: a Class. Who wants to help me scan the sales table? Pretend that you’re a schoolteacher in a classroom with a couple dozen students.…

My Five Favorite That Conference Memories

Last Updated August 22, 2013

Jes Schultz

Last week, I went to summer camp for geeks, and I had a great time! That Conference is a three-day, developer-focused, family-friendly technology conference. Camp was held for the first time in 2012. I heard such great things about it from last year’s campers that I decided to go this year! The sessions focused on…

Introduction to SQL Server Failover Clusters (video)

Last Updated February 9, 2017

Clustering, SQL Server

You’d love to know how to make your SQL Servers more highly available, but you’re just not sure what “clustering” is all about. We can help! In this 30 minute video Kendra Little shows you how failover clusters make your SQL Servers easier to manage. She uses diagrams and drawings to teach you the strengths…

The Stages of Performance Tuning

Last Updated August 18, 2013

Performance tuning is a weird art to learn, because you do it in public. Sure, you may puzzle over a technical problem at your desk or revise code on your own– but when it comes to really tuning hard problems, 99% of the time you’re doing it with other people. That’s the nature of real…

How to Use HP System Management Homepage

Last Updated February 13, 2017

Hardware, SQL Server

One of the big reasons you spend big money on server-quality hardware is to get cool stuff to make administration easier. Each hardware vendor provides their own software tools – Dell includes OpenManage, IBM includes Director, and HP includes their System Management Homepage. To illustrate how it works without violating anybody’s NDAs, I picked up…

How to Master SQL Server Index Tuning in One Step

Last Updated August 12, 2013

I’m going to tell you a secret. Index tuning is complicated, but it’s something you can become great at. You just need to practice it regularly. Here’s that one step: stop thinking index tuning is a problem for Future You. That’s it. Really. If you read this headline and didn’t skip the post, your job…

I’m ThatExcited about ThatConference

Last Updated August 10, 2013

A peek into my slides for ThatConference I about to head out to a vast waterpark in the Midwest to ThatConference, where I’m going to hang out with 1000 developers, their families, and some wild animals. I’m giving a session Monday called “How to Make Deploying Database Changes Fun Again”. I’ve had a blast distilling everything…

One-Line Romantic Stories of SQL Server

Last Updated August 1, 2013

When they finally sat down to define their relationship, he was stunned to discover that he had a child table. … Her love was like Transparent Data Encryption: sure, he felt a sense of security, but someone else had waltzed in the front door and stolen her heart. .. He always knew she’d run away…

What?! Queries are Failing in My SQL Server?

Last Updated May 2, 2015

Help is on the way I came across an interesting case recently where queries were failing in a high transaction SQL Server. We knew that queries were occasionally failing with error severity 20 because SQL Server Agent alerts were periodically firing. However, those built in alerts don’t capture exactly what has failed– they just say…

Trivial Optimization and Missing Indexes in SQL Server

Last Updated May 12, 2019

Indexing, SQL Server

Recently, Jes asked the team an index tuning question: “If a query has an index hint in it, will the optimizer ever suggest a missing index for that query?” I immediately loved the question because I’d never really thought about it before. I typically think of index hints as being a very risky game and…

SQL Server 2014 Standard Edition Sucks, and It’s All Your Fault [UPDATE]

Last Updated March 26, 2014

129

Every release lately, Microsoft has been turning the screws on Standard Edition users. We get less CPU power, less memory, and few (if any) new features. According to Microsoft, if you want to use more than $500 worth of memory in your server, you have to step up to Enterprise Edition. Seriously? Standard Edition licensing…

Will Instant File Initialization Really Help My Databases?

Last Updated March 8, 2015

Jes Schultz

Do you want to make your database server faster? If you answered no, you can go back to manually checking that all of last night’s backups succeeded. If you answered yes, think about all the ways you can do this. From proper database design to the right hardware for your workload to proper indexes, you…

On Bobcats per 100 Orders and Other Spurious Metrics

Last Updated July 3, 2013

Jeremiah Peschka

Did you know that you can ship a bobcat 1/30th of the time and still maintain 97% positive feedback on ebay? What other statistical lies are lurking out there for you to find? Cache Hit Ratio You’re tracking your SQL Server’s cache hit ratio. That number is consistently 97-99%, surely that means you don’t need…

New DMVs in SQL Server 2014 CTP1

Last Updated June 25, 2013

When you download and start playing with SQL Server 2014 CTP1, here’s a few of the new instrumentation tables and views: New Internal Tables plan_persist_context_settings plan_persist_plan – Hey, lookie there! Implies that we can persist execution plans beyond a SQL Server service restart. plan_persist_query plan_persist_query_text plan_persist_runtime_stats plan_persist_runtime_stats_interval New Views column_store_row_groups dm_db_merge_requests dm_db_xtp_checkpoint – Note that both…

Optimize for… Mediocre?

Last Updated May 26, 2020

Parameter Sniffing, SQL Server

Some query hints sound too good to be true. And, unfortunately, usually they aren’t quite as magical as they might seem. Frustration with unpredictable execution times People often learn about parameter sniffing when query execution times stop being predictable. Occasionally you’ll hear about a stored procedure taking much longer than normal, but the next time…

The Index Your Queries will Never Request (A Clustered Index!)

Last Updated April 10, 2017

Indexing, SQL Server

When it comes to indexes, SQL Server is really helpful. It lets you see what indexes queries are asking for both in execution plans, and missing index dynamic management views (“DMVs”). I like to look at the DMV missing index requests using sp_BlitzIndex®. When you look at missing index requests, it’s always important to remember…

The @DBAreactions Guide to Database Administration [Video]

Last Updated June 11, 2013

Sometimes, the best stories are the horror stories. Join Brent as he talks about some of the worst situations he’s seen in his years of database administration. He’ll share the inspiration behind some of his favorite entries at http://DBAreactions.tumblr.com. We’ll either laugh or cry. Or both. https://www.youtube.com/watch?v=kHRk7J_RWAM Liked that webcast? We’ve got many more coming up…

(Almost) Everything You Need to Know About SQL Server 2014

Last Updated June 3, 2013