The Toilet Analogy … or Why I Never Recommend Increasing Worker Threads

Lately I’ve noticed an increasing number of people recommend increasing worker threads. The reasons for the recommendations include things as simple as a troubleshooting step for reports of general system slowness or as complex as a means to mirror very large number of databases on a single server. In one instance, someone successfully convinced another person on a discussion group that they could recommend to their client that it is okay to mirror 400 to 600 databases on a single
read more

Breaking Down TempDB Contention

Breaking Down TempDB Contention What is tempDB contention? From the outside looking in, tempDB contention may look like any other blocking. There are two types of contention that tends to plague tempDB’s, especially when the tempDB is not configured to best practices (multiple, equally sized data files, located on a dedicated, high-speed drive, etc.). For the purpose of this blog, I want to focus on latch contention on the allocation pages. What are allocation pages? Allocation pages are special pages
read more

SQLSaturday 26 Session Files

SQLSaturday 26 Session Files: 10/3/2009 in Redmond, WA Thanks to everyone that attended my sessions at SQLSaturday 26 in Redmond, WA on 10/3!! This was my first SQLSaturday event. I was granted the opportunity to be a last minute replacement speaker and gave two presentations. This was my first time speaking in front of a large audience at an event. It was a thoroughly enjoyable experience, and I hope to speak at future events as well. As promised in my
read more

Shhhh!! Be vewy, vewy quiet. I'm hunting Select *!!

“Select *” is bad. Everyone knows it, but everyone still uses it. I use it. Most of the time it is fairly innocuous. No harm, no foul, right? But what about those precious milliseconds lost sending data across the network to client applications? That’s where you start to notice the effect of a Select *. This effect is amplified when we deal with tables with large data types such as XML and the new max data types.
read more

Breaking Up is Easy to Do

It’s nice to be able to package a process into a single, tidy, elegant query, but it isn’t always possible to do so. And even if it is possible, it may not be the best way to do it. Often, we can get better performance out of large or complex queries by breaking them up into smaller pieces. I encountered a great example of this today. A developer asked me about a query that was taking a really long time to process in the test environment. The particular step that was having issues is building a long string by concatenating short strings to pass through to a remote server for processing of data on the remote server. It was a simple, recursive string building query.
read more