SQL Server 2016 | SQL with Manoj

Enable Query Store on a Database – SQL Server 2016

June 2, 2015 4 comments

… continuing from my [previous post] on Query Store.

Here we will see how can we enable it on a Database:

–> Right click on the Database you want to enable Query Store and select Properties. Now select the new feature Query Store at the bottom left side as shown below:

Set the Enable option to True, and click OK.

–> Alternative: You can also enable the Query Store by this simple ALTER DATABASE Statement:

USE [TestManDB]
GO

ALTER DATABASE [TestManDB] SET QUERY_STORE = ON
GO

–> After enabling the Query Store you can check the Database, expand it in Object Explorer, you will see a new folder with the same name “Query Store”, on expanding it you will see 4 reports, as shown in below snapshot:

–> You can check more about Query Store on MSDN BoL [here] with more coverage on what information/stats it captures and how you can Query them.

Categories: Optimization Performance, SQL Server 2016 Tags: Query Store, SQL Server 2016

Export/Convert Table or SQL Query data to JSON string format | SQL Server 2016 – Part 1

June 1, 2015 6 comments

In my [previous post] we saw JOSN added as a new feature in SQL Server 2016.

–> Just like XML now you can also:

1. Store JSON data in SQL Server in table columns as NVARCHAR datatype.

2. Export SQL tables rows to JSON data.

3. Query external JSON data and store back in SQL tables.

–> Note:

– With this CTP2 release you can only export data as JSON string.

– But with the release of CTP3 you will also be able to read JSON data by T-SQL query and convert it into tabular (row/column) format, and will support indexes.

–> Just like XML for exporting JSON data you can use FOR JSON [AUTO | PATH] syntax:

1. FOR JSON AUTO: option automatically creates a nested JSON data with sub arrays based on the table hierarchy used in the Query. The AUTO option must have a FROM clause.

2. FOR JSON PATH: option enables you to define the structure of output of JSON data using the column names with aliases by using a dot separator.

–> Let’s see how export to JSON works:

– I’ll create a sample table and insert few rows in it:

CREATE TABLE Students (
	ID INT IDENTITY(1,1) NOT NULL, 
	FirstName VARCHAR(255), 
	LastName VARCHAR(255), 
	Class INT,
	Marks DECIMAL(3,1)
)

INSERT INTO Students (FirstName, LastName, Class, Marks)
SELECT 'Manoj',   'Pandey', 10, 80.5
UNION ALL
SELECT 'Saurabh', 'Sharma', 11, 82.7
UNION ALL
SELECT 'Kanchan', 'Pandey', 10, 90.5

1. Let’s check the “FOR JSON AUTO” option:

SELECT ID, FirstName, LastName, Class, Marks 
FROM Students
FOR JSON AUTO -- here

– Output with AUTO option:

– Output with AUTO and ROOT() option:

SELECT ID, FirstName, LastName, Class, Marks 
FROM Students
FOR JSON AUTO, ROOT('StudList') -- here

– This is how a formatted JSON looks like:

{
  "StudList": [
    {
      "ID": 1,
      "FirstName": "Manoj",
      "LastName": "Pandey",
      "Class": 10,
      "Marks": 80.5
    },
    {
      "ID": 2,
      "FirstName": "Gaurav",
      "LastName": "Pandey",
      "Class": 11,
      "Marks": 82.7
    },
    {
      "ID": 3,
      "FirstName": "Garvit",
      "LastName": "Pandey",
      "Class": 10,
      "Marks": 90.5
    }
  ]
}

This option as mentioned previously formats the JSON document automatically based upon the columns provided in the Query.

2. Now let’s check the “FOR JSON PATH” option: with this option you can use the dot syntax as used in below Query to form a nested output.

SELECT 
	ID,
	FirstName AS "StudentName.FirstName", 
	LastName AS "StudentName.LastName", 
	Marks
FROM Students
FOR JSON PATH -- here

– Output with PATH option:

[  
   {  
      "ID":1,
      "StudentName":{  
         "FirstName":"Manoj",
         "LastName":"Pandey"
      },
      "Marks":80.5
   },
   {  
      "ID":2,
      "StudentName":{  
         "FirstName":"Saurabh",
         "LastName":"Sharma"
      },
      "Marks":82.7
   },
   {  
      "ID":3,
      "StudentName":{  
         "FirstName":"Kanchan",
         "LastName":"Pandey"
      },
      "Marks":90.5
   }
]

As you can see with PATH option you can create wrapper objects (here “StudentName”) and nest properties (here “FirstName” & “LastName”).

–> You can also check this demo in this video:

With the current release of CTP2 here you saw how we can export a SQL Table rows to JSON data. As soon as CTP3 will release we will see how can we read data back from from JSON string and convert it to tabular (row/column) format.

–> Check my [next post] on how to Import/Read JSON string and convert it in rational-tabular format in form of rows/columns.

Check more about JSON support on [MSDN BoL].

Categories: JSON, SQL Server 2016 Tags: FOR JSON AUTO, FOR JSON PATH, JSON, JSON SQL, SQL Server 2016

Query Store – new feature in SQL Server 2016

May 30, 2015 2 comments

In my [previous blog post] on “SQL Server 2016 Enhancements” we discussed about the new features coming in. Query Store is on of those feature that will add a lot value in troubleshooting and getting insights on Query & overall performance of a database.

–> Query store is a new component in SQL Server that captures Queries, Query Plans, Runtime Statistics, etc. in a persistent store inside a database. You can think of it as a aircraft’s black box, for your database.

It can also enforce policies to direct the SQL Server Query Processor to compile queries to be executed in a specific manner, such as forcing plans.

It is a database-scoped persistent store of query workload history. Query store is primarily targeting administrative scenarios for performance troubleshooting and identifying regressed workloads.

Query store collects query texts and all relevant properties, as well as query plan choices and performance metrics. This collection process works across restarts or upgrades of the server and across recompilation of indexes

It provides many configurable options for customization and it integrates with existing Query Execution Statistics, Plan forcing, and Manageability tools.

–> Query Store lowers the bar dramatically for performance troubleshooting and allows you to perform several kinds of scenarios:
– Conduct system-wide or database-level analysis and troubleshooting
– Access the full history of query execution
– Quickly pinpoint the most expensive queries
– Get all queries whose performance regressed over time
– Easily force a better plan from history
– Safely conduct server restarts or upgrades
– Identify issues with upgrades

–> Typical performance troubleshooting workflow:

–> All data that query store stores will be available through the following Views:
– sys.query_store_query_text
– sys.query_store_query
– sys.query_store_plan
– sys.query_context_settings
– sys.query_store_runtime_stats_interval
– sys.query_store_runtime_stats

By combining these views in various queries, you will gain necessary insights about user workload for a period of time when Query Store was active. Query Store also has a rich UI component that uses several of these same DMVs as shown below:

I’ve collated all these details from SQL Server white papers and other sources from Microsoft.

Check my [next post] on how to enable the Query store on a database from SSMS.

Categories: DBA Stuff, SQL Server 2016 Tags: Query Store, SQL Server 2016

Installing SQL Server 2016 CTP2 | and new features in SSMS

May 29, 2015 4 comments

SQL Server 2016 was released as the Community Technology Preview (CTP) 2 just yesterday (27th May 2015). And in my [previous post] I discussed about it with the Registration and Direct Download link.

After downloading the bits I installed it on my machine and the setup process was very smooth with few changes, check the YouTube video or go through the post below:

1. After you execute the Setup.exe: you will be greeted with the similar Install window You just need to click on the Installation link on the left and then click on the first link shown below:

2. This will open up a new Setup window and takes you to the Setup Role page which this gives you 2 options, 1st Feature (custom) install and 2nd Default. I chose the first option to select only the features I want. Click Next.

3. Now on the Feature Selection page: you can select what exactly you want to work on. You can see here one more option PolyBase Query Service for External Data. Just select the features and click Next:

4. I got stuck at the the Feature Rule page: with this error Oracle JRE 7 Update 51 or higher is required, Failed. Just click on the Failed link and it will give you the URL from where you can download the JRE or Java Runtime Environment, download it from [here].

… the above Page also shows the limitation of Polybase that it will run only in one Instance per computer.

Re-run the rules and it will be Passed this time, click Next.

5. You will come to Instance Config page: If its is first SQL Server install on your PC then select as Default Instance, otherwise if you had installed SQL Server earlier you will have to choose a Named Instance and Click Next.

6. In the Database Engine Config page: you will see an extra option to set number of TempDB files. The label below mentions “The default value is 8 or the number of cores, whichever is lower. This value can be increased up to the number of cores”.

Update: Now with CTP 2.4 release the installer provides a separate tab for tempdb files configuration.

So, in my [C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER2016\MSSQL\DATA\] folder I could see 8 data files, with 1 log file:

7. on Ready to Install page just click Install, and it will take some time to finish the installation.

What’s new in SSMS:

–> After finishing the installation I opened the SQL Server Management Studio 2016: and the object explorer looked like this:

You will see three Databases present by default:
1. DWConfiguration
2. DWDiagnostics
3. DWQueue

… some of these tables in these databases contains pdw prefix, these tables & databases are related to Polybase feature. I will blog about this feature in my coming posts as in when I get more information on this.

–> I created a new Database by name TestManDB, and I got this while expanding some Objects in Object Explorer:

– – On expanding Tables you can see 2 new table options:
1. System Tables
2. External Tables – for Polybase to query Non-Relational data (also relational)

– – There is a new category External Resources (for Polybase), on expanding you get:
1. Data Sources
2. File Formats

– – And as you expand Stored Procedures folder you can see a SP present by default with following name under pdw schema:
– pdw.instpdw, with one parameter @DatabaseName NVARCHAR(MAX).

–> There is one hidden feature in SSMS, i.e. Query Store, check this [blog post] on how to enable it fr a database.

Categories: SQL Server 2016 Tags: Install SQL Server, Polybase, SQL Server 2016

Microsoft SQL Server 2016 Public Preview (CTP2) Available – download now

May 28, 2015 3 comments

Just got an email from Microsoft:

Microsoft SQL Server 2016 Public Preview Available – Try it Today!

[Register and Download CTP-2 Evaluation version (180 days)]

Direct download link:
– SQLServer2016CTP2-x64-ENU.box
– SQLServer2016CTP2-x64-ENU.exe

–> Smooth Installation with new setup-option and new features in SSMS:

Microsoft SQL Server 2016, the next major release of Microsoft’s flagship database and analytics platform, provides breakthrough performance for mission critical applications and deeper insights on your data across on-premises and cloud. The first public preview, SQL Server 2016 Community Technology Preview (CTP) 2, is now available for you to download to trial via Microsoft Azure.

–> Try it today for an early look at these new capabilities:

– Always Encrypted: helps protect data at rest and in motion

– Stretch Database: dynamically stretch your warm and cold transactional data to Microsoft Azure, demo video.

– Real-time Operational Analytics: our in-memory technologies are enhanced to provide real-time analytics on top of breakthrough transactional performance

–> Additional capabilities include:

1. PolyBase: More easily manage relational and non-relational data with the simplicity of T-SQL.

2. AlwaysOn Enhancements: Achieve even higher availability and performance of your secondaries, with up to 3 synchronous replicas, DTC support and round-robin load balancing of the secondaries.

3. Row Level Security: Enables customers to control access to data based on the characteristics of the user. Security is implemented inside the database, requiring no modifications to the application, demo video.

4. Dynamic Data Masking: Supports real-time obfuscation of data so data requesters do not get access to unauthorized data. Helps protect sensitive data even when it is not encrypted, demo video.

5. Native JSON support: Allows easy parsing and storing of JSON and exporting relational data to JSON, demo video.

6. Temporal Database support: Tracks historical data changes with temporal database support.

7. Query Data Store: Acts as a flight data recorder for a database, giving full history of query execution so DBAs can pinpoint expensive/regressed queries and tune query performance.

8. MDS enhancements: Offer enhanced server management capabilities for Master Data Services.

9. Enhanced hybrid backup to Azure: Enables faster backups to Microsoft Azure and faster restores to SQL Server in Azure Virtual Machines. Also, you can stage backups on-premises prior to uploading to Azure.

–> Other Benefits:

1. Enhanced in-memory performance provide up to 30x faster transactions, more than 100x faster queries than disk based relational databases and real-time operational analytics

2. New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes

3. Built-in advanced analytics provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database

4. Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android

5. Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology

6. Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes, demo video.

7. Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure

Learn more about SQL Server 2016: SQL Server 2016 Preview Page