0

SQL Server 2012 Editions & Differences

Hi Friends, Today we are going to discuss SQL server 2012 editions and various features which these editions support or not. However, I tried to compile all major features here but there is a huge list so trying to cover only major points.

So let’s start

SQL Server 2012 Editions

Below is the list of SQL server 2012 editions.

Principal Editions of SQL Server:

  1. Enterprise
  2. Business Intelligence
  3. Standard

Specialized Editions of SQL Server 2012:

  1. Web

Breadth Editions of SQL Server 2012:

Breadth editions of SQL Server are engineered for specific customer scenarios and are offered FREE or at a very nominal cost. 

  1. Developer 
  2. Express
  3. Evaluation

Features Support

Let’s understand each feature and see what all SQL server 2012 editions support the feature.

Maximum Compute Capacity Used by a Single Instance

This is the computing power which Sql server instance is going to consume. As we know that these days servers are having multiple sockets. Each socket is filled with multi-core units. Each unit have hyper threading enabled. So there is too much processing power a single server can have. It is important to know that what is the maximum processing power, a single Sql server instance can use.

Database Engine:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

Limited to lesser of 4 Sockets or 16 cores

Limited to lesser of 4 Sockets or 16 cores

Limited to lesser of 4 Sockets or 16 cores

Operating system maximum

Limited to lesser of 1 Socket or 4 cores

Operating system maximum

 Analysis Services, Reporting Services:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

Operating system maximum

Limited to lesser of 4 Sockets or 16 cores

Limited to lesser of 4 Sockets or 16 cores

Operating system maximum

Limited to lesser of 1 Socket or 4 cores

Operating system maximum

Maximum Memory Utilized (per instance of SQL Server)

These day servers can too much memory by installing high capacity or multiple RAM. But when it comes to use this memory by Sql server then it have few limitations. Let’s see what these limitations are.

Database Engine:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

64 GB

64 GB

64 GB

Operating system maximum

1 GB

Operating system maximum

 Analysis Services:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

Operating system maximum

64 GB

NA

Operating system maximum

NA

Operating system maximum

 Reporting Services:

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Operating system maximum

Operating system maximum

64 GB

64 GB

Operating system maximum

4 GB

Operating system maximum

Maximum Relational Database Size

As name implies, this is the maximum database size. This take disk space.

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

524 PB

524 PB

524 PB

524 PB

524 PB

10 GB

524 PB

Log Shipping

SQL Server log shipping is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations.

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

NA

NA

NA

Database Mirroring

SQL Server database mirroring is a disaster recovery and high availability technique that involves two SQL Server instances on the same or different machines. One SQL Server instance acts as a primary instance called the principal, while the other is a mirrored instance called the mirror. In special cases, there can be a third SQL Server instance that acts as a witness. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes (Safety Full Only)

Yes (Safety Full Only)

Witness only

Yes

Witness only

Yes

 Safety Full Only:

Data is written and committed on the principal and mirror databases synchronously. Only after committing on both databases, the database application can continue with activity.

 Witness Only:

The witness is the one that brings the third vote into the mix in order to create a quorum. Principal and mirror each has one vote, so they can tie 1:1 and no decision can be made. With a witness there can be a quorum established 2:1 or 1:2 (or o:2 if the principal is unreachable) and therefore a decision can reached who should be the new principal.

AlwaysOn Failover Cluster Instances

As part of the SQL Server AlwaysOn offering, AlwaysOn Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level—a failover cluster instance (FCI). An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes (Node support: Operating system maximum)

Yes (Node support: 2)

Yes (Node support: 2)

NA

Yes (Node support: Operating system maximum)

NA

Yes (Node support: Operating system maximum)

Online Index

In ONLINE mode the new index is built while the old index is accessible to reads and writes. Any update on the old index will also get applied to the new index. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

NA

NA

NA

Yes

NA

Yes

Online Schema Change

Another feature that can increase uptime is the ability to perform online schema changes where you can change a table’s schema and that table can be continuously available. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

NA

NA

NA

Yes

NA

Yes

Table and Index Partitioning

The data in partitioned tables and indexes is horizontally divided into units that can be spread across more than one filegroup in a database. Partitioning can make large tables and indexes more manageable and scalable. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

NA

NA

NA

Yes

NA

Yes

Auditing

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

Yes

Yes

Yes

User Defined Roles

SQL Server 2012, you can create user defined server roles and add server level permissions to the user defined server roles. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Replication

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes (Subscriber only

Yes

Yes (Subscriber only

Yes

Various Tools

These are various tools available with Sql Server.

SQL Management Objects (SMO), SQL Configuration Manager, SQL CMD (Command Prompt tool): 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

Yes

Yes

Yes

SQL Profiler: 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

No

Yes

No

Yes

SQL Server Agent: 

Enterprise

Business Intelligence

Standard

Web

Developer

Express

Evaluation

Yes

Yes

Yes

Yes

Yes

No

Yes

These are few major features which I listed. I’ll keep on adding other features in this article.

0

SQL Query Execution & Components Involved

Hi Friends, Today we are going to discuss about SQL query execution steps and all components which participates from start to end while performing this execution.

SQL Query Execution

Below is a very high level flow of information and control.

SQL Query Execution

 

There are following components which participate:

  1. Parser
  2. Algebrizer
  3. Query optimizer
  4. Query engine
  5. Storage engine
  6. Buffer manager
  7. Lock manager
  8. Resource monitor
  9. SQLOS

Let’s discuss one by one about role of each of above listed components. However every component in itself is a bible but I am trying to give little detail of each.

Parser

Whenever we write and submit query for execution, then parser is the first component which starts the work. During the parsing stage SQL Server performs basic checks on the code i.e. looks for invalid SQL syntax, incorrect use of reserved words, column and table names, and so on.

If parsing detects any errors, the process stops and the errors are returned.

If parsing completes without errors, it generates a parse tree. Parser passes this tree onto the next stage of query processing i.e. Algebrizer. The parse tree is an internal representation of the query.

Algebrizer

It does following tasks:

Step 1: Name resolution:

The algebrizer resolves all the names of the various objects, tables and columns in the query. It confirms that all objects exist and are visible in the security context of the user. This is where it checks the table and column names to ensure that they exist and that the user has access to them.

This step is important because the query may have aliases or synonyms, names that don’t exist in the database, that need to be resolved. When objects don’t exist in the database, SQL Server returns an error from this step, defining the invalid object name.

Step 2: Type derivation:

The algebrizer identifies, at the individual column level, all the data types for the objects being accessed. Determines the final type for each node in the parse tree.

Step 3: Aggregate binding:

It also determines the location of aggregates (such as GROUP BY, and MAX) within the query. This process is aggregate binding.

The algebrizer outputs a binary tree i.e. query processor tree. It passes this tree to the query optimizer. The algebrizer’s output includes a hash, a coded value representing the query. The optimizer uses the hash to determine whether there is already a plan generated and stored in the plan cache. If there is a plan there, the process stops here and that plan is used. This reduces all the overhead required by the query optimizer to generate a new plan.

Query Optimizer

The SQL Server Query Optimizer is a cost-based optimizer. It analyzes various execution plans for a given query, estimates the cost of each of these plans and then selects the plan with the lowest cost. Query Optimizer cannot consider every possible plan for every query so it actually has to do a cost-based balancing act, considering both the cost of finding potential plans and the costs of plans themselves. Therefore, it is the SQL Server component that has the biggest impact on the performance of databases. 

Query Engine

The query engine (or query processor) takes the query plans which query optimizer finalized and, no surprise here, executes them. There are many functions which query engine performs, some of them being:

  • It acts as a dispatcher for all commands in the sql query execution plan.  It iterates through all the commands in the plan until the batch is complete.
  • And it interacts with the storage engine to retrieve and update data from tables and indexes.

Storage Engine

In a database engine, the Storage Manager (sometimes called a Storage Engine or Storage System) is responsible for all aspects of storage, transactional requirements, low-level concurrency, and indexing.  It usually contains something like the following modules:

  • The Buffer Manager/Buffer Pool
  • The Lock Manager (for non-metadata locks)
  • Low-level I/O Subsystem
  • The Transaction and Recovery Manager
  • Index Manager(s)
  • The Base Table Manager
  • Top-level APIs

Buffer Manager

The SQL Server Buffer manager monitors how SQL Server uses memory to store data pages. It monitors the physical Input/output as SQL Server reads and writes database pages. It also monitors the buffer cache by using fast non-volatile storage such as solid-state drives (SSD). Buffer manager pre-fetch data pages to improve sql query execution.

Lock Manager

Lock managers implement the storage engine’s notion of locking.  They are usually Table-level locks, Page-level locks (rare nowadays), or Row-level locks.

Resource Monitor

SQL Server Resource monitor was introduced in SQL Server 2008. This feature controls the consumption of the available resources, by limiting the amount of the CPU, Memory and IOPS for incoming sessions. It tries to prevent performance issues that are caused by resources high consumption.

The Resource monitor simply differentiates the incoming workload and allocates the needed CPU, Memory and IOPS resources based on the predefined limits for each workload. In this way, the SQL Server divides resources  among the current workloads reducing the possibility of consuming all resources by single workload type.

SQLOS

SQLOS performs the following critical functions for SQL Server:

Scheduler and IO completion:

The SQLOS is responsible for scheduling threads for CPU consumption. Most threads in SQL Server run in cooperative mode. It means that the thread is responsible for yielding so that other threads can obtain CPU time. Most IO is asynchronous.  The SQLOS is responsible for signaling threads when IO is completed.

Synchronization primitives:

SQL server is a multi-threaded application, so SQLOS is responsible for managing thread synchronizations.

Memory management:

Different components within SQL Server, example plan cache, CLR, lock manager etc request memory from the SQLOS. Therefore, the SQLOS can control how much memory a component within SQL Server is consuming.

Deadlock detection and management of the same.

Exception handling framework.

Hosting services for external components such as CLR and MDAC:

SQL Server will run threads that are associated with external component in preemptive mode. Preemptive mode allows the SQLOS to prevent runaway threads (threads which will not yield and allow other threads to get CPU execution). Also the SQLOS can keep track of the memory, these external components consume. For example, for CLR the SQLOS can invoke garbage collection if the CLR process is taking up too much memory.

As you can see, SQLOS seems to be a great resource management capability as it makes sure the SQL Server instance can be always up and running.

 

Above is very high level top view of each mentioned components involved in sql query execution. We’ll dive deep into these in upcoming articles.