0

Database Core Components & Basic Introduction

Hi Friends, Today we are going to discuss about Database Core Components\Tools of Sql server and purpose for those. It’s a very basic and introductory information which I am going to provide here. But we’ll discuss each component in details in upcoming sessions.

So let’s start.

Database Engine

The Database Engine component of SQL Server is the core service for storing, processing, and securing data. This is the component which actually execute queries and process the data. This component secure data by storing it in various data files.

By installing database engine, you can create new databases on that instance and store your data. We cal this as relational engine also. Sql is a descriptive language so it passes the query to the engine and then engine is responsible to handle all relational work.

Whenever we connect to any database using tools like management studio for queries, we actually connect to database engine. 

SQL Server Agent

This is the component which use to take care of all automated tasks and jobs. It maintains list of all Sql jobs, their tasks and alerts. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand.

Sql server agent is a window service which runs in background. We can configure this service to start on windows startup.

SQL Server Agent Components:

  • Jobs:job is a specified series of actions that SQL Server Agent performs. 
  • Schedules:schedule specifies when a job runs.
  • Alerts: An alert is an automatic response to a specific event. 
  • Operators: An operator defines contact information for an individual responsible for the maintenance of one or more instances of SQL Server. 

SQL Server can notify operators of alerts through one or more of the following:

  • E-mail
  • Pager (through e-mail)
  • net send 

Database Mail

Database mail component is specifically for sending mails. There could be need of sending mails as alerts in case of any scheduled task failure. So one can configure SMTP and database mails, so that it can send automated alerts.

Not only automated alerts but one can send mails through T-Sql code also. The mail can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.

MSDTC

MSDTC (aka Microsoft Distributed Transaction Control) is a software or we can say it’s a windows service which is used by SQL Server and other applications when these want to make a distributed transaction between more than one machine\servers. This uses dual phase commit protocol.

When one server starts a transaction and this transaction include few statements which needs to be executed on another server and we want to secure this complete batch by one transaction, then MSDTC comes into picture. It tracks all success\failure of all statements and commits the entire batch if all statements are succeeded.

Business Intelligence

Business intelligence (BI) is a package or set of tools that enable the management of data for the purpose of analysis, reporting, mining, and visualization.

SQL Server includes three services designed for BI: Integration Services (IS, sometimes called SSIS for SQL Server Integration Services), Reporting Services (RS), and Analysis Services (AS).

SSIS

This is integration service which one can use for integration of data from various sources. It has a visual interface which we can use to design packages.

We can say SSIS as ETL i.e. Extract-Transform-Load tool. We can design package where it can fetch data from other source, file etc. and then perform the transformation according to requirement and then load to another system\file.

As it has visual interface so it becomes very easy to perform all these transformations\load rather than T-Sql. 

SSRS

Reporting Services (RS) for SQL Server 2012 is a full-featured, web-based, managed reporting solution. RS reports can be exported to PDF, Excel, or other formats with a single click and are easy to build and customize.

One can schedule these to pre-created and cached for users, e-mailed to users, or generated by user on-the-fly with parameters

There is new functionality in SSRS 2012 with the addition of Power View. This is a SharePoint integrated feature that provides for rich drag and drop visualization and data exploration. It is one of the hottest new features in SQL 2012.

SSAS

The Analysis Services service hosts two key components of the BI tool set: Online Analytical Processing (OLAP) hosts multidimensional databases where data is stored in cubes, whereas Data Mining provides methods to analyze datasets for nonobvious patterns in the data.

Building cubes in a multidimensional database provides a fast, pre-interpreted, flexible analysis environment. One can include robust calculations in a cube for later query and reporting. We can use these results to reporting or pivot table later.

One can use data mining to explore non trending patterns and specific patterns based on various algorithms. This can be very useful to perform data analysis so that business can get information about specific data patterns and make business strategies according to that analysis.

 

In addition to above components, there are few tools which are also very popular and very much used to worth discussing about.

Management Studio

This is a tool which gives you visual interface for servers, databases, tables, schema etc. One can connect to any database engine and query.

This is very useful tool which is used by almost all those who involves in database work either developer or DBA.

 

Configuration Manager

We can use this tool to start and stop any server, set the start-up options, and configure the connectivity. One can launch it from the Start menu or from Management Studio. It can show you all the services and servers running on a particular server.

It looks something like below image.

Profiler

Sql profiler is a visual tool which can track almost all events on any server. You can check queries, execution reports etc. One can use it for debugging\troubleshooting and performance purpose.

Profiler is being deprecated in favor of extended events.

Performance Monitor

When we install Sql server, it installs many performance counters. We can see these counters in Windows’s administrative tools. One can use these counters to check Sql server performance in terms of memory usage, I\O etc.

 

DB Engine Tuning Wizard

The Database Engine Tuning Adviser analyzes a batch of queries (from Profiler) and recommends index and partition modifications for performance. Database Engine Tuning Adviser examines how queries are processed in the databases you specify, and then recommends how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning.

 

Command Line Utilities

Sql provides few command line utilities to perform specific tasks through command. Following are few examples.

SqlCmd:

One can run this command and provide query to execute on database. Database connection will be provided as parameters in this utility.

sqlcmd -d AdventureWorks2012 -q "SELECT TOP 5 FirstName FROM Person.Person;SELECT TOP 5 LastName FROM Person.Person;"

BCP:

The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. One can use BCP utility to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. 

Below is the sample to export query data into file.

bcp "SELECT FullName, PreferredName FROM WideWorldImporters.Application.People ORDER BY FullName" queryout D:\BCP\People.txt -t, -c –T

Above is very basic information about major tools and components. In upcoming sessions we’ll discuss more details about each of these.

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.