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

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

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

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

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

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

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

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

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

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

Imagine, for example, that you run DBCC TRACEON(1117, -1) to globally enable trace flag 1117 on an instance of SQL Server 2005, where it had no effect because nowhere in the Database Engine’s code honored such flag. We could then say trace flag...

A colleague of mine exposed, a few days ago, a situation where his customer had some scripts they ran successfully against a SQL Server 2008 R2 instance they had in production. No errors there… happy and easy life. One day they decided to run those scripts...

Parallelizing the execution of DBCC CHECK* can give a significant performance boost to its execution and significantly reduce run times (especially if the objects stored in the database are spread across multiple files so the IOs can also be parallelized...

As you may have noticed, it has been written about Trace Flag 1117 in various blogs, and the general guidance they all provide is to enable it, particularly for SAP and Dynamics . However one might ask itself the question: In which circumstances, if any...