Blog

How to Find Out Whose Queries are Using The Most CPU

Last Updated August 10, 2020

You’ve got a bunch of users on the same SQL Server, and you wanna know who’s using the most resources. SQL Server doesn’t make this easy. You can query some management views to see which queries or databases have been using the most resources, but you can’t intuitively tell which USERS are.

To solve this problem, we’re going to use Resource Governor.

Wait. Come back.

When Resource Governor first came out, it didn’t get a lot of adoption. It’s an Enterprise Edition only feature, and its main job is to make queries slower. That’s rather unfortunate, since I don’t have a lot of folks coming to me saying, “Hey Brent, can you make my queries slower?” They don’t need to hire me for that – they have their own business intelligence department to take care of that.

We’re going to use just part of Resource Governor’s functionality:

Create workload pools as if we were going to cap/limit people’s CPU power
Create a classifier function so that when they log in, we can put them into different pools
~~Set limits on each workload pool’s CPU~~
Use Resource Governor’s reporting DMVs to query who’s been burning up our processors

You can watch me write this blog post, or keep reading for the script and demo version:

Configure Resource Governor to put people into groups.

What you’re about to read is by no means any kind of good practice. Here, I’m just showing you how to quickly configure Resource Governor as a proof of concept. Any database person worth their weight in Query Bucks would read the Resource Governor documentation before actually implementing this in real life. You, dear reader, are not actually worth your weight in Query Bucks, so you’re probably going to just copy/paste this into production. That’s why I like you.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Create pools for the groups of users you want to track: */
CREATE RESOURCE POOL pool_WebSite;
CREATE RESOURCE POOL pool_Accounting;
CREATE RESOURCE POOL pool_ReportingUsers;
GO
CREATE WORKLOAD GROUP wg_WebSite;
CREATE WORKLOAD GROUP wg_Accounting;
CREATE WORKLOAD GROUP wg_ReportingUsers;
GO
CREATE WORKLOAD GROUP wg_WebSite USING [pool_WebSite];
CREATE WORKLOAD GROUP wg_Accounting USING [pool_Accounting];
CREATE WORKLOAD GROUP wg_ReportingUsers USING [pool_ReportingUsers];
GO


/* For the purposes of my demo, I'm going to create
a few SQL logins that I'm going to classify into
different groups. You won't need to do this, since
your server already has logins. */
CREATE LOGIN [WebSiteApp] WITH PASSWORD=N'Passw0rd!', 
DEFAULT_DATABASE=[StackOverflow], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [WebSiteApp]
GO

CREATE LOGIN [AccountingApp] WITH PASSWORD=N'Passw0rd!', 
DEFAULT_DATABASE=[StackOverflow], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [AccountingApp]
GO

CREATE LOGIN [IPFreely] WITH PASSWORD=N'Passw0rd!', 
DEFAULT_DATABASE=[StackOverflow], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [IPFreely]
GO


/* On login, this function will run and put people
into different groups based on who they are. */

CREATE FUNCTION [dbo].[ResourceGovernorClassifier]() 
RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
	-- Define the return sysname variable for the function
	DECLARE @grp_name AS sysname;

	SELECT @grp_name = CASE SUSER_NAME()
		WHEN 'WebSiteApp' THEN 'wg_WebSite'
		WHEN 'AccountingApp' THEN 'wg_Accounting'
		WHEN 'IPFreely' THEN 'wg_ReportingUsers'
		ELSE 'default' END;

	RETURN @grp_name;
END
GO

/* Tell Resource Governor which function to use: */
ALTER RESOURCE GOVERNOR 
WITH ( CLASSIFIER_FUNCTION = dbo.[ResourceGovernorClassifier])
GO

/* Make changes effective
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
*/

USE [master]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

/* Create pools for the groups of users you want to track: */

CREATE RESOURCE POOL pool_WebSite;

CREATE RESOURCE POOL pool_Accounting;

CREATE RESOURCE POOL pool_ReportingUsers;

CREATE WORKLOAD GROUP wg_WebSite;

CREATE WORKLOAD GROUP wg_Accounting;

CREATE WORKLOAD GROUP wg_ReportingUsers;

CREATE WORKLOAD GROUP wg_WebSite USING [pool_WebSite];

CREATE WORKLOAD GROUP wg_Accounting USING [pool_Accounting];

CREATE WORKLOAD GROUP wg_ReportingUsers USING [pool_ReportingUsers];

/* For the purposes of my demo, I'm going to create

a few SQL logins that I'm going to classify into

different groups. You won't need to do this, since

your server already has logins. */

CREATE LOGIN [WebSiteApp] WITH PASSWORD=N'Passw0rd!',

DEFAULT_DATABASE=[StackOverflow], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

ALTER SERVER ROLE [sysadmin] ADD MEMBER [WebSiteApp]

CREATE LOGIN [AccountingApp] WITH PASSWORD=N'Passw0rd!',

DEFAULT_DATABASE=[StackOverflow], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

ALTER SERVER ROLE [sysadmin] ADD MEMBER [AccountingApp]

CREATE LOGIN [IPFreely] WITH PASSWORD=N'Passw0rd!',

DEFAULT_DATABASE=[StackOverflow], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

ALTER SERVER ROLE [sysadmin] ADD MEMBER [IPFreely]

/* On login, this function will run and put people

into different groups based on who they are. */

CREATE FUNCTION [dbo].[ResourceGovernorClassifier]()

RETURNS sysname

WITH SCHEMABINDING

BEGIN

-- Define the return sysname variable for the function

DECLARE @grp_name AS sysname;

SELECT @grp_name = CASE SUSER_NAME()

WHEN 'WebSiteApp' THEN 'wg_WebSite'

WHEN 'AccountingApp' THEN 'wg_Accounting'

WHEN 'IPFreely' THEN 'wg_ReportingUsers'

ELSE 'default' END;

RETURN @grp_name;

END

/* Tell Resource Governor which function to use: */

ALTER RESOURCE GOVERNOR

WITH ( CLASSIFIER_FUNCTION = dbo.[ResourceGovernorClassifier])

/* Make changes effective

ALTER RESOURCE GOVERNOR RECONFIGURE

Eagle-eyed readers like yourself will notice that I commented out the RECONFIGURE at the end. Resource Governor can be pretty dangerous: you can get yourself into a situation where people have trouble running queries because of goofs in the classifier function. Only run that final RECONFIGURE in a dev environment first and make sure you’re getting the results you’re aiming for.

Measure which groups of users are using the most CPU.

To do that, check out the DMV sys.dm_resource_governor_resource_pools:

SELECT *
FROM sys.dm_resource_governor_resource_pools;

1 2	SELECT * FROM sys.dm_resource_governor_resource_pools;

The result shows you how much CPU each group has consumed:

When you start examining this data, you might find that your first pass of creating pools & groups ends up with not-very-granular reporting data. Maybe 80% of the server’s CPU is burned up by a single group – that’s not very actionable. From there, you want to continue creating smaller and smaller groups until you can figure out exactly who or what application is burning up the most CPU power.

This DMV’s contents reset when the SQL Server service restarts. You’re going to be tempted to hoard this data by logging it every day into a table, and I think that’s a bad idea. Just like you made a lot of bad decisions in your youth when you were young and you needed the money, so did the rest of the people on your team. Every time the SQL Server instance starts up, think of it as a brand new day. The folks writing terrible queries may have learned the errors of their ways and turned over a fresh new leaf, read themselves a good Itzik Ben Gan book on T-SQL, and only written magically well-tuned queries since then.

Ah, probably not.

European Union Folks: Wanna Attend Mastering Index Tuning?

Last Updated August 10, 2020

Brent Ozar

SQLBits

You need to speed up a SQL Server app, but you only want to make index changes. You don’t want to buy hardware, change SQL Server, or change the code. I’ll teach you the most important advanced lessons in one day.

In my one-day Mastering Index Tuning online workshop at SQLbits, you’ll learn:

My D.E.A.T.H. Method to quickly redesign indexes in an existing database
How to use sp_BlitzIndex to Deduplicate & Eliminate indexes that need to get out
How to use sp_BlitzCache to Add & Tune indexes based on your query workloads
When to resort to Hand-crafted indexes like filtered indexes, indexed views, and indexed computed columns

The day will be 25% slides, 75% demos, and 100% useful for both cloud and on-premises installations. I’ll be using the Stack Overflow 2013 database, and you’re welcome to follow along: just have a laptop with SQL Server 2016 or newer, or have an Azure SQL DB instance with the Stack Overflow 2013 database already installed.

And because it’s being sold through SQLBits, everyone can attend!

SQLBits.com – with dates, logistics info
SQLBits pricing – it’s £300 for my workshop (available either Tues or Weds, 29-30 Sept), or £800 for the full conference with 2 workshops, plus all-day training on Thursday, Friday, and Saturday
Register here – see you in class!

SQL Server Cumulative Update Documentation Is Going Downhill, Fast

Last Updated August 7, 2020

Brent Ozar

SQL Server 2019

Microsoft appears to have given up on patch documentation, and that’s kinda scary for a product that costs thousands of dollars per core.

Yesterday’s SQL Server 2019 Cumulative Update 6 launched with the worst documentation I’ve seen yet. It’s been steadily going downhill, but CU6 represents a new low.

For years, we’d complained that the hotfix articles weren’t documented enough, and they “fixed” that problem by giving up on the articles altogether, and just publishing a short summary:

For some bugs, these short descriptions are probably fine. However, some of them post more questions than answers:

This sounds an awful lot like a performance issue, not a security issue, and it doesn’t have nearly enough details. What features does it pertain to? Should all users be concerned, like folks who just run parallel queries? Or is it only relevant to a specific feature that isn’t installed by default? How can people know if this fix is relevant to them?

Sure, maybe short notes are okay for some updates, but if they are, they sure as hell gotta be correct. We’re now at the point where not only are we getting 3-sentence notes, but the notes aren’t even right.

To make matters worse, CUs have been having showstopper issues – but those showstoppers are buried down in the tiny print at the end of the CU, far after someone clicks on the download link, like this Cumulative Update 4 bug that talks about SQL Server not being able to start up:

To put this in perspective, Azure Data Studio’s closed issues and commits have way more details, and that product is free. Customers paying $7,000 per core for SQL Server Enterprise Edition are supposed to just take down production every 30 days and apply barely-documented updates without a clear reason.

I don’t think that’s okay. What do you think? Are you still patching every 30 days like Microsoft wants you to, or are you less likely to frequently patch given the lack of documentation?

SQL Server Problems We Don’t Have Anymore

Last Updated August 5, 2020

Brent Ozar

Production Database Administration

I was cleaning out a folder of presentations and I realized there are a whole slew of problems that we had to worry about ten years ago, and they just hardly ever come up today.

Virtual Log Files (VLFs) – it’s been years since I’ve run across a server where this was an issue. I don’t know if people are just doing a better job of taking care of this, or if the widespread adoption of tools like sp_Blitz means that people know about it earlier, and fixing it before they call me. Either way, great!
Partition Alignment – remember when you had to pay attention to how drives were formatted? Remember when you even knew what kind of storage you were running on? These days, between virtualization and the cloud, the underlying storage architecture could change several times over after you install the server.
PFS/SGAM contention – between the better newer SQL Server installers creating a few TempDB files by default, and the widespread user education about 1 data file not being enough, it’s easily been 2-3 years since I’ve solved a problem by adding TempDB data files.

Hallelujah! Here’s to those problems never coming back.

On one hand, it’s not like our jobs are getting easier, either: there’s just a whole new class of challenges to deal with, whole new layers of abstraction.

But on the other hand, it is kinda disappointing what old problems we still have, and I’m going to link to the feature feedback requests to fix ’em:

Cost Threshold for Parallelism – it’s still 5, which leads to problems with CXPACKET waits for tiny queries. Just last month, I was dealing with a customer’s 32-core server where they’d neglected to change this default setting. Just that one change alone dramatically cleaned up their performance problems.
Configure backups during installation – you, dear reader, are a trained (maybe) professional (look, I’m being generous) who takes great care (I know, it’s getting hard to suspend your disbelief) in setting up backups to match your RPO and RTO. Many shops don’t have the luxury of employing a good-looking data person like yourself, though, and they struggle setting up their backups. It’s time to make that easier.
Proactively look for corruption in the background. Out in the real world, far too many folks just don’t take the time to set up DBCC CHECKDB jobs, and even when they do, they don’t know when to schedule ’em because they’re afraid of taking their servers down. SQL Server knows when it’s idle, and can track the progress of CHECKDB. It needs to regularly scan for corruption just like RAID controllers do.

Date Tables are Great for Users, but Not So Great for Performance

Last Updated August 5, 2020

Brent Ozar

Execution Plans, Videos

Date tables help users filter data based on day of week, holiday, this year vs last year, and other kinds of filters that business users usually wanna add to their reports. If you haven’t used a date table before, here are a couple of primers on how to create one and use it:

And for the purposes of this post, I’m going to use the date_calendar table created in the second bullet point above. Here’s what it looks like:

Typically when people use date tables, they join from their real tables over to the date table, and they do their date calculations & filtering by just specifying columns in the date table.

For example, if you wanted to find out what day of the week people usually sign up for Stack Overflow accounts (database), you might write a query like this:

SELECT d.day_name, COUNT(*) AS recs
  FROM dbo.Users u
  JOIN dbo.date_calendar d ON CAST(u.CreationDate AS DATE) = d.calendar_date
  GROUP BY d.day_name
  ORDER BY COUNT(*) DESC;

SELECT d.day_name, COUNT(*) AS recs

FROM dbo.Users u

JOIN dbo.date_calendar d ON CAST(u.CreationDate AS DATE) = d.calendar_date

GROUP BY d.day_name

ORDER BY COUNT(*) DESC;

The results look like this:

Pretty cool! But…does it perform well? To find out, let’s write an old-school version of the query that doesn’t use a date table, and we’ll compare the two using SET STATISTICS IO, TIME ON to measure CPU, duration, and logical reads:

SET STATISTICS TIME, IO ON;

/* With date table: */
SELECT d.day_name, COUNT(*) AS recs
  FROM dbo.Users u
  JOIN dbo.date_calendar d ON CAST(u.CreationDate AS DATE) = d.calendar_date
  GROUP BY d.day_name
  ORDER BY COUNT(*) DESC;

/* Old-school: */
SELECT DATENAME(weekday, u.CreationDate) AS WeekDay, COUNT(*) AS recs
  FROM dbo.Users u
  GROUP BY DATENAME(weekday, u.CreationDate) 
  ORDER BY COUNT(*) DESC;

SET STATISTICS TIME, IO ON;

/* With date table: */

SELECT d.day_name, COUNT(*) AS recs

FROM dbo.Users u

JOIN dbo.date_calendar d ON CAST(u.CreationDate AS DATE) = d.calendar_date

GROUP BY d.day_name

ORDER BY COUNT(*) DESC;

/* Old-school: */

SELECT DATENAME(weekday, u.CreationDate) AS WeekDay, COUNT(*) AS recs

FROM dbo.Users u

GROUP BY DATENAME(weekday, u.CreationDate)

ORDER BY COUNT(*) DESC;

The metrics tell an interesting story:

Date table method: 100,074 logical reads, CPU time 3,422 ms, elapsed time 3,425 ms
Old school method: 20,095 logical reads, CPU time 3,671 ms, elapsed time 1,467 ms

The actual execution plans are obviously different:

So why did the date table not perform as well as the old-school way?

SQL Server doesn’t understand the relationship between these two tables. It simply doesn’t know that all of the rows in the Users table will match up with rows in the calendar table. It assumes that the calendar table is doing some kind of filtering, especially given our CAST on the date. It doesn’t expect all of the rows to match.

SQL Server decides it’s going to scan the date_calendar table first, and then for each date that it finds, it does an index seek into the Users index on CreationDate:

If you hover your mouse over that index seek on CreationDate, the number of executions are worth a laugh:

This index seek was done over 18,000 times – once for every day in our calendar table.

Because SQL Server thought the date table would do some filtering, it also underestimated the number of rows it’d find in Users, so it also underestimated the CPU work required, so the date table approach only went single-threaded. The old school way understood that lots of rows would be involved, so the work was parallelized across multiple threads.

That’s a simple query where I’m not even doing filtering – but query plans get even more predictable as you build atop this, adding filters on the date table looking for weekdays, specific days of the month, or ranges. SQL Server just has a hard time guessing how many rows are going to come out of the related tables. The more complex your filtering becomes, the crazier the estimates become.

So if I have date tables, what should I do next?

This quick experiment doesn’t mean that you should abandon your date tables and force everyone to do calculations against the base tables. Date tables are WONDERFUL, and I recommend them highly.

However, you just need to be aware that:

If you filter on the date table rather than the base tables, you’ll likely get inaccurate estimates
The more you rely on functions or ranges of data in the date tables, the more likely you are to get increasingly bad estimates
To fix these estimates, you may need to add relational columns on your base tables that define exactly which rows in the calendar table match which rows in your base table
When doing performance tuning on a query that uses a date table, consider briefly tweaking the query to not use the date table, and see how much better the execution plan gets. If it’s a night and day difference, you may need to work on the date table’s relationships or switch this one particular query to filter directly on the base tables rather than the date tables.

Got questions? Want to try different approaches to my queries above? Grab the Stack Overflow database and try running your own experiments. That’s why I love using the Stack Overflow database as a sample: it contains real-world distribution on stuff like dates so you can prove your own ideas or disprove mine.

Watch me write this post

I streamed this post live on Sunday, July 19:

[Video] The Top 10 Developer Mistakes That Won’t Scale on Microsoft SQL Server

Last Updated July 30, 2020

Brent Ozar

Development, Videos

You’re a C# or Java developer building an application that stores data in Microsoft SQL Server, and you want to avoid common pitfalls that will cause you scalability problems down the road.

In this session from my live stream, you’ll learn why you probably want to avoid triggers, SELECT *, user-defined functions, dynamic SQL, big datatypes, heaps, storing binaries in the database, old indexes, TempDB, and SQL Server’s v1 features:

This slide deck is a decade old: I wrote it back in 2010, but I’m revisiting it now to see what’s changed. I’m kinda surprised at how much I still agree with a decade later! My feelings have changed about a lot of SQL Server tactics, but 9 out of 10 of these are still a problem.

Wanna see more of this? Every Saturday & Sunday morning from 9AM-11AM Eastern (iCal), you can catch me on my Twitch, YouTube, or Facebook channels.

A One-Slide Summary of the Differences Between TDE and Always Encrypted

Last Updated July 31, 2020

Brent Ozar

Development

The folks on Twitter liked this, so sharing it here:

It’s a one-slide summary from a SQL Critical Care client’s deck, so obviously it’s abridged, but I think it does a pretty good job of summing things up.

Some highlights of the conversation:

A solid 20 minutes of my talk from last year’s Summit, summed in one slide. Well, done, @BrentO! Could be simplified further to: TDE – what you do for compliance but not actual security, and Always Encrypted – what you do for actual security but not compliance

— Daniel Mallott (@DanielMallott) July 30, 2020

I have always thought of TDE as a chocolate fire guard. When the fire is going (database online) and someone hasn’t secured the database properly TDE is worthless. Sure TDE stops file theft (lower risk). I would include anyone with read access on protect list

— Kevin Lewis (@Kevin_S_Lewis) July 30, 2020

That’s it. That’s the post. If you want more words and pictures, follow @BrentO on Twitter.

Remember “Nothing Stops a Hekaton Transaction?” Yeah, About That.

Last Updated July 29, 2020

Brent Ozar

In-Memory OLTP (Hekaton)

When In-Memory OLTP first came out, Microsoft liked to go around bragging at conferences that “Nothing stops a Hekaton transaction.” I remember hearing that and kinda chuckling at the time, and I was reminded of it by a recent client who was running into blocking issues with…yep, Hekaton.

To reproduce it:

Do a large insert into an in-memory OLTP table
In another window, try to truncate that table (which gets blocked by query #1)
In another window, try to another insert into that table (which gets blocked by query #2)

The results of sp_WhoIsActive show blocking:

The reason is rather amusing: In-Memory OLTP tables don’t actually support TRUNCATE TABLE. If you try it, you hit an error:

Msg 10794, Level 16, State 96, Line 1
The statement 'TRUNCATE TABLE' is not supported with memory optimized tables.

1 2	Msg 10794, Level 16, State 96, Line 1 The statement 'TRUNCATE TABLE' is not supported with memory optimized tables.

But SQL Server doesn’t check for that until after the TRUNCATE TABLE command gets a schema modification lock. In order to get that, the first query has to finish – and until he does, query #3 can’t run. Presto, good ol’ blocking. (I can’t call that a SQL Server bug with a straight face, either.)

The solutions:

When your code throws errors, don’t swallow ’em. Log them somewhere, and actively track them down. In this case, the TRUNCATE TABLE was a leftover from before the table was migrated to In-Memory OLTP.
Before you migrate, read the T-SQL limitations of In-Memory OLTP and unsupported features.
If you need to quickly delete the contents of an entire In-Memory OLTP table, don’t bother trying swapping back and forth between different In-Memory OLTP tables for staging and production, either. sp_rename is supported as of SQL Server 2017, but it faces the same schema mod lock problem as TRUNCATE TABLE.

Shout out to the client manager who said, “You have to blog about that to show that we were ambitious but rubbish.” Cheers, Tiny Irish: y’all were smart enough to hire me, so there’s always that.

What SQL ConstantCare® Has Been Finding In Your Servers Lately

Last Updated July 28, 2020

Brent Ozar

SQL ConstantCare

SQL ConstantCare® analyzed over 3,000 of your SQL Servers this month. Here are the things we check for and the number of servers where we found this problem this month:

Backup

Back Up Your TDE Certificate – 137 servers affected
Take a Full Backup – 370 servers affected
Take a Log Backup – 353 servers affected

Planning

Database Size Increased – 205 servers affected

Query Tuning

Compilation Timeout – 704 servers affected
Joining to Table Valued Functions – 586 servers affected
Queries may have non-SARGable predicates – 1,057 servers affected
Query Forced to Run Single-Threaded – 803 servers affected
Query Slowed by Implicit Conversion – 387 servers affected
Query with Missing Index Request – 709 servers affected

Reliability

Call Microsoft to Report Memory Dumps – 131 servers affected
Check for Broken Log Shipping – 70 servers affected
Check for Corruption ASAP – 820 servers affected
Check Unusual Database States – 202 servers affected
Disable Priority Boost – 28 servers affected
Enable Checksums for Corruption Detection – 320 servers affected
Enable Remote Admin Connections – 567 servers affected
Full Text Indexes Not Updating – 49 servers affected
In-Memory OLTP Transaction Errors – 4 servers affected
Move TempDB Off the C Drive – 174 servers affected
Move User Databases Off the C Drive – 209 servers affected
No Cluster Nodes Available for Failover – 9 servers affected
Restart to Correct TempDB File Error – 10 servers affected
Server Keeps Restarting – 157 servers affected
Transaction Log Larger than Data File – 735 servers affected
Unsupported Build of SQL Server – 458 servers affected

And The Top 10 Overall

Update Available – 1,676 servers affected. I get it, patching is hard, and nobody’s doing it.
Queries may have non-SARGable predicates – 1,076 servers affected.
Consider Enabling Query Store – 1,025 servers affected. Keep in mind that this is 1,025 servers that are patched to the level where I’d be comfortable telling them to turn on Query Store, and…they’re still not doing it. I haven’t gotten to the point where I’m going to stop recommending it, but I’m getting pretty close.
Check for Corruption ASAP – 824 servers affected. This one just blows me away: even when people know they need to do it, they won’t. Sure, sometimes they offload it, but there are even tiny sub-100GB servers in here that aren’t getting checked. That’s why I’ve asked Microsoft to run DBCC CHECKDB in the background.
Query Forced to Run Single-Threaded – 819 servers affected. People love them some scalar functions, and we’re only warning you about queries in your top ~50 resource consumers.
Transaction Log Larger than Data File – 736 servers affected. This alert filters for log files over 5GB, too, so it’s not even like these are small databases. Folks just don’t know how to get their log files under control for some apps, and they’ve given up trying. (I need to write a blog post about the rules that are getting ignored the longest.) Why would I be concerned? Because it affects restore times: the log file doesn’t get Instant File Initialization.
Query with Missing Index Request – 733 servers affected. I don’t think this is just about Clippy’s bizarre suggestions, but it’s also about third party vendor apps where folks can’t touch the indexes.
Compilation Timeout – 724 servers affected. We’re specifically warning here about queries that are in your top ~50 resource consumers.
Joining to Table Valued Functions – 599 servers affected. SQL Server 2019’s scalar function inlining doesn’t help this, either.
Heap Needs to Be Rebuilt – 598 servers affected. A lot of folks are using Ola Hallengren’s maintenance solution, and they’re surprised to hear that Ola purposely ignores heaps.

What I Think About All This

“There’s so much corruption in here, there isn’t even any real data left.”

Now, I know you, dear reader. You have great confidence in your ability to manage your environment. You believe you’ve got everything under control, and you couldn’t possibly have the above problems on your servers, right? Right?

Except you do.

When companies hire me for consulting, they get SQL ConstantCare® for free, and the first email is usually pretty shocking. Just last week, a customer found out that SQL Server was dutifully reporting database corruption, but they didn’t know because they didn’t have corruption alerts configured correctly and no one was monitoring the suspect_pages table.

I get it: monitoring is expensive. Except it isn’t: SQL ConstantCare® is $495 a year, and that isn’t per-server: that’s one price to keep an eye on all of your servers. You’re busy. Offload some work to me.

[Video] How to Think Like Clippy (Subtitle: Watch Brent Wear Another Costume)

Last Updated July 27, 2020

Brent Ozar

Indexing, Videos

Remember Clippy, the Microsoft Office assistant from the late 1990s? He would pop up at the slightest provocation and offer to help you do something – usually completely unrelated to the task you were trying to accomplish.

Sadly, the Office team told Clippy that he didn’t make the stack rankings cut, so he relocated over to the SQL Server team. He knows better than to share his face in SSMS, but he toils away tirelessly suggesting indexes you should add. “Hey buddy! It looks like you’re querying the Users table by Reputation. Would you like help?”

In my Fundamentals of Index Tuning class, I explain how humans think about designing nonclustered indexes. After a few hours of the fundamentals, we switch gears and show how Clippy comes up with his column orders. Students are pretty disappointed at Clippy’s simplicity, but on the flip side, he gets these recommendations done in a millisecond – something you and I would be hard-pressed to pull off.

I decided Clippy deserved his own webcast, so this weekend, I let him take over my stream and defend himself. Enjoy!

Wanna see more of this? Every Saturday & Sunday morning from 9AM-11AM Eastern (iCal), you can catch me on my Twitch, YouTube, or Facebook channels.

Here’s the demo script Clippy used in the session:

/*
How Are Missing Index Hints Built?

Here's what you're going to learn in this demo:

* How SQL Server picks missing index column order
* What SQL Server doesn't consider: selectivity or statistics

This demo requires:

* Any supported version of SQL Server or Azure SQL DB
  (although the 10M row table creation can be pretty slow in Azure)
*/


/* Let's create a table with a few columns, and insert 100,000 identical rows: */
--DROP TABLE dbo.DiningRoom;

CREATE TABLE dbo.DiningRoom
  (FirstColumn INT,
   SecondColumn INT,
   ThirdColumn INT,
   FourthColumn INT,
   FifthColumn INT,
   SixthColumn INT
   );
INSERT INTO dbo.DiningRoom (FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn, SixthColumn)
  SELECT TOP 10000000 1, 1, 1, 1, 1, 1
  FROM sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
  CROSS JOIN sys.all_columns ac3;
GO
SELECT TOP 100 * FROM dbo.DiningRoom;
GO

/* Turn on actual execution plans, and check the missing index requests: */
SET STATISTICS TIME, IO ON;
GO
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 0;

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE SecondColumn = 0;
GO



/* Simple so far. Now let's try two columns: */
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 0
    AND SecondColumn = 0;

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE SecondColumn = 0
    AND FirstColumn = 0;
GO

/* 
/poll "What missing index recommendations will we get?" "SQL will ask for two different indexes" "Both will ask for FirstColumn, SecondColumn" "Both will ask for SecondColumn, FirstColumn"  anonymous

*/


/* 
What about selectivity?
If our where clause looks for one thing that doesn't exist, and one thing that
does, will SQL Server put the thing that doesn't exist first so it's faster?
*/
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 1
    AND SecondColumn = 0;

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 0
    AND SecondColumn = 1;
GO
/* 
/poll "Now what missing index recommendations will we get?" "SQL will ask for two different indexes" "Both will ask for FirstColumn, SecondColumn" "Both will ask for SecondColumn, FirstColumn"  anonymous

*/




/* 
TAKEAWAY #1:
missing index column order is determined by column order in the table.

It's kinda disappointing, but at least there's a little more logic than that.
And I know what you're thinking: selectivity matters, right? Wrong.
*/



/* Next, instead of equality searches ( = ), try inequality ( <> ).*/
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn <> 1
    AND SecondColumn <> 1;

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE SecondColumn <> 1
    AND FirstColumn <> 1;
GO


/* Still the same: FirstColumn, SecondColumn.

But now try equality on one field, inequality on the other:
*/
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 1    /* EQUALITY */
    AND SecondColumn <> 1; /* INEQUALITY */ 

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn <> 1   /* INEQUALITY */
    AND SecondColumn = 1;  /* EQUALITY */
GO


/* 
TAKEAWAY #2:
Equality searches go first
Inequality searches go second.

To see the difference, look in the execution plan XML, and you'll see the
equality searches are listed first, then the inequality searches.
*/



/* Another example of inequality vs equality: */
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn IS NULL       /* EQUALITY */
    AND SecondColumn IS NOT NULL; /* INEQUALITY */ 

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn IS NOT NULL   /* INEQUALITY */
    AND SecondColumn IS NULL;     /* EQUALITY */
GO



/* 
So it works like this:
Equality searched fields, in the order that the columns appear in the table
Inequality searched fields, ditto



So now it's your turn.
Without running the query, GUESS the index recommendation orders:
*/
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE ThirdColumn = 0
    AND FourthColumn <> 1;
GO
/* 
/poll "What will be the missing index recommendation?" "ThirdColumn, FourthColumn" "FourthColumn, ThirdColumn" "Something else"  anonymous

*/

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FourthColumn <> 1
    AND SecondColumn = 0;
GO
/* 
/poll "What will be the missing index recommendation?" "SecondColumn, FourthColumn" "FourthColumn, SecondColumn" "Something else"  anonymous

*/


/* Advanced test: guess the order of these: */
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn <> SecondColumn;
GO
/* 
/poll "What will be the missing index recommendation?" "FirstColumn, SecondColumn" "SecondColumn, FirstColumn" "Something else"  anonymous

*/
  
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FourthColumn = 0
    AND FirstColumn <> 0
    AND ThirdColumn = 0;
GO
/* 
/poll "What will be the missing index recommendation?" "FirstColumn, ThirdColumn, FourthColumn" "ThirdColumn, FourthColumn, FirstColumn" "FourthColumn, FirstColumn, ThirdColumn" "Something else"  anonymous

*/
	
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn <> 0
    AND SecondColumn IS NULL
    AND FourthColumn IS NOT NULL
    AND ThirdColumn = 1;
GO





/* 
This isn't just about execution plan hints, either: the same logic drives
the missing index recommendations in the DMVs. Run a few queries to generate
missing indexes:

Turn off execution plans first, then:
*/
SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 0
    AND SecondColumn = 0; /* BOTH EQUALITY */

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn <> 1
    AND SecondColumn <> 1; /* BOTH INEQUALITY */

SELECT 'Hi Mom!'
  FROM dbo.DiningRoom
  WHERE FirstColumn = 0
    AND SecondColumn <> 0; /* MIXED */

GO 10


/* And check the missing index DMVs: */
sp_BlitzIndex @TableName = 'DiningRoom'


/* 
SQL Server will recommend 3 similar indexes on FirstColumn, SecondColumn.
We're not doing any kind of advanced de-duping in sp_BlitzIndex: index tuning
still involves manual intervention to figure out which field should really
go first.

But now that you know the trick, you can do a better job of interpreting
real-life recommendations.

To learn more about how this works:

How does SQL Server determine key column order in missing index requests?
https://dba.stackexchange.com/questions/208947/
Shout out to Bryan Rebok for figuring this out!
*/
DROP TABLE dbo.DiningRoom;









/*
License: Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
More info: https://creativecommons.org/licenses/by-sa/3.0/

You are free to:
* Share - copy and redistribute the material in any medium or format
* Adapt - remix, transform, and build upon the material for any purpose, even 
  commercially

Under the following terms:
* Attribution - You must give appropriate credit, provide a link to the license,
  and indicate if changes were made.
* ShareAlike - If you remix, transform, or build upon the material, you must
  distribute your contributions under the same license as the original.
*/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

How Are Missing Index Hints Built?

Here's what you're going to learn in this demo:

* How SQL Server picks missing index column order

* What SQL Server doesn't consider: selectivity or statistics

This demo requires:

* Any supported version of SQL Server or Azure SQL DB

(although the 10M row table creation can be pretty slow in Azure)

/* Let's create a table with a few columns, and insert 100,000 identical rows: */

--DROP TABLE dbo.DiningRoom;

CREATE TABLE dbo.DiningRoom

(FirstColumn INT,

SecondColumn INT,

ThirdColumn INT,

FourthColumn INT,

FifthColumn INT,

SixthColumn INT

);

INSERT INTO dbo.DiningRoom (FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn, SixthColumn)

SELECT TOP 10000000 1, 1, 1, 1, 1, 1

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

CROSS JOIN sys.all_columns ac3;

SELECT TOP 100 * FROM dbo.DiningRoom;

/* Turn on actual execution plans, and check the missing index requests: */

SET STATISTICS TIME, IO ON;

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 0;

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE SecondColumn = 0;

/* Simple so far. Now let's try two columns: */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 0

AND SecondColumn = 0;

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE SecondColumn = 0

AND FirstColumn = 0;

/poll "What missing index recommendations will we get?" "SQL will ask for two different indexes" "Both will ask for FirstColumn, SecondColumn" "Both will ask for SecondColumn, FirstColumn" anonymous

What about selectivity?

If our where clause looks for one thing that doesn't exist, and one thing that

does, will SQL Server put the thing that doesn't exist first so it's faster?

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 1

AND SecondColumn = 0;

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 0

AND SecondColumn = 1;

/poll "Now what missing index recommendations will we get?" "SQL will ask for two different indexes" "Both will ask for FirstColumn, SecondColumn" "Both will ask for SecondColumn, FirstColumn" anonymous

TAKEAWAY #1:

missing index column order is determined by column order in the table.

It's kinda disappointing, but at least there's a little more logic than that.

And I know what you're thinking: selectivity matters, right? Wrong.

/* Next, instead of equality searches ( = ), try inequality ( <> ).*/

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn <> 1

AND SecondColumn <> 1;

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE SecondColumn <> 1

AND FirstColumn <> 1;

/* Still the same: FirstColumn, SecondColumn.

But now try equality on one field, inequality on the other:

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 1 /* EQUALITY */

AND SecondColumn <> 1; /* INEQUALITY */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn <> 1 /* INEQUALITY */

AND SecondColumn = 1; /* EQUALITY */

TAKEAWAY #2:

Equality searches go first

Inequality searches go second.

To see the difference, look in the execution plan XML, and you'll see the

equality searches are listed first, then the inequality searches.

/* Another example of inequality vs equality: */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn IS NULL /* EQUALITY */

AND SecondColumn IS NOT NULL; /* INEQUALITY */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn IS NOT NULL /* INEQUALITY */

AND SecondColumn IS NULL; /* EQUALITY */

So it works like this:

Equality searched fields, in the order that the columns appear in the table

Inequality searched fields, ditto

So now it's your turn.

Without running the query, GUESS the index recommendation orders:

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE ThirdColumn = 0

AND FourthColumn <> 1;

/poll "What will be the missing index recommendation?" "ThirdColumn, FourthColumn" "FourthColumn, ThirdColumn" "Something else" anonymous

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FourthColumn <> 1

AND SecondColumn = 0;

/poll "What will be the missing index recommendation?" "SecondColumn, FourthColumn" "FourthColumn, SecondColumn" "Something else" anonymous

/* Advanced test: guess the order of these: */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn <> SecondColumn;

/poll "What will be the missing index recommendation?" "FirstColumn, SecondColumn" "SecondColumn, FirstColumn" "Something else" anonymous

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FourthColumn = 0

AND FirstColumn <> 0

AND ThirdColumn = 0;

/poll "What will be the missing index recommendation?" "FirstColumn, ThirdColumn, FourthColumn" "ThirdColumn, FourthColumn, FirstColumn" "FourthColumn, FirstColumn, ThirdColumn" "Something else" anonymous

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn <> 0

AND SecondColumn IS NULL

AND FourthColumn IS NOT NULL

AND ThirdColumn = 1;

This isn't just about execution plan hints, either: the same logic drives

the missing index recommendations in the DMVs. Run a few queries to generate

missing indexes:

Turn off execution plans first, then:

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 0

AND SecondColumn = 0; /* BOTH EQUALITY */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn <> 1

AND SecondColumn <> 1; /* BOTH INEQUALITY */

SELECT 'Hi Mom!'

FROM dbo.DiningRoom

WHERE FirstColumn = 0

AND SecondColumn <> 0; /* MIXED */

GO 10

/* And check the missing index DMVs: */

sp_BlitzIndex @TableName = 'DiningRoom'

SQL Server will recommend 3 similar indexes on FirstColumn, SecondColumn.

We're not doing any kind of advanced de-duping in sp_BlitzIndex: index tuning

still involves manual intervention to figure out which field should really

go first.

But now that you know the trick, you can do a better job of interpreting

real-life recommendations.

To learn more about how this works:

How does SQL Server determine key column order in missing index requests?

https://dba.stackexchange.com/questions/208947/

Shout out to Bryan Rebok for figuring this out!

DROP TABLE dbo.DiningRoom;

License: Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)

More info: https://creativecommons.org/licenses/by-sa/3.0/

You are free to:

* Share - copy and redistribute the material in any medium or format

* Adapt - remix, transform, and build upon the material for any purpose, even

commercially

Under the following terms:

* Attribution - You must give appropriate credit, provide a link to the license,

and indicate if changes were made.

* ShareAlike - If you remix, transform, or build upon the material, you must

distribute your contributions under the same license as the original.

How to Install Azure Data Studio to Work with SQL Server

Last Updated July 23, 2020

Brent Ozar

Production Database Administration

Download Azure Data Studio on your desktop. Do this on your desktop, not the SQL Server itself. You always want to run management tools from your own machine to reduce the overhead on the SQL Server itself. After it downloads, install it – just accept the defaults. During the installation process, it may ask:

You don’t need to register Azure Data Studio. You can leave the checkbox enabled for Add to PATH (and no, I have no idea why PATH is CAPITALIZED), but you don’t need to restart immediately for the purposes of this blog post, nor do you need to do it quickly.

Launch Azure Data Studio. After the launch, it may ask if you want to enable preview features, and if so, click Yes.

Open the Extensions pane. Click View, Extensions. This is where you can enable all kinds of cool add-ons that give Azure Data Studio more power. Today, we’re looking for a power-up that will give us the ability to manage SQL Server Agent jobs, so type in SQL Server Agent in the search box:

And click the Install button. Which button? It doesn’t matter. Microsoft just likes to make it easier for you by spraying identically named buttons all over the screen. Relax. It’s not like these extra buttons cost you extra. This is all free to you. That’s the beauty of open source.

Now, you’re ready to connect to a SQL Server.

Click the Connections button up at the top left. It looks like a game of Qix. I would say that it looks like a physical server, but it’s been so long since you’ve seen a physical server that the comparison is about as relevant as a Nintendo game.

You can also access the Connections window by clicking Control-Shift-D.

Click on the New Connection button to add a connection to your SQL Server, and then fill in the details. For example, here’s my server’s connection details:

Server – the DNS name or IP address where your SQL Server answers connection requests. This is usually the same as the server name itself, but if you have fancier setups like named instances or non-default port numbers, you’ll need to specify those here.

Authentication type – probably Windows, try that first. If that fails, you either don’t have access to the server, or it’s configured with SQL authentication.

Database – leave at Default. If you pick a specific database, then you won’t be able to see the list of databases after you connect.

Server group – leave at default.

Name – this is a user-friendly name just for your own purposes. For me, I’m calling mine “Production” because sometimes my servers have long & complicated names, and it’s easier to tell at a glance which one I’m connected to. If you have lots of servers, you wouldn’t want to use “Production” – you might want to use, say, “Production – Accounting” to clarify which server you’re on.

If everything went well, you’ll see a list of databases like this:

If things did NOT go well, and you need to get help, here are a few places to get help:

The #AzureDataStudio channel in the community Slack – go here to get an invite if you’re not in it yet
The #SQLhelp channel in the community Slack
The #SQLhelp hash tag on Twitter

Just keep in mind that the folks on the other side of the screen are volunteers, so say please and thank you a lot when they help you out with your challenges.

How to Get Started with SQL Server Management Studio

Last Updated July 13, 2020

Brent Ozar

Production Database Administration

In the big picture, you need to:

Download the free Microsoft SQL Server Management Studio and install it, accepting the defaults
Connect to your SQL Server
Create a low-privileged login so that you don’t accidentally drop objects
Learn to switch back & forth between the low-privileged account and your regular one

Let’s get started!

1. Download and install SSMS (but not on the server itself)

The first step is really easy: go here to get the latest version of SSMS and install it. If you already have a version of SSMS, the installer will automatically update it to the latest version. It doesn’t matter what version of SQL Server you’re running in production: as long as you’re running a currently supported version of SQL Server (2012 & newer as of this writing), you always wanna run the latest version of SSMS, which will include a ton of bug fixes.

Do this installation on your desktop or laptop, not on the SQL Server itself. Over time, you’ll learn that running SSMS (as with any other app) on the SQL Server itself will slow it down. You wouldn’t remote desktop into the SQL Server and start playing Fortnite, now, would you? Don’t answer that. You probably would. You’re the kind of person who reads this blog, after all, and I … let’s just stop there. Install SSMS on your desktop.

2. Connect to your SQL Server

After launching SSMS, you get a connection dialog:

Server name – the DNS name or IP address where your SQL Server answers connection requests. This is usually the same as the server name itself, but if you have fancier setups like named instances or non-default port numbers, you’ll need to specify those here.

Authentication – probably Windows, try that first. If that fails, you either don’t have access to the server, or it’s configured with SQL authentication. You might have a username & password on a post-it note somewhere, like from the person who installed it, and it might have a username of “sa”. In that case, go ahead and use that for now. In the next step, we’ll set you up a low-privilege account.

Click Connect, and you’ll be handsomely rewarded with a window that looks like this:

At this point, you’re able to do … all kinds of dangerous things, actually. We need to fix that so you don’t do something stupid.

3. Create yourself a low-privileged login.

Start a new query by clicking File, New, Database Engine Query, or right-click on the SQL Server name and click New Query:

You’ll get an empty new-query window. Copy/paste the below into your new query window so we can find out if you have Windows-only authentication turned on:

SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')

1	SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')

If the result is 1, that means your SQL Server only allows Windows logins. In that case, I deeply apologize, but I’m not covering that yet in the scope of this blog post.

If the result is 0, good news! You can create SQL logins. To do that, copy/paste the below into your new-query window, and note that you have some changes to make:

USE [master]
GO
CREATE LOGIN [brent_readonly] WITH PASSWORD=N'changeme', 
DEFAULT_DATABASE=[tempdb], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
GRANT VIEW SERVER STATE TO [brent_readonly];
GO

USE [master]

CREATE LOGIN [brent_readonly] WITH PASSWORD=N'changeme',

DEFAULT_DATABASE=[tempdb], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

GRANT VIEW SERVER STATE TO [brent_readonly];

Change brent_readonly to be whatever username you want: typically your regular username, but append _readonly to it so you know that it’s your less-privileged account. Note that you have to change “brent_readonly” in two places.

Change the password from “changeme” to whatever you want.

If you get an error that says:

Msg 15118, Level 16, State 1, Line 3
Password validation failed. 
The password does not meet the operating system 
policy requirements because it is not complex enough.

Msg 15118, Level 16, State 1, Line 3

Password validation failed.

The password does not meet the operating system

policy requirements because it is not complex enough.

Then you need to use a more complex password.

Finally, let’s make sure the new login can’t write anything in your existing databases. Copy/paste this in, change brent_readonly to your login name (note that it’s in 3 places), and run it:

sp_msforeachdb 'USE [?];
CREATE USER [brent_readonly] FOR LOGIN [brent_readonly];
ALTER ROLE [db_denydatawriter] ADD MEMBER [brent_readonly];'

sp_msforeachdb 'USE [?];

CREATE USER [brent_readonly] FOR LOGIN [brent_readonly];

ALTER ROLE [db_denydatawriter] ADD MEMBER [brent_readonly];'

That only affects the databases you have in place today. If someone restores a database into this environment, you’ll be able to write to it. If you’ve got some time and you’re willing to roll up your sleeves a little, here’s how to deny writes in all new databases long term.

4. Learn to switch back & forth between accounts.

Now that you have a low-privileged account, you’re going to want to use this by default when you connect into SQL Servers with Management Studio. This helps prevent you accidentally causing a resume-generating event.

Close SSMS, and reopen it again. This time, at the connection dialog:

Choose SQL Server authentication because we created a new SQL login, and then type in your low-privileged username and password. Click Connect, and you’re now working a little more safely, without the superpowers of your regular domain login.

If you do need to switch over to your regular high-permission account, you could close SSMS entirely and reopen it, but there’s an easier way. To change accounts for the current query window, click the change-connection button at the top right. It looks like a plug/unplug button:

That’ll give you the connect-to-server dialog box. Note that the login change only affects the currently open query window: that’s the safest way to minimize the damage. Then, as soon as you’re done doing high-permissions stuff, close that window, and you’re back to your regular low-permission stuff.

Next steps for learning

Now that you have an account that’s safe to use for learning, here are a few next steps of tools to explore on how to get to know your SQL Server better.

How I Configure SQL Server Management Studio – my setup tips
sp_Blitz – totally free health check script that warns you about dangerous things in your SQL Server that may present problems later
How to do a health check – my free process for writing up a SQL Server’s health and performance issues

Announcing Saturday & Sunday Office Hours.

Last Updated July 20, 2020

Brent Ozar

Videos

During the quarantines, I’m doing free training. It looks like we’re going to be stuck inside here for another long while, so for the next couple of months, I’m bringing back Office Hours, but on a different schedule.

Every Saturday & Sunday morning from 6AM-8AM Pacific (iCal), you can catch me on my Twitch, YouTube, or Facebook channels. I’ll be:

Live coding in the First Responder Kit Github repo
Writing blog posts, demos, and presentations
Answering your questions

I totally understand that weekend mornings aren’t a great time fit for everybody’s schedule, but it’s the time that works best for my own schedule (given my existing work.) But if you can’t make it live, no worries – all of the streams will be recorded and available in Twitch, YouTube, and Facebook. If I have a particularly good stream, I’ll put it here on the blog as well.

Wanna see what they’re like? Here’s this past Saturday’s stream when I worked on pull requests in the First Responder Kit:

And here’s Sunday’s stream when I wrote a blog post about date table query plans and told the story of when Microsoft offered me a job:

If you follow me on my Twitch, YouTube, or Facebook channels, I also do other surprise broadcasts throughout the day. As I work, I just check to see if it’s something I can share with y’all publicly, and if so, I start a stream. I won’t usually announce topics ahead of time, but I will say that this coming Saturday’s stream will involve Clippy, a green spandex suit, and a pair of giant white hands. See you there!

Get Alerted When Your SQL Server Restarts with sp_SendStartupEmail

Last Updated September 3, 2020

Brent Ozar

Monitoring

When your SQL Server restarts, how do you find out?

I’ve seen a few senior DBAs do a really smart trick: they write a stored procedure to send them an email, and then they mark that stored procedure as a startup stored procedure so that it runs automatically whenever SQL Server starts up. (You could also try this with an Agent job scheduled to run at Agent startup, but … Agent may not actually start.)

So I wrote sp_SendStartupEmail to do just that, and the body of the email lists any databases with unusual states. It looks like this:

You can get it from my Github repo here, but it won’t be part of the First Responder Kit – I just wanted to put it out in Github so y’all could check in changes if you wanted. The documentation is in that script. (No, it’s not self-documenting code, hahaha – the documentation is in the comments.)

You can watch me write it during one of my recent Twitch / YouTube / Facebook streams:

What If You Really DO Need to Shrink a Database?

Last Updated November 2, 2022

Brent Ozar

Production Database Administration

You’ve heard that shrinking a database is bad because it introduces both external and internal fragmentation, it causes blocking, it causes transaction log growth while it runs, and it’s slow and single-threaded. You understand that if it’s just a matter of 10-20-30% of a database, and the database is only 100-200GB, you might as well just leave the space there, because you’re gonna end up using it anyway.

But your situation is different:

Your database is 1TB or larger
You’ve deleted 50% of the data
You have 500GB+ empty space
You’re never going to need that space because you’re now doing regular deletions and archiving

You’ve been tasked with reducing the database size to make restores to other environments easier, like when you need to restore to a QA or development environment. You don’t want those servers to need so much useless space.

Don’t shrink. Do this instead:

Add a new filegroup, add new empty files in it, and set it to be the new default filegroup
Move objects to the new filegroup using CREATE INDEX…WITH DROP_EXISTING = ON, ONLINE = ON commands
When you’ve moved all the objects over, shrink the old filegroup, and it’ll shrink super-quick
Just leave it there – this is your new database layout

This approach has several key advantages over shrinking:

It can use parallelism: you can specify how many cores to use with MAXDOP hints. This is really helpful on servers with MAXDOP = 1 at the server level because your query-level hint will override it, even overriding it upwards.
It can move a lot more than one 8KB page at a time. Heck, you can even do several index recreations simultaneously on different threads if you want to, really churning through the work quickly.
You pick the days/times for each object: maybe some of your objects have very heavy concurrency during some time windows, and you want to avoid touching those until a maintenance window.
You pick the settings for each object: rebuilding an index with ONLINE = ON is slower. Some of your objects might be archive tables or unused during certain days/times, so you can use ONLINE = OFF on those to get faster performance.

But it does have a few drawbacks, too:

It’s faster, but it’s also higher load: shrinking a database is a low overhead process: it’s hard for anybody to notice you moving just one 8KB page at a time, with just one CPU core. Index recreations, buckle up: people are gonna notice when you throw a ton of CPU cores at the problem and really start hammering your storage. This is just the flip side of the coin to finishing faster: if you wanna finish faster, you’re gonna do more work in less time.
This also means it generates more logs, faster: because we’re moving so much data and it’s a fully logged process, this can present problems for transaction log backups, log file sizes, database mirroring, Always On Availability Groups, and storage/VM replication. Ease into this gradually, starting with your smaller tables first, so you can see the impact it’s having on your transaction log sizes.
ONLINE = ON isn’t fully online: even online index recreations need a momentary schema mod lock in order to finish their work. Thankfully, since SQL Server 2014, we’ve had the WAIT_AT_LOW_PRIORITY option to help mitigate that problem.
You have to do some prep work: it’s easy to run DBCC SHRINKDATABASE, but the index recreation approach will take much more work if you want to leverage all the cool advantages I discussed above.

Thankfully, there’s help on that last one: Bob Pusateri has an in-depth writeup and a script to help.

Where is the SQL Server Community Networking Online?

Last Updated July 13, 2020

Brent Ozar

Professional Development

You’ve stumbled upon this blog, and you’re having a pretty good time. Now you’re wondering, “Where else can I network with database folks, get to know them, and talk shop?” You’re not getting out due to the quarantines, so your local user group isn’t fulfilling your networking cravings. You wanna know what else is going on. I’ve got a ton of good news.

We’re freakin’ everywhere, and it’s all free to join. Here are some of my favorite community gathering spots – but I don’t want you to think I use all of ’em all the time, by any means. Dabble around a little, then pick the ones that works the best for your own style.

SQLCommunity.Slack.com – get your instant free invite, then join channels like #SQLhelp for Q&A, #welcome for general chat. To see the list of popular channels, click the + sign at the top right of the channel list.

Then in the Sort method at the top right, choose most members to least, and that’ll find the lively rooms. I would disable notifications in your Slack client though – people tend to talk a LOT in here.

#SQLhelp, #SQLfamily, and #SQLserver on Twitter – the more you use Twitter, the more you start to follow specific people, have discussions, and build up your own personal network. This is probably my favorite out of the options in this post. In the beginning, before you’ve picked the folks you want to follow, just read posts with the #SQLfamily tag and find folks to follow.

DBA.StackExchange.com – like StackOverflow.com, but for database questions. I’m now up to 3 different ways to ask questions – Slack, Twitter, and DBA.se – but here’s how I think of ’em: Slack & Twitter are best for short, clear questions that are going to require a lot of back-and-forth discussion to get an answer, but you’re pretty sure you’ll get an answer quickly. DBA.se is for questions that will require more detailed documentation, and may also need to stick around on the web for a while in order to get a really good answer. (Questions disappear pretty quickly from Slack and Twitter.) Before you post a DBA.se question, check out Writing the Perfect Question by Jon Skeet. It’s a lot of work, but when you have a complex question, the free consulting help from the community pays off.

Reddit – /r/sqlserver, /r/sql, and /r/database – because these are general subreddits, the discussion isn’t very targeted: you get a wide mix of technical questions, career advice, and just interesting links. If you had to only pick one place to network, this probably wouldn’t be a bad choice because it gets you a wide variety. There are a lot of homework questions posted here, though.

SQLServerCentral.com forums – forums are so 2000, I know, but hear me out: a lot of us in the database industry aren’t exactly spring chickens. If you’re the kind of person who enjoys a good old-school forum, there are hundreds of other folks actively chatting over at SSC. Check the Active Threads and Latest Topics.

Blog comments: I know it’s gonna sound strange, but the best part of some web sites are actually the comments. For example, I read Hacker News and BringATrailer auctions mostly for the insightful (but also crazy) comments. If you read blog posts for the comments, it’s best to read the posts (and the comments) about a week after they originally go live. That way you’re seeing the regular readers’ comments, and the author’s responses, but you aren’t subjected to the crazy Googlers who stumbled in six months later and want to ask totally irrelevant stuff. Here’s how I do it: I share my favorites, all my subscriptions, or OPML file – there are a lot of people publishing a lot of good free content these days. I read hundreds of posts per week to keep up, so if you haven’t got time for the pain, just hit my live favorites or my live bookmarks. But just check into those once per week, and read the comments then.

In summary, the SQL Server community is one of the most active, open, and friendly free communities I’ve ever seen. Folks love sharing their knowledge and meeting new peers. Find the platform that works the best for you, and you can enjoy free networking with your peers all year long.

Just be aware that as you start to do networking, especially in the year 2020, you’re going to encounter a lot of folks who use networking spaces to talk about other stuff, not just SQL Server. People have all kinds of opinions about all kinds of subjects, and thanks to the online disinhibition effect, they sure like to share their opinions publicly. It’s up to you to find the right medium that works best for you. Don’t fall into doomscrolling, spending all your time reading everyone’s every opinion about every news story.

Curate what you see on a daily basis. If someone isn’t bringing you joy, unfollow them. If a social network isn’t making you happy, leave it. Use free networking to help you survive and thrive during these tough times: it can be a force for good and happiness.

Updated First Responder Kit for July 12, 2020: Fixing sp_BlitzFirst @ExpertMode

Last Updated July 12, 2020

Brent Ozar

First Responder Kit Updates

I don’t normally do multiple releases per month, but sp_BlitzFirst had a pretty big bug in the last release for those of us who do live performance tuning. In the July release, a well-meaning contributor tried to make sp_BlitzFirst faster when @OutputType = ‘NONE’, but inadvertently also made it faster when @ExpertMode = 1 – by skipping the output. Oopsie.

If you’re working directly with the releases on Github, note that the “master” branch has been renamed to “main” instead.

To get the new version:

Download the updated FirstResponderKit.zip
Azure Data Studio users with the First Responder Kit extension:
ctrl/command+shift+p, First Responder Kit: Import.
PowerShell users: run Install-DbaFirstResponderKit from dbatools

sp_BlitzFirst Changes

Fix: wait stats weren’t being returned when @ExpertMode = 1. (#2463, thanks Rich Benner and Joseph LaBonde.)

sp_BlitzIndex Changes

Fix: SQL Server has a bug that ignores isolation level hints on sys.identity_columns (note: no response from Microsoft on that one despite being open for several months, and hello, blue badges, that is why people get so pissed off about feedback.azure.com being like yelling into the void. This is a bug in your product, and you’re not even taking the time to read the bugs that get reported. HELLOOOO) Anyhoo, we get blocked by offline index rebuilds. To fix it, we now have a 1-second lock timeout, and sp_BlitzIndex will simply bail out if there is blocking. (#2176)

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. If you need a free invite, hit SQLslack.com. Be patient – it’s staffed with volunteers who have day jobs.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes me!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

Want Your Own Private Conference?

Last Updated July 8, 2020

Brent Ozar

Company News

Your team has been reading my blog, watching my free public streams, and using the First Responder Kit scripts for years. They’ve gone as far as they can go on their own, learning from Google, and now they’re ready for the next level.

I can bring the conference to you – except way better than a conference, because all of the sessions are specifically relevant to you, and you have plenty of private Q&A time with me to discuss SQL Server issues your company is facing – things you can’t ask in a busy public conference.

Here’s how it works:

Step 1: choose topics from my training catalog so you can figure out how many days of training you want.

Step 2: Check my availability, add the dates you want, then check out here. My available dates are at the bottom of that page. Payment is required to hold your dates. If you check out with the Check/PO option, I’ll contact you when I’ve received your check payment (address is in the invoice), and we can coordinate dates at that time.

Step 3: we get together on your dates and have fun learning! Let’s do this.

The 201 Buckets Problem, Part 2: How Bad Estimates Backfire As Your Data Grows

Last Updated July 7, 2020

Brent Ozar

Statistics

In the last post, I talked about how we don’t get accurate estimates because SQL Server’s statistics only have up to 201 buckets in the histogram. It didn’t matter much in that post, though, because we were using the small StackOverflow2010 database.

But what happens as our data grows? Let’s move to a newer Stack Overflow database, the 2018-06 one that I use for my Mastering training classes. We’ll create the same index, giving us the same statistics, and then look at the histogram to see how Miami grew over time:

USE StackOverflow;
GO
CREATE INDEX Location ON dbo.Users(Location);
GO
SELECT COUNT(*) FROM dbo.Users;
GO

USE StackOverflow;

CREATE INDEX Location ON dbo.Users(Location);

SELECT COUNT(*) FROM dbo.Users;

Now we’re up to about 9 million users:

And the statistics histogram for Miami’s area;

The data distribution has changed, but Miami still isn’t big enough to have its own bucket. Now:

Miami, FL is between México and Minneapolis (note that Michigan no longer has its own bucket)
AVG_RANGE_ROWS = 13.3 means that any location in between México and Minneapolis will get an estimate of 13.3 rows

So when we run our Miami query, what’s it look like now:

Estimated number of rows in Miami, FL is 13, but in actuality, 625 rows come out of the index seek. The variance is starting to grow now that our data sizes have grown larger.

So what do we do about Miami’s inaccurate estimates?

Again, in this case…nothing. 625 rows vs 13 rows isn’t really that big of a deal. In my Fundamentals of Query Tuning class, I talk about how you want to read query plans from right to left, looking for places where estimates vs actual are 10x off or more. In this case, we’re 48x off, but…it doesn’t really matter that much. SQL Server still allocated enough memory to do the sort, and it didn’t spill to disk. (Note there’s no yellow bang on the sort operator.)

However, as I noted yesterday, it will become a big deal if we join to other tables. Let’s see what happens if we add a join to Comments, for example, to display the top comments left by people from Miami:

SELECT TOP 1000 u.DisplayName, c.Score, c.Text
FROM dbo.Users u
INNER JOIN dbo.Comments c ON u.Id = c.UserId
WHERE u.Location = N'Miami, FL' 
ORDER BY c.Score DESC;

SELECT TOP 1000 u.DisplayName, c.Score, c.Text

FROM dbo.Users u

INNER JOIN dbo.Comments c ON u.Id = c.UserId

WHERE u.Location = N'Miami, FL'

ORDER BY c.Score DESC;

The actual plan:

We read plans from right to left, top to bottom, to understand what SQL Server did in what order. (You can also read from left to right in some cases, and I’m just legally required to say that, because if I don’t, Grant Fritchey and Hugo Kornelis will smother me as I sleep.)

So from right to left:

SQL Server did an index seek on Location = Miami, FL, and expected only 13 rows to come back
In reality, 625 rows came back
So we also had to do 625 key lookups
We had to do 625 index seeks on Comments.UserId because each person has their own set of comments
In total, we found 14,176 comments left by people in Miami, FL, so that poor Sort operator never stood a chance. He has a yellow bang because he ended up spilling to disk.

So, really, now, seriously,
what do we do about Miami’s inaccurate estimates?

In this example, the query still runs fairly quickly. But if you run into this kind of problem with a larger query, larger spills to disk, larger estimate variations, and so forth, here are some techniques you can consider for performance tuning:

Breaking the query up into phases using techniques like temp tables
Query hints, like different cardinality estimator versions or optimization hints (like optimize-for, asking for a larger grant, etc)
Newer compatibility levels with tricks like adaptive joins or adaptive memory grants

But the big takeaway for me is that 201 buckets in a histogram just isn’t enough to portray real-world data skew, especially as your data size grows into the millions of rows and beyond.

The 201 Buckets Problem, Part 1: Why You Still Don’t Get Accurate Estimates

Last Updated July 7, 2020

Brent Ozar

Statistics

I’ll start with the smallest Stack Overflow 2010 database and set up an index on Location:

USE StackOverflow2010
GO
CREATE INDEX Location ON dbo.Users(Location);
GO
SELECT COUNT(*) FROM dbo.Users;
GO

USE StackOverflow2010

CREATE INDEX Location ON dbo.Users(Location);

SELECT COUNT(*) FROM dbo.Users;

There are about 300,000 Users – not a lot, but enough that it will start to give SQL Server some estimation problems:

When you create an index, SQL Server automatically creates a statistic with the same name. A statistic is one 8KB page with metadata about the object’s contents. In this case, it’s a list of up to 201 Location values, and the number of people who live in each of those locations. You can see the statistics with DBCC SHOW_STATISTICS, as we talk about in my free class How to Think Like the SQL Server Engine:

This helps SQL Server estimate how many rows will be returned. If the location you’re looking for happens to be one of the outliers that SQL Server chose as large enough to get its own bucket in the histogram,like Ahmadabad, India above, then SQL Server knows a lot about it:

RANGE_ROWS = 54: this means that in the range of Locations between Adelaide, Australia and Ahmadabad, India, there were 54 rows, excluding Adelaide and Ahmadabad.
EQ_ROWS = 159: this means that Ahmadabad (the RANGE_HI_KEY) had exactly 159 rows.
DISTINCT_RANGE_ROWS = 25: this means that of the 54 rows in the RANGE_ROWS group, there were 25 distinct locations.
AVG_RANGE_ROWS = 2.16: this means that if you pass in a location between Adelaide and Ahmadabad, SQL Server will guess that it has 2.16 rows.

For example, if I query for Ahmadabad, India, which happens to be one of the outliers that got its own bucket, your estimates will be pretty doggone accurate, like this:

SELECT * FROM dbo.Users 
WHERE Location = N'Ahmadabad, India' 
ORDER BY DisplayName;

SELECT * FROM dbo.Users

WHERE Location = N'Ahmadabad, India'

ORDER BY DisplayName;

Because Ahmadabad is an outlier, and it has its own bucket as shown in the screenshot above, SQL Server estimates exactly 159 rows. (See the EQ_ROWS value in the screenshot above.)

This works really well – BUT, only if you’re one of the outliers. What if you’re a big location, but not big enough of an outlier to get your own statistics bucket? Like, say, Miami, FL?

Here, I’ve even used OPTION (RECOMPILE), and yet I’m getting an estimate of just 10 rows when there are actually 99 Floridians? To find out, scroll down in the DBCC SHOW_STATISTICS output until you’re in the Miami area:

There’s your problem. Miami, FL is in between Mexico and Michigan (strangely appropriate), and so the histogram means:

Miami, FL isn’t there
Miami, FL is in the bucket between Mexico and Michigan
AVG_RANGE_ROWS = 10.36 means that if you’re looking for any location between Mexico and Michigan, SQL Server will estimate that the location has 10.36 people in it – and in this case, that’s not so accurate for Miami.

Miami, FL isn’t big enough to be one of the 201 outliers featured in the statistics buckets, but it’s large enough that it has a relatively unusual number of people (and boy, as Richie can tell you, the people in Miami are unusual.)

Will updating statistics fix this?

To find out, we’ll give SQL Server the best possible chance at tackling the problem:

UPDATE STATISTICS dbo.Users WITH FULLSCAN;
GO
DBCC SHOW_STATISTICS('dbo.Users', 'Location');
GO

UPDATE STATISTICS dbo.Users WITH FULLSCAN;

DBCC SHOW_STATISTICS('dbo.Users', 'Location');

Which still produces the same stats:

No matter how many times you update stats, there are still just only 201 buckets max, and Miami doesn’t have enough data to be one of those outliers. To find similar locations, do a TOP 1000, and the folks in the 200-1000 range are probably going to be your outliers that get bad estimates:

SELECT TOP 1000 Location, COUNT(*) AS recs
FROM dbo.Users
GROUP BY Location
ORDER BY COUNT(*) DESC;

SELECT TOP 1000 Location, COUNT(*) AS recs

FROM dbo.Users

GROUP BY Location

ORDER BY COUNT(*) DESC;

And sure enough, down over the 200 range is our Floridian friend:

Note that the top 200 rows by count(*) aren’t necessarily the ones who get their own buckets. Depending on data distribution and who’s next to each other, some of these may be grouped together. Overall, the 200-1000 range are great examples of possible problematic estimates, though.

So what do we do about Miami’s inaccurate estimates?

Well, in this case…nothing. 99 rows vs 10 rows isn’t really that big of a deal, in the grand scheme of things. It will become a big deal if:

The data size starts to grow – note that I’m using the StackOverflow2010 database in this post, but stay tuned for tomorrow’s post
We join to other tables – in which case the number of rows in each related table will start to cause a problem

Stay tuned for tomorrow’s post in which we’ll start to explore situations where this would be a bigger issue.