The optimizer is the part of SQL Server that takes your query and reorders and rearranges your query to find the optimal execution plan. In theory.   In practice, that doesn’t always work out well. Often, the optimizer manages to come up with brilliant ways to execute a complex query very efficiently – but sometimes, it misses an ...

My previous post showed a simple test that appears to suggest that you may experience significant performance degradation if multiple users are calling the same SQLCLR function at the same time and they are all catching a lot of exceptions.   However, it’s not clear whether that behavior is limited to SQLCLR or applies to .NET in general. ...

If you have many try/catch blocks in your .NET code and your code execution actually passes through them, you should expect a performance hit. That’s intuitive and no surprise.   What is surprising is the extent to which you may experience severe performance degradation when multiple users are executing a piece of SQLCLR code (e.g. calling ...

Lock escalation is a funny thing. I've found myself on numerous occasions waging war against its concurrency-sapping existence, and rarely have I found myself wishing that it would work more aggressively. But there is a time and place for everything, and yesterday I discovered that a major change has occurred with regard to lock escalation in SQL ...

Let's start with some background on forwarding pointers: Forwarding pointers in heaps can be a mess to get rid of. A forwarding pointer happens when you modify a row so that the row doesn't fit on the same page anymore. SQL Server moves the row to a new page and leaves a forwarding pointer where the old row used to be. This means that ...

It would be fair to say that there aren't a huge number of programmability features added to SQL Server 2008 that will have a great impact on most of our day-to-day lives as SQL Server developers. The release was clearly manageability themed rather than programmability themed, and I can count the most interesting and far-reaching developer ...

All the previously posted results (May 25th and May 29th) on this exercise were obtained with query parallelism disabled (i.e. the sp_configure ‘max degree of parallelism’ option was set to 1).   Since the following test query is sensitive to query parallelism, we need to see what impact query parallelism may have.   DBCC ...

SQL Server can run in one of two modes: thread mode or fiber mode. By default, SQL Server runs in thread mode in which a SQL Server worker is associated with a Windows thread throughout all phases of its execution. This can be changed with the sp_configure option ‘Lightweight Pooling’. When Lightweight Pooling is turned on, SQL Server runs in ...

In my previous post on the performance impact of having a large number of virtual log files (VLFs) in a transaction log, I showed that a large number of VLFs could be very bad for SQL Server 2008 performance. The test workloads were large batch delete, update, and insert. In other words, they were single monolithic transactions that ...

It is generally known that having a large number of virtual log files (VLFs) in a database transaction log is undesirable. A blog post by the Microsoft support team in Stockholm showed that a large number of virtual log files could seriously lengthen the database recovery time. Tony Rogerson also reported that lots of virtual log files were bad ...