In the next series of posts, I'll focus on SQL Server I/O, revisiting some common issues and taking a closer look at some others. In each post and as always, I'll make the case with specific data points from my tests. For the first two posts in this series, let me check out the read-ahead technique used by SQL server. Read-ahead is an important ...

10 logical connections is a hard limit (see http://blogs.msdn.com/angelsb/archive/2005/01/13/352718.aspx and http://blogs.msdn.com/angelsb/archive/2004/09/07/226597.aspx for more details). If you go over this limit, it gets expensive from a performance standpoint. Also see ...

As a DBA I have always used stored procedures to access database data for the myriad of reasons that we all are aware of. My current project has made the decision to forgo stored procedures and access data directly from the underlying tables by means of JPA. I understand the concept of persistence, but I have seen any analysis that addresses my ...

I'm very excited about the new Management Data Warehouse coming in SQL Server 2008 (which was called Performance Studio at one point), but keep in mind there are lots of available tools in the current versions. One of the students in my class last week put together this list of his favorites. Note that some of the tools can be used together. For ...

My previous post shows that data compression may not come for free, although hopefully by the RTM time the adverse performance impact on inserts will have been reduced significantly. In this post, I want to show you that data compression can enhance read performance. Intuitively, data compression may reduce the number of pages SQL Server ...

You might encounter a situation, under a very heavy workload, where processing for certain transactions starts out very efficiently, but over time becomes much less efficient.    A good place to start troubleshooting is, of course, PerfMon.  But PerfMon can fail to reveal the culprit, since the CPU can remain stable and moderate ...

Before I begin, let me be very clear that the results I report in this post were obtained with SQL Server 2008 CTP6. Microsoft is still working on improving the performance of data compression. And in particular, the performance of inserting into a compressed table is expected to be significantly enhanced in SQL Server 2008 RTM. In my previous ...

SQL Server 2008 has introduced a long sought after feature -- Data Compression. This is a great feature, and I have no doubt it'll be widely used. The key compression method is called page compression, which uses the following three techniques to reduce the space taken up by duplicates on a page: Row compression. This technique changes the ...

For some reason, the title of this blog post makes me grin because it evokes a mental image of one guy trying to comically strangle another guy.  But I digress…   Speaking at a user group meeting recently, an attendee asked whether the mirroring process had any capability to speed up or slow down its workload according to the amount of ...

If you’ve ever taken the time to read http://www.tpc.org/results/FDR/TPCC/hp_orca1tb_win64_fdr.pdf, you might find a bit in there that’s confusing.   ''Microsoft SQL Server 2005 Enterprise Edition 64-bit Service Pack 1 was configured to utilize ''soft NUMA'', a feature that allows network connections to be affined to specific groups of CPUs ...