sql server 2016 | DBA Diaries

SQL Server 2016 New Features – Multiple TempDB Files Configured at Server Install

Posted on June 12, 2016 Written by Andy Hayes Leave a Comment

Configuring multiple tempdb files is not a new feature in SQL Server and has been a best practice for sometime to help ease and prevent tempdb contention which helps to increase the scalability of your SQL Server installation.

Before SQL Server 2016, this was a task which needed to be carried out after the installation but one of the new features of SQL Server 2016 is the ability to configure this as part of the install process.

As you step through the installation and get to the point of Database Engine Configuration, there is a tab for tempdb where the configuration can be done.

There is not a lot else to say about this really except that it’s another welcome new feature of SQL Server 2016! 🙂

SQL Server 2016 New Features – Dynamic Data Masking

Posted on June 7, 2016 Written by Andy Hayes Leave a Comment

Data security is always a hot subject and anything that database software vendors can do to enhance the security of data is usually welcome. One of the new features of SQL Server 2016 is the introduction of Dynamic Data Masking.

As it says on the tin, the data is masked which prevents non-privileged users from seeing it.

With Dynamic Data Masking in SQL Server 2016, there is control over how much of the data is masked from unauthorized viewers. In the case of a call center for example, as part of the authentication process, it may require customers to supply the last 4 digits/characters of a field in the database which is unique to them. In the United States, this might be a social security number for instance. The call center operator would not see the entire field, instead those 4 digits would be the only characters on display.

How to setup Dynamic Data Masking in SQL Server 2016

Masking rules may be defined using T-SQL functions at table design time. In this example I am using SQL Server 2016 RC3.

Let’s say we wanted to store some data specific to users and that the email address field should not be totally exposed when viewed from the application.

CREATE TABLE T1
  (ID IDENTITY PRIMARY KEY,
   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);

After inserting a row and connecting as a user that just had select permissions on the table, the data returned by my query shows the mask that has been applied from the email() function :

Email
--------------------
[email protected]

(1 row(s) affected)

Connecting to the same table as a user with elevated permissions such as db_owner returns the data in an unmasked form.

But isn’t data masking the same as encryption?

No, the two serve different purposes and should not be confused with each other.

With data encryption, the data in the tables is physically altered and stored in an encrypted format. This leads to changes in storage requirements and inclusion of encryption keys and functions to de-crypt the data making it very secure.

Data masking however does not alter the data that is stored inside of the database. There are no physical changes required at the database level and anyone with direct access to the physical data can read or write to the data in a typical way using normal SQL commands.

Data masking should never be used as a replacement for encryption.

For more information on this new feature in SQL Server 2016 including the other masking functions which are available, please check out the official documentation from Microsoft via this link

SQL Server 2016 New Features – Live Query Statistics

Posted on May 26, 2016 Written by Andy Hayes Leave a Comment

SQL Server 2016 Live Query Statistics provide a way to look into the execution plan to see what parts of the query are currently running.

The advantage of this feature is that for a slow query, instead of having to wait for it to complete before the execution plan is available, it’s now possible to view the execution plan in real time.

Sometimes execution plans can be huge, increasing the effort needed to work out which parts of the query are performing badly. With Live Query Statistics enabled, seeing this data in real time helps the DBA or developer find the root causes of poor performance faster.

How to Enable Live Query Statistics in SQL Server 2016

There is a new button on the SQL Editor toolbar which is responsible to activating this feature when inside a new query window.

Then simply execute the query and the results will appear on screen. In the following screenshot, the query is still in progress and all operators have not finished.

I have circled two of them and you can see that they are at different stages according to the percentages listed. Overall, the query is 40{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de} complete as seen highlighted in yellow. There is data for the number of rows passing through the operator and for the time lapsed on that part of the query.

When the query finishes, you can compare this visualization with the actual execution plan to understand the cost of the components relative to the total cost of the query.

This feature can also be accessed via activity monitor under the “Active Expensive Queries”. Right click the query you are interested in and click “Show Live Execution Plan”

A very nice feature indeed! 🙂

SQL Server 2016 New Features – Query Store

Posted on May 25, 2016 Written by Andy Hayes Leave a Comment

In this series of posts covering the new features in SQL Server 2016, I’m moving on to looking at the Query Store.

This tool provides a way to track query execution plan performance over time to help assist troubleshooting efforts. Figuring out why the database has suddenly started running slowly can be at times difficult to diagnose quickly. The query store is an extra tool in the DBA or developer’s armoury to help in such situations.

The store captures, queries, statistics and execution times for later review. Data is recorded by time making it possible for you to see when query plan changes happened on the server.

This is an improvement over just having query plans available in the query plan cache. The query plan cache only stores the last version of the execution plan and if the server comes under memory pressure, the plans are evicted.

What’s so important about the execution plans?

It’s the execution plans that tell the story about how the optimizer ran the query.

Such plans can be altered say if the table grows and the optimizer decided that instead of an index seek, it will be more efficient to do an index scan.

To name a few more scenarios; there might be a server wide change using sp_configure, some index was changed (or dropped), index statistics were updated, sp_recompile was called or the table schema was changed.

The execution plans are really important to help understand the decisions the optimizer made, enabling the performance troubleshooter to quickly see where the delays are in a given query.

At the time of writing I am using Release Candidate 3 of SQL Server 2016.

How to Enable the Query Store in SQL Server 2016

This is either done using T-SQL or via Management Studio and done on a per database basis.

T-SQL

ALTER DATABASE YourDB SET QUERY_STORE = ON;

Management Studio

Right mouse click the database you want to enable this for, choose “Properties->Query Store”.

Under “General”, you’ll see “Operation Mode (Requested)”, choose “Read Write” from the list of options.

On this screen that you can see further options, such as how long you want to keep the data in the store before it gets flushed, how big the store can be etc.

When would you typically use the Query Store?

To implement Plan Forcing
To determining the number of times a query plan was executed in a given window
To identify the top (N) queries by resource (CPU etc) in the past (X) hours
To audit the history of query plans
To analyze the resource usage patterns of a particular database

Plan Forcing

As there are multiple versions of the execution plan in the query store, it is possible through policies to direct a query to use a specific execution plan – this is known as “Plan Forcing”. By giving instruction to use a specific plan, this can help to quickly resolve a performance issue caused by an execution plan change.

Structure of the query store

The query store is divided up into two stores; the plan store and the runtime stats store. The former contains the execution plan information and the latter contains the execution statistics for each of the plans stored.

Viewing the data in the query store

Once the query store has been enabled, the contents of the store can be viewed using this query:

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
JOIN sys.query_store_query AS Qry
ON Pl.query_id = Qry.query_id
JOIN sys.query_store_query_text AS Txt
ON Qry.query_text_id = Txt.query_text_id;

There are also reports available in Management Studio. Under the Query Store section of the database, there are 4 reports exposed :

Query store regressed queries – these are the queries that have declined in performance. Data is available for each query in both tabular and graph form with differing options to report on metrics for duration, logical reads/writes, cpu time, memory consumption and physical reads.
Overall Resource Consumption – data is aggregated in graph form of the query runtime statistics during a specific time interval.
Top Resource Consuming Queries – shows the most expensive queries that were executed during a specific time interval.
Tracked Queries – shows the runtime statistics at query execution and provides a way for the troubleshooter to view the execution plan of a particular query.

Summary

The query store enables a DBA to record the changes to execution plans over time. Data is presented in easily digestible forms allowing quicker understanding of the reasons why a query might have deteriorated in performance.

It’s neat and it’s most welcome 🙂