SQL Server 2014 - Part 2

When receiving messages through the Service Broker, it's common practice to fetch the next from a , then parse the to determine what action to take.

When receiving messages through the Service Broker, it's more efficient to select messages from the queue into a variable, containing the message in format - before stepping through the table variable rows converting the message body into it's native format at that stage, ready for actioning.

When using the Service Broker, you receive messages by using the command. This command is written much like a traditional statement. You can specify the that should be returned, and the is specified using a statement.

When receiving messages from the Service Broker, it is more efficient to receive multiple at once, and to receive the message from the queue as the raw , and then convert it to (or whatever data type it was sent as) after it has been removed from the queue.

The steps involved to enable the Service Broker to send messages between instances are...

1. Configure the database key in the database.
2. Configure the database key in the application database.
3. Create a in each database.
4. Exchange the between the databases.
5. Create SQL Service Broker on each instance.
6. Configure to connect to the remote instance SQL Service Broker endpoint.

When encrypting communications between servers using Service Broker, you create the database master key (if it's not already been run) using the following command....

CREATE ENCRYPTION BY = 'YourSecurePassword1!'

If a database master key has been created, it is imperative that it's backed up to a file and stored offsite. In order to do this you would use the following command...

BACKUP TO = '<filename>' BY = '<secure-password>'

When using certificate authentication between endpoints, you must create in the databases of the instances that exchange messages. You can create using the statement within the database. When creating the certificates on each instance, they must have a . The recommended way to do this is to include the server and instance name.

Within the Service Broker, for SQL Server instances to send messages to another instance, you must create on each SQL Server instance.

In order to create an endpoint called 'ServiceBrokerEndpoint' - that listens on port 1234 on all IPv4 and IPv6 addresses hosted by the Server, using certificate 'MyServiceBrokerCertificate', requiring strong encryption - you would use the following command...

CREATE ENDPOINT
STATE = STARTED
AS TCP ( = 1234, LISTENER_=)
FOR SERVICE_BROKER
(AUTHENTICATION = CERTIFICATE ,
= ALGORITHM );

T-SQL is a data access and management language, whereas managed code is better suited for complex application logic.

One of the great benefits of SQLCLR is that any .NET code that SQL Server runs is completely isolated from SQL Server itself. .NET code runs within the SQL Server process space, but SQL Server uses a construct in .NET called the application to completely isolate and separate all resources that the .NET code uses from the resources that SQL Server uses.

The primary design goal of placing assemblies in application is to achieve scalability and security. Also, application domains have existed for quite a while to provide a form of between applications. This is necessary to ensure that code running in one application cannot (and does not) affect other unrelated applications.

SQL Server isolates code between databases by using application . As such, application exist for each that allows you to create, load, and register an assembly. This ensures that code can be executed independently of other assemblies registered in other , in isolation.

By default, CLR is disabled for users. This means that you cannot execute any .NET code until you purposefully enable CLR. Not everyone can enable CLR; only members of the and server roles can do so. In order to do this, they must issue the following commands...

EXEC sp_ '', 1
GO

GO

In order to reference a CLR written method, 'RunMe', that returns a string message, within the class 'SQLHelper' - built into the pre-registered assembly 'MySQLFunctions', you would use the following statement...

CREATE clrRunMe
@retVal nchar(50)
AS
NAME

In order to provide strict security - CLR supports a security model called (CAS) for managed code. In this model, permissions are given to assemblies based on the identity of the code. The set of permissions that can be granted to the assemblies by the SQL Server host policy level are determined by the permission that is set and specified during the creation of the assembly in SQL Server. SQLCLR supports three permission sets:

—Only local data access and internal computations are allowed. If no permission is specified during the assembly creation, this permission is applied by default. No access to external system resources such as files or the registry exists.

—This is the same permission as the above, but with the added capability to access external resources such as the filesystem, registry, networks, and environment variables.

—This means unrestricted access to all resources within SQL Server and outside SQL Server. This is the least secure and should rarely be used.

The following counters are extremely helpful in understanding the health and activity of .NET CLR programs running in a SQL-hosted environment:

.NET CLR —This provides detailed information about the types of CLR heap memory and garbage collection. These counters can be used to monitor CLR memory usage, and to flag alerts if the memory used gets too large. If the code is copying a lot of data into memory, you may have to check the code and take a different approach to reduce memory consumption, or add more memory.

.NET CLR —SQL Server isolates code between databases by using AppDomain. This set of counters enables monitoring of the number of AppDomains and the number of assemblies loaded in the system. You can use this counter to determine loaded CLR assemblies.

.NET CLR —This counter provides you with a good idea of how many errors the code generates. The values vary from application to application because sometimes developers use exceptions to test application functionality, so you should monitor over time to set the baseline and go from there. As this number increases, performance decreases.

There are two key Extended Events for monitoring CLR objects in SQL Server. The event occurs when a request to access an assembly occurs, and is used to monitor queries running CLR code. The clr__failure event occurs when managed code experiences a memory failure.

Dynamic Management Views (DMVs) return server state information that you can use to monitor the health of a server pertaining to SQLCLR:

sys.dm_clr_—Returns a row for each application domain in the server.

sys.dm_clr__—Returns a row for each managed user assembly loaded into the server address space.

sys.dm_clr_—Returns a row for each property related to SQL Server CLR integration, including the version and the state of the hosted CLR.

sys.dm_clr_—Returns a row for all CLR tasks currently running.

To provide more insight into the operation and execution of CLR assemblies, you can use the following DMVs to give you that information for CLR assemblies:

sys.dm_exec__—You can use this to view a cached query plan for a CLR query.

sys.dm_exec__—This contains a row per query statement within the cached plan.

Best practice states that a CLR assembly should not be used to data. This is what is meant to do. They should not spend their time accessing data. Send the data you want worked on to the assembly, instead of having the assembly pull it from SQL Server.

Securing data consists of two essential parts: (which is proving you are who you say you are) and (which defines the data you have access to, and what you can do to the data).

You have two ways to authenticate to the Microsoft SQL Server instance: via authentication and via authentication. When you install SQL Server, you have an option to select whether the SQL Server instance should support authentication only, or whether it should support both and authentication.

With SQL Server authentication, the actual username and password are stored in the database within the database instance.

When you use SQL Server authentication, the account and password are passed to the database instance, which then the password and compares the username and password against the list of SQL accounts stored within the master database.

You can configure SQL Server logins to follow the Windows password to enforce password and password .

A best practice is to authenticate to SQL Server with a Windows . That way, the SQL Server administrator only has to configure a small number of one time, and then the Windows security administrators can control access by assigning users to the Windows configured as in SQL Server.

Because of the differences in SQL Server and Windows authentication, authentication is considered to be much less secure, and should be disabled whenever possible.

A SQL Server is an identity that is configured in the SQL Server instance. The is used to authenticate a client to SQL Server. When an administrator creates a , it is associated with credentials—a SQL Server or Windows

Because the database is associated with a stored in the database of the instances, if a database is moved to a different SQL Server instance, the associated with the database must be created in the new instance to allow users to access the database.

SQL Server 2012 introduced a new concept called the user. These users exist only within a single database and are not associated with an instance-level .

A is simply a Windows account that doesn't have a corresponding login at the server level.

Proper - security within the database is key to keeping data within the SQL Server instance safe from intruders

The following three statements are used when changing permissions in SQL Server:

is used to assign rights.
is used to prevent access.
is used to remove either of the above

When granting permissions in SQL Server, you must remember that always overwrites a .

If we needed to assign rights for selecting and inserting data into the Users table to a user called MyUser - and gave them the ability to the same access to others... you would use the syntax...

, ON dbo.Users TO WITH

The biggest difference between instance-wide privileges and database-wide privileges is that instance-wide privileges are granted directly to the , whereas database-wide privileges are granted to , and these are mapped to

In-memory is an enterprise-level feature that brings to businesses and users of all sizes performance that was previously only attainable to organizations that could afford to spend several hundreds of thousands of dollars on hardware. It is available in SQL Server 2014 Developer, Evaluation, and Enterprise editions only. To perform the exercises that follow, you must be running one of those editions. In-memory is also referred to as tables.

When using In Memory , No changes are permitted once the memory optimized table has been created. No ALTER statements are allowed, all must be created inline as there is no support for , , or INDEX statements.

A memory-optimized table can have between and indexes, and, except for the index, they cannot be .

When using In Memory OLTP, if you wanted to search on a range of values within a memory optimised table - e.g. such as orders that occurred after a different date, then a hash index is not the type of index you should use. You should use a index.

The process of monitoring a SQL Server installation, identifying bottlenecks, implementing corrective action and monitoring the affects is called the .

When making changes to a SQL Server configuration or schema, it's important to understand the following concerns..

Will there be a significant increase to the base?
Will there be a significant increase the on the server?
Will there be a significant increase in data ?

describes the way the operating system behaves regarding power consumption. Each Windows operating system comes with three default options...

—This setting matches capacity to demand.

—This setting increases the performance of the CPU / hardware at the expense of energy consumption.

—This setting limits performance to save energy and reduce operating costs.

Leaving the Windows Server power configuration option to can cause an issue called . This is when, instead of being used, certain from your CPU are not being used.

In SQL Server, you can skip the of data files, database backups, and log backups to drastically increase the performance of these operations.

By adding the SQL Server to the 'Perform volume maintenance tasks' group in the Windows Local , you prevent initialising files occurring when they are created or extended (database files, backup files, log files etc) - and drastically increase the performance of those tasks.

Planning, sizing, testing, and monitoring can provide you with the information you need to determine and monitor SQL Performance. You can break down this process into three steps:

1. Start by identifying your critical targets for usage, allocation, and throughput.
2. Create a to measure against
3. Once deployed, monitor your critical measurements against your .

When planning the design and platform for your SQL Database you must consider the user load. Users usually fall into different groups based on either job function or feature usage and you need to know who is going to use the system. Particularly...

1. The of users and their
2. Peak rate
3. What they are going to do resulting in procedure calls / data access.

In order to monitor Server Performance, a Production DBA can use the following tools...

Windows —This gives a quick, high-level view of server performance and use of resources.
System —A detailed view of Server performance and per-instance SQL Server–specific counters.
(MDW) — A DB that collects Perfmon and Data Collector outputs for troubleshooting
(DMVs) — Sys objects that contain server state information for problem diagnosis

The (VMM) is the part of the operating system that manages all the physical memory and shares it between all the processes that need memory on the system. Its job is to provide each process with memory when it needs it, although the physical memory is actually shared between all the processes running on the system at the same time. It uses a (one per hard drive partition) to store memory to disk when it's not directly needed. When a request is made for a piece of data in memory that cannot be read from actual memory, this is called a .

Reducing VMM always results in better performance. If it occurs, for best performance, the should be on fast disks that have minimal disk usage activity, and the disks should be periodically to ensure that the is contiguous on the disks, reducing the disk head movement and increasing performance.

The metric in the Windows System Monitor to measure paging and page file usage is... Paging file: % Usage - which, as a guide, should be less than percent.

There are two kinds of that can occur when SQL Server requests new memory - they are classified as (when a new piece of memory is required that's yet to be created) and (when a piece of memory has already been created, and has been offloaded to a , and needs reloading). The type should be minimised as these cause poor performance.

is a commonly used tool for I/O subsystem measurement and characterization to baseline an I/O subsystem. It is both a workload generator and a measurement tool that can emulate a disk or network I/O load.

When monitoring SQL Server performance, a commonly used disk performance metric is disk latency, which is measured by Windows System Performance Monitor using the Avg Sec/, Avg Sec/, and Avg Sec/ counters. Target disk latencies are as follows:

Database transaction log—Less than ms : ideally ms
OLTP data—Less than ms
Decision Support Systems (OLAP and Reporting) data—Less than ms

A RAID set contains two or more disks, and the data is striped across all the disks. This RAID level provides no redundancy or fault tolerance because a disk failure destroys the array. During a write operation, the data is broken up into blocks, and the blocks are written onto the disks simultaneously. This increases bandwidth during read operations because multiple sections of the entire chunk of data are able to be read in parallel. However this does not implement any error checking. This is not recommended for any SQL Server volume.

With RAID , one disk is mirrored onto another—meaning two disks are needed to be configured in the RAID set. This is fast because reads can (but not always) occur from both disks, and writes incur minimal performance reduction. It provides redundancy from a disk failure, but increases storage costs because usage capacity is 50 percent of the available disk drives.

RAID is a mirrored set in a striped set with a minimum of disks. There will always be an even number of disks in the set. This is normally the fastest arrangement available.

Raid is striping with parity with a minimum of three disks. During writes it must calculate the data parity—for example, for each write operation in a three-disk array, it writes data across two disks and parity across the third disk. The RAID firmware distributes the parity blocks across all the disks in the RAID set to avoid a write hot spot.

A RAID set contains a minimum of four disks, and distributes two copies of the parity across the disks. This provides enhanced fault tolerance because two drive failures could occur without destroying the data on the array. This RAID implementation makes large RAID groups more practical because larger capacity drives extend the time needed to recover from a drive failure. However, this RAID level should not be used. It is actually Dual Parity and takes the parity region from RAID and duplicates it so each disk has two parity regions. While this allows it to recover from the possible loss of two drives, it has a performance penalty as well.

You should use fast, robust storage for SQL data files and SQL log files. In general, for most SQL Server implementations, the recommendation for both is to use with (RAID ). When unable to use this, the second preference would be RAID especially If the nature of the database implementation is to service an application that has a high number of operations compared to , or the database is configured to be -.

For critical systems, the operating system and SQL binary files should be on a disk array, but it needs to be only a single pair.

The goal of performance tuning SQL Server 2014 is to minimize the response time for each SQL statement and increase system throughput. This can maximize the scalability of the entire database server by reducing - latency, as well as optimizing throughput and processing time.

Different demands are made by an (OLTP) environment than are made by a (DSS) environment. A DSS environment often needs a heavily optimized subsystem to keep up with the massive amounts of data retrieval (or reads) it performs. An OLTP transactional environment needs an subsystem optimized for more of a balance between -and- operations.

Each SQL Server database is made up of potentially three file types...

a data file (.)
one or more data files (.)
files (.).

To maximize SQL Server performance gain, make sure you place the individual files and the files all on separate physical (LUNs). You can place reference-archived data or data that is rarely updated in a - filegroup. This filegroup can then be placed on slower disk drives (LUNs) because it is not used very often. This frees up disk space and resources so that the rest of the database may perform better.

Because database file location is so important to I/O performance, you should consider functional changes to the database when you create your primary data-file placement strategy. The reason for this is that this database's performance has a rather large impact on system performance because it is the most dynamic database on the system, and needs to be the quickest.

To avoid timeouts, you should set the autogrow operation (especially for the database) to a growth rate that is appropriate for your environment. In general, you should set the growth rate to a number that will be large enough to allow normal query activity to continue without other growth iterations. This should be in a minimum of GB increments. If you have instant initialization turned on, the typical blocking that occurs for data file growths should not occur.

If you notice your files growing in between restarts, make the default equivalent to the highest level of growth. Be sure to make every file the same size.

When configuring the database, pre-allocate space for the database files based on the results of monitoring growth of normal operation, however to prevent SQL Server from stopping, leave enabled in case runs out of space

Per SQL Server instance, as a rule of thumb, create one data file per or , all equal in size up to data files

Best practices dictate that you must ensure that is in mode, which enables space recovery

In SQL Server terms, is the breaking up of a large object (such as a table) into smaller, manageable pieces. A is the unit on which is based.

As systems have become increasingly faster and more powerful, the preferred method of has become to use the SQL Server capability to database tables and their indexes over filegroups within a single database. This generally supercedes the use of (DPVs). One important note is that requires an Edition of SQL Server, whereas DPVs does not.

If you choose to use partitioning, data size matters. You should not partition a table that is less than GB. You may not see performance benefits if the table is too small, and may even create additional overhead.

When implementing partitioning within SQL Server, you must create a partition in order to provide the contiguous boundary ranges of each partition. It should be declared as either or - which determines whether each value declared is the lower or upper value range for the partition.

If you wished to create a partition function called SplitYears that, based upon a Date & Time, would store data in the following partitions... 2005 - 2006 , 2007 - 2008, 2009 - 2010, 2011, 2012+ - you would use the following syntax...

CREATE
()
AS
FOR VALUES (
'20050101 00:00:00.000', '20070101 00:00:00.000','20090101 00:00:00.000', '20110101 00:00:00.000',
'20120101 00:00:00.000')

As a best practice, objects should be created and mapped to a filegroup outside of the filegroup, leaving the that filegroup for objects only. This ensures database availability if an outage occurs that affects the availability of any filegroup outside of the filegroup.

A partition is what maps database objects such as a table to a physical entity such as a filegroup, and subsequently to a file. Creating one involves declaring a mapping for the partition used, to individual filegroups.

When used in production, leads to better utilization because fewer reads and read-ahead reads are required. Data is on and in - and this allows for a more efficient use of buffer pool resources.

In SQL Server 2014 Enterprise Edition, if you wanted to modify a table to use Row Compression, you would execute the following statement...

ALTER MyTable WITH ( = )

In SQL Server 2014 Enterprise Edition, if you wanted to modify a table to use Page Compression, you would execute the following statement...

ALTER MyTable WITH ( = )

SQL Server 2014 has a special algorithm for scheduling user processes using the SQL Operating System. It manages one per one logical for user processes. There are two types of these within SQL Server:

are used by internal SQL Server processes. There is currently a 1:1 ratio to logical core plus four additional schedulers used to monitor internal processes for AlwaysOn, the Dedicated Administrator Connection, and In-Memory OLTP.

are available for user queries and user processes. The SQLOS is the internal operating system built in SQL Server. It provisions CPU utilization, thread scheduling, task execution, and memory distribution.

For reasons of data integrity, only one processor can update any piece of data at a time. Other processors that have copies in their caches can have their local copy “invalidated” and thus must be reloaded. This mechanism is referred to as , which requires that all the caches are in agreement regarding the location of all copies of the data, and which processor currently has permission to perform the update.

By default, the of () value is set to , which enables SQL Server to consider all processors when creating an execution plan. In most systems, setting this to a value equivalent to the number of in one NUMA node is recommended. This limits the overhead introduced by implementing this..

When using System Performance Monitor, the SQL Server counter signifies the balance between servicing user requests from data in the data cache (memory) and having to request data from the I/O subsystem (disk). Ideally, this value should be well over percent.

When monitoring SQL Server using the System Performance Monitor - another reliable indicator of instance memory pressure is the SQL Server : Buffer Manager : (PLE) counter. This counter indicates the amount of time that a buffer page remains in memory (in seconds) - this should not fall below a calculated threshold. The rule of thumb to calculating the threshold is to calculate it using the following formula... MaxSQLServerMemory(GB) x .

Be careful not to under-allocate total system memory, because it forces the operating system to start moving page faults to a physical disk called . When excessive takes places, it uses disk and resources, which can introduce in the overall server, resulting in slower database performance. You can identify a lack of adequate system memory by monitoring the Memory: / sec performance counter. It should be as close to as possible, because a higher value indicates that more hard-paging is taking place