次の方法で共有

Why Should I use SQL Enterprise Edition?

  • [アーティクル]

A lot of customers have been asking me if they need to use Enterprise Edition. the basic answer is that EE is sold mainly on the enhanced features which can be compared at:

https://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

You will need to look at EE for sure if you require:

  • Three Node cluster+
  • More than four processors
  • Synchronous Mirroring on Multiple Threads or any Asynchronous Mirroring.
  • Numerous Increased availability features.
  • Web Farm for Reporting Services
  • Partitioning, table or cubes.
  • Advanced BI features
  • etc

But what if you do not require any of the advanced features because you are using a shrink wrapped database for use with a product like MOSS, or just the DbEngine for a relation engine. Does Enterprise offer any performance benefits? the answer is YES.

There are some key enterprise edition only performance benefits across RAM, Parallelism, Query Plans and DISK I/O that will lead to better performance on high end systems, which I will try to list here.

1) Lock Pages in Memory

Lock Pages In Memory" allows SQL Server 2005 to manage its own memory (as opposed to having the operating system do it). It is extremely important to give this right to the SQL Server Service account, especially on 64-bit SQL Server 2005 systems with lots of RAM. It also is required on 32-bit systems to enable AWE.

See https://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx

2) Advanced Scanning (aka Merry-go-round scan)

In SQL Server Enterprise Edition, the advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. The Database Engine reads each page one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached.

See https://msdn2.microsoft.com/en-us/library/ms191475.aspx and  Merry-Go-Round Culprits for performance variances

3) Larger Read Ahead Buffering on Storage Devices

Determining I/O section mentions that EE does up to 1024k read ahead buffering on a Storage Area Network (std only does 64k). This indicates that EE is more suitable to SAN’s which need more buffering due to increased latency.

https://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx

4) Large Page Extensions

SQL Enterprise Edition retrieves pages up to eight at a go.

https://msdn2.microsoft.com/en-us/library/aa337525.aspx

5) Parallel index Operations

This is particularly useful in data warehouses where indexes may be frequently dropped and re-created.

https://msdn2.microsoft.com/en-us/library/ms189329.aspx

Comments

  • Anonymous
    January 30, 2008
    I love this article.  You have provided a very comprehensive look at the question of why I should use Enterprise.Thanks,
  • Anonymous
    March 30, 2008
    Good article!But... you do NOT need to upgrade to enterprise edition if your standard edition is suffering from the OS paging out all of Sql Server's working set: that is a long-standing bug in the OS memory management which has recenlty and silently been fixed by MS.Our 64 bit 16GB  standard editions (Dell/Broadcom) were suffering this phenomenen for over a year. We've had a long running support case with Microsoft, who didn't manage to solve the problem. We have been in contact with other parties suffering the same.With one of Microsoft's recent updates the problem suddenly went away, silently...Also, as far as I know, the EE's "Lock Pages in Memory" feature will only lock certain types of Sql Server memory, not the whole working set.
  • Anonymous
    March 30, 2008
    The comment has been removed