Sql Server 2014

Front 1

Buffer Pool Extension

Back 1

Extends buffer cache to non-volatile storage
Improves performance for read-heavy OLTP workloads
Simple configuration with no changes to existing applications.

Front 2

Memory-Optimized Tables

Back 2

Defined as C structs, compiled into DLLs, and loaded
into memory
Can be persisted as filestreams, or non-durable
Do not apply any locking semantics
Can be indexed using hash indexes
Can co-exist with disk-based tables
Can be queried using Transact-SQL
Cannot include some data types, including text, image, and nvarchar(max)
Do not support identity columns or foreign key constraints

Front 3

Create a memory-optimized table

Back 3

CREATE TABLE dbo.MemoryTable
(OrderId INTEGER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000),
OrderDate DATETIME NOT NULL,
ProductCode INTEGER NULL,
Quantity INTEGER NULL)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Front 4

Range Indexes
Latch free, in-memory B-Tree structure
Effective for range scans, equality predicates, and inequality predicates

Back 4

CREATE TABLE tab2
(col1 INT NOT NULL INDEX r_idx NONCLUSTERED)
WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_ONLY)

Front 5

Hash Indexes
Rows assigned to buckets based on hashed key
Multiple rows in the same bucket form a linked list
Effective for equality predicates
Query results are not sorted

Back 5

CREATE TABLE tab1
(col1 INT NOT NULL INDEX h_idx NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100))
WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_ONLY)

Front 6

Turn on a trace flag

Turn on a trace flag at a server level

Back 6

Turn on a trace flag

DBCC TRACEON (1204)

DBCC TRACEOFF (1204)

Turn on a trace flag at a server level

DBCC TRACEON (1204, -1)

-1 is for server level

Front 7

See all traces that apply to the connection run

Back 7

DBCC TRACESTATUS (-1)

Front 8

SQL Service Broker def

Back 8

SQL Service Broker is framework, which enables the creation of variety of objects that can send messages within DB.

Messages can be sent from DB to DB.

Front 9

SQL Service Broker messages can be processed and retrieved on

Back 9

Schedule via SQL Server agent

Demand by Windows application service

Automatically by setting an active stored procedure

Front 10

SQL Service Broker uses three commands

Back 10

CREATE CONVERSATION DIALGO cmd that creates the conversation on which messages are then sent

SEND cmd that is used to send messages from the queue for processing

RECEIVE cmd that is used to remove messages from the queue for processing

Front 11

DMV that returns a row for each application domain inthe server

Back 11

sys.dm_clr_appdomain

Front 12

DMV that returns a row for each manged user assembly loaded into the server address space

Back 12

sys.dm_clr_loaded_Assemblies

Front 13

DMV that return a row for each property related to SQL SErver CLR integration, including the version and the state of the hosted CLR

Back 13

sys.dm_clr_properties

Front 14

DMV that returns a row for all CLR tasks currently running

Back 14

sys.dm_clr_taks

Front 15

If there is not enough space for tempdb the user may receive the following errors

1101 or 1105

3959

3967

Back 15

1101 or 1105 - the session connecting to SQL Server must allocate space in tempdb

3959 - the version store is full

3967 - the version store must shrink because the tempdb is full

Front 16

To rebuild your system DB you can use the following cmd

Back 16

setup.exe /QUITE /ACTION=REBUILDDATABASE /INSTANCENAME=instance_name /SQLSYSADMINACCOUNT=accounts /SAPWD=sa password