Posts by Kendra Little

Kendra Little

167 posts

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.

Do Lots of Connections Slow Down Your SQL Server?

Last Updated May 23, 2014

Kendra Little

SQL Server

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…

DBCC CheckDB FAQ: Check for Corruption in SQL Server

Last Updated February 9, 2017

Kendra Little

Backup and Recovery, CHECKDB and Corruption

DBCC CheckDB: Frequently Asked Questions 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…

How to Tell if You Need More Tempdb Files

Last Updated November 4, 2020

Kendra Little

SQL Server, TempDB

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…

How to Configure Anti-Virus for SQL Servers

Last Updated February 13, 2017

Kendra Little

Production Database Administration

Oh, that’s not a virus! That’s just replication. Anti-virus is a devilish beast. If you don’t implement it, someone’s sure to log onto your SQL Server and download some free tool that puts you at risk as soon as you’re not looking. If you do implement it, sometimes it can make you crazy. And often it’s…

4 Lightweight Ways to Tell if a Database is Used

Last Updated May 1, 2014

Kendra Little

SQL Server

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. Is anyone using this? 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…

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

Last Updated February 13, 2017

Kendra Little

SQL Server, TempDB

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…

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

Last Updated February 9, 2017

Kendra Little

Professional Development

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 performance Performance is only one component to database administration. Let’s…

The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast

Last Updated April 5, 2014

Kendra Little

SQL Server

Is this really happening? Sometimes I run into a query plan that just shocks me. It’s like looking up and suddenly seeing an adult dressed as a squirrel riding a unicycle down a busy city street. You have to stop and ask yourself, “Did that really just happen?” (I live in Portland, Oregon, so yeah,…

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

Last Updated February 13, 2017

Kendra Little

Index Maintenance, SQL Server

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…

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

Last Updated February 13, 2017

Kendra Little

In-Memory OLTP (Hekaton), SQL Server, TempDB

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…

Need High Availability for SQL Server? Use Windows Server 2012.

Last Updated February 9, 2017

Kendra Little

Always On Availability Groups

SQL Server DBAs are slow to adopt Windows Server 2012 and Server 2012 R2. I frequently find teams planning new implementations of SQL Server 2012 on Windows Server 2008 R2. There seem to be two main reasons for this: DBAs want to change a minimal number of factors, and are slow to trust a new…

Extended Events for Tracking Code Improvements (video)

Last Updated February 21, 2014

Kendra Little

SQL Server

Ever wanted to prove that your code or index changes had a positive impact? In this 30 minute video, Kendra shows you how to use Extended Events to measure a production workload– and how you can aggregate the results to prove your changes made a difference. Want to skip straight to the demos? Here’s a…

The Evolution of SQL Server Instrumentation

Last Updated February 19, 2014

Kendra Little

SQL Server

One of my favorite things about SQL Server is the instrumentation. It’s incredibly good at giving you information about what’s going on in the database engine and query optimizer. Want to know why your server is slow? Need to know what made it go offline? You have a lot of tools available. This hasn’t always…

PREEMPTIVE_OS_WRITEFILEGATHER and Instant File Initialization (IFI)

Last Updated February 13, 2014

Kendra Little

SQL Server

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…

Statistics Matter on Temp Tables, Too

Last Updated February 13, 2017

Kendra Little

SQL Server, TempDB

Temp tables are like real tables, just a little tricker. When you’re starting out writing TSQL, it’s easy to want to do all your work in a single query. You learn about derived sub-queries, CROSS APPLY statements, and common table expressions. Suddenly, each of your queries is so complex that you hardly know what you’re…

UPDATE STATISTICS: the Secret IO Explosion

Last Updated February 13, 2017

Kendra Little

Index Maintenance, SQL Server, Statistics

I first knew something was up when I looked at the job history for a simple maintenance plan. It had two steps: Rebuild all the indexes in the database – this took 10 minutes each night. Update statistics – this took 2-3 hours each night. What was going on? Statistics in SQL Server are small,…

Full Scans/sec Doesn’t Mean What You Think

Last Updated January 15, 2014

Kendra Little

SQL Server

When is a “Full Scan” not a Full Scan? The performance counter “Full scans/sec” sounds pretty scary. If you see spikes in this counter, you’re likely to think that your queries are madly scanning all the data in your tables– there are no stylish, well performant seeks, just drunken queries spewing IO everywhere. If you…

SSMS Trick: Edit Large Procedures with the Splitter Bar

Last Updated January 10, 2014

Kendra Little

SQL Server

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…

SQL Server DMVs that Lie

Last Updated December 21, 2013

Kendra Little

SQL Server

SQL Server has amazing instrumentation. Dynamic management views and functions give you great insight into what’s happening in your SQL Server and what’s slowing it down. Interpreting these views and functions takes a lot of time, but with practice and skill you can use them to become great at performance tuning. But nothing’s perfect. Some…

Top 10 Signs Your SQL Server is Neglected

Last Updated February 13, 2017

Kendra Little

CHECKDB and Corruption, SQL Server

“That’s an interesting configuration you’ve got there.” Sometimes you see someone with toilet paper sticking to their shoe, their shirt tucked into their underwear, or badly in need of a tissue to get rid of whatever that is hanging out of their nose. It’s only right to find a discreet way to let them know…