Year: 2011

Starting with SQL Server 2005 you can create, rebuild, or drop indexes while concurrent users read or modify the underlying table or clustered index data and any associated nonclustered indexes during these index operations. This mode of performing index operations is known as online. To be able to use online index operations and therefore provide…

Starting with version 2005, SQL Server introduces the concept of partitioned tables and indexes. Partitioning can make large tables and indexes more manageable and scalable. By using partitioning, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes in earlier…

This is something many users point out as a defect, but which is not. It is the fact that despite the version of SQL Server you are using, the column produced by SET SHOWPLAN_XML or SET STATISTICS XML containing the query plan, is named “Microsoft SQL Server 2005 XML Showplan”. Well, one can argue that…

There are two reasons why Activity Monitor would show duplicate rows in its Processes area, just like in the following screen capture: The first and most commonly known is if that session is executing a parallel query plan, where one or more iterators are being run by multiple worker threads. In that case, there…

Recent Note: SQL Server 2008 R2 Service Pack 1 Cumulative Update 4 (10.50.2796.0) is the first build servicing showplanxml.xsd with the updated version. So, install that CU to get rid of this problem. Last week, while working with a customer in the optimization of some of his queries, he raised to my attention that his…

When a new scan is started on any given B-Tree, SQL Server checks whether the new scan can consume items in any order (non-deterministic) and whether another scan is already active for the same object. If that’s the case, the scans are linked together and the new scan piggy backs at the current position of…

A colleague of mine described the following situation: Long time ago they setup a database mirroring environment with 3 servers (2 partners + a witness). Then they changed their mind and decided that clustering fit better their HADR necessities. So they completely rebuilt the 2 partners (reinstalled operating system, etc), while forgetting to properly remove…

As you may know from having read the documentation, whitepapers, or personal synthetic experiments, SQL Server’s Storage Engine supports a performance optimization mechanism named read-ahead (RA for short). Its aim is anticipating the data and index pages needed to fulfill a query execution plan and bringing those pages into the buffer pool (cache) before they…

Beginning with SQL Server 2008 R2, log manager uses locked pages whenever possible for log cache buffers. This improves performance because the kernel of the OS doesn’t need to lock these buffers during IO. The improvement effects may vary depending on many factors, but this change alone showed improvements around 1% in the results of…

SQL Server Configuration Manager uses the WMI provider for Configuration Management. More specifically, it uses the SetEnable/SetDisable methods of the ClientNetworkProtocol class. The following PowerShell script will leverage that class to enumerate the client protocols and whether they are enabled or not (ProtocolOrder == 0 means disabled): Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement -class ClientNetworkProtocol | Select-Object ProtocolName,…