Blog

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 I immediately wanted to test it out and see if SQL Server would do what I thought it would do.

Parameterization reduces compilation and plan cache bloat

For anything but the simplest queries, SQL Server takes literal values in your query literally. If you don’t parameterize the query, SQL Server won’t do it for you. That means that each of these three queries will get its own execution plan (even if the execution plans are all identical):

SELECT TOP 1 Id INTO #byebye FROM dbo.Posts WHERE Id=1
GO
SELECT TOP 1 Id INTO #byebye FROM dbo.Posts WHERE Id=2
GO
SELECT TOP 1 Id INTO #byebye FROM dbo.Posts WHERE Id=3
GO

If you do this a lot, it can impact performance, particularly in a busy transactional database. It costs CPU to compile each query and SQL Server has to work much harder to manage an execution plan cache which is churning through frequent queries that are never-reused.

(For super-simple queries, simple parameterization may be applied. This is also sometimes called “auto parameterization” by people who worked with SQL Server 2000.)

White space, formatting, and comments can increase compilation and plan cache bloat for parameterized queries

I’m going to use spaces as examples in this post, but basically this is just a shorthand for talking about any variance in the syntax for executing a query.

Basically, these three queries will each get their own execution plan in SQL Server because the string being executed is different– even though it’s only different inside of a comment bracket, and even though the difference is simply the number of spaces in that comment:

SELECT TOP 1 Id /* Comment =   */ INTO #byebye FROM dbo.Posts WHERE Id=@Id
GO
SELECT TOP 1 Id /* Comment =    */ INTO #byebye FROM dbo.Posts WHERE Id=@Id
GO
SELECT TOP 1 Id /* Comment =     */ INTO #byebye FROM dbo.Posts WHERE Id=@Id
GO

The differing format in the whitespace give you just as much compilation burn and plan cache bloat as if the query were not parameterized.

‘Forced parameterization’ reduces execution plan bloat for non-parameterized queries

You can’t always re-write your whole application. The ‘forced parameterization’ database setting can help out sometimes — it tells SQL Server to look for those literal values and to try to treat them like parameters. This database setting can dramatically reduce compilation work by enabling execution plan reuse in some databases.

Like anything else, it has some limitations:

  • It impacts the whole database. If you only want to do individual queries, you have to use Plan Guides.
  • It won’t do anything if the database compatibility level is set to 80. (But plan guides will work.)

The Question: Does the ‘forced parameterization’ setting help with whitespace in query strings?

If SQL Server is smart enough to find those parameters in the string and remove them, shouldn’t it be smart enough to find the whitespace / dynamic comments and ignore them, too?

To figure this out I wrote up some test TSQL and executed it against a restored copy of the StackOverflow database.

Forced parameterization test: non-parameterized queries

For this test, I set up some simple code to execute non-parameterized dynamic SQL which also had different whitespace for each query. I ran the query 1,000 times in a loop, and then checked the execution plan cache to see how many copies of the query I had. Here’s what the code looks like:

/* This code is for test systems only, not production */
ALTER DATABASE StackOverflow SET PARAMETERIZATION FORCED;
GO

USE StackOverflow;
GO

DBCC FREEPROCCACHE;
GO

SET NOCOUNT ON;

DECLARE
    @dsql NVARCHAR(MAX)= N'SELECT TOP 1 Id /* Comment = %REPLACE-COMMENT% */ INTO #byebye FROM dbo.Posts WHERE Id=%REPLACE-ID%',
    @dsql2 NVARCHAR(MAX)= N'',
    @i INT = 1;

WHILE @i < 1001
BEGIN
    SET @dsql2=REPLACE(@dsql,N'%REPLACE-COMMENT%',REPLICATE(N' ',@i));
    SET @dsql2=REPLACE(@dsql2,N'%REPLACE-ID%',@i);

    PRINT @dsql2;
    EXEC sp_executesql @dsql2;

    SET @i=@i+1;
END

SELECT TOP 25
	qs.[query_hash] ,
	COUNT(DISTINCT plan_handle) AS number_of_plans ,
	COUNT(DISTINCT query_plan_hash) as distinct_plans,
	SUM(qs.[execution_count]) AS execution_count
FROM     sys.dm_exec_query_stats qs
GROUP BY qs.[query_hash]
ORDER BY number_of_plans DESC;
GO

Here’s what the queries looked like:

Forced-Parameterization-Comments-Non-Parameterized-String-Queries

Here’s how many execution plans I got:

Forced-Parameterization-Comments-Non-Parameterized-String

Forced parameterization “worked”, and it overcame the different whitespace for the queries! I had one plan run 1,000 times.

Forced parameterization test: parameterized queries

So, what if you have a parameterized query, but it has a problem with dynamic comments/ whitespace? Will turning on forced parameterization help that, too?

/* This code is for test systems only, not production */
USE master;
GO
ALTER DATABASE StackOverflow SET PARAMETERIZATION FORCED;
GO

DBCC FREEPROCCACHE;
GO

USE StackOverflow;
GO

SET NOCOUNT ON;

DECLARE @dsql NVARCHAR(MAX)= N'SELECT TOP 1 Id /* Comment = %REPLACE-COMMENT% */ INTO #byebye FROM dbo.Posts WHERE Id=@Id',
 @dsql2 NVARCHAR(MAX)= N'',
 @i INT = 1;

WHILE @i < 1001
BEGIN
 SET @dsql2=REPLACE(@dsql,N'%REPLACE-COMMENT%',REPLICATE(N' ',@i));

 PRINT @dsql2;
 EXEC sp_executesql @dsql2, N'@Id int', @Id=@i;

 SET @i=@i+1;
END

SELECT TOP 25
 qs.[query_hash] ,
 COUNT(DISTINCT plan_handle) AS number_of_plans ,
 COUNT(DISTINCT query_plan_hash) as distinct_plans,
 --MIN(qs.creation_time) AS min_creation_time ,
 --MAX(qs.last_execution_time) AS max_last_execution_time ,
 SUM(qs.[execution_count]) AS execution_count
FROM sys.dm_exec_query_stats qs
GROUP BY qs.[query_hash]
ORDER BY number_of_plans DESC;
GO

Here’s what the queries looked like:
Forced-Parameterization-Comments-Parameterized-String-Queries

Here’s how many execution plans I got:

Forced-Parameterization-Comments-Parameterized-String

Oh, ouch. Everything does hash out to the exact same query hash, but I got 1 plan for each execution, 1,000 plans total. For a parameterized query, the ‘Forced Parameterization’ database setting didn’t clean up the dynamic comments / whitespace for me.

Recap: what we saw

The ‘forced parameterization’ database setting is smarter than you might think, but it doesn’t fix every problem.

For our test query, it was able to force parameterize a query even if there were comments with different amounts of whitespace in it.

However, for query that was already parameterized, it didn’t fix the issue of dynamic comments/whitespace causing extra compilation and plan cache bloat.

The Most Confusing Words, Phrases, and Acronyms in SQL Server

Words are hard. Acronyms are even harder. Here’s the words that give me the most trouble when talking to people about SQL Server. And thanks to all my Twitter friends quoted here for their suggestions!

Replication

“I have a question about replication…”

“Which kind? Transactional? Merge? Peer to Peer?”

“Availability Groups.”

Technically, the word “replication” refers to very specific features to publish and subscribe to data at the table level in SQL Server. But users commonly use the word as a general term to cover other technologies used to replicate data between SQL Servers (see there, I just said “replicate”), so you never quite know what anyone means at first without clarifying.

Snapshot

“We use snapshots for that.”

“Oh, a database snapshot? Or a replication snapshot?”

“Oh, no, we do it with the SAN.”

The word snapshot is super-popular when it comes to SQL Server technologies. It’s even the name of an isolation level.

Cluster

“We’ve got a SQL Server on that cluster.”

“Oh, a Windows failover cluster?”

“Sort of? It’s virtualization.”

You can cluster in many different ways. You may have a cluster of virtualization hosts. Or you could have SQL Server installed in a Windows Failover Cluster. Or you could have both!

Cluster + AG

“We’ve got a cluster and an AG.”

“Is the cluster in the AG?”

“Yes, on all three servers.”

“Cluster” is more confusing than ever, because AlwaysOn Availability Groups require a Windows Failover Cluster. And you might combine a Windows Failover Cluster Instance with your AlwaysOn Availability Group, so there’s a failover cluster and… a cluster. Yeah, there’s a proper way to use these terms (and their related acronyms), but practically speaking nobody understands them. When things get confusing, just surrender and draw a picture. It’s easier.

HAG

“We’ve got three SQL Servers in a HAG.”

“In a what?”

I’m not sure why, but some Microsoft blogs and posts started using the acronym HAG for “High Availability Group.” Most SQL Server posts just use the shorter acronym “AG” for “Availability Group”, or spell out AlwaysOn Availability Groups.

“Truncating the log”

“We truncate the log every hour.”

“Oh. What command do you use to run that?”

“Let me look. Oh, it’s BACKUP LOG.”

“Are you using TRUNCATE_ONLY?”

“No way!”

“Truncating the log” is an older phrase that still sticks around. People start saying “truncate” instead of “backup”. They mean completely different things — and it’s really important to clarify what’s being run.

DAC

“Have you enabled the DAC?”

“No, we don’t use DAC packages. That feature was terrible.”

“No, I mean the Dedicated Admin Connection.”

Histogram

Histogram: A target for Extended Events in SQL Server 2012 or later. (But if you’re using an earlier version of Extended Events, it’s called a “Bucketizer”.)

Histogram: A table showing the distribution of values used in a statistic.

These are completely unrelated. Although I guess you could probably use an Extended Events histogram target to run a trace showing if a procedure recompiled because statistics updated on a table (which means that histogram would have changed). I have no idea if that sentence even makes any sense.

Statistics

Speaking of statistics…

“Let’s look at the stats for that query.”

“You mean the statistics on the indexes?”

“No, the statistics in dm_exec_query_stats.”

I have a huge problem with this word when I’m teaching. I frequently have to use ‘statistics’, and make sure that what I’m saying isn’t totally misleading because the word can have multiple meanings.

“In memory” data

“We’re really interested in keeping the data ‘In Memory’.”

“Do you mean by using Hekaton?”

“No, we mean by just adding memory to the server.”

It could be worse…

Until we have perfect terms, I’ll be here, using the DAC to take a snapshot of your statistics on the cluster.

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.

Interested in trying out sp_BlitzTrace™ yourself? Download it in our free SQL Server download pack.

You can also read more about rebuild vs reorganize, or sign up for our online course, Database Maintenance Starter Kit for $149.

Did You Give SQL Server 2012 Standard Edition Enough Memory?

Who needs disk when you have memory?

Who needs disk when you have memory?

It’s a bummer that SQL Server 2012 Standard Edition only allows you 64GB of memory. And it’s good news that the limit goes up to 128GB of memory in SQL Server 2014.

But are you getting as much memory as you can out of Standard Edition?

Check out Microsoft KB 2663912:

Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool). The rest of the caches in the SQL Server memory manager can use much more memory than is specified by these edition limits. …. Other caches (such as the procedure cache, the metadata cache, and so on) can consume memory up to the size specified by the “max server memory” configuration.

This means that if you’re using Standard Edition of SQL Server 2012 or higher, you should be setting your max server memory higher than the “limit” — because you can use more than you might think!

So if I need to design a VM for a SQL Server that has 300 GB of data and is using SQL Server 2012 Standard Edition with its 64GB limit for data cache, to get the biggest benefit of memory for my licensing dollar I would want something like:

  • 96GB of memory for the VM
  • SQL Server “max server memory (MB)” set at 88064 (86GB)

Different workloads will use different amounts of memory in non-buffer pool caches — and not all workloads  need 64GB of data cache, of course. But for those that do, don’t think “max server memory (MB)” is the cap on all your memory — for SQL Server 2012 and higher, it’s the cap for your buffer pool.

Thanks to Bob Ward , who mentioned this in a recent talk at SQL Intersection. (And I suspect he’s the one behind this great KB.)

Transactional Replication Architecture: Isolating Subscriber Tables

The best part about transactional replication is that it’s flexible. And the worst thing about transactional replication is that it’s flexible.

A poor replication configuration will cause you performance pains and increase your risks of development and administrative errors. Here’s one important design principle: isolate your replication subscriber tables from one another, and from non-subscriber tables.

Our example environment

We’ve got an environment with two SQL Server databases used for OLTP purposes: SalesDB and CRMDB. Each database uses SQL Server Standard Edition. The databases support different applications, and they’re on totally separate SQL Server instances (and failover clusters).

The SalesDB and CRMDB support completely different applications, but data from selected tables in them both is needed for internal reports. SQL Server transactional replication has been chosen as the technology to keep a copy of the data up to date on the reporting instance. Replication is already in use in the environment and a dedicated SQL Server has been configured for distribution.

There’s still choices to make: how do you configure the subscriber? My recommendation is that each replication subscription be isolated in individual databases. Let’s take a look at why.

The all-in-one subscriber

In this model, we have one big database named ReportDB on the Warehouse01 subscriber server. This database holds reporting tables as well as tables from replication subscriptions.

Transactional Replication Architecture 1- Large Subscriber

 

SQL Server will let you do this, but it’s got some downsides:

1. You’ve got a single transaction log (and a single recovery model) for ReportDB. Not all of the data in ReportDB may be recoverable from other sources. If for any reason you need to be in the FULL recovery model for ReportDB, you’re in a sticky situation if you have to reinitialize replication and reload the subscriber tables: you’ve got to do it fully logged, and your transaction log backups will be bigger and slower.

2. It’s easy for DBAs and Developers to screw this up. You may want to drop all the replication subscriber tables at some point if you have to re-initialize replication. I would much rather isolate drop table commands to a database where everything in the database is replication related, and I don’t have to worry about accidentally dropping the wrong thing! Similarly, it’s much easier to set up security so that application accounts can only read subscriber tables (not write to them), when they’re in their own database.

3. You’ve got fewer options when it comes to reinitialization. The ability to initialize a replication subscriber from a restored backup is huge– running snapshots on your publishers makes your users sad.

Isolated subscribers

Here’s the alternate model, where each subscriber has its own database:

Transactional Replication Architecture 2 - Isolated Subscriber Tables

 

Note that the subscriber databases are named in a way that you can identify their role. (As noted by David in the comments, it’s much easier if you don’t re-use the name of a publishing database.)

This model has a lot of benefits:

  • More options to re-initialize
  • Supports different recovery models on replication subscriber databases and ReportDB (which can allow minimal logging during bulk inserts if you’re re-initializing that way)
  • Safer for administrators
  • Easier to set up security
  • Easier for new DBAs to and developers to understand (the architecture is much more transparent)

What If I’m Not Doing it This Way? Do I Have to Change All My Code?

Not necessarily. I’ve worked with this with two different models.

Synonyms: ReportDB could contain synonyms for all the replication subscriber tables. This makes the tables seem like they’re in ReportDB, while they’re actually in other databases.

Dynamically configured procedures. In one environment I worked in where replication was common, there was a configuration table where you configured the name of the replication subscriber databases needed by an application. When code was deployed, the installation process for stored procedures checked this configuration table and dynamically compiled the stored procedures referencing the name of the database in that environment. This sounds like more work, but it was very useful for the developers, who might need to deploy builds to instances configured differently than production.

Want To Learn More About Replication?

We’ve got you covered. Start with these articles:

 

 

 

New Online Instructor-Led Course: Database Maintenance Starter Kit

Preview-Database-Maintenance-Starter-Kit

BYO popcorn

We’re changing things up a little bit. Jes Schultz Borland and I are offering a brand new course that combines two things we love:

  • Video training where you can learn anytime
  • A live instructor-led discussion for a small class of students (just 30 people)

None of it requires you to travel. And it’s all at a price that your boss can’t resist.

Meet the SQL Server Database Maintenance Starter Kit – Class is in Session for 30 Students on December 29, 2014

You know you need good maintenance for your SQL Server, but you’re not sure exactly WHAT maintenance you should do. You’ve run our free sp_Blitz® procedure — but you’re not clear on what to prioritize out of all the rows you return. You’ve heard of database recovery models, but aren’t sure about the risks of changing it. A little voice in the back of your head asks if you’re running backups often enough, and the right kinds of backups.

This course is for you! For $149, you get 18 months access to 3 hours of recorded training and the chance to attend an additional 3 hour webinar of group discussion on database maintenance.

Our first class will have its 3 hour webinar on Monday, December 29, 2014, 9 am to noon Central. Only 30 students can enroll!

3 Hours of Recorded Video Training on Database Maintenance (18 months access)

Demos galore

Jes introduces you to her good friend, the SQL Server Agent

The day you purchase the course, you’ll be able to dive right into 3 hours of recorded video content. Jes and I will teach you:

  • The pros and cons of maintenance plans vs SQL Server Agent jobs
  • How to select the right recovery model
  • How to plan a backup strategy
  • How to check for corruption in your databases (and get notified if it happens)
  • The different types of index maintenance operations (and what can go wrong)
  • Why you need to be careful with index maintenance

3 Hour Instructor-Led Discussion Session (small groups: 30 students per session only)

When you enroll, you pick a date and time for a three hour online discussion session, led by Jes and myself. Each class will contain 30 students maximum — that means you get customized content and a chance to ask questions! Right now we’re only offering this on Monday, December 29, 2014, 9 am to noon Central. On that day you can join a session where you will:

  • Learn more about the “challenges” we give you as a downloadable PDF as part of the course
  • Take quizzes on course topics (and see how your answers do)
  • Ask questions and make comments to Jes and myself using the GoToWebinar chat feature

The Fine Print / FAQ / LOL / The Deets

  • The 3 hour discussion session will NOT be recorded — it’s all about showing up and participating.
  • We can’t guarantee that your firewall won’t block GoToWebinar, that your computer audio will work, or that someone won’t schedule a meeting with you during that time period — those things all depend on you.
  • The course is non-refundable. Read the terms of purchase here.
  • If you’d like to test your connection to GoToWebinar before buying, join one of our free Tuesday weekly events.
  • If the date and time of the session don’t work for you, that’s OK. We may schedule more of these in the future at different times of day. Feel free to leave a comment on this post letting us know what region you’re in, so we know where interest is!
  • If you just want 18 months access to the videos, you can still buy the course and just not attend the Instructor-Led discussion session. There’s no discount price for that. (It’s still a good deal! Did we mention it’s THREE hours of videos packed with demos?)

Read more about the class or buy it now.

How many CPUs is my parallel query using in SQL Server?

Parallelism can be confusing. A single query can have multiple operators that run at the same time. Each of these operators may decide to use multiple threads.

You set SQL Server’s “max degree of parallelism” to control the number of processors that can be used, but it’s not immediately obvious what this mean. Does this setting limit the total number of CPU cores the entire query statement can use? Or does it limit the total number of CPU cores that a single parallel operator within the query can use?

Good news: we can see how it works by running a simple test query and looking at some SQL Server DMVs.

My parallelism test setup

This test is run against a virtualized SQL Server with 4 virtual CPUs. SQL Server’s “max degree of parallelism” setting is at 2. My test instance is on SQL Server 2014.

My simple parallel query

All of my observations are taken while running the following test query against a restored copy of the StackOverflow database. I have ‘actual execution’ plans turned on so that when I query sys.dm_exec_query_profiles, I get some interesting details back.

SELECT p.Id, a.Id
FROM dbo.Posts as p
JOIN dbo.Posts as a on p.AcceptedAnswerId=a.Id;
GO

Here’s what the execution plan looks like:

parallel plan

The plan has seven operators with parallel indicators. Two of those operators are scanning nonclustered indexes on the Posts table.

The question we’re exploring here is whether the scans of those nonclustered indexes will use multiple threads that share the same two CPU cores, or whether they will each get two different CPU cores (and use all four).

Starting simple: tasks and workers

While my parallel ‘Posts’ query is executing on session 53, I can spy on it by querying SQL Server’s DMVs from another session:


select ost.session_id,
    ost.scheduler_id,
    w.worker_address,
    ost.task_state,
    wt.wait_type,
    wt.wait_duration_ms
from sys.dm_os_tasks ost
left join sys.dm_os_workers w on ost.worker_address=w.worker_address
left join sys.dm_os_waiting_tasks wt on w.task_address=wt.waiting_task_address
where ost.session_id=53
order by scheduler_id;

Here are a sample of the results:
schedulers-workers

The scheduler_id column is key. Each scheduler is mapped to one of my virtual CPU cores. My query is using 2 virtual CPU cores. At this moment I have two tasks on scheduler_id 0, and three tasks on scheduler_id 2.

But why leave it at that, when we can overcomplicate things? Let’s poke around a little more.

dm_exec_query_profiles and query plan nodes

There’s one more thing you need to know about our query plan. Each node in the plan has a number. The index scans are node 4 and node 6:
parallel plan -nodes
If I run my query with ‘actual execution plans’ enabled, I can spy on my query using the sys.dm_exec_query_profiles DMV like this:

select ost.session_id,
    ost.scheduler_id,
    w.worker_address,
    qp.node_id,
    qp.physical_operator_name,
    ost.task_state,
    wt.wait_type,
    wt.wait_duration_ms,
    qp.cpu_time_ms
from sys.dm_os_tasks ost
left join sys.dm_os_workers w on ost.worker_address=w.worker_address
left join sys.dm_os_waiting_tasks wt on w.task_address=wt.waiting_task_address
    and wt.session_id=ost.session_id
left join sys.dm_exec_query_profiles qp on w.task_address=qp.task_address
where ost.session_id=53
order by scheduler_id, worker_address, node_id;
cpus-threads-query-nodes

Click to nerd out on this in a larger view

Here’s a sample of the output:

I’ve only got two schedulers being used again – this time it happened to be scheduler_id 1 and scheduler_id 2. Looking at the node_id column, I can see that the index scan on query plan node 4 is using both scheduler_id 1 and scheduler_id 2: the very top line and the bottom line of the output show the current row_count for the runnable tasks. The scan on query plan node 6 isn’t really doing work right at the instance this snapshot was taken.

Recap: Maxdop limits the cpu count for the query

Even if your query has multiple parallel operators, the operators will share the CPUs assigned to the query, which you can limit by the ‘max degree of parallelism’ setting.

Credits: thanks to Paul White confirming that I had the basics of this concept right. If you liked this post, you’d love his great post, Parallelism Execution Plans Suck.

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.

SELECT COUNT(*)
FROM dbo.Posts
WHERE PostTypeId=2

I get a parallel plan with an estimated cost of 272.29. (I can tell it’s parallel because of the yellow circle with double arrows on three of the operators.)

execution plan parallel

I decide I’m going to test out ‘Cost Threshold for Parallelism’ and make this plan go serial. This is a server wide configuration, but I’m on my test instance, so no worries.

exec sp_configure 'cost threshold for parallelism', 275;
GO
RECONFIGURE
GO

I run my query again and look at my actual plan this time…

Actual plan- paralleism

Hey wait, that’s still parallel! It has the same estimated cost, and that cost is below where I set the cost threshold for parallelism. This seems broken.

At this point, I might get confused and think SQL Server was using a cached plan. But changing configuration options like cost threshold for parallelism will trigger recompilations– that shouldn’t be it.

What ‘Cost’ is SQL Server Using?

The secret is in the serial plan. I need to look at the estimated cost for this query — it’s the same as our original, but I’ve added a MAXDOP 1 hint to force a serial plan:

SELECT COUNT(*)
FROM dbo.Posts
WHERE PostTypeId=2 OPTION (MAXDOP 1)
GO

Estimated cost - serial plan

The estimated cost for the serial version of the plan is 287.963, which is over the threshold I set at 275! This is the cost that is being considered and which is above the bar I set for who gets to go parallel. I can prove it by raising my cost threshold to just above this level:

exec sp_configure 'cost threshold for parallelism', 288;
GO
RECONFIGURE
GO

And now when I run my query (with no maxdop hint to force it), I get a serial plan.

serial plan because of cost threshold

Cost Threshold Doesn’t Just Apply to the Plan You See

Behind the scenes, the optimizer is considering many possible plans. The Cost Threshold for Parallelism setting comes into play during that process and uses costs that you don’t see in the final plan.

This can be tricky to remember. Special thanks to Jeremiah, who’s explained this to me in the past (more than once!) This is covered in SQL Server Books Online, but oddly enough most of us don’t seem to find it there.

How to Configure Quorum in SQL Server (video)

“Quorum” is incredibly important to keep your SQL Server online when you use Windows Failover Clustering or AlwaysOn Availability Groups. Learn what quorum is, how to see the current quorum configuration, how to change it, and guidelines for how to configure quorum in three common real-world scenarios.

What Changed Our Career Trajectories?

Jeremiah-Caffeinates-TSQL

Jeremiah has coffee at the PASS Summit

We’re a group of specialists and teachers here at Brent Ozar Unlimited. But we didn’t start that way– we started out as engineers and developers.

So what was it that changed our career trajectories? For each of us, we had a lot of smaller factors along the way, but there have been one or two big game changers that have really made a difference.

Jeremiah: The PASS Summit

The first time I went to the PASS Summit, I attended at my own expense and used up half of my vacation time to attend. I’d been involved in the SQL Server community for a few short months, and I had been chatting via email with a goofy DBA named Brent Ozar.

Brent and I both attended pre-conference sessions, but we were in different ones. It happened that our speakers took breaks at same time, and I ended up running into Brent over on one of the breaks. We chatted and ended up eating lunch together.

I’m a shy person and a big conference can be daunting when you don’t know anybody, so I ended up tagging along with Brent throughout a lot of the conference. The upside of hanging out with Brent is that he’ll talk to anybody – speakers, Microsoft employees, or random attendees. The PASS Summit was my gateway into both the SQL Server community – I made a lot of friends that week – and into my current career.The connections I made at the PASS Summit that year planted the seeds for the rest of my career.

Kendra: Twitter

Someone helped me discover the typo and find it.

Kendra’s first tweet. #SQLHELP didn’t exist yet.

I never would have guessed that Twitter would change my life. And I’ve never been passionate or crazy about Twitter – I think it’s useful, but if I go a day or two without using it, I don’t mind.

But Twitter is the way that I started connecting to the larger SQL Server community. I started following people, then following their friends, and started tweeting about SQL Server. I asked and answered questions. Twitter gave me a sense of what other SQL Server developers and DBAs cared about, what problems they faced, and what they were interested in.

I’m a pretty naturally shy person, and I never knew what to talk to people about at conferences and events when I first met them. Twitter has helped me solve that problem: you can sense the mood of a conference while you’re there. You can tweet about it. You can talk about the tweets with people!

To get started with Twitter, check out our free eBook.

Jes: SQL Saturday

Jes at SQLSaturday Chicago

Jes at SQLSaturday Chicago

Specifically SQL Saturday Chicago 2010 – my first! I’d been attending my “local” user group for a few months, I’d been reading blogs, I was participating in forums, and I was even on Twitter before I went to this event. I knew I loved working with SQL Server, and I knew there were people that could help me when I had questions. But getting to attend an event – for free, nonetheless! – and getting to meet these people was amazing.

I remember attending a session by Brad McGeehee, who’d written “Brad’s Sure DBA Checklist”, which I kept in a binder at my desk. I remember getting to watch Chuck Heinzelman and Michael Steineke set up a cluster and test it. I even went to a session by one Brent Ozar about a script called sp_Blitz. At the end of the day, with a full brain, I went to the lobby and saw a group of the presenters sitting around chatting. I bravely approached and joined the circle, which included Brent and Jeremiah, and chatted with them. Had I not attended that event, had I not made the connections I did, I doubt I would be where I am in my career today!

Look for a SQL Saturday near you!

DOUG: SQL CRUISE

Dinner with Vikings

Doug as his true viking self

I’d recently presented at my first two SQL Saturdays when the opportunity came to go on SQL Cruise. A contest that for some cosmic reason I felt was begging me to enter — send in your story of victory with SQL Server and win a free spot on the cruise. I made a video about refactoring bad code with a magical hammer, and won the contest. But that was just the beginning.

During the cruise, I:

  • Had a dream about a murder mystery happening on the cruise, which led me to write and present my SQL Server Murder Mystery Hour session.
  • Got some phenomenal career advice from people like Brent, Tim Ford, and Buck Woody, that is still helping me to this day.
  • Became friends with the three people who ended up hiring me three years later for the best job I have ever had.

That’s my career adventure. What will yours be?

Brent: Meeting “Real” DBAs

In My Native Habitat

The DBAs told Brent this hat would make his code better.

I was a lead developer at a growing software business and I felt like I had no idea what was going on inside the database. Our company suddenly grew large enough to hire two “real” database people – Charaka and V. They were both incredibly friendly, fun, and helpful.

I was hooked. Who were these cool people? Where did they come from? How did they learn to do this stuff?

They both took time out of their busy jobs to answer whatever questions I had, and they never made me feel stupid. They loved sharing their knowledge. They weren’t paranoid about me taking their jobs or stealing their secrets – and looking back, they probably just wanted me to be a better developer.

They insisted that sure, I had what it took to be a database admin. I went for it, and it’s been a rocket ship ever since.

css.php