Watch Brent Tune Servers: Ignite 2015

At Microsoft Ignite 2015 in Chicago, I demonstrated how to tune a few SQL Server workloads with my favorite process:

Measure how fast the SQL Server is going
Check its bottlenecks
Apply some easy-to-tweak configuration settings and measure the difference

How to Get the Stack Overflow Demo Database

The nice folks at Stack Exchange make all of their data available online. Well, it’s technically your data since you’re the one answering questions. Well, technically it’s someone else’s, because you’re one of those lazy people who never answers questions. You only log in to downvote my answers. But I digress.

The thing is, it’s available in XML because people use all kinds of databases. (Stack uses Microsoft SQL Server, but they cleanse the data and export it to a more universal format.) For you to get a SQL Server database copy, you have to:

Download the XML data dump via BitTorrent
Extract it (it’s around 60GB as of this writing)
Use the Stack Overflow Data Dump Importer (SODDI) to bring it into SQL Server

I documented the full Stack Overflow data dump import process here.

How to Follow Along with My Demos

Here’s the full demo script:

/*
Watch Brent Tune Servers

Brent Ozar, Brent Ozar Unlimited
v1.0 - May 2015

(C) 2015, Brent Ozar Unlimited.
See http://BrentOzar.com/go/eula for the End User Licensing Agreement.

*** OUR DEMO ENVIRONMENT ***
Isn't AdventureWorks boring? Let's work on something a little more exciting.
Stack Exchange, the makers of StackOverflow.com and DBA.StackExchange.com,
make all of their databases available with a Creative Commons license.

There's a little bit of work required - you download the Stack Exchange XML
data dump via BitTorrent, and then use an import tool to bring it into SQL
Server. It's easier than it sounds. Here's how: 

http://BrentOzar.com/go/querystack

The end result is a ~60GB StackOverflow database, which is a lot of fun to
play around with.
*/


/*
Setting Up the Server - Optional
Sometimes I purposely cripple my virtual machines to make them slow. Not today.
For today's session I don't want to fake bad hardware, so I'll make sure my
SQL Server instance has some breathing room with parallelism and memory. You
can learn the logic behind these settings here: http://BrentOzar.com/go/setup

EXEC sys.sp_configure N'show advanced options', N'1'
GO
RECONFIGURE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'0'
GO
EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
GO
EXEC sys.sp_configure N'max server memory (MB)', N'16000'
GO
RECONFIGURE
GO
ALTER DATABASE [StackOverflow] SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE [StackOverflow] SET DELAYED_DURABILITY = DISABLED WITH NO_WAIT
GO
ALTER DATABASE [StackOverflowReports] SET READ_COMMITTED_SNAPSHOT OFF WITH NO_WAIT
GO
USE StackOverflowReports;
GO
DROP INDEX [BGO_NonClusteredColumnStoreIndex] ON [dbo].[rptUsersTags]
GO
USE StackOverflow;
GO
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
GO
*/



/*
Let's start with something simple: a single query.
Let's pretend our whole server exists just to run this one query over and over.
*/
SELECT SUM(Reputation) AS TotalReputation
FROM dbo.Users u
WHERE u.LastAccessDate &gt; CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) as DATE) 






/*
It runs pretty quickly - only about a second on my laptop VM. But what happens
if we run a load test?

In order to vaguely simulate a production load, I'm using Adam Machanic's neat
free utility SQLQueryStress: http://www.datamanipulation.net/sqlquerystress/
You can run a query thousands of times from dozens (or more) connections at the
same time. Don't install this on the SQL Server itself - install it on your
desktop or laptop, and generate the load there. You can learn more about how to
use it here: http://BrentOzar.com/go/stresstest

I'm going to run this query on 10 threads, 10,000 times, and see what happens.
*/









/*
You'll notice that CPU goes up to 100%, and it doesn't seem like we're able
to run that many of these queries at once.

How fast is our SQL Server going?

When we think about a car, and we want to know how a car is performing, we
look at the dashboard. The speedometer tells us how fast the car is going in
miles (or kilometers) per hour.

In SQL Server, our speedometer is Batch Requests per Second.

You can get this via a Perfmon counter, but since I need to measure it all the
time when I'm working, I wrote a quick stored procedure to be my SQL Server's
dashboard:
*/



sp_BlitzFirst;




/*
Alright, now we've got a baseline. What can we try to make it faster?

Well, in today's session, we're not tuning the query. We're only going to make
changes behind the scenes to influence what SQL Server does with queries. But
just for laughs, let's look at the actual execution plan for this:
*/
SELECT SUM(Reputation) AS TotalReputation
FROM dbo.Users u
WHERE u.LastAccessDate &gt; CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) as DATE);





/*
If you hover your mouse over the clustered index scan, SQL Server expected
millions of rows to come back - but none actually did. This isn't a statistics
problem, it's not rocket surgery - it's a bug in the SQL Server engine. They're
rare, but they do happen.

For years, Microsoft has been steadily improving the engine and the ways it
builds execution plans, but with each new version of the SQL Server engine,
they would rather give you PREDICTABLE execution plans than DIFFERENT ones. To
get the newest improvements in execution plans with SQL Server 2012, you have
to turn on a trace flag.

Let's do it.
*/

DBCC TRACEON(4199, -1);
DBCC FREEPROCCACHE;
GO



/* Then turn off actual execution plans and see what's happening: */
sp_BlitzFirst;












/*
THAT'S AWESOME! Let's use trace flag 4199 everywhere, right?

Easy tiger - two things to know.

First, this is a contrived workload. I'm load testing just one query, and it's
a query that I know is affected by the improvements in trace flag 4199. Your
real life workload may not have any queries affected by trace flag 4199. I wish
I had an easy way for you to tell if your queries would instantly benefit from
4199, but you'll have to roll up your sleeves and do some testing.

Second, trace flags are less-tested branches of the SQL Server code. When
you roll with a trace flag, you can get surprise gotchas like this bug:

FIX: "Cannot bulk load" error when you run a query that contains INSERT 
statements if trace flags 4199 and 610 are enabled on a server that is running
SQL Server 2014:
https://support2.microsoft.com/kb/3055799/en-us?sd=rss&amp;spid=17645

So this isn't one of those things you can just do live in production and hope
for the best. Let's go ahead and turn it back off for now.
*/




DBCC TRACEOFF(4199, -1);
DBCC FREEPROCCACHE;
GO











/*
And this is where SQL Server 2014 starts to change things up a little. Each
database has a setting called Compatibility Level. In the past, this has
controlled the way T-SQL gets interpreted, but that's it - it didn't magically
turn on advanced features.

Starting with SQL Server 2014, compatibility level has new functionality: when
you set it to SQL Server 2014 (120), it includes the cool improvements that
used to require trace flag 4199. Let's check it out:
*/

ALTER DATABASE [StackOverflow] SET COMPATIBILITY_LEVEL = 120
GO

/* And prove that we don't have any trace flags up our sleeves: */
DBCC TRACESTATUS;





/* And let's rerun our load test: */
sp_BlitzFirst;


/* 
You can look at the execution plan, too, and see that it's doing the same index
seek that we got with trace flag 4199:
*/
SELECT SUM(Reputation) AS TotalReputation
FROM dbo.Users u
WHERE u.LastAccessDate &gt; CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) as DATE);
GO







/*
Here's where these switches make sense:

On SQL Server 2012? Trace flag 4199 might get you better query plans in some
circumstances, but don't go enabling it by default. You have to be able to take
your time getting to know the workload, and ideally, testing it in development.
If you're dealing with a vendor app, they may not support trace flags, though.
You can learn more about commonly applied trace flags and updates in this KB:

https://support2.microsoft.com/kb/2964518/



But on SQL Server 2014? Party time!

When you first migrate, stay with compat level 110 (2012) or lower. You want to
let people get comfortable with the new hardware and any other changes you made
during the migration.

A couple weeks later, in a maintenance window, switch the database compat level
to 120 (2014), and monitor performance. It's possible that you could have
regressions where some queries perform much worse - in that case, flip it back.
But otherwise, you just got something for nothing.

*/












/*
Next, let's look at a reporting workload. I've got a StackOverflowReports
database with a wide, denormalized reporting table that probably looks like one
of your data warehouse fact tables:
*/
USE StackOverflowReports;
GO
SELECT [UserId]
      ,[Tag]
      ,[Age]
      ,[CreationDate]
      ,[DisplayName]
      ,[DownVotes]
      ,[EmailHash]
      ,[LastAccessDate]
      ,[Location]
      ,[Reputation]
      ,[UpVotes]
      ,[Views]
      ,[WebsiteUrl]
      ,[AnswersTY]
      ,[AnswersTYavgScore]
      ,[AnswersLY]
      ,[AnswersLYavgScore]
      ,[AnswersTotal]
      ,[AnswersTotalavgScore]
      ,[CommentsTY]
      ,[CommentsTYavgScore]
      ,[CommentsLY]
      ,[CommentsLYavgScore]
      ,[CommentsTotal]
      ,[CommentsTotalavgScore]
      ,[QuestionsTY]
      ,[QuestionsTYavgScore]
      ,[QuestionsLY]
      ,[QuestionsLYavgScore]
      ,[QuestionsTotal]
      ,[QuestionsTotalavgScore]
      ,[AboutMe]
      ,[AccountId]
      ,[BadgesTY]
      ,[BadgesLY]
      ,[BadgesTotal]
  FROM [dbo].[rptUsersTags]
  WHERE UserId = 26837
GO


/*
That's a lot of columns. How am I supposed to index that when I can never
predict how my end users are going to query it? For example, check out these
analytical queries:
*/



/* Which popular tag's users are the oldest? */
SELECT Tag, AVG(Age) AS AvgAge, COUNT(1) AS Users
FROM dbo.rptUsersTags
GROUP BY Tag
HAVING COUNT(1) &gt; 1000
ORDER BY AVG(Age) DESC;


/* Which tag's users have the lowest reputation, for users who were active this year? */
SELECT TOP 50 Tag, AVG(Reputation) AS AvgReputation, COUNT(1) AS Users, SUM(Reputation) AS TotalReputation
FROM dbo.rptUsersTags
WHERE QuestionsTY &gt; 10 OR AnswersTY &gt; 10 OR CommentsTY &gt; 10
GROUP BY Tag
HAVING COUNT(1) &gt; 1000
ORDER BY AVG(Reputation)


/* Which users have asked questions on at least 100 tags this year? */
SELECT UserId, DisplayName, Reputation, COUNT(DISTINCT Tag) AS Tags, 'http://stackoverflow.com/users/' + CAST(UserId AS VARCHAR(20)) AS Url
FROM dbo.rptUsersTags
WHERE QuestionsTY &gt; 0
GROUP BY UserId, DisplayName, Location, Reputation
HAVING COUNT(DISTINCT Tag) &gt;= 100
ORDER BY COUNT(DISTINCT Tag) DESC






/*
The queries are different.
The groupings are different.
The order-bys are different.

How on earth are we supposed to deal with that? This is a typical problem for
data warehouses where we can't predict what users will query for, but we have
to make the system go fast anyway.

If we think about our speedometer, we're not even going to hit 1 batch request
per second! While one of those queries runs - any one of them - check your
speedometer in another window:
*/

sp_BlitzFirst;


/*
Our wait stat is PAGEIOLATCH.

SQL Server stores data on disk in 8KB pages. Think of them like pages in a
phone book - you know, the white book your grandfather kept above the fridge
so he could find the nearest pizza place.

PAGEIOLATCH means SQL Server is doing IO to read PAGEs from disk. (There are
other kinds of waits that look similar, like PAGELATCH or LATCH, but that's
just Microsoft's way of making sure smart people like you have job security.)

In order to fix PAGEIOLATCH, I have a few options:

* Query less data
* Get more memory
* Make storage go faster

SQL Server 2014 introduced a new way to cache data: Buffer Pool Extensions.
This lets you set up space on a local SSD to cache data that SQL Server would
have ordinarily ejected from memory.

It's perfect when your commonly-queried tables are larger than memory. Today,
I'm querying a 30GB table, but I've only got ~6GB of RAM on this VM. So let's
turn on Buffer Pool Extensions to cache more data:
*/

ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON
    (FILENAME = 'B:\BufferPoolExtensions\KidPool.BPE', SIZE = 40 GB);
GO




/* And then let's try one of our queries again: */
SELECT Tag, AVG(Age) AS AvgAge, COUNT(1) AS Users
FROM dbo.rptUsersTags
GROUP BY Tag
HAVING COUNT(1) &gt; 1000
ORDER BY AVG(Age) DESC;









/* 
It's actually slower, not faster. Why is that?

Well, the first time you run queries after enabling BPE, the files have to be
created and populated, so some folks might guess we're warming up the pool,
populating data into Buffer Pool Extensions that it'll read later. And that's
true, but it's not the reason the query is slower. This query will be slower
no matter how many times you run it, even if the data is already in our SSD's
Buffer Pool Extensions.

Run the query while looking at this Perfmon counters for the drives where your
data files live, AND where your Buffer Pool Extensions live:

* Physical Disk: Avg. Disk Bytes/Read
*/




SELECT Tag, AVG(Age) AS AvgAge, COUNT(1) AS Users
FROM dbo.rptUsersTags
GROUP BY Tag
HAVING COUNT(1) &gt; 1000
ORDER BY AVG(Age) DESC;




/*
You'll see:

* Data file drive - 256KB reads (typically)
* Buffer Pool Extension drive - 8KB reads

SQL Server only reads one page at a time per BPE read, whereas with Developer
Edition and Enterprise Edition, SQL Server will ask for much more data each
time it talks to your drives.

Because of this design, Buffer Pool Extensions only make sense when:

* You have a transactional app (OLTP)
* That does small, one-record lookups
* From a table that is too big to fit in RAM
* And is und

That doesn't mean BPE is bad - it's just like any other SQL Server tuning tool.
If you want to make queries faster without changing the application, you can.

You can do it, and SQL Server can help.

But you have a lot of tools in the toolbox, and you have to know when to reach
for each of them. That isn't the right tool here, so we'll turn it back off:
*/
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
GO



/* 
And restart our SQL Server. 


We're going to need to find a different tool - and good news, MSSQL has one.

Sometimes we try doing all kinds of different indexes to just guess what users
will query. It's a guessing game.

SQL Server 2012 introduced an interesting pain relief option: non-clustered
columnstore indexes. In a nutshell, every column is its own index.

Let's build a columnstore index on each of our reporting tables using the GUI,
and check the resulting scripts. While it runs, we'll talk more:
*/


CREATE NONCLUSTERED COLUMNSTORE INDEX [BGO_NonClusteredColumnStoreIndex] ON [dbo].[rptUsersTags]
(
  [UserId],
  [Tag],
  [Age],
  [CreationDate],
  [DisplayName],
  [DownVotes],
  [EmailHash],
  [LastAccessDate],
  [Location],
  [Reputation],
  [UpVotes],
  [Views],
  [WebsiteUrl],
  [AccountId],
  [BadgesTY],
  [BadgesLY],
  [BadgesTotal],
  [QuestionsTY],
  [QuestionsTYavgScore],
  [QuestionsLY],
  [QuestionsLYavgScore],
  [QuestionsTotal],
  [QuestionsTotalavgScore],
  [AnswersTY],
  [AnswersTYavgScore],
  [AnswersLY],
  [AnswersLYavgScore],
  [AnswersTotal],
  [AnswersTotalavgScore],
  [CommentsTY],
  [CommentsTYavgScore],
  [CommentsLY],
  [CommentsLYavgScore],
  [CommentsTotal],
  [CommentsTotalavgScore]
)WITH (DROP_EXISTING = OFF)

GO





/* 
It takes a few minutes because it's basically storing our data in a totally
different way. 

It's great for:
* Very wide tables
* Where report users don't query all of the columns
* And they sort different columns all the time
* And we need grouping aggregations
* Read-only tables, loaded overnight in batches

In summary - perfect for data warehouse fact tables like sales history.

In SQL Server 2012, when you added a nonclustered columnstore index, it makes
the table read-only. You can't do any inserts, updates, or deletes. This means
it's really only good for data warehouses that are loaded nightly, and then
queried all day long. When you want to load more data, you need to drop the
nonclustered columnstore index, load your data, then build the index again.

Judging by how long it's taking us to build this index on a ~30GB table, you
can probably guess why this isn't for everybody.

In SQL Server 2014, though, you can actually build a CLUSTERED columnstore
index, and it's updatable! SQL Server goes through some tricks behind the
scenes to keep it up to date for you automatically. Sadly, you couldn't add
conventional nonclustered index to a table with a clustered columnstore index,
and nonclustered columnstore indexes are still read-only.

Here's where columnar indexes make sense:
* Your chief wait type is PAGEIOLATCH (reading data pages from disk)
* Your tables are very wide (lots of columns)
* Your report users don't query all of the columns
* And they sort different columns all the time
* And we need grouping aggregations
* And we only load data in batches, like overnight
* For SQL 2012, where you can afford to rebuild this index nightly

To learn more about columnstore indexes, check out Niko Neugebauer's site:
http://www.nikoport.com/columnstore/



After the build finishes, retry our queries again: 
*/

/* Which popular tag's users are the oldest? */
SELECT Tag, AVG(Age) AS AvgAge, COUNT(1) AS Users
FROM dbo.rptUsersTags
GROUP BY Tag
HAVING COUNT(1) &gt; 1000
ORDER BY AVG(Age) DESC;


/* Which tag's users have the lowest reputation, for users who were active this year? */
SELECT TOP 50 Tag, AVG(Reputation) AS AvgReputation, COUNT(1) AS Users, SUM(Reputation) AS TotalReputation
FROM dbo.rptUsersTags
WHERE QuestionsTY &gt; 10 OR AnswersTY &gt; 10 OR CommentsTY &gt; 10
GROUP BY Tag
HAVING COUNT(1) &gt; 1000
ORDER BY AVG(Reputation)


/* Which users have asked questions on at least 100 tags this year? */
SELECT UserId, DisplayName, Reputation, COUNT(DISTINCT Tag) AS Tags, 'http://stackoverflow.com/users/' + CAST(UserId AS VARCHAR(20)) AS Url
FROM dbo.rptUsersTags
WHERE QuestionsTY &gt; 0
GROUP BY UserId, DisplayName, Location, Reputation
HAVING COUNT(DISTINCT Tag) &gt;= 100
ORDER BY COUNT(DISTINCT Tag) DESC





/*
Magical! Revolutionary! 

How much would you pay for an index that fast? 10GB of drive space? 20GB? 30GB?
EVEN MORE? Let's go find out how much space it takes - right-click on the table
in Object Explorer, and click Properties, Storage. Compare the data size to the
index size.

This index is less than 1GB in size for a 30GB table, even though it's storing
almost all of the columns.

How? The magic of compression. See, in a reporting data warehouse type system,
where we're dealing with denormalized data, it often looks the same:
*/
SELECT [UserId]
      ,[Tag]
      ,[Age]
      ,[CreationDate]
      ,[DisplayName]
      ,[DownVotes]
      ,[EmailHash]
      ,[LastAccessDate]
      ,[Location]
      ,[Reputation]
      ,[UpVotes]
      ,[Views]
      ,[WebsiteUrl]
      ,[AnswersTY]
      ,[AnswersTYavgScore]
      ,[AnswersLY]
      ,[AnswersLYavgScore]
      ,[AnswersTotal]
      ,[AnswersTotalavgScore]
      ,[CommentsTY]
      ,[CommentsTYavgScore]
      ,[CommentsLY]
      ,[CommentsLYavgScore]
      ,[CommentsTotal]
      ,[CommentsTotalavgScore]
      ,[QuestionsTY]
      ,[QuestionsTYavgScore]
      ,[QuestionsLY]
      ,[QuestionsLYavgScore]
      ,[QuestionsTotal]
      ,[QuestionsTotalavgScore]
      ,[AboutMe]
      ,[AccountId]
      ,[BadgesTY]
      ,[BadgesLY]
      ,[BadgesTotal]
  FROM [dbo].[rptUsersTags]
  WHERE UserId = 26837
GO



/*
If we're storing every column in its own index, look at how many duplicates we
have. Columnar indexes store this data much more efficiently, and it's not
unusual to see a 90% (or higher) compression rate on warehouse-type data.

SQL Server 2016 gets even better:
* Nonclustered columnstore indexes are updateable
* Nonclustered columnstore indexes can be filtered
* Clustered columnstore indexes can have regular b-tree indexes too
* Both get better performance for aggregates (MIN, MAX, SUM, COUNT, AVG)
  and for string predicates (myfieldname LIKE '%foo%')
* Better concurrency (support of snapshot isolation and RCSI, row-level locking
  if you have nonclustered b-tree indexes)
* Better index reorganize results (removes deleted rows, less memory pressure)
* Readable on AlwaysOn Availability Group secondaries

To learn more about SQL Server 2016's improvements, check out Ignite session
BRK4556 - Ignite attendees can watch the video and download the slides from
http://ignite.microsoft.com.

*/





/*
Next, let's talk about a different kind of workload.

We've been looking at workloads that do a lot of reads - what about writes?
Let's say we have a PostViews table that tracks the views for every post at
Stack Overflow. It's not as crazy as it sounds - this data is actually
persisted in the database:
*/
USE StackOverflow;

SELECT TOP 100 *
FROM dbo.Posts;
GO



/*
Keeping that up to date is pretty hard work. You can see the latest stats here:

http://stackexchange.com/performance

Stack gets thousands of hits per second on their web servers. So let's think
about a SQL Server that would handle JUST tracking view count, nothing else.

We would run this one query thousands of times per second:
*/

EXEC dbo.usp_UpdatePostViews @PostId = 25;





/* First, make sure the entire PostViews table is cached in RAM: */
SELECT COUNT(*) FROM dbo.PostViews;


/* Let's run a lot of those with SQLQueryStress and check our bottleneck: */
sp_BlitzFirst @ExpertMode = 1;




/*
Our bottleneck is WRITELOG - writing to the transaction log. SQL Server has to
log everything we do because if we lose power or Godzilla attacks the data
center in the middle of a transaction, SQL Server has to be able to roll back.

Some folks might say, "Put the database in SIMPLE recovery mode." Bad news:
we're already there. SIMPLE recovery doesn't mean we log less data - it just
means the transaction log is available for reuse as soon as our transaction
finishes.

So how do we fix WRITELOG? Before SQL 2014, the answer was, "Write less data."

Meet our new friend: 
*/

ALTER DATABASE [StackOverflow] SET DELAYED_DURABILITY = FORCED WITH NO_WAIT
GO



/*
Delayed durability is a new option that lets SQL Server say a transaction is
complete even before the transaction log has been hardened (meaning, before the
log file has been written to disk.)

Rerun our load test with SQLQueryStress, and check our speedometer:
*/

sp_BlitzFirst @ExpertMode = 1;




/*
The speedometer doesn't always change much - this query's speed fluctuates a
lot - but check out the wait times.

Our WRITELOG waits simply disappear. 

Awesome, right? Turn on delayed durability everywhere, right? Wrong. Just like
the other tuning tools we talked about, there are big drawbacks to this tool,
and you need to know where it's appropriate.

Delayed durability is great when:
* WRITELOG is the biggest bottleneck holding you back
* You can't make your transaction log storage go any faster
* You're okay losing transactions in the event of a power failure, server crash
* You're even okay losing transactions during a graceful shutdown

That's right: read that last bullet carefully. Here's the Books Online page:

https://msdn.microsoft.com/en-us/library/dn449490.aspx

"SQL Server shutdown and restart:
For delayed durability, there is no difference between an unexpected shutdown 
and an expected shutdown/restart of SQL Server. Like catastrophic events, you 
should plan for data loss. In a planned shutdown/restart some transactions that 
have not been written to disk may first be saved to disk, but you should not 
plan on it. Plan as though a shutdown/restart, whether planned or unplanned, 
loses the data the same as a catastrophic event."

Everybody got that? Choose carefully.

There are some really cool options with delayed durability - you can force it
on every transaction, or you can just ALLOW it, and let each transaction choose
whether it wants delayed durability or not, like this:
*/


ALTER DATABASE [StackOverflow] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
GO


/*
Now transactions can have it if they want - and I do, so let's add it to our
stored procedure:
*/
ALTER PROCEDURE [dbo].[usp_UpdatePostViews] @PostId INT = NULL AS

BEGIN TRAN
  SET @PostId = FLOOR(RAND()*25829713);
  UPDATE dbo.PostViews
    SET ViewCount = ViewCount + 1
    WHERE PostId = @PostId

COMMIT TRAN WITH (DELAYED_DURABILITY = ON);
GO


/*
This way, only my PostViews stored procedure gets delayed durability.

For today, though, my data is kind of important to me, so I'm going to turn
that back off.
*/



ALTER DATABASE [StackOverflow] SET DELAYED_DURABILITY = DISABLED WITH NO_WAIT
GO








/*
Let's check out another kind of workload - a noisy, messy one.

To do this, I'm going to need to call a lot of different queries at once. I'm
using a technique I blogged about here:

http://BrentOzar.com/go/stresstest

I'm going to be calling usp_NoisyWorkload from a bunch of threads. Fire up
SQLQueryStress and run StackOverflowReports.dbo.usp_NoisyWorkload from dozens
of sessions, thousands of times. While it's running, let's look at the running
queries with Adam Machanic's amazing tool, sp_WhoIsActive:
*/



sp_WhoIsActive;





/*
We've been talking a lot about wait types at the server level. sp_WhoIsActive
shows you wait types at the QUERY level when you need to do detailed tuning. In
this case, we have queries that are piled up waiting on LCK* - locks.

Many queries are trying to read and write to the same table at the same time.

There's a lot of ways to fix this - index tuning is my favorite, followed by
query tuning - but sometimes you don't have control over that. Sometimes you
aren't allowed to change the queries or the indexes.





Not allowed to change queries or indexes? Check out optimistic locking, also
known as Read Committed Snapshot Isolation, aka RCSI, aka The Artist Formerly
Known As The Turbo Button.

With RCSI:

* Readers don't block writers
* Writers don't block readers
* (But writers can still block each other)

This is different than SQL Server's default isolation level, known as Read
Committed, also known as Careful Mode. Careful is awesome, but optimistic
locking is just...more awesome.

To turn it on, we have to get everybody out of the database, then run:
*/


ALTER DATABASE [StackOverflowReports] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO




/*
Then, even without changing our code, run our load test again, and look at the
difference in wait types:
*/


sp_WhoIsActive;





/*
The locking is dramatically reduced! Now, we've still got simultaneous writers
blocking each other, but now the readers can get their job done without being
blocked.

RCSI is great when:

* Your biggest wait is LCK*
* You can't fix the indexes or the queries
* You're okay with the risks: BrentOzar.com/go/rcsi
* Your tempdb can handle additional workloads

It's especially great with SQL Server 2012 and 2014, which allow us to use
local solid state drives for tempdb in a cluster.
*/



/*
WHEW! We've covered a lot of tuning options:

* Trace flag 4199
* SQL 2014 Compatibility Level
* Columnstore indexes
* Buffer pool extensions
* Delayed durability
* Read committed snapshot isolation

To get this script and the Stack Overflow databases, visit:

http://BrentOzar.com/go/faster
*/

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

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

937

938

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

954

955

956

957

958

959

960

961

962

963

964

965

966

967

968

969

970

971

972

Watch Brent Tune Servers

Brent Ozar, Brent Ozar Unlimited

v1.0 - May 2015

See http://BrentOzar.com/go/eula for the End User Licensing Agreement.

*** OUR DEMO ENVIRONMENT ***

Isn't AdventureWorks boring? Let's work on something a little more exciting.

Stack Exchange, the makers of StackOverflow.com and DBA.StackExchange.com,

make all of their databases available with a Creative Commons license.

There's a little bit of work required - you download the Stack Exchange XML

data dump via BitTorrent, and then use an import tool to bring it into SQL

Server. It's easier than it sounds. Here's how:

http://BrentOzar.com/go/querystack

The end result is a ~60GB StackOverflow database, which is a lot of fun to

play around with.

Setting Up the Server - Optional

Sometimes I purposely cripple my virtual machines to make them slow. Not today.

For today's session I don't want to fake bad hardware, so I'll make sure my

SQL Server instance has some breathing room with parallelism and memory. You

can learn the logic behind these settings here: http://BrentOzar.com/go/setup

EXEC sys.sp_configure N'show advanced options', N'1'

RECONFIGURE

EXEC sys.sp_configure N'max degree of parallelism', N'0'

EXEC sys.sp_configure N'cost threshold for parallelism', N'50'

EXEC sys.sp_configure N'max server memory (MB)', N'16000'

RECONFIGURE

ALTER DATABASE [StackOverflow] SET COMPATIBILITY_LEVEL = 110

ALTER DATABASE [StackOverflow] SET DELAYED_DURABILITY = DISABLED WITH NO_WAIT

ALTER DATABASE [StackOverflowReports] SET READ_COMMITTED_SNAPSHOT OFF WITH NO_WAIT

USE StackOverflowReports;

DROP INDEX [BGO_NonClusteredColumnStoreIndex] ON [dbo].[rptUsersTags]

USE StackOverflow;

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;

Let's start with something simple: a single query.

Let's pretend our whole server exists just to run this one query over and over.

SELECT SUM(Reputation) AS TotalReputation

FROM dbo.Users u

WHERE u.LastAccessDate > CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) as DATE)

It runs pretty quickly - only about a second on my laptop VM. But what happens

if we run a load test?

In order to vaguely simulate a production load, I'm using Adam Machanic's neat

free utility SQLQueryStress: http://www.datamanipulation.net/sqlquerystress/

You can run a query thousands of times from dozens (or more) connections at the

same time. Don't install this on the SQL Server itself - install it on your

desktop or laptop, and generate the load there. You can learn more about how to

use it here: http://BrentOzar.com/go/stresstest

I'm going to run this query on 10 threads, 10,000 times, and see what happens.

You'll notice that CPU goes up to 100%, and it doesn't seem like we're able

to run that many of these queries at once.

How fast is our SQL Server going?

When we think about a car, and we want to know how a car is performing, we

look at the dashboard. The speedometer tells us how fast the car is going in

miles (or kilometers) per hour.

In SQL Server, our speedometer is Batch Requests per Second.

You can get this via a Perfmon counter, but since I need to measure it all the

time when I'm working, I wrote a quick stored procedure to be my SQL Server's

dashboard:

sp_BlitzFirst;

Alright, now we've got a baseline. What can we try to make it faster?

Well, in today's session, we're not tuning the query. We're only going to make

changes behind the scenes to influence what SQL Server does with queries. But

just for laughs, let's look at the actual execution plan for this:

SELECT SUM(Reputation) AS TotalReputation

FROM dbo.Users u

WHERE u.LastAccessDate > CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) as DATE);

If you hover your mouse over the clustered index scan, SQL Server expected

millions of rows to come back - but none actually did. This isn't a statistics

problem, it's not rocket surgery - it's a bug in the SQL Server engine. They're

rare, but they do happen.

For years, Microsoft has been steadily improving the engine and the ways it

builds execution plans, but with each new version of the SQL Server engine,

they would rather give you PREDICTABLE execution plans than DIFFERENT ones. To

get the newest improvements in execution plans with SQL Server 2012, you have

to turn on a trace flag.

Let's do it.

DBCC TRACEON(4199, -1);

DBCC FREEPROCCACHE;

/* Then turn off actual execution plans and see what's happening: */

sp_BlitzFirst;

THAT'S AWESOME! Let's use trace flag 4199 everywhere, right?

Easy tiger - two things to know.

First, this is a contrived workload. I'm load testing just one query, and it's

a query that I know is affected by the improvements in trace flag 4199. Your

real life workload may not have any queries affected by trace flag 4199. I wish

I had an easy way for you to tell if your queries would instantly benefit from

4199, but you'll have to roll up your sleeves and do some testing.

Second, trace flags are less-tested branches of the SQL Server code. When

you roll with a trace flag, you can get surprise gotchas like this bug:

FIX: "Cannot bulk load" error when you run a query that contains INSERT

statements if trace flags 4199 and 610 are enabled on a server that is running

SQL Server 2014:

https://support2.microsoft.com/kb/3055799/en-us?sd=rss&spid=17645

So this isn't one of those things you can just do live in production and hope

for the best. Let's go ahead and turn it back off for now.

DBCC TRACEOFF(4199, -1);

DBCC FREEPROCCACHE;

And this is where SQL Server 2014 starts to change things up a little. Each

database has a setting called Compatibility Level. In the past, this has

controlled the way T-SQL gets interpreted, but that's it - it didn't magically

turn on advanced features.

Starting with SQL Server 2014, compatibility level has new functionality: when

you set it to SQL Server 2014 (120), it includes the cool improvements that

used to require trace flag 4199. Let's check it out:

ALTER DATABASE [StackOverflow] SET COMPATIBILITY_LEVEL = 120

/* And prove that we don't have any trace flags up our sleeves: */

DBCC TRACESTATUS;

/* And let's rerun our load test: */

sp_BlitzFirst;

You can look at the execution plan, too, and see that it's doing the same index

seek that we got with trace flag 4199:

SELECT SUM(Reputation) AS TotalReputation

FROM dbo.Users u

WHERE u.LastAccessDate > CAST(DATEADD(m, DATEDIFF(m, 0, getdate()), 0) as DATE);

Here's where these switches make sense:

On SQL Server 2012? Trace flag 4199 might get you better query plans in some

circumstances, but don't go enabling it by default. You have to be able to take

your time getting to know the workload, and ideally, testing it in development.

If you're dealing with a vendor app, they may not support trace flags, though.

You can learn more about commonly applied trace flags and updates in this KB:

https://support2.microsoft.com/kb/2964518/

But on SQL Server 2014? Party time!

When you first migrate, stay with compat level 110 (2012) or lower. You want to

let people get comfortable with the new hardware and any other changes you made

during the migration.

A couple weeks later, in a maintenance window, switch the database compat level

to 120 (2014), and monitor performance. It's possible that you could have

regressions where some queries perform much worse - in that case, flip it back.

But otherwise, you just got something for nothing.

Next, let's look at a reporting workload. I've got a StackOverflowReports

database with a wide, denormalized reporting table that probably looks like one

of your data warehouse fact tables:

USE StackOverflowReports;

SELECT [UserId]

,[Tag]

,[Age]

,[CreationDate]

,[DisplayName]

,[DownVotes]

,[EmailHash]

,[LastAccessDate]

,[Location]

,[Reputation]

,[UpVotes]

,[Views]

,[WebsiteUrl]

,[AnswersTY]

,[AnswersTYavgScore]

,[AnswersLY]

,[AnswersLYavgScore]

,[AnswersTotal]

,[AnswersTotalavgScore]

,[CommentsTY]

,[CommentsTYavgScore]

,[CommentsLY]

,[CommentsLYavgScore]

,[CommentsTotal]

,[CommentsTotalavgScore]

,[QuestionsTY]

,[QuestionsTYavgScore]

,[QuestionsLY]

,[QuestionsLYavgScore]

,[QuestionsTotal]

,[QuestionsTotalavgScore]

,[AboutMe]

,[AccountId]

,[BadgesTY]

,[BadgesLY]

,[BadgesTotal]

FROM [dbo].[rptUsersTags]

WHERE UserId = 26837

That's a lot of columns. How am I supposed to index that when I can never

predict how my end users are going to query it? For example, check out these

analytical queries:

/* Which popular tag's users are the oldest? */

SELECT Tag, AVG(Age) AS AvgAge, COUNT(1) AS Users

FROM dbo.rptUsersTags

GROUP BY Tag

HAVING COUNT(1) > 1000

ORDER BY AVG(Age) DESC;

/* Which tag's users have the lowest reputation, for users who were active this year? */

SELECT TOP 50 Tag, AVG(Reputation) AS AvgReputation, COUNT(1) AS Users, SUM(Reputation) AS TotalReputation

FROM dbo.rptUsersTags

WHERE QuestionsTY > 10 OR AnswersTY > 10 OR CommentsTY > 10

GROUP BY Tag

HAVING COUNT(1) > 1000

ORDER BY AVG(Reputation)

/* Which users have asked questions on at least 100 tags this year? */

SELECT UserId, DisplayName, Reputation, COUNT(DISTINCT Tag) AS Tags, 'http://stackoverflow.com/users/' + CAST(UserId AS VARCHAR(20)) AS Url

FROM dbo.rptUsersTags

WHERE QuestionsTY > 0

GROUP BY UserId, DisplayName, Location, Reputation

HAVING COUNT(DISTINCT Tag) >= 100

ORDER BY COUNT(DISTINCT Tag) DESC

The queries are different.

The groupings are different.

The order-bys are different.

How on earth are we supposed to deal with that? This is a typical problem for

data warehouses where we can't predict what users will query for, but we have

to make the system go fast anyway.

If we think about our speedometer, we're not even going to hit 1 batch request

per second! While one of those queries runs - any one of them - check your

speedometer in another window:

sp_BlitzFirst;

Our wait stat is PAGEIOLATCH.

SQL Server stores data on disk in 8KB pages. Think of them like pages in a

phone book - you know, the white book your grandfather kept above the fridge

so he could find the nearest pizza place.

PAGEIOLATCH means SQL Server is doing IO to read PAGEs from disk. (There are

other kinds of waits that look similar, like PAGELATCH or LATCH, but that's

just Microsoft's way of making sure smart people like you have job security.)

In order to fix PAGEIOLATCH, I have a few options:

* Query less data

* Get more memory

* Make storage go faster

SQL Server 2014 introduced a new way to cache data: Buffer Pool Extensions.

This lets you set up space on a local SSD to cache data that SQL Server would

have ordinarily ejected from memory.

It's perfect when your commonly-queried tables are larger than memory. Today,

I'm querying a 30GB table, but I've only got ~6GB of RAM on this VM. So let's

turn on Buffer Pool Extensions to cache more data:

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION ON

(FILENAME = 'B:\BufferPoolExtensions\KidPool.BPE', SIZE = 40 GB);

/* And then let's try one of our queries again: */

SELECT Tag, AVG(Age) AS AvgAge, COUNT(1) AS Users

FROM dbo.rptUsersTags

GROUP BY Tag

HAVING COUNT(1) > 1000

ORDER BY AVG(Age) DESC;

It's actually slower, not faster. Why is that?

Well, the first time you run queries after enabling BPE, the files have to be

created and populated, so some folks might guess we're warming up the pool,

populating data into Buffer Pool Extensions that it'll read later. And that's

true, but it's not the reason the query is slower. This query will be slower

no matter how many times you run it, even if the data is already in our SSD's

Buffer Pool Extensions.

Run the query while looking at this Perfmon counters for the drives where your

data files live, AND where your Buffer Pool Extensions live:

* Physical Disk: Avg. Disk Bytes/Read

SELECT Tag, AVG(Age) AS AvgAge, COUNT(1) AS Users

FROM dbo.rptUsersTags

GROUP BY Tag

HAVING COUNT(1) > 1000

ORDER BY AVG(Age) DESC;

You'll see:

* Data file drive - 256KB reads (typically)

* Buffer Pool Extension drive - 8KB reads

SQL Server only reads one page at a time per BPE read, whereas with Developer

Edition and Enterprise Edition, SQL Server will ask for much more data each

time it talks to your drives.

Because of this design, Buffer Pool Extensions only make sense when:

* You have a transactional app (OLTP)

* That does small, one-record lookups

* From a table that is too big to fit in RAM

* And is und

That doesn't mean BPE is bad - it's just like any other SQL Server tuning tool.

If you want to make queries faster without changing the application, you can.

You can do it, and SQL Server can help.

But you have a lot of tools in the toolbox, and you have to know when to reach

for each of them. That isn't the right tool here, so we'll turn it back off:

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;

And restart our SQL Server.

We're going to need to find a different tool - and good news, MSSQL has one.

Sometimes we try doing all kinds of different indexes to just guess what users

will query. It's a guessing game.

SQL Server 2012 introduced an interesting pain relief option: non-clustered

columnstore indexes. In a nutshell, every column is its own index.

Let's build a columnstore index on each of our reporting tables using the GUI,

and check the resulting scripts. While it runs, we'll talk more:

CREATE NONCLUSTERED COLUMNSTORE INDEX [BGO_NonClusteredColumnStoreIndex] ON [dbo].[rptUsersTags]

(

[UserId],

[Tag],

[Age],

[CreationDate],

[DisplayName],

[DownVotes],

[EmailHash],

[LastAccessDate],

[Location],

[Reputation],

[UpVotes],

[Views],

[WebsiteUrl],

[AccountId],

[BadgesTY],

[BadgesLY],

[BadgesTotal],

[QuestionsTY],

[QuestionsTYavgScore],

[QuestionsLY],

[QuestionsLYavgScore],

[QuestionsTotal],

[QuestionsTotalavgScore],

[AnswersTY],

[AnswersTYavgScore],

[AnswersLY],

[AnswersLYavgScore],

[AnswersTotal],

[AnswersTotalavgScore],

[CommentsTY],

[CommentsTYavgScore],

[CommentsLY],

[CommentsLYavgScore],

[CommentsTotal],

[CommentsTotalavgScore]

)WITH (DROP_EXISTING = OFF)

It takes a few minutes because it's basically storing our data in a totally

different way.

It's great for:

* Very wide tables

* Where report users don't query all of the columns

* And they sort different columns all the time

* And we need grouping aggregations

* Read-only tables, loaded overnight in batches

In summary - perfect for data warehouse fact tables like sales history.

In SQL Server 2012, when you added a nonclustered columnstore index, it makes

the table read-only. You can't do any inserts, updates, or deletes. This means

it's really only good for data warehouses that are loaded nightly, and then

queried all day long. When you want to load more data, you need to drop the

nonclustered columnstore index, load your data, then build the index again.

Judging by how long it's taking us to build this index on a ~30GB table, you

can probably guess why this isn't for everybody.

In SQL Server 2014, though, you can actually build a CLUSTERED columnstore

index, and it's updatable! SQL Server goes through some tricks behind the

scenes to keep it up to date for you automatically. Sadly, you couldn't add

conventional nonclustered index to a table with a clustered columnstore index,

and nonclustered columnstore indexes are still read-only.

Here's where columnar indexes make sense:

* Your chief wait type is PAGEIOLATCH (reading data pages from disk)

* Your tables are very wide (lots of columns)

* Your report users don't query all of the columns

* And they sort different columns all the time

* And we need grouping aggregations

* And we only load data in batches, like overnight

* For SQL 2012, where you can afford to rebuild this index nightly

To learn more about columnstore indexes, check out Niko Neugebauer's site:

http://www.nikoport.com/columnstore/

After the build finishes, retry our queries again:

/* Which popular tag's users are the oldest? */

SELECT Tag, AVG(Age) AS AvgAge, COUNT(1) AS Users

FROM dbo.rptUsersTags

GROUP BY Tag

HAVING COUNT(1) > 1000

ORDER BY AVG(Age) DESC;

/* Which tag's users have the lowest reputation, for users who were active this year? */

SELECT TOP 50 Tag, AVG(Reputation) AS AvgReputation, COUNT(1) AS Users, SUM(Reputation) AS TotalReputation

FROM dbo.rptUsersTags

WHERE QuestionsTY > 10 OR AnswersTY > 10 OR CommentsTY > 10

GROUP BY Tag

HAVING COUNT(1) > 1000

ORDER BY AVG(Reputation)

/* Which users have asked questions on at least 100 tags this year? */

SELECT UserId, DisplayName, Reputation, COUNT(DISTINCT Tag) AS Tags, 'http://stackoverflow.com/users/' + CAST(UserId AS VARCHAR(20)) AS Url

FROM dbo.rptUsersTags

WHERE QuestionsTY > 0

GROUP BY UserId, DisplayName, Location, Reputation

HAVING COUNT(DISTINCT Tag) >= 100

ORDER BY COUNT(DISTINCT Tag) DESC

Magical! Revolutionary!

How much would you pay for an index that fast? 10GB of drive space? 20GB? 30GB?

EVEN MORE? Let's go find out how much space it takes - right-click on the table

in Object Explorer, and click Properties, Storage. Compare the data size to the

index size.

This index is less than 1GB in size for a 30GB table, even though it's storing

almost all of the columns.

How? The magic of compression. See, in a reporting data warehouse type system,

where we're dealing with denormalized data, it often looks the same:

SELECT [UserId]

,[Tag]

,[Age]

,[CreationDate]

,[DisplayName]

,[DownVotes]

,[EmailHash]

,[LastAccessDate]

,[Location]

,[Reputation]

,[UpVotes]

,[Views]

,[WebsiteUrl]

,[AnswersTY]

,[AnswersTYavgScore]

,[AnswersLY]

,[AnswersLYavgScore]

,[AnswersTotal]

,[AnswersTotalavgScore]

,[CommentsTY]

,[CommentsTYavgScore]

,[CommentsLY]

,[CommentsLYavgScore]

,[CommentsTotal]

,[CommentsTotalavgScore]

,[QuestionsTY]

,[QuestionsTYavgScore]

,[QuestionsLY]

,[QuestionsLYavgScore]

,[QuestionsTotal]

,[QuestionsTotalavgScore]

,[AboutMe]

,[AccountId]

,[BadgesTY]

,[BadgesLY]

,[BadgesTotal]

FROM [dbo].[rptUsersTags]

WHERE UserId = 26837

If we're storing every column in its own index, look at how many duplicates we

have. Columnar indexes store this data much more efficiently, and it's not

unusual to see a 90% (or higher) compression rate on warehouse-type data.

SQL Server 2016 gets even better:

* Nonclustered columnstore indexes are updateable

* Nonclustered columnstore indexes can be filtered

* Clustered columnstore indexes can have regular b-tree indexes too

* Both get better performance for aggregates (MIN, MAX, SUM, COUNT, AVG)

and for string predicates (myfieldname LIKE '%foo%')

* Better concurrency (support of snapshot isolation and RCSI, row-level locking

if you have nonclustered b-tree indexes)

* Better index reorganize results (removes deleted rows, less memory pressure)

* Readable on AlwaysOn Availability Group secondaries

To learn more about SQL Server 2016's improvements, check out Ignite session

BRK4556 - Ignite attendees can watch the video and download the slides from

http://ignite.microsoft.com.

Next, let's talk about a different kind of workload.

We've been looking at workloads that do a lot of reads - what about writes?

Let's say we have a PostViews table that tracks the views for every post at

Stack Overflow. It's not as crazy as it sounds - this data is actually

persisted in the database:

USE StackOverflow;

SELECT TOP 100 *

FROM dbo.Posts;

Keeping that up to date is pretty hard work. You can see the latest stats here:

http://stackexchange.com/performance

Stack gets thousands of hits per second on their web servers. So let's think

about a SQL Server that would handle JUST tracking view count, nothing else.

We would run this one query thousands of times per second:

EXEC dbo.usp_UpdatePostViews @PostId = 25;

/* First, make sure the entire PostViews table is cached in RAM: */

SELECT COUNT(*) FROM dbo.PostViews;

/* Let's run a lot of those with SQLQueryStress and check our bottleneck: */

sp_BlitzFirst @ExpertMode = 1;

Our bottleneck is WRITELOG - writing to the transaction log. SQL Server has to

log everything we do because if we lose power or Godzilla attacks the data

center in the middle of a transaction, SQL Server has to be able to roll back.

Some folks might say, "Put the database in SIMPLE recovery mode." Bad news:

we're already there. SIMPLE recovery doesn't mean we log less data - it just

means the transaction log is available for reuse as soon as our transaction

finishes.

So how do we fix WRITELOG? Before SQL 2014, the answer was, "Write less data."

Meet our new friend:

ALTER DATABASE [StackOverflow] SET DELAYED_DURABILITY = FORCED WITH NO_WAIT

Delayed durability is a new option that lets SQL Server say a transaction is

complete even before the transaction log has been hardened (meaning, before the

log file has been written to disk.)

Rerun our load test with SQLQueryStress, and check our speedometer:

sp_BlitzFirst @ExpertMode = 1;

The speedometer doesn't always change much - this query's speed fluctuates a

lot - but check out the wait times.

Our WRITELOG waits simply disappear.

Awesome, right? Turn on delayed durability everywhere, right? Wrong. Just like

the other tuning tools we talked about, there are big drawbacks to this tool,

and you need to know where it's appropriate.

Delayed durability is great when:

* WRITELOG is the biggest bottleneck holding you back

* You can't make your transaction log storage go any faster

* You're okay losing transactions in the event of a power failure, server crash

* You're even okay losing transactions during a graceful shutdown

That's right: read that last bullet carefully. Here's the Books Online page:

https://msdn.microsoft.com/en-us/library/dn449490.aspx

"SQL Server shutdown and restart:

For delayed durability, there is no difference between an unexpected shutdown

and an expected shutdown/restart of SQL Server. Like catastrophic events, you

should plan for data loss. In a planned shutdown/restart some transactions that

have not been written to disk may first be saved to disk, but you should not

plan on it. Plan as though a shutdown/restart, whether planned or unplanned,

loses the data the same as a catastrophic event."

Everybody got that? Choose carefully.

There are some really cool options with delayed durability - you can force it

on every transaction, or you can just ALLOW it, and let each transaction choose

whether it wants delayed durability or not, like this:

ALTER DATABASE [StackOverflow] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT

Now transactions can have it if they want - and I do, so let's add it to our

stored procedure:

ALTER PROCEDURE [dbo].[usp_UpdatePostViews] @PostId INT = NULL AS

BEGIN TRAN

SET @PostId = FLOOR(RAND()*25829713);

UPDATE dbo.PostViews

SET ViewCount = ViewCount + 1

WHERE PostId = @PostId

COMMIT TRAN WITH (DELAYED_DURABILITY = ON);

This way, only my PostViews stored procedure gets delayed durability.

For today, though, my data is kind of important to me, so I'm going to turn

that back off.

ALTER DATABASE [StackOverflow] SET DELAYED_DURABILITY = DISABLED WITH NO_WAIT

Let's check out another kind of workload - a noisy, messy one.

To do this, I'm going to need to call a lot of different queries at once. I'm

using a technique I blogged about here:

http://BrentOzar.com/go/stresstest

I'm going to be calling usp_NoisyWorkload from a bunch of threads. Fire up

SQLQueryStress and run StackOverflowReports.dbo.usp_NoisyWorkload from dozens

of sessions, thousands of times. While it's running, let's look at the running

queries with Adam Machanic's amazing tool, sp_WhoIsActive:

sp_WhoIsActive;

We've been talking a lot about wait types at the server level. sp_WhoIsActive

shows you wait types at the QUERY level when you need to do detailed tuning. In

this case, we have queries that are piled up waiting on LCK* - locks.

Many queries are trying to read and write to the same table at the same time.

There's a lot of ways to fix this - index tuning is my favorite, followed by

query tuning - but sometimes you don't have control over that. Sometimes you

aren't allowed to change the queries or the indexes.

Not allowed to change queries or indexes? Check out optimistic locking, also

known as Read Committed Snapshot Isolation, aka RCSI, aka The Artist Formerly

Known As The Turbo Button.

With RCSI:

* Readers don't block writers

* Writers don't block readers

* (But writers can still block each other)

This is different than SQL Server's default isolation level, known as Read

Committed, also known as Careful Mode. Careful is awesome, but optimistic

locking is just...more awesome.

To turn it on, we have to get everybody out of the database, then run:

ALTER DATABASE [StackOverflowReports] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

Then, even without changing our code, run our load test again, and look at the

difference in wait types:

sp_WhoIsActive;

The locking is dramatically reduced! Now, we've still got simultaneous writers

blocking each other, but now the readers can get their job done without being

blocked.

RCSI is great when:

* Your biggest wait is LCK*

* You can't fix the indexes or the queries

* You're okay with the risks: BrentOzar.com/go/rcsi

* Your tempdb can handle additional workloads

It's especially great with SQL Server 2012 and 2014, which allow us to use

local solid state drives for tempdb in a cluster.

WHEW! We've covered a lot of tuning options:

* Trace flag 4199

* SQL 2014 Compatibility Level

* Columnstore indexes

* Buffer pool extensions

* Delayed durability

* Read committed snapshot isolation

To get this script and the Stack Overflow databases, visit:

http://BrentOzar.com/go/faster

1 Comment.

Tune SQL Server’s Settings – HomeWork Bureau
June 19, 2019 9:11 am

[…] Watch Brent Tune Servers – at Microsoft Ignite 2015, he took several SQL Server workloads, found the bottleneck, and then tweaked various settings for quick fixes. […]

Watch Brent Tune Servers: Ignite 2015

Watch Brent Tune Servers: Ignite 2015

How to Get the Stack Overflow Demo Database

How to Follow Along with My Demos

1 Comment.

Save your cart?