How to Change SQL Server Settings with the Scientific Method
When you're trying to fix a problem (or even just researching something you're curious about), do what scientists do.
1. Ask a question. "Why is this database so slow?"
Category: SQL Server
Does Updating Statistics Cause a Recompile if No Data Has Changed?
tl;dr -- Not necessarily.
One of our students asked me a great question: if you update statistics on every table in the database, is that effectively the same as dumping the procedure cache on the instance? Will every execution plan have to be recompiled the next time it is run? I thought it was a great question and it spurred an interesting discussion about maintenance and recompilation.
Tip for Learning Extended Events – Use “New Session” (Not “New Session Wizard”)
Extended Events have become one of my favorite tools for learning about tuning SQL Server. Whenever I wonder how something works, I can fire up a test instance and play around with XEvents. It's always interesting, and at least 5% of the time I end up learning what I intended. Not everything is sunshine and rainbows. Finding…
What is the PREEMPTIVE_DEBUG Wait in SQL Server?
Recently we got an email asking for help from a DBA who was concerned about a high percentage of the PREEMPTIVE_DEBUG wait on a SQL Server. They were investigating poor performance and had searched the internet high and low and not found information on what this wait means. They were stumped. What to Do When You Find an Unusual…
Performance Tuning
Read Committed Snapshot Isolation: Writers Block Writers (RCSI)
When learning how Read Committed Snapshot Isolation works in SQL Server, it can be a little tricky to understand how writes behave. The basic way I remember this is "Readers don't block writers, writers don't block readers, but writers still block writers." But that's not so easy to understand. Let's take a look at a simple test…
Whitespace, Comments, and Forced Parameterization in SQL Server
A question came up in our company chat room the other day: does the forced parameterization database setting help with plan cache bloat caused by dynamic comments or whitespace? I love the topic of parameterization in SQL Server, probably because it's one of those things that's really pretty weird (but seems like it'd be straightforward). So…
Rebuild or Reorganize? How to Set Up Index Maintenance in SQL Server (video)
Index maintenance is confusing. Should you use maintenance plans or customized SQL Agent jobs? Join Kendra to learn the difference between index ‘rebuild’ and ‘reorganize’ commands, and how to avoid the pitfalls of maintenance plans in SQL Server.
https://www.youtube.com/watch?v=6nJKIeJC2lg
What Is Commodity Hardware?
You may have heard the term "commodity hardware" thrown around when describing solutions like Redis, Elasticsearch, or kCura's new Data Grid.
Commodity hardware refers to cheap, standardized servers that are easy to buy off the shelf from any vendor. Here's a typical example of a 2u, 2CPU commodity hardware server:
SQL Server Tasks You Probably Shouldn’t Automate
Every now and then I run across an automated script that does something a little suspicious. I'm not saying you should never put these things into a script, but if you do, seriously think about the surprise gotchas when someone runs the script:
DBCC commands other than CHECKDB
sp_configure (and especially RECONFIGURE afterwards)
ALTER SERVER CONFIGURATION
ALTER DATABASE
ALTER AVAILABILITY GROUP
CREATE INDEX or DROP INDEX
KILL
SHUTDOWN
And most of the database engine management stored procedures
Why You Simply Must Have a Date Table [Video]
As a developer, one of the things I can no longer live without is a date table. Who wants to type DATENAME and DATEPART over and over again? Not this guy, and once you have a date table, you won't want to either.
In this 16-minute video, I'll give you four reasons why you should stop writing so many date functions and concatenations, and start looking them up in a table instead.
Performance Tuning
Who’s Allowed to Add Indexes to ISV Apps?
Performance tuning of independent software vendor (ISV) databases is a little tricky.
To understand who does what, let's think through all of the work required with building and hosting a database application:
Doug Broke It: Microsoft Access Data Types [Video]
In this video, Doug talks about how changing a column's data type in SQL Server can be dangerous when there are Access clients using it, and a reference table to help avoid type mismatches when planning data type changes.
Click here to see the list of SQL Server data types according to Access.
News & Opinion
Brent’s Bad Idea Jeans [Video]
Over the years, I've come up with some rather “interesting” answers to SQL Server problems including:
Warming up SQL Server's buffer pool by selecting all the data from all the tables
Creating indexes with triggers
Adding nonclustered indexes to clustered columnstore indexes
Compressing backups the hard way
Putting databases on a RAM drive
How Would You Change Always On Availability Groups?
SQL Server 2012 introduced AlwaysOn Availability Groups, a way to achieve high availability, disaster recovery, and scale-out reads. SQL 2014 brought some improvements around higher uptime and more scale-out, and all signs point to continued improvements in the next version of SQL Server, too. (I love it when Microsoft brings out features like this and continues to invest in them over time.)
SQL Server’s Cost Threshold for Parallelism
"Should a query get to use more than one CPU core?" That's an important question for your SQL Server. If you're not sure what parallelism is, get started by exploring the mysteries of CXPACKET with Brent. He'll introduce you to the the setting, 'Cost Threshold for Parallelism'.
Let's test Cost Threshold for Parallelism
I generate an estimated execution plan for the following query. I'm running against a copy of the StackOverflow database that doesn't have many indexes.
When Does Index Fragmentation Matter?
A while back, we posted about how SQL Server index fragmentation doesn't matter. That blog post generated a lot of heated discussion and has served to make people aware that fragmentation may not be the source of their woes. Sometimes, though, there are data access patterns where fragmentation really does matter. Perpetual Fragmentation Some data…
Why Core-Based Licensing Matters for Performance Tuning
If you don't license all of your SQL Server's CPU cores, you need to pay particular attention to your server hardware.
Say you're using an HP DL580 with four CPU sockets, each of which has a Xeon processor plugged in with 10 cores. That's a total of 40 real cores, or 80 logical processors if you've enabled hyperthreading.
Production DBA
How to Restore a Page in SQL Server Standard and Enterprise Edition
One of the many restore features in SQL Server is the ability to restore one or more pages of data. This can be very convenient in some narrow situations - for example, corruption occurs on one page or an oops update is made to one record. The page restore process is not straightforward, however, and,…
#SQLPASS #Summit14 Keynote LiveBlog: Dr. Rimma Nehme
8:25AM Pacific: Goooood morning Seattle! It's time for day 2 of the PASS Summit, and this morning we've got a technical keynote lined up. I'm a much bigger fan of these than the day 1 marketing keynotes. Dr. Rimma Nehme from the Microsoft Gray Systems Lab is talking Cloud Databases 101. Here's the abstract: Cloud…
Announcing the 2015 #SQLPASS FreeCon (and taking applications)
Four years ago, we put on the FreeCon: a free invitation-only pre-conference event before the PASS Summit here in Seattle. The FreeCon was like a pre-conference session focused on your career and the SQL Server community. The goal was to spark fires, encourage people to reach for new levels of community contribution, and build connections to help people succeed.
