Each edition of SQL Server supports a specified number of processor sockets multiplied by the number of logical CPUs in each socket. The Enterprise Edition of SQL Server supports up to 8 CPU sockets, while the Standard only supports up to 4. All this...

Notice that when I wrote this repro and captured the screenshots available below, I didn’t think they will be rendered in this blog. Unfortunately, you will have to click on each image to be able to read the descriptions of every relevant chunk of data...

A colleague who is a consultant in Chile, reported a weird scenario he was observing in one customer and for which he couldn’t articulate an explanation. I saw his question posted in one of our internal distribution lists but couldn’t put...

Fuzzy Lookup is a component which, when used as part of a data flow in an SSIS (SQL Server Integration Services) package, enables you to locate matching records in a reference table just like the standard Lookup transformation does, with the added value...

Starting with SQL Server 2008 the product supports the ability to start using or stop using CPUs that are dynamically added or removed to a running system, either physically, logically (by online hardware partitioning), or virtually through a virtualization...

If you connect from SQL Server Management Studio (SSMS) to an instance of SQL Server, whether it is named or default, and rather than using the proper machine name (or machine name followed by the instance name, separated by the backslash) you use either...

This is another issue that was kindly exposed by our colleague, John Huang , from Canada, through the MCM community distribution list. Once an activated stored procedure finishes its execution, Service Broker runtime code doesn’t verify whether the maximum...

John Huang , a colleague from the MCM community , recently exposed to the rest of us a weird case which he decided to present as an “Interesting blocking issue”. I don’t know the exact details of what he was trying to implement, but basically, he had...

The INSTEAD OF INSERT Triggers topic in the documentation describes the following: An INSERT statement that is referencing a view that has an INSTEAD OF INSERT trigger must supply values for every view column that does not allow nulls . This includes...

As with any index, SQL Server can decide to use an indexed view (materialized views) in its query plan only if the query optimizer determines it is beneficial to do so . Indexed views can be created in any edition of SQL Server. But only in the Enterprise...

Together with the definition of each linked server goes a series of options that control different behavioral aspects of the way the linking server interacts with the linked one. Among those options there us one called “lazy schema validation”...

SQL Server can use read-only distributed partitioned views as a scale out mechanism , but any attempt to update any of these heterogeneous objects will fail with error 4451 (Views referencing tables on multiple servers are not updatable in the edition...

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