John McCormack's blogs

JSON support for SQL Server 2016

14th July 2016 By John McCormack Leave a Comment

JSON and SQL Server 2016

This post is an introduction to the new JSON support for SQL Server 2016. The post is an entry level post, suitable for someone who is using JSON methods for the first time but who does have some knowledge of T-SQL and a basic understanding of what JSON is.

Background:

JSON support has been introduced into SQL Server for the first time in SQL Server 2016. It was a highly requested feature on Microsoft Connect. Since there are new methods added to SQL Server, I wanted to test them out and get to understand them.

The code:

This code will only work in SQL Server 2016 so you need to have an instance of 2016 available to use for the demo. You can also use Azure SQL Database. See announcement.

Database compatibility level 130 is required for OPENJSON function. Other functions are available in all levels. Note that you need to have new V12 server.

—

Step 1

Use an existing database or create a new one. I created one called Learning. Then create a table to use for the demo called Weather.

[sql]

USE Learning;

DROP TABLE IF EXISTS Weather; — This syntax is new too.
CREATE TABLE Weather
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[Location] nvarchar (128),
DateInserted datetime DEFAULT GETDATE(),
WeatherDetail NVARCHAR(max) — No JSON data type, we just use NVARCHAR(max)
)

[/sql]

Step 2

Get a JSON source that you want to use. There are a number of free data sources available online. For the demo, I have used the local weather forecast in JSON format from Yahoo weather. https://developer.yahoo.com/weather/

Now that we have a table and some valid JSON, we want to insert the JSON text into the table we just created. Replace the values for Location and WeatherDetail in the code below with your own values with your own or simply just run the code I have provided below.

[sql]
INSERT INTO Weather ([Location],WeatherDetail)
VALUES
(‘Paisley’,
‘{
“query”: {
“count”: 1,
“created”: “2016-04-05T17:39:05Z”,
“lang”: “en-GB”,
“results”: {
“channel”: {
“units”: {
“distance”: “mi”,
“pressure”: “in”,
“speed”: “mph”,
“temperature”: “F”
},
“title”: “Yahoo! Weather – Paisley, Scotland, GB”,
“link”: “http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-31342/”,
“description”: “Yahoo! Weather for Paisley, Scotland, GB”,
“language”: “en-us”,
“lastBuildDate”: “Tue, 05 Apr 2016 06:39 PM BST”,
“ttl”: “60”,
“location”: {
“city”: “Paisley”,
“country”: “United Kingdom”,
“region”: ” Scotland”
},
“wind”: {
“chill”: “48”,
“direction”: “270”,
“speed”: “18”
},
“atmosphere”: {
“humidity”: “63”,
“pressure”: “991.0”,
“rising”: “0”,
“visibility”: “16.1”
},
“astronomy”: {
“sunrise”: “6:34 am”,
“sunset”: “8:7 pm”
},
“image”: {
“title”: “Yahoo! Weather”,
“width”: “142”,
“height”: “18”,
“link”: “http://weather.yahoo.com”,
“url”: “http://l.yimg.com/a/i/brand/purplelogo//uh/us/news-wea.gif”
},
“item”: {
“title”: “Conditions for Paisley, Scotland, GB at 05:00 PM BST”,
“lat”: “55.842018”,
“long”: “-4.42209”,
“link”: “http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-31342/”,
“pubDate”: “Tue, 05 Apr 2016 05:00 PM BST”,
“condition”: {
“code”: “30”,
“date”: “Tue, 05 Apr 2016 05:00 PM BST”,
“temp”: “52”,
“text”: “Partly Cloudy”
},
“forecast”: [
{
“code”: “12”,
“date”: “05 Apr 2016”,
“day”: “Tue”,
“high”: “53”,
“low”: “44”,
“text”: “Rain”
},
{
“code”: “11”,
“date”: “06 Apr 2016”,
“day”: “Wed”,
“high”: “47”,
“low”: “41”,
“text”: “Showers”
},
{
“code”: “30”,
“date”: “07 Apr 2016”,
“day”: “Thu”,
“high”: “53”,
“low”: “40”,
“text”: “Partly Cloudy”
},
{
“code”: “39”,
“date”: “08 Apr 2016”,
“day”: “Fri”,
“high”: “49”,
“low”: “39”,
“text”: “Scattered Showers”
},
{
“code”: “12”,
“date”: “09 Apr 2016”,
“day”: “Sat”,
“high”: “49”,
“low”: “41”,
“text”: “Rain”
},
{
“code”: “11”,
“date”: “10 Apr 2016”,
“day”: “Sun”,
“high”: “51”,
“low”: “40”,
“text”: “Showers”
},
{
“code”: “28”,
“date”: “11 Apr 2016”,
“day”: “Mon”,
“high”: “53”,
“low”: “41”,
“text”: “Mostly Cloudy”
},
{
“code”: “12”,
“date”: “12 Apr 2016”,
“day”: “Tue”,
“high”: “52”,
“low”: “42”,
“text”: “Rain”
},
{
“code”: “39”,
“date”: “13 Apr 2016”,
“day”: “Wed”,
“high”: “53”,
“low”: “41”,
“text”: “Scattered Showers”
},
{
“code”: “5”,
“date”: “14 Apr 2016”,
“day”: “Thu”,
“high”: “53”,
“low”: “40”,
“text”: “Rain And Snow”
}
],
“description”: “<![CDATA[<img src=\”http://l.yimg.com/a/i/us/we/52/30.gif\”/>\n \nCurrent Conditions:\n Partly Cloudy\n \n \nForecast:\n Tue – Rain. High: 53Low: 44\n Wed – Showers. High: 47Low: 41\n Thu – Partly Cloudy. High: 53Low: 40\n Fri – Scattered Showers. High: 49Low: 39\n Sat – Rain. High: 49Low: 41\n \n \n<a href=\”http://us.rd.yahoo.com/dailynews/rss/weather/Country__Country/*https://weather.yahoo.com/country/state/city-31342/\”>Full Forecast at Yahoo! Weather</a>\n \n \n(provided by <a href=\”http://www.weather.com\” >The Weather Channel</a>)\n \n]]>”,
“guid”: {
“isPermaLink”: “false”
}
}
}
}
}
}’
)

[/sql]

Step 3

Check your table contents by runnning [sql]SELECT * FROM [dbo].[Weather];[/sql]

Step 4 – ISJSON

The ISJSON built-in function is used to verify that your JSON text is properly formatted. The queries below select the values for Location and WeatherDetail from dbo.Weather and also the return value of the ISJSON function against these values. We can see that the value of Location is Paisley and that this is not a valid JSON value. We also see the value of WeatherDetail and that this IS a valid JSON value.

[sql]

SELECT [Location] FROM [dbo].[Weather];
SELECT ISJSON([Location]) as [ISJSON] FROM [dbo].[Weather];
SELECT WeatherDetail FROM [dbo].[Weather];
SELECT ISJSON(WeatherDetail) as [ISJSON] FROM [dbo].[Weather];

[/sql]

Step 5

Let’s insert another JSON value with fewer details into dbo.Weather. This will help us when we try to query it later.

[sql]
INSERT INTO Weather ([Location],WeatherDetail)
VALUES (‘Caleta de Fuste’,'{
“Forecasts”:[
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 12:00AM”,
“WeatherDetails”:”Dry”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 3:00AM”,
“WeatherDetails”:”Dry”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 6:00AM”,
“WeatherDetails”:”Cloud”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 9:00AM”,
“WeatherDetails”:”Sunny”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 12:00PM”,
“WeatherDetails”:”Sunny”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 3:00PM”,
“WeatherDetails”:”Sunny”
}
},
{
“Forecast”:{
“Location”:”Caleta de Fuste”,
“Date”:”Apr 11 2016 6:00PM”,
“WeatherDetails”:”Windy”
}
}
]
}’
)

[/sql]

Let’s just check what we have in the table (and it’s validity) before we move on.

[sql]

SELECT *, ISJSON(WeatherDetail) as [ISJSON] FROM Weather;

[/sql]

Nice. Both fields contain valid JSON.

Step 6 – OPENJSON

We will use the table-valued funtion OPENJSON to parse JSON text and return the JSON objects into the rows and columns we know and love. OPENJSON transforms the array of JSON objects into a table, in which each object is represented as one row, and key/value pairs are returned as cells. Source: https://msdn.microsoft.com/en-gb/library/dn921897.aspx

[sql]

— As you will see, this query displays the JSON data as an easy to read table.
DECLARE @json nvarchar(max) = (SELECT WeatherDetail FROM Weather where ID = 2)
SELECT *
FROM OPENJSON (@json,N’$.Forecasts’)
WITH
(
Location NVARCHAR(64) N’$.Forecast.Location’,
Date datetime N’$.Forecast.Date’,
WeatherDetails NVARCHAR(MAX) N’$.Forecast.WeatherDetails’
)

[/sql]

Step 7 – FOR JSON

Let’s now create a new table and insert some data so we can understand how to use FOR JSON.

[sql]

DROP TABLE IF EXISTS Forecast;
CREATE TABLE Forecast
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[Location] nvarchar (128),
[Date] datetime,
WeatherDetail NVARCHAR(max) — No JSON data type
)

— Insert the data which we wil work with next
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 00:00:00.000′,’Light Rain’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 03:00:00.000′,’Light Rain’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 06:00:00.000′,’Heavy Rain’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 09:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 12:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 15:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 18:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Paisley’,’2016-04-11 21:00:00.000′,’Thunder’)
—
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 00:00:00.000′,’Dry’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 03:00:00.000′,’Dry’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 06:00:00.000′,’Cloud’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 09:00:00.000′,’Sunny’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 12:00:00.000′,’Sunny’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 15:00:00.000′,’Sunny’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 18:00:00.000′,’Windy’)
INSERT INTO Forecast ([Location],[Date],WeatherDetail)
VALUES (‘Caleta de Fuste’,’2016-04-11 21:00:00.000′,’Windy’)

[/sql]

Step 8 FOR JSON AUTO

Use AUTO mode with the FOR JSON clause. (Formatted automatically based on structure of SELECT)

[sql]

— This select gives us our data in the familiar table format.
SELECT * FROM [dbo].[Forecast]
ORDER BY [Location],[Date]

— This select now formats our data as JSON
SELECT * FROM [dbo].[Forecast]
ORDER BY [Location],[Date]
FOR JSON AUTO;

[/sql]

Limitation: SSMS still uses the XML editor to show JSON so it doesn’t display it correctly when you click on it.

Due to this, a good tool to Format your JSON into a readable format if needed can be found at https://jsonformatter.curiousconcept.com/

Step 9 – FOR JSON PATH

Use PATH mode with the FOR JSON clause to maintain control over format. The name value pairs are generated by using this syntax in the select: AS [Forecast.Location]. The root node is named by the value we pass in after the word root: ROOT(‘Forecasts’)

[sql]

SELECT
[Location] AS [Forecast.Location],
CAST([Date] as nvarchar) AS [Forecast.Date],
WeatherDetail as [Forecast.WeatherDetails]
FROM [dbo].[Forecast]
ORDER BY [Location],[Date]
FOR JSON PATH, ROOT(‘Forecasts’);

[/sql]

This is much nicer when we paste the output into https://jsonformatter.curiousconcept.com/.

Comparing to XML functions

[sql]

SELECT * FROM [dbo].[Forecast]
ORDER BY [Location],[Date]
FOR XML AUTO;

SELECT
[Location] AS [Location],
CAST([Date] as nvarchar) AS [Date],
WeatherDetail as [WeatherDetails]
FROM [dbo].[Forecast]
ORDER BY [Location],[Date]
FOR XML PATH (‘forecast’), ROOT(‘Forecasts’);

[/sql]

What’s next?

This blog post is quite large and still only covers a small part of JSON support for SQL Server 2016. There are more built in functions such as JSON_VALUE, JSON_QUERY, & JSON_MODIFY which I hope to cover soon.

Useful resources

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/10/07/bulk-importing-json-files-into-sql-server/
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/09/22/openjson-the-easiest-way-to-import-json-text-into-table/
https://msdn.microsoft.com/en-gb/library/dn921897.aspx
https://blogs.msdn.microsoft.com/jocapc/2015/05/16/json-support-in-sql-server-2016/
https://jsonformatter.curiousconcept.com/

SQL server wait statistics query – With link to help

23rd May 2016 By John McCormack Leave a Comment

SQL Server Wait Types Library

Following the release of SQL Server Wait Types Library by Paul Randal via SQLSkills.com, I have updated my query which finds the most prolific wait types to include a link to the waits library for that particular wait type. The handy thing about the way Paul has named the posts is that they all end with the exact wait type name so this name can be pulled out from a query and appended to a fixed URL. This means we can copy the contents of the cell in the help_url column into our browser to find the entry in the SQL Server Wait Types Library.

More about the query

As sys.dm_os_wait_stats is reset when SQL server is restarted, it’s worth knowing the start time of the instance so you can get information on averages per minute or hour (or some other time scale). This is included in the query below. Please bear in mind that the contents of this DMV can be cleared out by running DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR); so server start time is not completely reliable as a measure for working out averages.

SQL server wait statistics query

[SQL]

— Declare variables
DECLARE
@sqlserver_start_time DATETIME,
@current_time DATETIME,
@mins INT,
@hours INT;

— Set variables
SET @sqlserver_start_time = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info); — Needed to work when collection in dm_os_wait_stats started
SET @current_time = (SELECT SYSDATETIME());
SET @mins = (SELECT DATEDIFF(MINUTE,@sqlserver_start_time,@current_time));
SET @hours = (SELECT @mins/60 AS Integer);

— Uncomment PRINT statements for more info
— PRINT @sqlserver_start_time
— PRINT @current_time
— PRINT @mins
— PRINT @hours

SELECT TOP 10
wait_type,
wait_time_ms,
waiting_tasks_count,
waiting_tasks_count/@hours as waiting_tasks_count_PerHour,
waiting_tasks_count/@mins as waiting_tasks_count_PerMin,
wait_time_ms/waiting_tasks_count AS avg_wait_time_ms,
‘https://www.sqlskills.com/help/waits/’+wait_type as help_url — SQLSkills resource on waits. If web page not complete, find another source.
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC — Overall time spent waiting by wait type. (Could change to waiting_tasks_count DESC)

[/SQL]

SQL Server Threadpool Waits

22nd April 2016 By John McCormack Leave a Comment

Threadpool Waits

I encountered the following error this week:

Event Description: SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems.

This SQL Server hosts the databases for a few utility apps and has always ticked along without any concerns. However, more and more databases had been added over time and most recently Altiris was added and this is when the problems began.

Searching online, I came across this question and answer in which Jonathan Kehayias suggested the problem could be caused by threadpool starvation. It doesn’t seem to be a common problem so I had to look into to it to find out more.

In his first answer, Jonathan suggested that scaling out or splitting the workload across more SQL servers would work better than scaling up because the max number of worker threads increase would not be sufficient by scaling up. I looked into the server but realised it was had a very low spec of only 2 CPUS and 4GB memory to support 20 databases of varying sizes so in this case, maybe scaling up would help. Had I read on, I would have seen that Jonathan didn’t expect the guy’s server to have such low spec (1041 databases 1CPU 2GB RAM) and he also advised scaling up.

Measuring the threadpool waits

Querying the DMV sys.dm_os_wait_stats returns a count of waiting tasks since the last restart. I ran the query below and found that this system had experienced 18,000 threadpool waits per day since its last restart. 300,000 in total.

[sql]

select *
from sys.dm_os_wait_stats
where wait_type = ‘THREADPOOL’

[/sql]

As the stats are cumulative, I wanted to know how often the threadpool waits were happening right now. I set up a sql agent job to collect the waiting_tasks_count from sys.dm_os_wait_stats along with the date of collection and scheduled this to run every minute. I found that I was receiving threadpool waits at a rate of 822 per hour, roughly 14 per minute.

How to fix

Fortunately for me, this box was a virtual machine (VM) and as such, adding CPU and memory was a fairly seamless process. I doubled the CPUs and increased memory to 16GB to give SQL Server a fighting chance. I could go further if needed.

Once this was added, I continued to monitor the count of threadpool waits every minute and found they had reduced from 822 per hour to <1 per hour.

Summary

To summarise, although I’m sure it’s not always the answer, I found that beefing up a very low spec server had a dramatic improvement on threadpool waits. I should add this was a 64bit SQL Server running 2008 R2.

Other resources

Whilst deciding what to do and getting more information on threadpool starvation, I read a number of other resources which are listed below.

https://blogs.msdn.microsoft.com/psssql/2009/11/24/doctor-this-sql-server-appears-to-be-sick/ (Part 6)

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/11/12/the-benefits-of-attending-pass-realized.aspx

https://www.brentozar.com/archive/2014/05/connections-slow-sql-server-threadpool/

As always, your comments and suggestions for improvement are welcome.

Disk space monitoring with SQL Server

30th March 2016 By John McCormack 2 Comments

In this post, I discuss 3 methods (of varying complexity) to carry out some disk space monitoring with SQL server. This can be ad-hoc or you can build it into SQL agent jobs to run on a schedule. Now the first thing I’d like to say is this isn’t a replacement for good monitoring software, it’s more of a compliment to monitoring software. It’s a double check in case something goes wrong with your monitoring. Having some handy scripts is useful as well, because it lets you find out info on an ad-hoc basis, rather than relying on alerts or checking manually. [Read more…]

Compressing backups with Litespeed for SQL Server

11th March 2016 By John McCormack Leave a Comment

I recently looked at an issue where free space on a backup drive was running low. One option was to increase the storage. Another was to look at the composition of the backups and see if I could try compressing backups with Litespeed for SQL Server.

2 particular backups stood out. Database1 was 93GB and Database2 was 297GB. Incidently, this was only half of the total size as we stripe across 2 drives so the total size was 186GB and 594GB.

Example backup script for Litespeed.

[sql]
exec master.dbo.xp_backup_database
@database = ‘Database1’,
@filename = ‘D:\Backup\Database1\Database1_1.BKP’,
@filename = ‘G:\Backup\Database1\Database1_2.BKP’,
@backupname = Database1 backup’,
@desc = ‘Backup of Database1’,
@encryptionkey = ‘LiteSp1234567’,
@cryptlevel = 8,
@init = 1,
@logging = 0,
@throttle = 99,
@comment = ”,
@with = ‘SKIP’,
@with = ‘STATS = 5’,
@compressionlevel = 1
[/sql]

For the purposes of this post, the line we are most interested in is @compressionlevel = 1. By increasing @compressionlevel on a scale from one to eight, we can reduce the size of our backups. One thing to note is that the higher the level of compression, the higher the CPU utilisation will be. More highly compressed backups will usually take more time. It is worth testing the backups using incremental increases in @compressionlevel to find the one that best suits your server.

LiteSpeed for SQL Server 7.5 allows a compression level of 1 – 8.
1 = Medium Compression
2 = Medium High Compression
3-6 = High Compression
7-8 = Extreme Compression.
(Previous versions allowed 9-11 but these are now classed as Level 8)

It is worth testing the backups using incremental increase in @compressionlevel to find the one that best suits your server.

My results:

In the end, I opted for @compressionLevel = 5 for Database1 and @CompressionLevel = 3 for Database2. The increased backup time to use @compressionlevel=5 for Database1 is justifed, given the 64GB reduction in backup size. For Database 2 backup, there was too large a jump in processing time between levels 3 and 5. If we had no option, we could live with it but the original reduction in size by using @CompressionLevel = 3 was sufficient.

Side note: As a side benefit I wasn’t considering, our backup team advised the tape backup duration reduced by 1.5 hours so there was a positive knock on effect for another team too.

Source: http://documents.software.dell.com/litespeed-for-sql-server/7.5/netvault-litespeed-for-sql-server-user-guide/back-up-databases/compression-levels