SQL Server 2012 components The architecture of SQL Server database engine is as shown below: There are four major components in SQL Server database engine. The protocol layer, query processor, storage engine, and SQLOS. Every instruction must interact with the four components. The protocol layer receives the request and translates it into the form the relational engine can work. It takes the results from the queries, translates it in the form the client can understand. The query processor accepts T-SQL batches and checks for the functionality. Once the batch is executed, if the data is required, the request for the data is passed to the engine. The storage engine manages the data access. The SQLOS layer maintains the activities considered as the responsibility of the operating system. They can be synchronization, deadlock detection, memory management, thread management. Protocols The application used the protocols for the communication with the database engine. The tabular data stream packet is used. The SQL Server network interface ( SNI ) protocol layer encapsulates the TDS packet in a standard communication protocol. SQL Server supports multiple protocols simultaneously from various clients. The following protocols are available to the user. Shared Memory - The protocol does not need any configuration settings. Client using the shared memory protocol connects to the SQL Server instance on the same machine. Named Pipes - The protocol is developed for the local area network ( LAN ). A part of memory is used by one process for passing the information to another process. TCP/IP - It is the common protocol for the internet. It communicates across different interconnected computer networks. It consists of standards for routing network and security features. Query Processor The query processor is also known as the relational engine. It consists of the server components that check for the query requirements. It contains two components as query optimization and query execution. The processor manages the query execution as the data requests from the engine and the results are returned. Query optimizer The query optimizer accepts the query tree as input and prepares for the optimization. The Data Definition Language ( DDL ) statements are compiled in the internal form. The Data Manipulation Language ( DML ) statements are processed in one or more ways. The query optimizer produces a execution plan. There are some steps involved in normalizing the query. The first step is to normalize the query and convert a single query into multiple queries. The optimizer normalizes the query. It determines the plan for query execution. It is cost based. The optimizer selects a plan for query execution. It considers the type of statement requested by the user. The amount of data in tables affected, type of statement requested, indexes in every table, sampling of data values in the query are checked. The query optimizer considers the access methods for solving a query and selecting the most effective plan. Once the normalization and optimization is completed, the normalization tree is produced. The command specifies the table is affected, indexes used, criteria for the selection of data. The storage engine The storage engine consists of components needed for accessing and managing the data. The storage engine consists of access methods, locking and transaction services, and utility commands. Access methods The access methods codes are called when the server needs the data. It sets up and requests the data pages and index pages and creates the OLE DB rowset for returning the relational engine. The access methods contain code to open table, retrieve qualified data, and update data. The pages are not retrieved, the request to the buffer manager is made. The manager serves the page in the cache or reads from the disk. Transaction services The SQL server checks the atomicity of transactions. It means that either all transactions are completed or none. The transactions must be durable. It means that if the transaction is committed, it must be recoverable by the server. The ACID properties must be adhered by the transactions. In SQL Server, if there is a system failure before the transaction is committed, all the work is rolled back. Write ahead logging has the ability to roll back work in progress or roll forward committed work. It ensures that the record of transaction is added in a disk in the log before it is committed. The writes to the log are always synchronous. The writes to the data pages are asynchronous. The component co ordinates with the locking code considering the lock release, depending on the isolation level. The code versioning for removal of the older versions not in need is checked. The two concurrency models consisting of the ACID properties of transactions are: Pessimistic concurrency: It ensures the correctness and consistency by locking the data so that it is not modified. Optimistic concurrency: It provides a consistent data by placing the older versions of rows with committed values. They are placed in the temdb also known as version store. Locking operations Locking function is useful in a multi user database system as SQL Server. The server helps user manage multiple users simultaneously. It checks that the transactions follow the isolation level. SQL Server 2012 configuration Configuration manager is used for managing the services associated with the SQL Server, configuring the network protocols, managing the connectivity configuration for system connected to SQL Server. The manager can be accessed by clicking Start, All Programs, Microsoft SQL Server 2012, Configuration tools, SQL Server configuration manager. Network protocols A specific protocol must be enabled on client and server side for the client to connect and communicate with the server. It server listens to the requests on all the protocols. The TCP/IP protocol must be installed on the client and server. They are installed on the windows setup. On the client system, the SQL native client is installed and configured to use the network protocol enabled to the server. The client tools connectivity setup is used. The native client is a standalone data access application programming interface. It is used for both OLE DB and ODB connectivity. Managing services The configuration manager is used for start, pause, resume, or stop the server related services. The services availability depends on the SQL Server components. User must have SQL server service and SQL server agent service. It is preferred over windows service management tools. When the user uses SQL Server tool as configuration manager to modify the account used by the SQL server or agent service, the tool automatically adds additional configurations as settings permissions for the registry. Nonessential services Once the service is not required, they must be disabled by the user. In Windows server, click Start, right click computer, and select Manage. Expand the services and applications node in the computer management, click services. User can change the service startup by right clicking the name and selecting Properties. Unnecessary services add overhead to the system. They should not be marked as Startup. Firewall setting Firewall system helps prevent unauthorized access to the computer resources. If user needs to access the system resources, the firewall must be configured on the system SQL server is running. The following steps are used for firewall settings Configure the SQL Server instance to use the TCP/IP port. The default port of the server can be modified. Configure the firewall to allow access to the specific port for the authorized users Once the SQL server is configured for listening to a specific port and opening the port, the SQL server executable can be listed. SQL Server configuration settings SQL server automatically configures the system; it dynamically adjusts the important options. The default values can be accepted easily. The sp_configure system stored procedure is used for setting the configuration. The sys.configurations view helps user check the column is_advanced. The options which are advanced can be viewed. The Server properties sheet is used to set the configurations options. If the user uses the sp_configure stored procedure, no changes will be effected till the RECONFIGURE command executes. The RECONFIGURE WITH OVERRIDE command is useful when the option is outside the specified range. Memory Options Min Server memory and Max Server memory - The SQL server adjusts the total amount of the memory resources to be used. The Min and Max server options are used for the manual control. The default value for the min server memory is 0 MB and max server is 2147483647. The sp_configure stored procedure is used to change both the options to same value. User connections - SQL Server 2012 dynamically adjusts the number of simultaneous connections to the server if the setting is set to 0. The SQL server does not allocate the complete memory for each connection. The server allocates the pointers with many entries for user connections. Scheduling options The SQL server contains a special algorithm for scheduling user process through SQLOS. It manages the scheduler per logical processor and checks that only one process can run the scheduler at a time. The SQLOS manages the user connections and maintain the CPU balance. Lightweight Pooling The SQL server operates in thread mode. The server lets the user connections work in fiber mode. The fibers are less expensive to manage than threads. The lightweight pooling option has a value 0 which states that the server should execute in fiber mode. Max Worker Threads SQL Server uses the operating system thread services by keeping the pool of threads that takes request from the queue. The threads are divided evenly among the SQLOS schedulers such that the number of threads available for every scheduler is the Max Worker Threads setting divided by the number of CPU. They have the default value as 0. The number of threads are configured by the SQL Server. Affinity I/O Mask and Affinity64 I/O Mask The Affinity I/O Mask and Affinity64 I/O Mask options control the affinity of the processor for I/O operations. User can set value 0 for Affinity I/O Mask and Affinity Mask for CPU, 1 for Affinity I/O Mask option and 0 for Affinity I/O Mask and 1 for Affinity Mask. User must ensure that the bits don’t overlap. Filestream access level The database engine integrates with the NTFS file system for storing binary large object data as files on the file system. The user can access data using the Windows system cache for caching the file data for reducing the effect of filestream data on the server performance. The filestream object must be enabled using the SQL Server configuration manager. In the configuration manager, right click on the server, select Properties. Check to enable the filestream for T-SQL access. Once the filestream is enabled, set the configuration value. The following values can be added. 0: It disables the filestream support for an instance 1: It enables the filestream for T-SQL access 2: It enables the filestream for Win32 streaming access Querying processing options Min memory per query - The configuration option allows user to specify the minimum amount of memory granted by any of the operations before executed. The operations like sort, merge and hash receive memory dynamically. Query wait - It controls the wait for the query for the memory till it is available. If the value is -1, the query waits 25 times than the estimated execution time. A value 0 or more, specifies the number of seconds the query waits. Blocked Process threshold - The option allows the administrators to request notification when the user task has been blocked for more than the configured number of seconds. When the value is set to 0, n notification is provided. The value up to 86,400 seconds can be set. Index Create Memory - The Min Memory per Query option applies to the sorting and hashing used during the query execution. It is not applied during the index creation. Query Governor Cost Limit - It is used to specify the maximum number of seconds the query can run. If user specifies the non zero value, the server disallows the execution of any query that has estimated cost exceeding the value.