Posts by Kendra Little

Do Lots of Connections Slow Down Your SQL Server?

SQL Server
4 Comments
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…
Read More

How to Tell if You Need More Tempdb Files

SQL Server, TempDB
37 Comments
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…
Read More

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

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…
Read More

Are Table Variables as Good as Temporary Tables in SQL 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

The Evolution of SQL Server Instrumentation

SQL Server
3 Comments
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…
Read More

Statistics Matter on Temp Tables, Too

SQL Server, TempDB
18 Comments
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…
Read More

Full Scans/sec Doesn’t Mean What You Think

SQL Server
9 Comments
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…
Read More

SQL Server DMVs that Lie

SQL Server
21 Comments
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…
Read More