SQL Server

Database servers suck, don’t they? Don’t you just hate dealing with these things? They’re totally different than any other server in the shop, making a mockery of any CPU, memory, and storage you throw at ’em. What the hell’s going on inside? Today I’ll give you the basics – starting with the question every sysadmin…

Does This Transaction Log Make My Database Look Fat?

Last Updated July 20, 2013

Face it; your database doesn’t get much exercise. It spends its day sitting on the same old server in the same old rack eating garbage all day long. Sure once in a while it may go for a spin, but SSDs are chipping away at that. They may hop around in a VM farm, but…

Indexing for Deletes

Last Updated February 13, 2017

Indexing, SQL Server

If you’re only indexing to make reads faster, you need to think again. Indexes can make every operation in the database faster, even deletes. The Problem: Deletes are Very Slow I was lazily researching new development techniques one day when I received an email from a client asking why deletes could be slow. I rattled…

The Dedicated Admin Connection: Why You Want It, When You Need It, and How To Tell Who’s Using It

Last Updated August 27, 2017

Kendra Little

Locking, Blocking, and Isolation Levels, SQL Server

There’s a DAC in SQL Server that you should know and love. Sadly, you probably either haven’t met or you’ve forgotten to turn it on. DAC? What’s That? First, a little disambiguation. The acronym ‘DAC’ is too popular. For the context of this article, our DAC is the ‘Dedicated Admin Connection’. SQL Server implements a…

Five Ways to Fight Blocking Video

Last Updated February 13, 2017

Kendra Little

What do *you* do when your database has a problem with blocking? Most people reach for the “NOLOCK” hint. Bad news: that may introduce other problems, and it doesn’t truly resolve the root problem in your application code. In this video Kendra Little summarizes five big-picture application changes that fight blocking long term. This 300…

The Stages of Growth

Last Updated June 7, 2020

Everyone poops and everyone grows. We all reach that growth stage in our own time, but sooner or later we all encounter it. Once you reach a growth stage you will experience some of these common emotions. I can’t believe it’s not scaling! Denial It’s 8:37AM and your developers just barged into your cubicle yelling, “The…

Windowing Function Examples for SQL Server

Last Updated November 26, 2020

SQL Server, T-SQL

Aggregations and grouping can be a pain in the rear for the novice SQL developer. Way back in 2003, the ANSI/ISO standards people figured this out and added windows and ranking functions to the standard. In 2005, Microsoft added a few of these functions (ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()) as well as the OVER() clause…

SQL Server 2012 CTP3 is Here! Five Things to Know About the Next Version of SQL Server

Last Updated February 9, 2017

237

SQL Server Denali CTP3 is here, and today I’m excited to be able to talk about this new public preview of the next version of SQL Server. CTP1 came out back in November 2010, and CTP2 wasn’t released to the public, so this is your first chance in several months to see progress. Here’s what…

Scaling SQL Server: Growing Out

Last Updated June 7, 2020

Scaling up is hard, scaling out is even harder. We all know that we can scale reads by adding some kind of replication or read-only copies of databases or using a massive caching layer. What happens when we have to scale writes? Borrowing Brent’s grocery store metaphor, a database that is effectively scaled for reads…

SQL Server Locking and You!

Last Updated February 13, 2017

Locking, Blocking, and Isolation Levels, SQL Server

Did you know that SQL Server’s locking has a name? It’s called two-phase locking. If we’re really getting specific about it, SQL Server uses what’s called strong strict two-phase locking or SS2PL. We’ll get there in a few minutes, right now we’re going to take a look at what makes up two-phase locking. But First,…

DBA Nightmare: SQL Server Down, No Plans

Last Updated February 13, 2017

Backup and Recovery, SQL Server

Managing data is about managing risk, but no matter how we good we are at managing risks, they’re still risks. We’ve seen several high-profile data failures recently: Sony’s database of Playstation users and financial data was hacked Amazon’s supposedly independent cloud services went down together, and some data was lost permanently The state of Texas…

Announcing Our New Weekly Community Recap Email

Last Updated May 8, 2019

You’re overworked. You don’t have the time to sit around reading blogs, keeping up on the latest industry news, and reading web comics. That’s where we come in. To stay on top of our game, we have to spend a couple days per week honing our skills. We’ve started a weekly email recapping the best…

Five Favorite Free SQL Server Downloads

Last Updated August 14, 2013

So you’re lazy and you’re company’s broke – what to do? Here’s my favorite free downloads to help manage SQL Server: Understand execution plans with SQL Sentry Plan Explorer – I dunno about you, but viewing execution plans in SQL Server Management Studio is a pain in my rear. The scrolling sucks, the cost numbers…

Scaling up or scaling out?

Last Updated September 15, 2021

When you need SQL Server to handle more load, there’s two big-picture ways to do it: Scale up – use a more powerful SQL Server Scale out – use more SQL Servers Quick disclaimer first – before you try either of these solutions, the easiest thing is to circumvent the whole problem – don’t ask…

PASS Summit Speaker Feedback

Last Updated February 7, 2018

The 2010 PASS Summit attendee feedback just arrived, and I’m very, very honored to have two sessions in the top 10. Last year, I had one session in the top 10. Based on the trends of this data, I look forward to the 2019 PASS Summit evals when I will have all 10 of the…

Microsoft Atlanta: Cloud-Based SQL Server Monitoring

Last Updated March 26, 2020

Microsoft Atlanta is a new cloud-based SQL Server monitoring tool. Companies can deploy an agent on their SQL Server 2008 and 2008 R2 instances, and the agent will communicate up to Microsoft’s servers in the cloud (through a secure gateway also installed inside the company’s network.) Microsoft aggregates the data, analyzes it looking for problems,…

How to Use sp_WhoIsActive to Find Slow SQL Server Queries

Last Updated July 28, 2019

SQL Server database administrators need to be able to quickly find out what queries and stored procedures are running slow. Microsoft includes sp_who and sp_who2 in SQL Server 2005 and 2008, but there’s a much better tool, and it’s completely free. In this five minute tutorial video, I explain how to use sp_WhoIsActive from Adam…

The Top 10 Developer Mistakes That Won’t Scale

Last Updated January 5, 2019