Live classes start in 21d 02h 03m — Save your seat

Author: Kendra Little

My goal is for you to understand your SQL Server’s behavior– and learn how to change it. When I’m not figuring out the solutions to your database problems, you’ll find me at user group meetings in Portland, Oregon. I also love to draw.
Performance Tuning

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

June 3, 2014

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.

Read more about Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables) 20 comments — Join the discussion
Performance Tuning

How to Tell if You Need More Tempdb Files

May 22, 2014

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.

Read more about How to Tell if You Need More Tempdb Files 41 comments — Join the discussion

4 Lightweight Ways to Tell if a Database is Used

May 5, 2014

We've all found those databases. They're on your production SQL Server instance, but nobody seems to know if they're being used, or what's using them.

You could run some sort of trace, but you'd probably drag down performance on your SQL Server. What you need is a quicker way to find out if the database is in use.
Is a Login Using the database?
And if so, what query did they run last? My favorite way to see this is with Adam Machanic's sp_whoisactive.

Read more about 4 Lightweight Ways to Tell if a Database is Used 52 comments — Join the discussion
Performance Tuning

Memory and IO in Tempdb: SQL 2014 and SQL 2012 SP1 CU10 Improvement

April 29, 2014

Update on 5/25/2014: At least parts of this improvement have been backported to SQL Server 2012 SP1 CU 10. Read the KB here. After applying CU10 to SQL Server 2012 SP1, the test query shown in this post performed as it did against SQL Server 2014. I was excited to read Bob Dorr's recent post…

Read more about Memory and IO in Tempdb: SQL 2014 and SQL 2012 SP1 CU10 Improvement 19 comments — Join the discussion
T-SQL & Development

“If You Can’t Do ____, You’re Not a Production DBA”

April 24, 2014

Recently I saw a tweet that said, "If you can't read a query execution plan, you're not a production DBA."I love execution plans, and there are really great reasons to learn to read execution plans, but I disagree completely.Database Administration is not all about performancePerformance is only one component to database administration. Let's take Jill as…

Read more about “If You Can’t Do ____, You’re Not a Production DBA” 28 comments — Join the discussion

Why Index Fragmentation and Bad Statistics Aren’t Always the Problem (Video)

April 5, 2014

Do you rely on index rebuilds to make queries run faster? Or do you always feel like statistics are "bad" and are the cause of your query problems? You're not alone-- it's easy to fall into the trap of always blaming fragmentation or statistics. Learn why these two tools aren't the answer to every problem…

Read more about Why Index Fragmentation and Bad Statistics Aren’t Always the Problem (Video) 1 comment — Join the discussion
Performance Tuning

Are Table Variables as Good as Temporary Tables in SQL 2014?

April 2, 2014

There's a couple of new features in SQL Server 2014 that provide options for how you work with temporary objects. Will inline index creation or memory optimized temporary tables forever change the way you code? Let's take a look! Inline Index Creation SQL Server 2014 brings us a TSQL improvement called "inline specification of CLUSTERED…

Read more about Are Table Variables as Good as Temporary Tables in SQL 2014? 31 comments — Join the discussion

PREEMPTIVE_OS_WRITEFILEGATHER and Instant File Initialization (IFI)

February 13, 2014

The other day I was running a script I'd written to expand out some tables in the AdventureWorks sample database for some testing. The script was taking a long time, and at first I thought I was just spoiled because I usually run my tests on my laptop, whose storage is backed by SSDs. In this case I was running my test against a SQL Server instance up in AWS.

Read more about PREEMPTIVE_OS_WRITEFILEGATHER and Instant File Initialization (IFI) 33 comments — Join the discussion

SSMS Trick: Edit Large Procedures with the Splitter Bar

January 15, 2014

Here's a SQL Server Management Studio trick that comes in super handy when you've got long pieces of code. I almost never see anyone use this, and I think the reason is that few people know about it.

When I'm working on a stored procedure, sometimes I want to add in a new variable or a temp table. I declare 'em all at the top of the procedure, but I'm working much farther down in the proc than that.

Read more about SSMS Trick: Edit Large Procedures with the Splitter Bar 11 comments — Join the discussion

Q: Can High MaxDOP make a query SLOWER?

December 23, 2013

Answer: Yep, sometimes it can.I used to think that higher degrees of parallelism followed a law of diminishing returns-- you could add more threads, but the benefits would taper off. But it's a bit more complicated than that. Microsoft's recommendation to be careful when setting maxdop to values over 8 is a warning worth heeding.Lowering maxdop…

Read more about Q: Can High MaxDOP make a query SLOWER? 15 comments — Join the discussion