SQL Server 2012 Internal Note_SQL Server Architecture and Configuration

SQL Server 2012 Internal Note_SQL Server Architecture and Configuration

SQL Server Editions

Verify what edition you are running

SELECT SERVERPROPERTY('Edition');
SELECT SERVERPROPERTY('EngineEdition');
-- EngineEdition = 3, Enterprise, Enterprise Evaluation, or Developer
-- EngineEdition = 2, Standard, Web, or Business Intelligence, and fewer features are available
-- EngineEdition = 4, Express
-- EngineEdition = 5, SQL Azure

SQL Tools

1. For graphical system, use client tool as SQL Server Management Studio.
2. For non-graphical system, use commend line tools as SQLCMD in SQL PowerShell.

SQL Server Metadata

System base tables

System base tables store information about the SQL server, and are used for internal purposes within the Databased Engine.

View names of all system tables as an admin

USE master;
SELECT name FROM sys.objects
WHERE type_desc = 'SYSTEM_TABLE';

The only way to see the data in the system base table is to make connection using DAC (dedicated administrator connection).

In SQL Server 2012, three types of system metadata objects are intended for general use: Compatibility Views, Catalog Views, and Dynamic Management Objects.

Compatibility Views

Compatibility views provides the access to a subset of the SQL Server 2000 system tables and are accessible from any database, backward compatibility only (no metadata related to added features)

Catalog Views

SQL Server 2005 introduced a set of catalog views as a general interface to the persisted system metadata.

All catalog views (as well as the Dynamic Management Objects and compatibility views) are in the sys schema, which you must reference by name when you access the objects.

Reference a catalog view called objects

SELECT * FROM sys.objects;

Columns displayed for catalog views vs compatibility views

SELECT * FROM sys.databases;
SELECT * FROM sysdatabases; 
/* Interchangable
SELECT * FROM sys.sysdatabases;
SELECT * FROM sysdatabases;
SELECT * FROM dbo.sysdatabases;
*/

There are more columns for SELECT * FROM sys.databases, each possible database property has its own column in sys.databases.

image-20220601111344995

As a view, sys.databases can be queried and filtered:

-- Find databases are in the simple recovery model
SELECT name FROM sys.databases
WHERE recovery_model_desc = 'SIMPLE';

Inheritance model of catalog views

The base views contain a subset of columns and a superset of rows, and the derived views contain a superset of columns and a subset of rows.

SELECT * FROM sys.objects;
SELECT * FROM sys.tables;
SELECT * FROM sys.views;

sys.objects contains all the columns for attributes common to all types of objects and shows metadata for procedures and views in addition to that for tables.

sys.tables contains all the columns in sys.object and additional columns that are meaningful for tables, and shows only rows for tables.

image-20220601121558169

Note that metadata appearing only in the msdb database isn’t available through catalog views but is still available in system tables, in the schema dbo.

See definition of views

-- See the definition of sys.tables
-- Method 1
SELECT object_definition (object_id('sys.tables'));
-- Method 2
SELECT * FROM sys.system_sql_modules
WHERE object_id = object_id('sys.tables');

image-20220601122525275

Dynamic Management Objects

DMV, Dynamic Management Objects include both views and functions, provides internal behavior of SQL Server, some of them allow tracking of detailed resource history, they are all in sys schema and have a name that starts with dm_.

The DMVs aren’t based on real tables stored in database files but are based on internal server structures

Main categories of DMVs

Category	Contains	Example
dm_exec_*	Information about user execution code and connections	sys.dm_exec_sessions
dm_os_*	Low-level system information such as memory and scheduling	sys.dm_os_schedulers
dm_tran_*	Information about current transactions	sys.dm_tran_locks
dm_logpool*	Details about log pools used to manage SQL Server 2012’s log cache
dm_io_*	Keeps track of input/output activity on network and disks	sys.dm_io_virtual_file_stats
dm_db_*	Details about databases and database objects such as indexes	sys.dm_db_index_operational_stats

-- Return statistics for the log file in the AdventureWorks2019 database
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks2019'), 2);  
GO
-- Returns information for all tables and indexes within the instance of SQL Server
SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);    
GO

Other Metadata

Information Schema Views

Information schema views, introduced in SQL Server 7.0, were the original system table-independent view of the SQL Server metadata. The information schema views show only objects compatible with the SQL-92 standard.

System Functions

Property function

Property functions provide individual values for many SQL Server objects as well as for SQL Server databases and the SQL Server instance itself.

Return values are scalars, they can be used as values returned by SELECT statements and as values to populate columns in tables.

-- View the recovery model of a single database
SELECT DATABASEPROPERTYEX('msdb', 'Recovery');
-- View the recovery model of all databases
-- _desc are "friendly name" columns
SELECT name, recovery_model, recovery_model_desc
FROM sys.databases;

image-20220601155752867

Short-cuts for catalog view access

-- Find the database ID for the AdventrureWorks2019
-- 1
SELECT database_id
FROM sys.databases
WHERE name = 'AdventureWorks2019';
-- 2
SELECT DB_ID('AdventureWorks2019');

System Stored Procedures

System stored procedures are the original metadata access tool.

Most of the system stored procedures introduced in the very first version of SQL Server are still available.

With the system stored procedures, you have to accept the data that it returns.

Better to use catalog views:

-- See databases that the login sue owns
SELECT name FROM sys.databases
WHERE suser_sname(owner_sid) = 'sue';
-- See databases that are in a lower compatibility level
SELECT name FROM sys.databases
WHERE compatibility_level < 150;

Layers Of Metadata

“Other Metadata” refers to system information not contained in system tables.

Preferred interfaces to the system metadata are the catalog views and system functions.

image-20220601165008965

Components of the SQL Server engine

The major components of the SQL Server Database Engine.

Protocols

Translate client requests for relational engine, translate results for clients.

The SQL Server Network Interface (SNI) protocol layer on both the server and client computers encapsulates the TDS (tabular data stream) packet inside a standard communication protocol, such as TCP/IP or Named Pipes.

SQL Server can support multiple protocols; each client connects to SQL Server with a single protocol.

Available protocols:

• Shared Memory
  • The simplest protocol
  • Clients using this protocol can connect to only a SQL Server instance running on the same computer
  • Use this protocol for trouble-shooting when you suspect that the other protocols are configured incorrectly.
• Named Pipes
  • For local area network (LANs)
  • A portion of memory is used by one process (output) to pass information (input) to another process (local or remote computer)
• TCP/IP
  • Most widely used
  • Communicate across interconnected computer networks with diverse hardware architectures and operating systems
  • Includes standards for routing network traffic and offers advanced security features.

Query processor (Relational engine)

Determine exactly what your query needs to do and the best way to do it.

Parsing and binding components

Process queries into query trees, raise syntax or name resolution error and identify where the error occurs.

Parsing component:

1. Check proper syntax and spelling of keywords

Binding component:

1. Resolve object names into unique object ID

After parsing and binding, convert queries into query trees

The Query Optimizer

Takes the query tree, compile entire command batch and optimize optimizable queries, result in an execution plan.

Non-optimizable statements:

1. Compile into an internal form
2. Such as flow-of-control and Data Definition Language (DDL) commands

Optimizable statements:

1. Pass to Query Optimizer
2. Such as Data Manipulation Language (DML) statements SELECT, INSERT, UPDATE, DELETE, and MERGE

Steps:

1. Normalization
   1. breaks down a single query into multiple, fine-grained queries
2. Optimization
   1. Determine the most cost-effective execution plan based on internal metrics that include estimated memory requirements, CPU utilization, and number of required I/Os involving sampling data (distribution statistics).
   2. Avoid exhaustive optimization to ensure the time for optimizing is less than executing any plan.
3. Compilation
   1. Compile normalized tree into execution plan (a data structure)

The execution plan:

• A data structure
• Includes actual commands, steps for checking constraints
• Steps for calling a trigger are not compiled into the execution plan, the existence of a trigger can cause many more steps to be executed.

The query executor

Run the execution plan, dispatch all commands in the plan.

Most commands require interaction with the storage engine to modify or retrieve data and to manage transactions and locking. (Object Linking and Embedding (OLE) DB rowsets involved)

Storage engine

Include components responsible for accessing and managing data in the database.

Access methods

Contains components to open a table, retrieve qualified data, and update data.

Used for any data modification operations that need to modify index entries (SELECT, UPDATE, DELETE)

Row and index operations

Row operation:

• Manipulated on data.
• Retrieves, modifies, and performs operations on individual rows.
• Can handle large object type (LOB)—text, image, or ntext

Index operation:

• Manipulated on index pages
• Maintains and supports searches on B-trees
• An index is structured as a tree, with a root page and intermediate-level and lower level pages