Back in November of 2006, Microsoft released "SQL Server 2005 Waits and Queues" as a best practices article. Many people in the SQL Server community have referenced this article as a great place to get started with understanding waits in SQL Server. Heck, I've recommend it on a regular basis.
The last time I recommended it I told the recipient, "This is great, but only read it from page X to Y."
Most of us get caught up in fragmentation, finding the slowest queries, and looking at new features. We forget the little things that make managing a SQL Server easier - like cylcing the SQL Server error logs. What's the Error Log? The SQL Server error log is a file that is full of messages generated…
Diskspeed, or diskspd.exe, is Microsoft’s replacement for SQLIO. While I’m not going to replace our SQLIO tutorial, you can use the information in here to replicate the same type of tests on your systems to get the information you need. During the Dell DBA Days, Doug and I used diskspd as one of our techniques…
Enter Query Store Query Store, in short, is a way to track query performance over time. In Microsoft's words, "The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review". It's like sys.dm_exec_query_stats but it persists across reboots! And it has execution plans! The Query Store is a…
SQL Server 2016 brings support for JSON. For those of you who don't know, JSON is JavaScript Object Notation - it's a way of representing application level objects as a string. Imagine XML without anywhere near as much line noise, and you're pretty much there. Our Sample Document In this example, to retrieve orders and…
It's common to try to build the perfect index for a query. This index only uses the exact right columns for the key columns and other, presentational, data is pushed to the INCLUDE section of the index. While you can create a narrow key this way, the entire index row can become wide.
A Sample Report
Do foreign keys hurt the performance of inserts? Okay, we all know that foreign keys do require some work, but the crazy people of the internet have wildly varying guesses as to just how much work is involved. Estimates varied from "barely any" to "as much as 50% slower". I figured that you were going…
Every now and then, you need to figure out which version of SQL Server you're using without knowing in advance. This might happen in a script (like sp_BlitzCache) or you might be using it in a migration. Getting the SQL Server Version with @@VERSION THe first thing that comes to mind is @@VERSION. On my…
We've all had to move a database. For databases above a small size, this is a painful process. While there are a number of ways that you can move a database without downtime, they all involve a lot of extra work. What if there were something easier? What's the Real Problem? For most people, moving…
We've all had this problem before - the production server is significantly more powerful than the development server. You tune a query and you're pretty sure you've made it faster, but there's that doubt in the back of your mind. There are so many things that can change performance - memory, CPU count, disk performance…
Common table expressions are a feature of SQL that lets a developer create a query that can be referenced multiple times. This feature gives developers another tool to add flexibility or just to simplify code.
Why Common Table Expression?
Why is it called a Common Table Expression (CTE)? Because that's what the documentation calls it!
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…
sp_BlitzCache is a fantastic tool for finding the worst queries in your SQL Server. Did you know that it has a number of advanced options available to you? These options can help you dig into your worst queries, slice and dice the data, and get back exactly what you need, without wading through hundreds of results.…
Microsoft just announced a new round of D-grade VMs that have 60% faster CPU and local SSD than can go up to 7,000 IOPS in a canned IOmeter test. Before jumping to conclusions or, even worse, picking a cloud provider, it's best to look at these numbers critically. CPU Speeds The new CPU is being…
The only thing you ever need to use for database identity is an IDENTITY, right? Well, maybe. There are a lot of different options and they all have different pros and cons.
IDENTITY columns
The default way to identify objects in SQL Server is to use an INT or BIGINT column marked as an IDENTITY. This guarantees relatively sequential numbers, barring restarts and failed inserts. Using identity columns put the responsibility for creating and maintaining object identity in the database.
Let's skip the shenanigans and get right down to it - you're responsible for SQL Server and someone in your company wants to move things into Amazon Web Services (AWS). You've got SQL Server setup covered thanks to our helpful SQL Server setup checklist and you're confident you've got hardware under control, but things are different up in the clouds.
If you've been following the blogosphere, or heading to SQL Saturdays, you'll know that many folks are excited about the brand new SQL Server cardinality estimator (CE for short). As Kendra showed in her article The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast, the new CE provides dramatically improved query plans. What if…
Finding the worst queries in SQL Server is easy, right? You just query one of the dm_exec_* DMVs, bring in the SQL text and the execution plan, and then you spend hours looking at execution plans trying to figure out what’s wrong in the query, much less how to fix it. All that’s changing - we’re introducing sp_BlitzCache®.…
Extended Events were introduced with SQL Server 2008. Extended Events is a replacement for Server Side Traces and so much more – it’s a lightweight way to look at SQL Server performance, events, deadlocks, locking, blocking, and more. In this webcast, Jeremiah Peschka provides a high-level introduction to Extended Events. You’ll learn about how Extended Events are structured, how to find out what’s available, and where to get started.
SQL Server 2014 contains some interesting new features. Although SQL Server Standard Edition is limited to 128GB of memory, teams deploying on Standard Edition have an option to fit more of their working set in low latency storage - SQL Server Buffer Pool Extensions. How SQL Server Normally Deals With Data During SQL Server’s normal…
