What’s in Enterprise only? (Online Index Operations)

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…

0

What’s in Enterprise only? (Partitioned Tables and Indexes)

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…

1

Clicking on the XML link containing a query plan doesn’t display the graphical plan but opens the XML editor instead

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…

1

What’s in Enterprise only? (Concurrent, Shared, or Merry-go-round Scans)

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…

1

How can you drop the witness of a mirroring session when the partners have been dismantled?

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…

1

The Read Ahead that doesn’t count as Read Ahead

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…

0

What’s in Enterprise only? (Log manager uses locked pages for log cache buffers)

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…

0

Programmatically enabling/disabling SQL Native Client Protocols

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,…

0