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 ...

I guess that many people using UPDATE … FROM on a daily basis do so without being aware that they are violating all SQL standards.   All versions of the ANSI SQL standard that I checked agree that an UPDATE statement has three clauses – the UPDATE clause, naming the table to be updated; the SET clause, specifying the columns to change and ...