DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Header Right

Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on

SQL Server 2016 New Features – Live Query Statistics

by 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.

enable sql server 2016 new features live query statistics

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.

sql server 2016 new features live query statistics in action

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% 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

by 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.

enable sql server 2016 new features query store

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 :

sql server 2016 new features query store reports in management studio

  • 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 🙂

SQL Server 2016 New Features – Always Encrypted

by Leave a Comment

always encrypted

In my previous post, I listed a number of the SQL Server 2016 new features. Let’s look at the first one in that list – Always Encrypted.
 
With Always Encrypted the key to unlocking the encryption resides with the application. The encyption key is never passed to SQL Server.

God like administrators cannot view the data on the server in its unencrypted form. This provides a clear separation between the data managers and the data owners as to who can actually see the sensitive data. In this case, the data owners can view the data as they own the applications.

There are two types of encryption  :

  • Deterministic Encryption – where the same plain text value is stored in the column, the encrypted value will be the same. This allows grouping, filtering and joining by encrypted values but can expose patterns in the encryption which could open up opportunities for guessing what the values are beneath. The probability of successfully guessing values increases when the number of unique values is small.
  • Randomized Encryption – the opposite. Randomized encryption values makes the data more secure from pattern guessing however grouping, filtering and joining become impossible to do with any accuracy.

I’ve got the key, I’ve got the secret…..

The Always Encrypted Keys used by the applications come in two types :

  • Column Encryption Keys – these keys are used to encrypt sensitive data in columns and all values in a column can use the same key. Encrypted values of those keys are stored in system catalogs and it’s advised to have a secure backup of those keys too.
  • Column Master Keys – these keys protect the column encryption keys. They have to be stored in a trusted key store. Information, including location of the master keys is stored in system catalog views.

Who’s driving?

The Always Encrypted Driver does the work between the client and the database to either encrypt or decrypt the data accordingly. This happens transparently without any additional code needed to be added to the application.

How to enable Always Encrypted?

1/ Create a new database and a new table with some columns that you are wanting to encrypt
2/ You can then right mouse click on the database and choose “Tasks->Encrypt Columns”
3/ You’ll be presented with the Always Encrypted Wizard introduction screen, click “Next”
4/ Here you can choose which columns to encrypt, which encryption type and whether to create a new encryption key or use an existing one.

always encrypted encrypt columns wizard

5/ You’ll be presented with the Master Key configuration page where you can choose which column master key to use, where to store it (in a certificate store or in an Azure key vault) and the master key source.

always encrypted encrypt columns master key configuration

6/ The next screen gives you the option whether to proceed immediately or to create a Powershell script to run it later. Click through and it will do it’s bit and should come back that it has completed.

Before I encrypted the columns, I added some data to both the “Deterministic” and “Randomized” columns of my table. 10 rows were added with exactly the same data. The only difference being that the strings were different for each column.

So the data looked like this, added 10 times :

INSERT INTO AETest(Deterministic, Randomized)
VALUES('This is deterministic','This is randomized');

After the encryption, I queried the data to view the encrypted patterns. As you can see the two types of encryption are clearly visible with deterministic returning the same pattern for all 10 rows and random behaving, well, randomly. 🙂

always encrypted encrypt columns sample data

SQL Server 2016 – What Are The New Features?

by Leave a Comment

SQL Server 2016 New FeaturesSQL Server 2016 will be made available to purchase by the public on 1st June 2016 and if we compare this schedule with Microsoft’s plans of a major release every 2 years, this one comes 26 months after SQL Server 2014 which arrived on 1st April 2014.

So will the list of new features in SQL Server 2016 persuade businesses to upgrade?

This list is by no means complete and so I think I’ll be updating this list as I discover them.

As SQL Server will come in different versions, typically the more businesses are prepared to pay, the richer the feature set available.

  • Always Encrypted – as it says on the tin, the data is always encrypted. This means that access to encrypted data will only be available to the application using SQL Server. The client application has the encryption key and this is never passed to SQL Server.
  • Query Store – in older versions of SQL Server, query plans can be examined using DMV’s which interrogate the plan cache. Once the plans exit the cache, they are no longer visible for comparison anywhere. The query store provides a way to store older versions of execution plans allowing comparisons between old and new to be made. A nice one for those wanting to check performance of plans over time.
  • Live Query Statistics – allows the execution plan to be viewed live as the query is executing – nice!
  • Dynamic Data Masking – when switched on, this feature controls what data is masked from view to users who are not allowed to view it. For example confidential columns may be obscured from view to a group of unauthorized users but the other columns would be visible. Parts of columns may also be obscured from view.
  • Multiple TempDB Files – adding additional tempdb files is not a new feature as such and has been good practice to implement on multi-core machines. What has been added is the ability to do this upon installation of SQL Server.
  • Polybase – allows the querying of Hadoop or Azure blob storage data sets using T-SQL. It’s possible to write queries which join the relational data in SQL Server with the semi-structured data in Hadoop.
  • Row Level Security – access to row data can be restricted based on specified SQL logins. This will be done by filter predicates defined inside of inline table value functions. Security policies will ensure that the filters are executed.
  • R Support – this feature enables data scientists to run R code directly against the SQL Server. Data will no longer need to be exported separately to facilitate these kind of analytic operations.
  • Stretch Database – allows on premise databases to be extended into the cloud. Frequently used data would be stored on premise with older data stored offsite in an Azure database inside the cloud. A question one might ask is what if the on-premise location’s internet link goes down? Only a feature for organizations with good communications redundancy perhaps?
  • JSON Support – MySQL’s doing it in the latest 5.7 release, Postgres has been doing it for years, Oracle’s doing it and it’s coming soon in MariaDB. Relational database products are adapting to include support for JSON and SQL Server has joined the party. JSON data can be parsed and stored in relational format. Functions are included which allow for JSON to be queried and output results from queries can also be formatted as JSON.
  • Temporal Tables – helps to facilitate automatic row versioning. Every time a row is updated in the base table, a copy of the older version is made inside the temporal table. The temporal table is physical object inside of the database. It is separate to but linked to the base table.
  • Managed Backup – this is automated managed backup of your on-premise backups to Azure.

Improvements to existing features in SQL Server

“There is always room for improvement” and existing features have been enriched in places too. What I have been able to ascertain so far is is that there are improvements made to Columnstore Indexes, AlwaysOn and In-Memory OLTP. I’m sure I’ll discover more as I delve deeper.

A worthy release?

It certainly would appear that there are more features than what was included in SQL Server 2014 and if businesses were thinking of upgrading then this version would be well worth considering.

As ever, those decisions will be based around cost and requirements. There will still be businesses running on older versions of SQL Server no longer supported (2005 support finished ended as of April 12th 2016) and they are quite happy to do so because those versions do the job just fine.

For those businesses requiring a supported version, increased performance and some or all of the newer features but have been delaying upgrading, this release may be enough to persuade them to now move forward.

How to Find I/O Usage Per Database in SQL Server

by Leave a Comment

If you’re looking at performance and trying to understand I/O on a per database level in SQL Server, sadly there is not a report in Management Studio that you can run from those available.

It will need some T-SQL to pull out this information from an existing DMV – sys.dm_io_virtual_file_stats.

The information returned by the DMV is data which has been recorded since the SQL Server was last restarted and so it is therefore cumulative data.

It helps the DBA to understand where the I/O distribution is across databases at both the data and log file level.

Let’s take a look at I/O at the database level using this T-SQL:

WITH IO_Per_DB
AS
(SELECT 
  DB_NAME(database_id) AS Db
  , CONVERT(DECIMAL(12,2), SUM(num_of_bytes_read + num_of_bytes_written) / 1024 / 1024) AS TotalMb
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) dmivfs
 GROUP BY database_id)

 SELECT 
    Db
    ,TotalMb
    ,CAST(TotalMb / SUM(TotalMb) OVER() * 100 AS DECIMAL(5,2)) AS [I/O%]
FROM IO_Per_DB
ORDER BY [I/O%] DESC;

On my test instance this produces the following output:

find io usage per database in sql server

I can see that the top I/O consumer is the sample MS database AdventureWorks and this is because I’ve been running some simple SELECT’s against the tables. If I start running queries against the others, the distribution will quickly change so it’s important not to assume anything about the results without regular sampling.

Take some scenario where workload patterns are constant across the day and the results do not change much. Then in the evening some large job runs which reads a lot of data from a database which is otherwise not very active during the day. At that time of the day, that database may show up at the top of the report having generated a lot of I/O. However after this time, normal workloads would resume and the results will change again.

Looking at I/O usage at the database file level in SQL Server

The results above are very high level. If you want to look in more detail, for example at the data file level, you can run something like this:

WITH IO_Per_DB_Per_File
AS
(SELECT 
    DB_NAME(dmivfs.database_id) AS Db
  , CONVERT(DECIMAL(12,2), SUM(num_of_bytes_read + num_of_bytes_written) / 1024 / 1024) AS TotalMb
  , CONVERT(DECIMAL(12,2), SUM(num_of_bytes_read) / 1024 / 1024) AS TotalMbRead
  , CONVERT(DECIMAL(12,2), SUM(num_of_bytes_written) / 1024 / 1024) AS TotalMbWritten
  , CASE WHEN dmmf.type_desc = 'ROWS' THEN 'Data File' WHEN dmmf.type_desc = 'LOG' THEN 'Log File' END AS DataFileOrLogFile
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) dmivfs
 JOIN sys.master_files dmmf ON dmivfs.file_id = dmmf.file_id AND dmivfs.database_id = dmmf.database_id
 GROUP BY dmivfs.database_id, dmmf.type_desc)

 SELECT 
    Db
  , TotalMb
  , TotalMbRead
  , TotalMbWritten
  , DataFileOrLogFile
  , CAST(TotalMb / SUM(TotalMb) OVER() * 100 AS DECIMAL(5,2)) AS [I/O%]
FROM IO_Per_DB
ORDER BY [I/O%] DESC;

I’ve included some extra columns here to help display the megabytes read and written as well as the data file the I/O was generated against.

In the following results, I ran an update against one of the larger tables to help demonstrate. You can see the log file for the AdventureWorks db saw some activity and the distribution of I/O is now more transparent.

find io usage per database per data file in sql server

How to Get SQL Server Developer Edition for Free

by Leave a Comment

sql server developer edition is freeSQL Server Developer Edition – whether you are wanting to learn the product at home to advance your career or you need a copy for your enterprise to test and develop on, it’s historically been a good investment for relatively little cost.

Personally for home use, I have always downloaded the evaluation versions of SQL Server that expire after 6 months. That’s kind of annoying as after that time, you need to either activate the installation with a purchased licence key or reinstall the product. I’ve preferred to do this because for personal use, I have still been reluctant to spend the money on the development version. The hassle of backing up anything I have done and re-installing has been better for me than spending the money.

The good news is that this has changed as Microsoft have announced that SQL Server Developer Edition is now free to download. I think this is only a good thing for increasing the adoption of SQL Server.

You need to register to Visual Studio Dev Essentials to get access to this download. It is also free to register. 🙂

In my opinion Microsoft appears to be making a number of good strategic decisions of late (Visual Studio Community, SQL Server on Linux, .NET on Linux etc) and this move is consistent with their apparent direction of making their products more accessible and compatible on systems which are not running a form of the Windows operating system.

For more information on this topic, check out this official post from Microsoft.

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close