BOOST YOUR SQL PERFORMANCE BY USING THE NEW

B

OOST YOUR

SQL P

ERFORMANCE BY USING THE NEW

T

HE

R

OAD TO

SQL S

ERVER

2014

SQL Server 2008 R2

• Multi-Server Admin • Data-Tier Applications • PowerPivot • Report Builder 2.0 • Master Data Services • Prepared Instances

2008

2010

2012

2014

SQL Server

2008

• Audit • Compression

• Change Data Capture • Data Collector • Resource Governor • Policy-Based Management • PowerShell provider • Spatial Data • Filestream Data

SQL Server

2012

• AlwaysOn HA • Columnstore Indexes • Contained databases • User-Defined Server Roles

• Data Quality Services • SSAS Tabular Mode • SSIS Catalog • SSRS Power View • SSRS Data Alerts • Deploy to SQL Azure

SQL Server 2014

• In-Memory OLTP • Updateable columnstore

• Buffer Pool Extensions • Enhanced AlwaysOn HA • New server-level

permissions

• Deploy to Azure VM • Power View from MD

SQL Azure

re

Win Srvr 2008

OS Win Srvr 2008 R2

Win Srvr 2012 Win Srvr 2012 R2

E

NHANCEMENTS TO

SQL S

ERVER

M

ANAGEMENT

S

TUDIO



Code Snippets



Quickly create Transact-SQL statements for common tasks



Create custom snippet files for your own code



Enhanced Debugging Functionality



Specify break conditions, filters, and actions



Monitor Transact-SQL expressions



Display pop-up information



Keyboard Shortcuts

T

RANSACT

-SQL E

NHANCEMENTS



The WITH RESULT SETS clause



The THROW Statement



The OFFSET and FETCH Keywords



Sequence Objects



The OVER Clause

T

HE

WITH RESULT SETS

CLAUSE



Override schema of stored procedure results with

explicit column names and data types

T

HE

THROW S

TATEMENT

•

Raise a custom error message:

•

Re-raise an error from a CATCH block:

BEGIN TRY

SELECT 100/0 AS 'Problem';

END TRY

BEGIN CATCH

PRINT 'Code inside CATCH is beginning'

PRINT 'Error: ' + CAST(ERROR_NUMBER()

AS VARCHAR(255));

THROW;

T

HE

OFFSET

AND

FETCH K

EYWORDS



Retrieve a page of rows as a subset of a query result

based on:



An OFFSET value indicating the first row to include

S

EQUENCE

O

BJECTS



Define a sequence of numeric values



Use NEXT VALUE FOR to allocate the next available

value in the sequence

T

HE

OVER C

LAUSE



OVER defines a window, or set, of rows to be used by a

window function, including any ordering



With a specified window partition clause, the OVER

clause restricts the set of rows to those with the same

values in the partitioning elements

I

NLINE

S

YNTAX FOR

I

NDEXES

•

Create indexes when creating tables

CREATE TABLE dbo.SalesOrders

(

SalesOrderID INTEGER PRIMARY KEY NONCLUSTERED,

OrderDate DATETIME NOT NULL,

CustomerID INTEGER NOT NULL,

ProductID INTEGER NOT NULL,

N

EW AND

E

NHANCED

T

RANSACT

-SQL F

UNCTIONS



Conversion Functions



Date and Time Functions



Logical Functions

C

ONVERSION

F

UNCTIONS

•

PARSE

•

TRY_PARSE

•

TRY_CONVERT

SELECT PARSE('£345.98' AS money USING 'en-GB')

-- Returns 345.98

SELECT TRY_PARSE('£345.98' AS money USING 'en-US')

-- Returns NULL because £ is not a valid US symbol

SELECT TRY_CONVERT(float, 1.2)

-- Returns 1.2

D

ATE AND

T

IME

F

UNCTIONS

Function Example Syntax

DATEFROMPARTS DATEFROMPARTS (2010, 12, 31) DATETIMEFROMPARTS DATETIMEFROMPARTS (2010, 12, 31, 23, 59, 59, 0) SMALLDATETIMEFROMPARTS SMALLDATETIMEFROMPARTS (2010, 12, 31, 23, 59) DATETIME2FROMPARTS DATETIME2FROMPARTS (2010, 12, 31, 23, 59, 59, 1, 7) TIMEFROMPARTS TIMEFROMPARTS (23, 59, 59, 1, 5) DATETIMEOFFSETFROMPARTS DATETIMEOFFSETFROMPARTS(2010,12,31,14,23,23,1,8,0,7) EOMONTH EOMONTH (GETDATE(), 1)

•

Construct date and time values from component parts)

L

OGICAL

F

UNCTIONS

•

CHOOSE

•

IIF

DECLARE @MyChoice = 3

SELECT CHOOSE (@MyChoice,'Cash', 'Credit Card', 'Debit Card')

-- Returns the 3rd _{option ('Debit Card')}

DECLARE @i = 3

SELECT IIF(@i % 2 = 0, 'Even', 'Odd')

S

TRING

F

UNCTIONS

•

CONCAT

•

FORMAT

SELECT CONCAT(FirstName, ' ', LastName) FROM Customers

-- Returns names in the format 'Firstname Lastname'

SELECT FORMAT(UnitPrice, 'C', 'en-GB') FROM Sales

S

TORING AND

Q

UERYING

D

OCUMENTS



FileTables



Full-Text Enhancements



Customizable Proximity



Statistical Semantic Search

F

ILE

T

ABLES

CREATE TABLE FileStore AS FileTable WITH (FileTable_Directory = 'Documents');

Name Full Path

SELECT [name],

FileTableRootPath() + file_stream.GetFileNamespacePath() [Full Path] FROM FileStore;

F

ULL

-T

EXT

E

NHANCEMENTS

•

Additional languages supported



Greek



Czech

•

Updated word breakers and stemmers

•

Property-scoped searching



Search for properties such as Author and Title

emitted by iFilters

C

USTOMIZABLE

P

ROXIMITY

•

Nearness of results often an indicator of relevance

•

SQL Server 2008 provided fixed determination of nearness



Fixed NEAR is now deprecated

•

SQL Server 2014 NEAR allows specification of



Maximum number of non-search terms that separate

matches



Requirement for ordering within matches

SELECT [name] As FileName FROM FileStore

I

N

-M

EMORY

D

ATABASE

C

APABILITIES



The Buffer Pool Extension



Columnstore Indexes

W

HAT

I

S THE

B

UFFER

P

OOL

E

XTENSION

?

Data files Buffer cache

(RAM) Buffer cache extension(SSD)

Pages

Clean Pages

•

Extends buffer cache to

non-volatile storage

•

Improves performance for

read-heavy OLTP workloads

•

SSD devices are often more

cost-effective than adding physical

memory

•

Simple configuration with no

changes to existing applications.

B

UFFER

P

OOL

E

XTENSION

S

CENARIOS

•

OLTP operations with a high volume of reads

•

Up to 32 GB of physical memory

•

Buffer Pool Extension is 4x to 10x physical memory

C

ONFIGURING THE

B

UFFER

P

OOL

E

XTENSION

•

Enable using ALTER SERVER CONFIGURATION

•

To Reconfigure, disable and then re-enable

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION ON

(FILENAME = 'E:\SSDCACHE\MYCACHE.BPE',

SIZE = 50 GB);

W

HAT

A

RE

C

OLUMNSTORE

I

NDEXES

?

•

In-memory, compressed data in pages based on columns instead

of rows

ProductID OrderDate Cost 310 20010701 2171.29 311 20010701 1912.15 312 20010702 2171.29 313 20010702 413.14 data page 1000

ProductID OrderDate Cost 314 20010701 333.42 ProductID 310 311 312 313 314 315 316 317 318 319 OrderDate 20010701 … 20010702 … … 20010703 … … … … Cost 2171.29 1912.15 2171.29 413.14 333.42 1295.00 4233.14 641.22 24.95

C

OLUMNSTORE

I

NDEX

S

CENARIOS

•

Columnstore indexes are most suitable for:



Databases with star or snowflake schemas



Tables with large numbers of rows



Tables that contain data that responds well to

C

LUSTERED AND

N

ON

-C

LUSTERED

C

OLUMNSTORE

I

NDEXES

•

Clustered Columnstore Indexes



SQL Server 2014 Enterprise, Developer, and Evaluation

Edition Only



Includes all columns in the table



Only index on the table



Updatable

•

Non-Clustered Columnstore Indexes



Includes some or all columns in the table

C

REATING A

C

OLUMNSTORE

I

NDEX

CREATE CLUSTERED COLUMNSTORE INDEX csidx_FactSalesOrderDetails ON FactSalesOrderDetails;

CREATE NONCLUSTERED COLUMNSTORE INDEX nccsidx_FactSalesOrder ON FactSalesOrder (CustomerKey, SalesPersonKey, ProductKey, OrderDateKey, OrderNo, ItemNo, Quantity, Cost,

W

HAT

A

RE

M

EMORY

-O

PTIMIZED

T

ABLES

?

•

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,

M

EMORY

-O

PTIMIZED

T

ABLE

S

CENARIOS

•

Optimistic concurrency optimizes latch-bound

workloads:

•

Multiple concurrent transactions modify large numbers

of rows

•

A table contains “hot” pages

•

Applications should handle conflict errors:

•

Write conflicts

C

REATING

M

EMORY

-O

PTIMIZED

T

ABLES

•

Add a filegroup for memory-optimized data

•

Create a memory-optimized table

ALTER DATABASE MyDB

ADD FILEGROUP mem_data CONTAINS MEMORY_OPTIMIZED_DATA; GO

ALTER DATABASE MyDB

ADD FILE (NAME = 'MemData' FILENAME = 'D:\Data\MyDB_MemData.ndf') TO FILEGROUP mem_data;

CREATE TABLE dbo.MemoryTable

(OrderId INTEGER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),

OrderDate DATETIME NOT NULL, ProductCode INTEGER NULL,

M

EMORY

-O

PTIMIZED

T

ABLE

I

NDEXES

•

Hash indexes

•

Assign rows to buckets based on hashing algorithm

•

Multiple rows in the same bucket form a linked list

•

Range indexes

•

Use a latch-free BW-Tree structure

CREATE TABLE dbo.IndexedMemoryTable

(OrderId INTEGER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),

OrderDate DATETIME NOT NULL, ProductCode INTEGER NULL,

Q

UERYING

M

EMORY

-O

PTIMIZED

T

ABLES

•

Query Interop

•

Interpreted

Transact-SQL

•

Enables queries that

combine

memory-optimized and

disk-based tables

•

Native Compilation

•

Stored procedure

converted to C and

compiled

Tab1 Tab2 Tab3 Tab4

Transact-SQL

SELECT t1.col1, t3.col2 FROM Tab1 t1 JOIN Tab2 t2 ON t1.Col1 = t2.col1; Query Interop Native Compilation CREATE PROCEDURE… #define __in HRESULT hkp_(… 0110101101 Translate to C Compile to DLL

C

REATING

N

ATIVE

S

TORED

P

ROCEDURES

•

CREATE PROCEDURE Statement

•

NATIVE_COMPILATION option

•

SCHEMABINDING option

•

EXECUTE AS option

•

BEGIN ATOMIC clause

CREATE PROCEDURE dbo.DeleteCustomer @CustomerID INT

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS

BEGIN ATOMIC WITH

P

LANNING

M

EMORY

-O

PTIMIZED

T

ABLES

•

Use the AMR tool to analyze existing workloads

•

Use the Transaction Performance Collection Sets

•

View Transaction Performance Analysis Overview

report from Data Collector MDW

•

Displays tables scattered by performance gain and

migration effort

High Gain Lo T1 T2 T3

N

EW AND

E

NHANCED

D

YNAMIC

M

ANAGEMENT

V

IEWS



Troubleshooting Queries



Demonstration: Using sys.dm_exec_query_stats



Viewing Disk and Operating System Information



Demonstration: Using sys.dm_os_volume_stats



Viewing SQL Server Configuration Information



Demonstration: Using sys.dm_server_services and

sys.dm_server_registry

T

ROUBLESHOOTING

Q

UERIES



sys.dm_exec_query_stats



Troubleshoot long running queries



Identify CPU and I/O intensive queries

In this demonstration, you will see how to:

V

IEWING

D

ISK AND

O

PERATING

S

YSTEM

I

NFORMATION



sys.dm_os_volume_stats



sys.dm_os_windows_info

In this demonstration, you will see how to:

V

IEWING

SQL S

ERVER

C

ONFIGURATION

I

NFORMATION



sys.dm_server_memory_dumps



Filename, Creation_time; Size_in_bytes



sys.dm_server_services



Service startup mode, Current state, Service Account



sys.dm_server_registry



Current version, Available services, Network configuration

T

HANK

Y

OU

Follow us on: