What’s in Enterprise only? (honor Lazy Schema Validation option in a Linked Server)

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” (aka deferred schema check or delayed schema check) whose default value is false. When it is…


What’s in Enterprise only? (Updatable Distributed Partitioned Views)

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 of this SQL Server instance ‘%.*ls’. See books online for more details on feature support…


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…


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

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

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…


What’s in Enterprise only? (Running DBCC CHECK* in parallel)

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.) Under certain circumstances, DBCC CHECKDB, CHECKFILEGROUP, or CHECKTABLE can run on several threads in parallel,…


What’s in Enterprise only? (Fast Recovery)

During a crash recovery or a database mirroring failover, SQL Server Enterprise Edition (or a Developer Edition or an Evaluation Edition, both of which behave exactly like the Enterprise does. The only difference among them three is their licensing.) let users access the database before the full recovery process finishes (i.e. after the redo phase…


What’s in Enterprise only? (up to 2TB of memory)

Beginning with SQL Server 2008 R2, there’s a significant difference in the amount of memory that SQL Server can allocate from the OS to its buffer cache (Buffer Pool). An Standard Edition will not grow it further than 64GB, no matter how much physical memory is available to the system on which the instance of…

4

What’s in Enterprise only? (Backup Mirroring)

Increasing the reliability of your backups by providing redundancy is something you can achieve with SQL Server’s mirrored backups, which is an out-of-the-box feature available only in the Enterprise Edition (or a Developer Edition or an Evaluation Edition, both of which behave exactly like the Enterprise does. The only difference among them three is their…