Blog

How to Use Our HA/DR Planning Worksheets [Video]

You’re in charge of making sure the database never goes down, but your managers won’t give you any budget. You’re tired of feeling guilty like it’s your fault there’s an outage – and now there’s help. Microsoft Certified Master Brent Ozar helps businesses understand that they have to pay the piper if they want high availability. He’ll share his simple two-page worksheet to get the business users on your side in this 30-minute webcast.

Get the worksheets here.

For more videos like this:

Temp Tables vs Table Variables vs Memory Optimized Table Variables [Video]

Should you use temp tables or table variables in your code? Join Microsoft Certified Master Kendra Little to learn the pros and cons of each structure, and take a sneak peek at new Memory Optimized Table Variables in SQL Server 2014.

This video is a recording of a live webcast, so please excuse any audio and video imperfections. Want the scripts from the webcast? Scroll on down.

Restore AdventureWorks2012

We’ll be changing some things in the database, so I like to restore it to a database named “Showdown” for easy cleanup, but you don’t have to do it that way.

use master;
GO

RESTORE DATABASE Showdown FROM 
	DISK=N'S:\MSSQL\Backup\AdventureWorks2012-Full Database Backup.bak'
	WITH REPLACE, 
	MOVE 'AdventureWorks2012_Data' TO N'S:\MSSQL\Data\Showdown_Data.mdf',
	MOVE 'AdventureWorks2012_Log' TO N'S:\MSSQL\Data\Showdown_Log.ldf';
GO

ALTER DATABASE Showdown SET RECOVERY SIMPLE;
GO
ALTER DATABASE Showdown SET COMPATIBILITY_LEVEL=120;
GO 

USE Showdown
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO

Test Temp Tables: Old School

In this example we create a nonclustered index after the temp table is created. By running it repeatedly you can see that the temp object is recreated each time.

IF OBJECT_ID ('dbo.TempObjectFightClub') IS NULL
    EXEC ('CREATE PROCEDURE dbo.TempObjectFightClub AS RETURN 0')
GO
ALTER PROCEDURE dbo.TempObjectFightClub
    @StateProvinceID int
AS

    CREATE TABLE #TestMe  (
	    i INT IDENTITY PRIMARY KEY CLUSTERED,
	    City VARCHAR(60),
	    StateProvinceID INT
    );

    CREATE NONCLUSTERED INDEX ixTestMe_StateProvinceID 
        ON #TestMe(StateProvinceID);

    INSERT #TestMe (City,StateProvinceID)
    SELECT City, StateProvinceID
    FROM [Person].[Address]

    SELECT City
    FROM #TestMe
    WHERE StateProvinceID=@StateProvinceID;

    SELECT
        name,
        ps.index_id,
        ps.row_count,
        create_date,
        modify_date
    FROM tempdb.sys.objects AS so
    join tempdb.sys.dm_db_partition_stats ps on so.object_id = ps.object_id
    WHERE
        name LIKE N'#%'
        and row_count > 0
GO

EXEC dbo.TempObjectFightClub @StateProvinceID=1;
GO

Test Table Variables

Here we use the new SQL Server 2014 inline index creation syntax to create a nonclustered index on the table variable. (We couldn’t do that before!) But looking at the execution plan, things get a little weird.

IF OBJECT_ID ('dbo.TempObjectFightClub') IS NULL
    EXEC ('CREATE PROCEDURE dbo.TempObjectFightClub AS RETURN 0')
GO
ALTER PROCEDURE dbo.TempObjectFightClub
    @StateProvinceID int
AS

    DECLARE @TestMe TABLE (
	    i INT IDENTITY PRIMARY KEY CLUSTERED (i),
	    City VARCHAR(60),
	    StateProvinceID INT,
        --This index is created using the 2014 syntax 
	    INDEX ixTestMe_StateProvinceID NONCLUSTERED (StateProvinceID)
    );

    INSERT @TestMe (City,StateProvinceID)
    SELECT City, StateProvinceID
    FROM [Person].[Address]

    SELECT City
    FROM @TestMe
    WHERE StateProvinceID=@StateProvinceID;

    SELECT
        name,
        ps.index_id,
        ps.row_count,
        create_date,
        modify_date
    FROM tempdb.sys.objects AS so
    join tempdb.sys.dm_db_partition_stats ps on so.object_id = ps.object_id
    WHERE
        name LIKE N'#%'
        and row_count > 0;
GO

EXEC dbo.TempObjectFightClub @StateProvinceID=1;
GO

<h3>Temp Tables Revisited with Inline Index Declaration</h3>

This test shows that the new "inline index" creation in SQL Server 2014 lets us create that nonclustered index when the temp table is created, which then allows the cached object to be re-used.

1
IF OBJECT_ID ('dbo.TempObjectFightClub') IS NULL
    EXEC ('CREATE PROCEDURE dbo.TempObjectFightClub AS RETURN 0')
GO
ALTER PROCEDURE dbo.TempObjectFightClub
    @StateProvinceID int
AS

    CREATE TABLE #TestMe  (
	    i INT IDENTITY PRIMARY KEY CLUSTERED,
	    City VARCHAR(60),
	    StateProvinceID INT,
        /* This index is created using the 2014 syntax */
	    INDEX ixTestMe_StateProvinceID 
            NONCLUSTERED (StateProvinceID)
    );

    INSERT #TestMe (City,StateProvinceID)
    SELECT City, StateProvinceID
    FROM [Person].[Address]

    SELECT City
    FROM #TestMe
    WHERE StateProvinceID=@StateProvinceID;

    SELECT
        name,
        ps.index_id,
        ps.row_count,
        create_date,
        modify_date
    FROM tempdb.sys.objects AS so
    join tempdb.sys.dm_db_partition_stats ps on so.object_id = ps.object_id
    WHERE
        name LIKE N'#%'
        and row_count > 0;
GO

EXEC dbo.TempObjectFightClub @StateProvinceID=1;
GO

Memory Optimized Table Variables

Oh Brave New World. First, enable things:


ALTER DATABASE Showdown ADD FILEGROUP [ImaNewFileGroup] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE Showdown ADD FILE (name='imanewfilegroup_1', filename='S:\MSSQL\Data\imanewfilegroup_1')
	TO FILEGROUP [ImaNewFileGroup];
GO
ALTER DATABASE Showdown SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;
GO

Then create the table type:

CREATE TYPE TM1 as TABLE (
	City VARCHAR(60) COLLATE Latin1_General_100_BIN2,
	StateProvinceID INT NOT NULL,
	INDEX ixTestMe_StateProvinceID NONCLUSTERED (StateProvinceID)
) WITH (MEMORY_OPTIMIZED = ON)
GO

Now, let’s test!

IF OBJECT_ID ('dbo.TempObjectFightClub') IS NULL
    EXEC ('CREATE PROCEDURE dbo.TempObjectFightClub AS RETURN 0')
GO
ALTER PROCEDURE dbo.TempObjectFightClub
    @StateProvinceID int
AS

    DECLARE @TestMe TM1;
    
    INSERT @TestMe (City,StateProvinceID)
    SELECT City, StateProvinceID
    FROM [Person].[Address]

    SELECT City
    FROM @TestMe
    WHERE StateProvinceID=@StateProvinceID;

    -- Look at the object we're using. Hm, where is it?
    SELECT * FROM  sys.dm_db_xtp_object_stats;
    SELECT * FROM  tempdb.sys.dm_db_xtp_object_stats;

GO

EXEC dbo.TempObjectFightClub @StateProvinceID=1;
GO

A Manager’s Guide to Tuning Code

In your business application, you have a slow screen or function. Your team is pointing fingers between the application code, the database, and the hardware.

To make it go faster, here are your main options:

Tuning Options - Mark All That Apply

Tuning Options – Mark All That Apply

Check all of the options you’re willing to consider, and X out all of the options you’re NOT willing to consider.

The more boxes you X out, the more expensive the rest of the boxes become.

For example, on some projects with third party vendor software – say, SharePoint or Dynamics – managers give me requirements like this:

Tuning third party software

Tuning third party software

If those are my only options, I’m going to have to push those limits pretty hard, and I’m going to have to sink a lot of money into those options. I may have to step up to SQL Server Enterprise Edition and cache the entire database in RAM.

On the other hand, when I’m dealing with an in-house application with really agile developers, with the application hosted in Amazon Web Services, the grid looks more like this:

Tuning apps in the cloud

Tuning apps in the cloud

As you can probably guess, the tuning options are much more flexible here – not to mention cheaper.

So when you need to make your app go faster, tell your staff what options are on the table, and which ones are off.

Introducing… Your Contributions

You’ve got great ideas, don’t lie. We want to help you get those ideas out there… and into our scripts. It’s great to say that our tools (sp_AskBrent®sp_Blitz®, sp_BlitzIndex®, and sp_BlitzCache™) are as good as they are today because of your help. We’re adding two new ways to make it easier to get a chance to share code and collaborate on some awesome tools.

Feature Requests

Make your voice heard! Mostly by other users, not us. We don't listen to that stuff. But we'll pretend.

Make your voice heard! Mostly by other users, not us. We don’t listen to that stuff. But we’ll pretend.

Starting right now, you can head over to http://support.brentozar.com and submit new feature requests or vote on existing feature requests. You only get so many votes until we implement your feature, so make ‘em count!

This is also your chance to interact with us during the development of the feature.

Contribute Code

A number of you have done this already! You’ve contributed a bunch of fixes, enhancements, and new checks for sp_Blitz®.

In the past you’ve had to email code contributions. We’ve made it easier – there’s now a form with a place for you to dump sample code. In fact, your sample code gets turned into a feature request in our version control system which, in turn, emails everyone. That really means we’ll get to your changes faster.

Ready to share your code? Head over to http://brentozar.com/contributing-code/ and get started.

Don’t freak out at the licensing agreement – the lawyers caught wind of what we were doing and said we should get you to sign it. All it says is that you’re the person who owns the code and you’re giving us the right to use your code; you still own the code.

What Are You Waiting For?

You’re full of great ideas! We’ve got two new ways for you to share them! Sound off or code off.

Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)

There’s an important rule for tuning stored procedures that’s easy to forget: when you’re testing queries from procedures in SQL Server Management Studio, execute it as a stored procedure, a temporary stored procedure, or using literal values.

Don’t re-write the statemet you’re tuning as an individual TSQL statement using local variables!

Where it goes wrong

Stored procedures usually have multiple queries in them. When you’re tuning, you usually pick out the most problematic statement, maybe from the query cache), and tune that.

You don’t always want to run all the other queries. Maybe some of them insert, update, or delete records. Or maybe they just take too much time to run while you’re testing.

How Local variables cause trouble

Let’s say the first statement in this stored procedure is the #1 query on our server.

IF OBJECT_ID('dbo.GetCities') IS NULL
	EXEC ('CREATE PROCEDURE dbo.GetCities AS RETURN 0')
GO

ALTER PROCEDURE dbo.GetCities
	@StateProvinceID int
AS
SELECT DISTINCT City
FROM Person.Address
WHERE StateProvinceID=@StateProvinceID;

/* imagine lots of other statements here */
GO

We’ve found the statement’s execution plan in the cache:

01-execution-plan-from-cache

I right click on the plan and find from the bottom of the XML statement that it was compiled for the value @StateProvinceID=80.

02-execution-plan-compiled-for-value

I want an easy way to test it, so I quickly modify it to run as a single statement, using a local variable:

DECLARE @StateProvinceID int = 80

SELECT DISTINCT City
FROM Person.Address
WHERE StateProvinceID=@StateProvinceID;
GO

And I get a totally different execution plan! The original plan from the cache had nested loops, while now I’m seeing a nonclustered index scan:

03-different-execution-plan-local-variable

This is confusing because I very carefully pulled the value it was compiled for from the original execution plan.

The problem is not the value I’m using for @StateProvinceID. The problem is not my statistics. The problem is local variables.

Local Variables are Weird

Local variables effectively “anonymize” the value being passed in on a parameter in SQL Server. It’s like you’re using Optimize for Unknown all the time.

That makes your local code behave differently than your stored procedure. It’s frustrating and confusing. It can lead you to create the wrong indexes, resort to index hints, or start using RECOMPILE hints everywhere.

Better Option: Temporary Stored Procedures

Here’s an easy alternative that not many people know about: Create a temporary stored procedure, like this:

CREATE PROCEDURE #GetCities
 @StateProvinceID int
AS
SELECT DISTINCT City
FROM Person.Address
WHERE StateProvinceID=@StateProvinceID;
GO

exec #GetCities @StateProvinceID=80;
GO

Hey, look, my nested loop plan is back, just like I found in the execution plan cache:

04-actual-execution-plan-temporary-stored-procedure

This is much like a temporary table: it’s scoped to my session. It lets me quickly and easily test and execute this statement in the context of a stored procedure without modifying my application database.

I prefer this option for testing because it helps me remain conscious that I need to test the procedure with multiple values of the parameter and make sure it stays fast with common values.

(Note: I’m not saying you should use temporary stored procedures in your production code. These are just useful for the tuning process when you don’t want to modify the original application code.)

Another Option: Literal Values

If I’m in a hurry and I just need to tune the procedure for one value, I’ll simply do something like this:

SELECT DISTINCT City
FROM Person.Address
WHERE StateProvinceID=80;
GO

By removing the parameter altogether I get a plan optimized for this specific StateProvinceID, and it’s my nested loop plan again:

05-actual-execution-plan-literal-value

Whenever I change the StateProvinceID value, I’ll get a different execution plan for each specific value.

Local Variables Sneak Up on You

Trust me: this is hard to remember out there in the real world. I’ve caught myself more than a few times rewriting a statement with a local variable, only to discover that I’m getting a different execution plan than I expected.

Whenever you’re rewriting a problem statement that’s run as part of a larger parameterized query, remember that temporary stored procedures can help your tuning process.

Do Lots of Connections Slow Down Your SQL Server?

Threads are important.

Threads are important.

I sometimes hear database administrators say, “This database must be involved in our performance problem: it has too many connections.”

Lots of connections might cause a problem for your SQL Server, or it might not. The good news is that there’s a way to clearly tell if they’re dragging down performance or not.

Lots of connections can be confusing

If you use old built in tools like sp_who2 to see what’s happening in your SQL Server, many connections make it hard to see what’s going on. Firing up my test instance, I get back more than 45 rows from sp_who2 without any applications running at all. Sorting through that can be a mess.

For this reason, I’m not a fan of sp_who2. My usual question for the SQL Server isn’t “who’s connected?” I usually want to know other things:

Both of these tools help keep lots of connections from slowing YOU down as you look at your SQL Server.

How can I prove that lots of connections are causing a performance problem?

SQL Server can manage a lot of connections without too many issues. Yes, it’s better if an application cleans up its connections over time. We recommend tracking the User Connections performance counter, as patterns in this counter may coincide with other events and be useful information.

But just having a high amount of connections doesn’t prove that was the cause of performance problems. For a smoking gun as to why SQL Server is slow, look in two places:

  1. SQL Server Wait Statistics (look for THREADPOOL waits)
  2. The SQL Server Error Log (look for the creepy error below)

Threadpool Waits: a sign of trouble

When lots of connections want to run queries at the same time, SQL Server may need to allocate more worker threads. This process can be slow, and at a certain point SQL Server may reach the maximum worker threads allowed for your configuration.

If this happens on your server, you will see “THREADPOOL” waits in the results from a live sample of sp_AskBrent®, or when you query your wait statistics since startup.

“New queries have not been picked up by a worker thread”: Thread Starvation

When this gets really bad, you may see a nasty error message in your SQL Server Error Log. This message says something like this:

New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the “max worker threads” configuration option to increase number of allowable threads, or optimize current running queries.

The message neglects to mention a few critical things:

What to do if you have THREADPOOL waits or Thread Starvation

First, don’t panic. Avoid changing any settings before you know exactly what they’ll do.

Here’s where to start:

How’s your parallelism? If you have the “max degree of parallelism” setting at the default value of 0 (or a very high number), a good first step is to learn about CXPACKET waits and options for configuring parallelism.

Do you have lock waits? If you have high lock waits (query), look into which tables have the most blocking and how to track down the queries involved.

Have you identified the most frequently run queries in the problem period? Good indexing or improved TSQL for those queries can dramatically reduce needed threads. (Handy tools here.)

What do the perf counters say? Take a look at those performance counters we recommend to see if there’s a jump in the number of connections when the problem occurs or not.

Approaching the problem this way dispels the mystery of whether all those connections really are the problem (or not).

DBCC CheckDB FAQ: Check for Corruption in SQL Server

DBCC CheckDB: Frequently Asked Questions

It's eating our logo. Will it stop at nothing???

It’s eating our logo. Will it stop at nothing???

Q: Do I really need to run DBCC CheckDB? Yes. If you’re even partially in charge of a SQL Server and you’d like to keep your job, you should make sure CheckDB is being run regularly. (Seriously!)

Q: But WHY do I need to run CheckDB? CheckDB helps alert you when data becomes corrupt. Data corruption can cause your users to get incorrect data, cause queries to fail, or can even take your entire SQL Server instance offline. Corruption can happen anytime. It could be caused by a problem in SQL Server, an issue with Windows, or an issue with your storage. It could also happen due to other types of software– something like a filter driver (replicating data, defragmenting drives, etc) or a virus scanner.

Q: How often do I need to run CheckDB? Every day you’d like to keep your job, you should run CheckDB. OK, maybe that’s a bit extreme.

Most people run CheckDB against user databases every weekend. That’s “normal”. Is normal good enough for you? Well, maybe not! When availability is important, people choose to run CheckDB more frequently: often once per day.

For system databases, why not check them every night?

Q: How can I make CheckDB faster? First, do you really need to make it faster? If you have a regular maintenance window where decreased performance is OK, that’s often good enough.

The best way to speed up CheckDB is to offload the work to a totally different server. Do this by automating a process where you regularly restore a full backup, then run CheckDB. This approach has multiple benefits:

  • Tests your restores
  • Doesn’t impact the production database (especially if they’re using independent storage)
  • Secondary instance can be configured to use as a “warm standby” in case the primary server fails

And sure, you can potentially offload CheckDB work to a virtualized SQL Server instance. Just make sure that it has enough resources to run CheckDB fast enough.

Q: Are there any shortcuts with LogShipping, Mirroring, or AlwaysOn Availability Groups?
Bad news: None of those technologies mean you don’t have to run CheckDB against your main production database. The only valid way to offload CheckDB is to run it against a restored full backup.

In fact, AlwaysOn Availability groups may mean you probably have to run more CheckDB, not less.

Database-Corruption-Sleeping-Under-Desk

This is what a corrupt database looks like

Q: Do some features make CheckDB slower? Yep, some indexes check slower than others. Having nonclustered indexes on computed columns can make CheckDB crawl, as can having indexed sparse columns. If you’re using those features and CheckDB is painfully slow, you might consider disabling the nonclustered indexes before running the job– but that means writing custom code and making sure that you handle any error situations when the job fails. (Kind of gross, right? That could lead to a bad day. Head on back up and see “How can I make CheckDB faster” for a better answer.)

Q: What about “PHYSICAL_ONLY” or “NOINDEX”? Can I use those? Well, you can, but then you might not detect corruption as fast. That could be really bad, right? I’m not a fan of either of these options because they turn off valid features in CheckDB that could really save your bacon. I’d much rather offload CheckDB to another server by restoring a backup as describe above– it has much greater benefits and is even better for performance of your production database.

Q: Should I run CheckDB against TempDB? Yes– you should. CheckDB can’t do every single check against TempDB that it can against other databases (the output will let you know that it can’t create a snapshot), but it can still run a variety of checks against TempDB and it’s supported to run it. Don’t skip it! You should be checking master, model, msdb and tempdb, plus all your user databases.

Q: How can I automate running DBCC CheckDB? You can do this by using the Check Database Integrity task in a Maintenance Plan. You can also use a SQL Server Agent job and a free script to help with database maintenance.

Q: Is CheckDB all I need to protect me from corruption? Running CheckDB can help you find out about corruption, but you also need:

To allow alerts and jobs to notify you, you need to configure Database Mail, enable the database mail profile on the SQL Server Agent (and then restart the SQL Server Agent service), configure an operator, and then set the operator on the alerts and jobs.

Q: What if I find corruption? Corruption happens– that’s why this is so important. The first thing you should do is sign up for our online DBA Interview Training to find that next job. (Awkward laugh.)

All kidding aside (and that was a joke! stop updating your resume!), you must respond to the issue immediately. The first thing to do is to alert others on your team to the issue and make a plan. You need to make sure that a full CheckDB has been run against the database, that you’ve read the output of CheckDB carefully in the SQL Server Error Log.

Gail Shaw has posted an article with a great list of do’s an don’ts when you hit corruption here (login required, sorry). If the database is important to your business, I urge you to spin up a support call to Microsoft Support right away– even while you read Gail’s article. At around $500 USD Microsoft Support is your absolute best value when it comes to getting help right away for corruption. (Yep, I’m an independent consultant and I’m telling you that!)

Once you have the specific incident resolved, make sure you follow up. How long has the issue existed? Can root cause of the original corruption be identified? Could you have the issue again, or somewhere else in your environment? It’s rare for CheckDB to strike once and not repeat itself, so it’s important to follow up.

How to Work Around Standard Edition’s Limits [Video]

64GB RAM, no partitioning, no AlwaysOn Availability Groups, no encryption – what’s a DBA to do? Brent Ozar gives you a few easy workarounds in this 30-minute video.

Be aware that audio on this one sucks pretty bad – it accidentally recorded from the wrong microphone instead of our good professional ones. Sorry about that!

How to Tell if You Need More Tempdb Files

You may have read that you need to have more than one data file in SQL Server’s tempdb. This can happen even if you’re using blazing fast storage. If you create a lot of tiny objects in tempdb you may hit a bottleneck on special pages that SQL Server uses internally to allocate all those objects. For certain workloads, adding more tempdb files speeds up SQL Server.

Most database administrators aren’t sure how to tell when they need to add more tempdb files. Here’s how you can find out for yourself.

Tempdb stress: my virtual machine setup

I stressed out my tempdb on a virtual machine with 6GB of memory, 4 virtual CPUs, Windows Server 2012 R2 and SQL Server 2014. The virtual machine is backed by solid state storage. My tempdb was configured with a single data file.

How I ran my stress test against tempdb

I used ostress.exe. This command line utility lets you easily run a query multiple times across many threads. You can get ostress.exe for free by downloading the RML utilities.

Here’s the ostress.exe command that I called:

ostress.exe -E -d"tempdb" -Q"exec dbo.tempdbstress" -n5 -r300 -b -q

The dbo.tempdbstress procedure looks like this:

use tempdb;
GO
CREATE PROCEDURE dbo.tempdbstress
AS
SET NOCOUNT ON;
SELECT TOP(5000) a.name, replicate(a.status,4000) as col2
into #t1
FROM master..spt_values a
CROSS JOIN master..spt_values b OPTION (MAXDOP 1);
GO

Finding GAM and PFS contention in tempdb with sp_AskBrent® and Wait Stats

While this is running, look at waits in two ways. In one session, take a sample of waits with sp_AskBrent®:

exec sp_AskBrent @ExpertMode=1, @Seconds=10;
GO

I see PAGELATCH_UP, PAGELATCH_SH and PAGELATCH_EX waits:

wait stats sample-1 tempdb file

I also have a lot of just plain old CPU wait (the SOS_SCHEDULER_YIELD), because I’m bashing at only 4 virtual CPUs (and I’m running ostress.exe on the same VM as SQL Server).

Finding GAM and PFS contention in tempdb with sp_whoisactive

You can see this in another way: using Adam Machanic‘s free sp_whoisactive tool:

exec sp_whoisactive;
GO

This lets you see the contention if you catch it at the right instant. Bonus: you can confirm exactly what type of pages its occuring on! I can see here that this is explicitly PFS waits. “PFS” means “page free space”: poor tempdb is hitting a hot spot on one of those special pages just recording how much space is available and where.

sp_whoisactive pfs wait

And here I can see the GAM waits. “GAM” means “Global Allocation Map”. That’s another one of those special pages that can get hot and slow things down in tempdb. It’s tracking where objects are allocated. (There’s also “SGAM” or “Shared Global Allocation Map” pages that you might see.)

sp_whoisactive gam wait

Both of these types of waits can be alleviated if you add more data files to tempdb, because then you’ll get more PFS pages and GAM/SGAM pages across the files.

How to tell if you need more tempdb data files

First of all, if you just have one tempdb file, consider adding more files as preventive medicine. Don’t go crazy with this. The formula recommended by Microsoft in KB 2154845 to use one tempdb data file for each logical processor up to 8 processors is a good place to start.

Look at waits that have occurred since startup. Do you have PAGELATCH_UP or PAGELATCH_EX waits? (Note that these don’t have an “IO” in them. PAGEIOLATCH waits are different.)

If you do, that’s not necessarily 100% proof that the issue is in tempdb, but it’s a good indicator. If you don’t, well, it’s probably not a big issue.

If you do find these waits, dig deeper:

  • Identify when the PAGELATCH_% waits are growing the most, and dig into those periods with sp_whoisactive. You can log sp_whoisactive results to a table
  • Look at your top queries. The queries that are part of the issue might be suitable for caching in an application tier.

Tempdb file configuration reminders

Remember: this only applies to tempdb data files. You only need one tempdb log file.

Also remember that the tempdb data files will be used proportionally with their size. This means that you should keep the files equally sized. You can use trace flag 1117 to force all tempdb files to grow at the same time when one grows, but this will apply that behavior to every database on the instance (not just tempdb).

We recommend pre-growing out your tempdb files when you configure your SQL Server instance.

You can add tempdb files without restarting the SQL Server instance. However, we’ve seen everything from Anti-Virus attacking the new files to unexpected impacts from adding tempdb files. And if you need to shrink an existing file, it may not shrink gracefully when you run DBCC SHRINKFILE. For best results, deploy the configuration first to your pre-production environments, use a change request, and perform the change in a planned maintenance window.

How Much Memory Does SQL Server Need?

A frequently asked question is “How much memory does SQL Server need?” It’s difficult to figure out how much memory you should allocate to SQL Server.

Sizing Memory for a New SQL Server

Sizing memory for a new SQL Server application sounds like it’s going to be tricky. But there is one thing that makes this simpler: SQL Server Standard Edition.

SQL Server Standard Edition limits available memory; there are two options based on the version of SQL Server:

  • SQL Server 2008 and earlier: You get unlimited memory. Rejoice in this, it’s about the old good thing your AMC Pacer of a database has going for it. Plan for an eye opener when you look at licensing changes.
  • SQL Server 2008R2 – 2012: Put 96GB of memory in the server. There is a limit of 64GB of memory on these versions of SQL Server: just give SQL Server 64GB and walk away.
  • SQL Server 2014 or newer: Put 192GB of memory in the server. SQL Server 2014 allows up to 128GB of memory for Standard and BI Edition. Give SQL Server the maximum and walk away. This can get more complex if you’re using SQL Server Analysis Services, but have you ever known me to talk about SSAS?

Sizing memory is more difficult with SQL Server Enterprise Edition. Experience with capacity planning makes it easier to size SQL Server. Let’s assume you don’t have a capacity planning team, what should you do?

Get the cheapest, biggest, DIMMs that money can buy. What does that mean? Well, there’s a significant price difference between 16GB and 32GB DIMMs (that’s memory sticks to you and me). For example, a Dell 820 can accept up to 24 DIMMs. What’s the cost look like?

  • 16GB DIMMs - 384GB of system memory, $5,849.28
  • 32GB DIMMs - 768GB of system memory, $19,779.60
  • 64GB DIMMs - 1536GB of system memory, $108,310.32

The 32GB DIMMs fall into the “cheapest, biggest, DIMMs that money can buy” category. Besides, the cost of RAM is likely to be a rounding error compared to the cost of licensing SQL Server Enterprise Edition.

Sizing Memory for an Existing SQL Server

This is where things start to get tricky. There are a few different metrics available to measure the amount of memory that SQL Server is working with.

Remember, we’re trying to measure the size of the active working set of data. This is difficult to do, but there are a few easy ways that we can get most of the way there.

Sizing Memory with Page Life Expectancy

On a single CPU socket system, this is easy:

SELECT  object_name,
        counter_name,
        cntr_value AS [value]
FROM    sys.dm_os_performance_counters
WHERE   LTRIM(RTRIM(object_name)) = 'SQLServer:Buffer Manager'
        AND LTRIM(RTRIM(counter_name)) = 'Page life expectancy' ;

If you’ve got more than one CPU socket (or you’re on a system with multiple NUMA nodes), then you might want to get a bit more specific. The previous query averages memory consumption across all CPU sockets/NUMA nodes. This query looks at each one individually:

SELECT  object_name,
        counter_name,
        instance_name AS [NUMA Node] ,
        cntr_value AS [value]
FROM    sys.dm_os_performance_counters
WHERE   LTRIM(RTRIM(object_name)) = 'SQLServer:Buffer Node'
        AND LTRIM(RTRIM(counter_name)) = 'Page life expectancy' ;

Why track the page life expectancy at all? PLE can be a simple indicator of memory and I/O health. If PLE ever decreases, then you know you’re reading from disk. That doesn’t mean the active working set of data is bigger than memory, it just means that you’re reading from disk at some point.

Track page life expectancy for a regular period of time. Make sure to measure PLE at the same time of day and at regular intervals. Graph your PLE and use that to determine trends about memory use. If PLE is trending down – you may need more RAM.

By the way, here are a few things that could need a read from disk (and decrease PLE):

  • Rebuilding all indexes
  • DBCC CHECKDB

Measuring Disk Activity with Performance Monitor

Perfmon is a great way to track physical disk activity. You don’t need to do any complicated math to determine if disks are being accessed. Once you’re ready to measure, fire up perfmon and add the following counters for every disk that contains non-system SQL Server data files; don’t include log file drives.

  • Physical Disk \ Avg. Disk sec/Read
  • Physical Disk \ Disk Reads/sec
  • SQLServer: SQL Statistics \ Batch Requests/sec

Monitor these counters for at least a week, just like you should monitor PLE for at least a week. You don’t need to collect this information at a particularly fine granularity – even once an hour will do. Just make sure you take into account when regular maintenance (index rebuilds, DBCC, etc) is running so you can account for any reads and writes during those periods.

If you don’t see heavy read activity during business hours, then you can assume you’re not under memory pressure.

By including the Batch Requests/sec counter, we can track disk activity to SQL Server activity. We don’t need worry about system level reads that happen when there’s no SQL Server activity. Also make sure anti-virus is configured to ignore SQL Server files.

If reads increase along side Batch Requests/sec, then we know there’s some correlation – as user activity increases, so does disk activity. If you want to figure out the exact cause, you’ll need to get more invasive and use SQL Server Profiler or Extended Events.

For more information on collecting and analyzing perfmon counters, take a look at SQL Server Perfmon (Performance Monitor) Best Practices.

Measuring with the DMVs

We can get even more scientific using the DMVs. This is where things can get really tricky and it’s important to exercise a great deal of caution – not because the DMVs are inaccurate, but because they contain a great deal of information from many sources.

While we can use sys.dm_io_virtual_file_stats to measure I/O against specific files, it’s important to take into account regular maintenance on those database files. Database backups (both full and differential) will register reads against the data files. In order to determine how many reads are happening against a file, we need a quick and dirty measure.

DECLARE @startup DATETIME,
        @tbb DECIMAL(38, 0),
        @total_reads DECIMAL(38, 0),
        @total_data_size DECIMAL(38, 0),
        @mb DECIMAL(38, 0) ,
        @gb DECIMAL(38, 0) ;

SET @mb = 1024.0 * 1024.0 ;
SET @gb = @mb * 1024.0 ;

SELECT  @startup = create_date
FROM    sys.databases
WHERE   name='tempdb' ;

SELECT  @tbb = SUM(CAST(COALESCE(backup_size, 0) AS DECIMAL(38,0)))
FROM    sys.databases d
        LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE   b.backup_start_date >= @startup
        AND b.type <> 'L'
        AND d.database_id > 4 ;

SELECT  @total_reads = SUM(num_of_bytes_read)
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) vfs
WHERE   vfs.database_id > 4 ;

SELECT  @total_data_size = SUM(CAST(size * 8 AS DECIMAL(38,0))) / @mb
FROM    sys.master_files
WHERE   [type] <> 1
        AND database_id > 4 ;

SELECT  (@total_reads - COALESCE(@tbb, 0)) / @gb AS [Non-backup reads (GB)] ,
        ((@total_reads - COALESCE(@tbb, 0)) / @gb) /
            DATEDIFF(DAY, @startup, CURRENT_TIMESTAMP) AS [Non-backup reads / day (GB)] ,
        @total_data_size  AS [Total Data Size (GB)] ;

This query, while not exact, will at least take into account the reads and writes against the file but will exclude regular backups. If you’re performing index maintenance, that index maintenance will also be included. There’s no easy way to measure the load of index maintenance apart from taking a snapshot of sys.dm_io_virtual_file_stats before and after the database maintenance occurs.

Again – this method isn’t exact, but it will give you a better understanding of the physical I/O requirements of your server since the last SQL Server start up.

Indexes?

There’s one other thing to keep in mind – if you have the right indexes in place, SQL Server won’t need to read from disk and you won’t need to keep the entire database in memory. Adding memory until you don’t perform additional I/O is one way to solve the problem, but tuning indexes is another. Check out sp_BlitzIndex® for ways to tune up your indexes.

Summarizing our Sizing Journey

For new SQL Servers – fill it with memory.

For existing SQL Servers there are three ways to check memory:

  1. Page life expectancy.
  2. Measuring disk activity with Performance Monitor.
  3. Measuring disk activity through the DMVs.

None of these are precise – they all miss out certain key measurements – but they all give us a good enough idea without requiring heavy weight monitoring.

css.php