SQL Server

Backup and Recovery, SQL Server

When is a “Full Scan” not a Full Scan? The performance counter “Full scans/sec” sounds pretty scary. If you see spikes in this counter, you’re likely to think that your queries are madly scanning all the data in your tables– there are no stylish, well performant seeks, just drunken queries spewing IO everywhere. If you…

Improving the Performance of Backups

Last Updated February 13, 2017

Jes Schultz

There will come a time when your database backups will not be as fast as you would like them to be. Over time, the volume of data you’re storing will grow, and backups will take longer – but your weekly or daily maintenance windows don’t grow as well. Backups can be resource-intensive, using I/O and…

sp_Blitz® v32: Emailing, Serializable Locking, Simultaneous Agent Jobs, and More

Last Updated January 20, 2014

The latest version of our SQL Server health check stored procedure is out today. Here’s what we’ve added in the last couple of versions – big thanks to the folks who keep making this even better for the community: Changes in v33 – January 20, 2014: Bob Klimes fixed a bug that Russell Hart introduced…

How to Query the StackExchange Databases

Last Updated August 15, 2024

Update – October 2015 – You can download a Torrent of a database (rather than a data dump) now. Most of my demos involve my favorite demo database: Stack Overflow. The Stack Exchange folks are kind enough to make all of their data available via BitTorrent for Creative Commons usage as long as you properly attribute…

Careful Adding Indexes with Always On Availability Groups

Last Updated February 9, 2017

Always On Availability Groups, SQL Server

In theory, you can add indexes online with SQL Server Enterprise Edition. In theory, with AlwaysOn Availability Groups, you can add and drop indexes on the primary replica whenever you want. In theory, you can perform read-only queries on the secondaries whenever you want, and nobody gets blocked. In practice, these things don’t always add…

SSMS Trick: Edit Large Procedures with the Splitter Bar

Last Updated January 10, 2014

Here’s a SQL Server Management Studio trick that comes in super handy when you’ve got long pieces of code. I almost never see anyone use this, and I think the reason is that few people know about it. When I’m working on a stored procedure, sometimes I want to add in a new variable or…

A terrible execution plan featuring three sort operators.

Dynamic Sorting

Last Updated January 9, 2014

Jeremiah Peschka

While working on some DMV scripts, I came up with a lazy way to have a user definable sort order in the query that seemed like pure genius. I showed it to the team and they’d never seen anything like it before. The Situation Users like to be in control. They want to define custom…

Why Are You Still Using SQL Server 2005?

Last Updated May 13, 2014

Jes Schultz

The year: 2005. What was happening with the Brent Ozar Unlimited® crew? I was working on the help desk for a small company while attending Fox Valley Tech College to earn my associate degree. I think I still wanted to go into programming at that point in time! I’d never been to a user group…

You Won the Tribal Awards With Us

Last Updated February 13, 2017

Blogging, SQL Server

The Simple-Talk Tribal Award Winners are out, and we won in two categories – Blog of the Year for the Brent Ozar Team Blog, and me as the Person You’d Most Like to Have a Beer With. See, now we’re having a beer virtually. It’s almost like – no, it’s nothing like. We need real…

What’s the Smallest SQL Server You Should Build?

Last Updated February 13, 2017

SQL Server, Virtualization

Before we pick CPUs or memory, let’s start by looking at SQL Server 2012’s licensing costs: Sticker Price for SQL Server 2012 These are MSRP prices and do not include the extra costs for Software Assurance (maintenance) or any discounts for being a school, non-profit, or those photos you have of Bill Gates. Physical, Standard…

SQL Server DMVs that Lie

Last Updated December 21, 2013

SQL Server has amazing instrumentation. Dynamic management views and functions give you great insight into what’s happening in your SQL Server and what’s slowing it down. Interpreting these views and functions takes a lot of time, but with practice and skill you can use them to become great at performance tuning. But nothing’s perfect. Some…

Top 10 Signs Your SQL Server is Neglected

Last Updated February 13, 2017

CHECKDB and Corruption, SQL Server

“That’s an interesting configuration you’ve got there.” Sometimes you see someone with toilet paper sticking to their shoe, their shirt tucked into their underwear, or badly in need of a tissue to get rid of whatever that is hanging out of their nose. It’s only right to find a discreet way to let them know…

How to Cache Stored Procedure Results

Last Updated February 13, 2017

SQL Server, T-SQL

Say you run an online store, and on each item’s page, you need to show related items that people purchased. Let’s take an Amazon page for my favorite rechargeable AA batteries: Frequently Bought Together In a perfect world, we would cache this data in the web/app tier – but back here in the real world,…

Meet Doug Lane (Video)

Last Updated February 12, 2017

Company News, SQL Server

Our mysterious employee #2 – where did he come from? How did he get into SQL Server? What does he want to learn in his first year at work with us? The answers to those questions, and more, will reveal themselves in this webcast recording: https://www.youtube.com/watch?v=SPeX4d_piiU

Q: Can High MaxDOP make a query SLOWER?

Last Updated December 21, 2013

Answer: Yep, sometimes it can. I used to think that higher degrees of parallelism followed a law of diminishing returns– you could add more threads, but the benefits would taper off. But it’s a bit more complicated than that. Microsoft’s recommendation to be careful when setting maxdop to values over 8 is a warning worth heeding.…

Vote for yourself in the new Tribal Awards.

Last Updated December 20, 2013

When you vote for us in Simple Talk’s new Tribal Awards, you’re voting for yourself. Best Free Script – sp_Blitz® – Sure, I started this all by myself a few years ago, but it’s grown into something huge. Scroll down through the change log and get a load of the dozens of contributors who have helped…

How Would You Change Windows Azure SQL Database?

Last Updated December 12, 2013

Cloud Computing, SQL Server

This artist formerly known as SQL Azure is a cloud service something akin to Microsoft SQL Server. SQL Azure is still under legal drinking age. When it first came out, it had a lot of challenges – a small subset of T-SQL commands and datatypes, inability to take backups, and absurdly small database sizes. But much…

The Four Answers To “How Big Is Your SQL Server?”

Last Updated December 8, 2013

Wanna know where you fit in relation to someone else? Get the answers to these questions. Now that’s a big trophy. 1. How many SQL Server instances do you have? The pro folks use the word “instances” rather than servers because clusters may have multiple SQL Server instances per node. 2. How many DBAs work…

Always On Availability Groups, Backup Checksums, and Corruption

Last Updated February 9, 2017