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

Catching Transient CPU Spikes Using SQL Trace

If there was one thing I wish everyone who works with SQL Server would learn, it would have to be to use server-side SQL Traces instead of the client application SQL Server Profiler. Is Profiler really that bad? On a live production system, yes it is. Let me attach Profiler to any system, and I can bring it to a crawl in a matter of milliseconds. In fact, I do a demo of this very thing in my presentation on
read more

Making Sense of Replication Errors in Replication Monitor

If you’ve ever had to deal with errors in Replication Monitor, your first experiences with it probably involved confusion. Sometimes the error messages are too generic and do not help you find a resolution. Replication Monitor also tells you the command that failed. This clears up everything, right? The command reported by Replication Monitor will look something like this: (Transaction sequence number: 0x0001C7A40000AAE700C700000000, Command ID: 1) Helpful!! Right? Well, actually, it is very helpful. What to do with it? There
read more

Measuring Transactional Replication Latency Without Tracer Tokens

Measuring Transactional Replication Latency Without Tracer Tokens SQL Server 2005 introduced Tracer Tokens (SQL 2005 | SQL 2008), a new methodology for programmatically measuring replication latency in transactional replication. To measure latency with a tracer token, you simply insert a tracer token at the publisher. The replication process will trace the token as it moves through the steps of the process and report back how long it took for the token to reach the distributor and the subscriber. Sounds great,
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

SQL Server Error Logs, Part 1

The active SQL Server error log is one of the first places people look when there is a problem. For many people, this is a frustrating exercise. The interfaces for viewing the log in Enterprise Manager and Management Studio are slow and can not be filtered or searched. There is a solution to that problem. You make the error logs fast and searchable by using the undocumented extended stored procedure xp_readerrorlog.
read more