Monday, November 12, 2018

Load and run custom T-SQL scripts in SSMS via Snippets

If you work with SQL Server on a daily basis, it is very likely you have a lot of custom scripts you have to execute frequently, maybe you have stored them on a folder and you open them manually as you need them, or have saved them on a solution or project file, maybe you execute a custom .bat or PowerShell file to load them when you open SSMS...

Every method has its pros and cons, and on this post, I will show you a new method to load your custom scripts on any open query window on SSMS via Snippets.

What is a Snippet?


According to Microsoft Documentation:
Code snippets are small blocks of reusable code that can be inserted in a code file using a context menu command or a combination of hotkeys. They typically contain commonly-used code blocks such as try-finally or if-else blocks, but they can be used to insert entire classes or methods.

In short words is custom code (T-SQL Scripts for us) that you use a lot and you want to have always available to use.
In SSMS there are a lot of code snippets available by default, that you can use right away, let us see one of them in action, we will use a snippet to insert a create table template:


You can see how easy is to load the code in any existing window, even if this already has code on it, so now let us create one with our custom code.


Creating a custom Snippet


These files are stored in XML format with a .snippet extension, you can find the official documentation here.

The basic template is this (you can find the file on my GitHub):


<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
 <CodeSnippet Format="1.0.0">
 <Header>
 <Title>Your Snippet title</Title>
        <Shortcut></Shortcut>
 <Description>Your snippet description</Description>
 <Author>Author of the snippet</Author>
 <SnippetTypes>
  <SnippetType>Expansion</SnippetType>
 </SnippetTypes>
 </Header>
 <Snippet>
  <Declarations>
   <Literal>
    <ID>Variable1</ID>
    <ToolTip>Variable1 description</ToolTip>
    <Default>variable1 default value</Default>
   </Literal>
  </Declarations>
  <Code Language="SQL">
<![CDATA[
SELECT $Variable1$
]]>
  </Code>
 </Snippet>
 </CodeSnippet>
</CodeSnippets>

Explaining it:

  • You need to insert your custom code inside the <![CDATA[]]> label. 
  • If you have variables, you must enclose them in $ characters ($Variable1$ for our template, and then declaring the variable with the name you use inside the <Declarations> section.
  • Then declare the Script name, description, and author in the <Header> section.
  • Save it with the .snippet extension.
I have a simple script that I use a lot to see the elapsed and remaining time for any session on SQL server, so let us use it for this example, the query is the following (Note: I didn't make this script, but I was not able to find the author, so if you know him/her, please let me know so I can give the proper credit):


SELECT 
 r.session_id,
 r.command,
 CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
 CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
 CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
 CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
 CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
 CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
 CASE WHEN r.statement_end_offset = -1 
 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))AS [SQL Text]
FROM sys.dm_exec_requests r WHERE command LIKE  '%%' --Text of the Session you want to monitor, like BACKUP or DBCC


For this script, our variable will be the text in the like statement, so after using the snippet template, our code should look like this:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
 <CodeSnippet Format="1.0.0">
 <Header>
 <Title>Running Queries ETA</Title>
        <Shortcut></Shortcut>
 <Description>Display estimated completed time for any given query</Description>
 <Author>SQLGuatemala.com</Author>
 <SnippetTypes>
  <SnippetType>Expansion</SnippetType>
 </SnippetTypes>
 </Header>
 <Snippet>
  <Declarations>
   <Literal>
    <ID>YourCommand</ID>
    <ToolTip>Command to look for</ToolTip>
    <Default>BACKUP</Default>
   </Literal>
  </Declarations>
  <Code Language="SQL">
   <![CDATA[
SELECT 
 r.session_id,
 r.command,
 CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
 CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
 CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
 CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
 CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
 CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
 CASE WHEN r.statement_end_offset = -1 
 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))AS [SQL Text]
FROM sys.dm_exec_requests r WHERE command LIKE  '%$YourCommand$%'
]]>
  </Code>
 </Snippet>
 </CodeSnippet>
</CodeSnippets>


We save it with the .snippet extension to a folder in our computer, the next step is to load it to SSMS.


Loading the Snippet into SSMS


Open SSMS and go to Tools> Code Snippets Manager



The following window will open. If you want to load a complete snippet folder, click on Add, or if you want to load just one snippet, click on Import.



For this example we will load the entire folder where we store the snippet template and the one we created in the previous step, so we click on Add, and then locate the snippet folder:



We can see that the custom Snippets folder has been created and the 2 snippets are loaded.



After that, we can use the custom code snippets on any window we want.

Loading our Custom Snippet


Now you can load your custom code in any SSMS open query window, we will use it to know how much time is taking a DBCC command we are running on the other window:




For more information on T-SQL snippets, visit this link.

Thursday, November 8, 2018

Enable Machine Learning Services on SQL Server

R Services (SQL Server 2016) or Machine Learning Services (2017 and 2019 CTP) provide you with the ability to perform data analysis from the database itself using T-SQL.

You can learn a little more about what you can do in the SQL Server blog.

On this post, I will show you how to setup and configure it so you can start using it right away in your applications and reports.

To install it

You have to choose it from instance features on your SQL server setup window, you then choose the languages you want to work (R, Python)



Note: if your computer does not have access to the internet, you will have to download two packages separately and then locate them in the setup window.

Continue the setup as usual and finish it.

After you run the setup, please make sure that SQL Server LauchPad service is running for the instance you have installed the Machine Learning Services.




I recommend you to patch the instance with the latest update available in case you encounter any issue with the service startup.


To configure it


Just connect to the instance where the Machine Learning Services was added, using your favorite tool, and run the following T-SQL to enable the code execution:


EXEC sp_configure  'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE

You must see the following output:



After that, you have to restart the SQL Server service for it to work.

our last step is just to test it...


Testing the script execution


To execute scripts you must use the sp_execute_external_script procedure, you can find the official documentation here.

This is the most basic command I can think you can execute to see if everything is ok, displaying the license information of the R language:


EXEC sp_execute_external_script  
@language =N'R',
@script= N'license()'

Note that the first execution after a service restart will take some time to complete, but after waiting you should see the license information in the Messages window:


And now... a simple example with real data


For this example, we will take Application.Countries table from WideWorldImporters test database and display it sorted by population in descendent order with an R script, running this T-SQL:



USE WideWorldImporters
GO


EXEC sp_execute_external_script  @language =N'R',
@script=N'
dsTest<- InputDataSet
OutputDataSet <- dsTest[order(-dsTest$LatestRecordedPopulation),]
',
@input_data_1 =N'
SELECT 
 CountryName,
 FormalName,
 LatestRecordedPopulation,
 Continent
FROM [Application].[Countries]
WHERE Region = ''Americas'''
WITH RESULT SETS 
 (
  ( 
  [CountryName] [nvarchar](60) NOT NULL,
  [FormalName] [nvarchar](60) NOT NULL,
  [LatestRecordedPopulation] [bigint] NULL,
  [Continent] [nvarchar](30) NOT NULL
  )
 );
GO


If you have the database installed and run it, you can see the output with the data sorted from the R script:



You have learned how to execute simple R scripts against SQL server tables from the database engine, so now you can make more complex examples and do your own data analysis scripts.

For more in-depth information visit this link.

Monday, October 15, 2018

How much affects encryption to your database performance?

Transparent data encryption (TDE) helps you to secure your data at rest, this means the data files and related backups are encrypted, securing your data in case your media is stolen.
This technology works by implementing real-time I/O encryption and decryption, so this implementation is transparent for your applications and users.

However, this type of implementation could lead to some performance degradation since more resources must be allocated in order to perform the encrypt/decrypt operations.

On this post we will compare how much longer take some of the most common DB operations, so in case you are planning to implement it on your database, you can have an idea on what to expect from different operations.

Setup of our example


For this example we will use the Wide World Importers sample database, and restore it 2 times, one decrypted, and the other one encrypted. Please note that the restored database is around 3.2 GB size, a relatively small one.

To encrypt one of the databases we use the following T-SQL (more info about how TDE works here):

USE master;
GO


CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Y0uR_P@$$worD_123';
GO


CREATE CERTIFICATE MyServerCert
	WITH SUBJECT = 'WWI Encryption';
GO


USE [WideWorldImporters_encrypted];
GO


CREATE DATABASE ENCRYPTION KEY
	WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO


ALTER DATABASE [WideWorldImporters_encrypted]


SET ENCRYPTION ON;
GO


After the above T-SQL has been executed, now we have the 2 databases ready for our example:


So it is just a matter of testing and measuring different operations and compare them (in no particular order):

DBCC CHECKDB


We run a dbcc checkdb on each database with the following T-SQL:

SET STATISTICS TIME ON;

DBCC CHECKDB([WideWorldImporters]) WITH NO_INFOMSGS

DBCC CHECKDB([WideWorldImporters_encrypted]) WITH NO_INFOMSGS

SET STATISTICS TIME OFF;

we can see the results, it took around 6% more to complete:



BACKUP DATABASE


Before we start this one, read this post about issues found on database backups with TDE enabled, so it is strongly recommended to patch your instances before using it.

SET STATISTICS TIME ON;

BACKUP DATABASE [WideWorldImporters] TO  
DISK = N'D:\DBAWork\WideWorldImporters-Full.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'WideWorldImporters-Full', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 100

BACKUP DATABASE [WideWorldImporters_encrypted] TO  
DISK = N'D:\DBAWork\WideWorldImporters_encrypted-Full.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'WideWorldImporters_encrypted-Full', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 100

SET STATISTICS TIME OFF;

Surprisingly, this one took less time with encryption enabled, around 15% less time... but wait! this is because of the number of pages processed by each backup operation, because if you check the average speed, you can see the encrypted backup operation is in fact, slower (around 33%), so in the case of this operation, it can vary for your environment:



Restore Database


If you plan to restore an encrypted database to a different server, remember that you must back up your master key and certificate and restore it on the new server in order to do it, otherwise you will not able to recover encrypted data.
Since now we are on the same instance, master key and certificate are already there, so we just run the simple RESTORE command:

SET STATISTICS TIME ON;

USE [master]
RESTORE DATABASE [WideWorldImporters] FROM  
DISK = N'D:\DBAWork\WideWorldImporters-Full.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 100

RESTORE DATABASE [WideWorldImporters_encrypted] FROM  
DISK = N'D:\DBAWork\WideWorldImporters_encrypted-Full.bak' 
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 100

SET STATISTICS TIME OFF;

As with the backup operation, the restore took less time for the encrypted database, but you can see that is because of the number of pages processed, because if you check the average restore speed, for the encrypted database is slower (167% slower), so also check this one for your own databases:



Rebuild indexes


We execute Rebuild index statements for random tables with the following T-SQL:

SET STATISTICS TIME ON;

USE [WideWorldImporters]
GO

Print '--- Normal DB ---'

ALTER INDEX [CCX_Warehouse_StockItemTransactions] ON [Warehouse].[StockItemTransactions] 
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)

ALTER INDEX [IX_Sales_OrderLines_Perf_20160301_01] ON [Sales].[OrderLines] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [UQ_Warehouse_StockItems_StockItemName] ON [Warehouse].[StockItems] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [PK_Application_People] ON [Application].[People] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [FK_Purchasing_PurchaseOrderLines_StockItemID] ON [Purchasing].[PurchaseOrderLines] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Print '--- END Normal DB ---'

USE [WideWorldImporters_encrypted]
GO

Print '--- Encrypted DB ---'


ALTER INDEX [CCX_Warehouse_StockItemTransactions] ON [Warehouse].[StockItemTransactions] 
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)

ALTER INDEX [IX_Sales_OrderLines_Perf_20160301_01] ON [Sales].[OrderLines] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [UQ_Warehouse_StockItems_StockItemName] ON [Warehouse].[StockItems] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [PK_Application_People] ON [Application].[People] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ALTER INDEX [FK_Purchasing_PurchaseOrderLines_StockItemID] ON [Purchasing].[PurchaseOrderLines] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Print '--- END Encrypted DB ---'

SET STATISTICS TIME OFF;

You can see the relevant execution results:




The encrypted database took around 24% more time (on average for all the operations) to complete.


Update Statistics

 

Now we execute update statistics with full scan on various tables with the following T-SQL:

SET STATISTICS TIME ON;

USE [WideWorldImporters]
GO

Print '--- Normal DB ---'

UPDATE STATISTICS [Warehouse].[StockItemTransactions] 
WITH FULLSCAN

UPDATE STATISTICS [Sales].[OrderLines] 
WITH FULLSCAN

UPDATE STATISTICS [Warehouse].[StockItems] 
WITH FULLSCAN

UPDATE STATISTICS [Application].[People] 
WITH FULLSCAN

UPDATE STATISTICS [Purchasing].[PurchaseOrderLines] 
WITH FULLSCAN

Print '--- END Normal DB ---'

USE [WideWorldImporters_encrypted]
GO

Print '--- Encrypted DB ---'

UPDATE STATISTICS [Warehouse].[StockItemTransactions] 
WITH FULLSCAN

UPDATE STATISTICS [Sales].[OrderLines] 
WITH FULLSCAN

UPDATE STATISTICS [Warehouse].[StockItems] 
WITH FULLSCAN

UPDATE STATISTICS [Application].[People] 
WITH FULLSCAN

UPDATE STATISTICS [Purchasing].[PurchaseOrderLines] 
WITH FULLSCAN

Print '--- END Encrypted DB ---'

SET STATISTICS TIME OFF;

And these are the results:




Even when some of the tables took less time, on average the encrypted database took around 15% more time to complete the statistics update. This should be other to have into account to check for your own database.


For this post we will not test any DML or SELECT statement, but you are free to do your own tests and determine if TDE implementation suits for you.

As we stated earlier, we have demonstrated that additional workload is put on TDE enabled databases in order to support the encrypt/decrypt operations. Now is is your turn to perform your own validations to see how much is impacted your own databases with this, since this can vary on different storage types (for example on fast SSD storages, it could be barely noticed).

Wednesday, October 3, 2018

Query to determine XE Session status

On a previous post, we discussed what is an extended event (XE) and how to create one.

With the following T-SQL you can quickly check what are the currently running Extended Events sessions:



SELECT 
 ES.name,
 iif(RS.name IS NULL, 0, 1) AS running
FROM sys.dm_xe_sessions RS
RIGHT JOIN sys.server_event_sessions ES ON RS.name = ES.name
--WHERE es.name = '<YOUR XE SESSION NAME>'



The column running will show 0 if the session is currently stopped and 1 if it is currently running.
In the commented WHERE clause, you can filter for a specific session.

Hope this helps you to monitor your XE sessions!

how to embed a PowerBI report into HTML code

PowerBI is a powerful reporting solution for Business Intelligence for analytical reports, it is not meant to replace SSRS, the focus of this tool is to be able to visualize data interactively from different data sources in a collaborative way.

On this post I will show you a quick way to embed your PowerBI reports into HTML code, that can help you to publish reports to websites.


For this, you will need a PowerBI Pro subscription (you can try it by free for 60 days)
Once you are logged to your PowerBI Pro account, select any published report you want to embed, as follows (for this example I will use a test report I have with imported dataset)

Generating HTML code for the report


Click on File and select Publish to web



A message to create an embed code will appear, click on it



A warning will also appear since your data will be public, click on publish



And that is all, now you have your HTML code to share it wherever you want.




What if you want to stop sharing it?


Maybe you should not reveal all the data from a report, or you want to stop sharing it, you must delete the embedded code to achieve it.

In your PowerBI site, click on Settings> Manage Embed codes



In the next page, click on (...) and click on delete




Published report


This is the final result of the published report:


Tuesday, September 11, 2018

Pride and Prejudice... and Triggers

https://voiceofthedba.com/2018/09/03/t-sql-tuesday-106-trigger-headaches-or-happiness/
This post is part of #tsql2sday event :)
I know, in the world of the database administrators and developers, the triggers are the ugly duck of the database development.

They are hard to track, hard to maintain, can have a weird transaction behavior if you are not careful and can lead to blocking your entire database if they are poorly programmed (yes, this is the prejudice).


But as anything, if treated well and programmed carefully they can be handy in a lot of special situations, but read again... I put SPECIAL SITUATIONS, let me tell you 2 histories I have with triggers, a good one and a bad one:


The Good


Some years ago, more than I want to admit, I was working on a local bank,  there was a requirement from the banking regulation authority, they ask each bank to report any currency exchange operation in near real-time (a maximum of 30 minutes to report an operation, I cannot remember exactly, but around these times) and the implementation time was some kind of short.

From a database design, there was a lot of tables involved from different types of banking transactions, also, for changing all the source code for all transactions and test all of them to provide a good solution could take a long time to complete, so instead, we choose to use triggers.

Since everybody wants their money, and want it fast, my solution was to just insert the raw data via triggers from all involved tables to a single staging table without performing any validation, so additional overhead can be avoided to any transaction. Then a second process scheduled to run every 5 or 10 minutes collects all the data from the staging table and perform the required validations and formatting, then the transactions were processed and sent to the reporting interface asynchronously.

This process executed for years and was deployed in a fairly quick time without disrupting the existing program logic.


The Bad


Because not everything goes as expected, in another job, we have to migrate a SQL Server from an old version to a newer one, and we had enough time to optimize/change stored procedures and user code to use new features and run faster on the new server.
Everything went smoothly during development and user testing... until we go live... We did not include in the release some triggers that were on some tables on the original server, these triggers perform some extra validation for some special cases on business transactions, since we optimize some of the tables and processes, just deploying the scripts won't work.

That time I spent almost 4 or 5 days living in the office, first solving the issues for already existent transactions and then trying to implement the trigger logic on the stored procedures itself, luckily our team could implement the special cases without using triggers and we weren't fired after that :)



As with the Jane Austin novel... several love/hate histories can be written about triggers, and the only way you can determine if you love or hate them, is via personal experience, get rid of the prejudice and give them a try, maybe they can work for you!

Monday, September 10, 2018

Different methods to generate query execution plans

One of the things I enjoy about my job is when I have to develop or review some T-SQL code, for my own projects or reviewing someones else code. In both cases, you often have to tune some queries or review them to see if there is room for some performance improvement.

An Execution plan is the method SQL Server engine will choose to execute (or would use if is an estimated plan) any given query based on the object indexes and statistics, server load, parameters, tables involved, operators, conversions, to provide the optimal execution time and the minimum resource usage. For human analysis, these are stored on .sqlplan or .xml extensions so you can save and analyze them later or on another machine from where it was generated.

For this type of analysis, you need to check execution plans, and it is important that you know how to generate and save them to be shared or for later analysis. On this post, we will discuss a wide range of the options you have to generate and save an execution plan.


1. Using SSMS


Just select the Include Actual Execution Plan (Ctrl +M) on the toolbar as follows:



To save the execution plan


Right Click on the execution plan and select Save Execution Plan As...





2. Using SQL Server Operations Studio


When executing a query select the Explain option at the right:




3. Using T-SQL: SHOWPLAN_TEXT


This method represents the execution plan in a text format, using rows in a hierarchical tree representation.

Useful when you don't want a graphical interface or you are using a command line tool.
To enable it, just execute the following statement at the beginning of your code, and set if off once done:


SET SHOWPLAN_TEXT ON;
GO

-- YOUR QUERY GOES HERE!
GO

SET SHOWPLAN_TEXT OFF;
GO

Please note that using this option, the query is not executed, only the execution plan is generated.
The results look something like this:





4. Using T-SQL: SHOWPLAN_ALL


Similar to the previous method, this one displays the execution plan in a text format, using rows in a hierarchical tree representation.

This option also does not execute the query and provide us with more detailed information than the showplan_text, also useful when using a command-line tool.

To use it, just use the following T-SQL code:


SET SHOWPLAN_ALL ON;
GO

-- Your query goes here!
GO

SET SHOWPLAN_ALL OFF;
GO

And these are the results:




5. Using T-SQL: SHOWPLAN_XML


This method generates the execution plan in an XML format (the .sqlplan files are stored as XML) and when generated from a graphical interface, like SSMS, if you click on it, it will display it graphically.

As with SHOWPLAN_TEXT and SHOWPLAN_ALL, this option does not execute the query, only generates the estimated execution plan.

To use it, just use the following T-SQL:


SET SHOWPLAN_XML ON;
GO

-- Your query goes here!
GO

SET SHOWPLAN_XML OFF;
GO

And the output looks like this:




The previous options are more suited when you have access to the actual query, and you are developing/tuning a particular statement, but if you need to generate plans for previous queries executed on the servers you have 3 options: DMVs, Extended Events, and QueryStore:



6. Using Dynamic Management Objects


Using the DMF: sys.dm_exec_query_plan, you can generate execution plans for any active session or sessions in the cache for your SQL Server instance, the output is given in an XML format that you can use in SSMS or save for later analysis.

This is a sample usage of the DMF using info from the cached plan with the DMV sys.dm_exec_query_stats, you can use another object if you want:


SELECT  
 ph.query_plan, 
 qs.* 
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) ph;

This is the output of the query:





7. Using Extended Events


In this post, I explain how to create an Extended Event, just create your session and select any event you want to track.

In the global fields to capture, just select the event Plan Handle:



With this field, you can use the DMF sys.dm_exec_query_plan to obtain the execution plan.

Note: this method only works for cached execution plans, otherwise the DMF will return NULL.
even when you can capture and persist execution plans out of the cache with extended events, this is a very resource intensive task that I don't recommend to do, that is why that method is out of scope on this post.




8. Using Query Store


Since this option is not available in all SQL Servers, it was introduced in SQL Server 2016 and also you have to enable Query Store, I left this option as the last one, but it is also important to know how to obtain an execution plan from it.

If you have Query Store already enabled on your database, just navigate to your database and select the Query Store folder, in there expand it and select any of the reports you want to analyze, for this example we will use Top Resource Consuming Queries:


The bars will show you the most resource-intensive queries in descending order, so just select any bar and you will see the current execution plan used at the bottom.

We will not discuss all the capabilities of Query Store on this post, but if you have more than one execution plan for a query, the different execution plans can be selected on the circles at the right side:





We have discussed a lot of different options you have to generate execution plans. In some later posts, we will use the info obtained to tune queries.



For further reading about execution plans, you can access this link:

https://www.red-gate.com/simple-talk/sql/performance/execution-plan-basics/


Wednesday, August 22, 2018

Format Query as JSON

JSON (JavaScript Object Notation) is a file format used to transmit data from various applications, very similar to XML, it also used to stored NoSQL unstructured data, and because of this versatility, Many REST applications and web services use it.

Since this is a standard file format, you should be able to generate data in JSON format from SQL Server.

Fortunately, since SQL Server 2016, there is a native way to do it, in this post I will show you how to do it.

Generate a simple JSON file


The most basic syntax to generate a JSON file is this:


SELECT <field list>
FROM <YourObject>
FOR JSON AUTO 

Using this simple test table with 10 records as an example:


SELECT TOP 10
 *
FROM [dbo].[MyTestTable]



If we use JSON AUTO on this sample data we will have the following output:


SELECT TOP 10
 *
FROM [dbo].[MyTestTable]
FOR JSON AUTO

Query executed
Part of the JSON AUTO output


Using dot syntax


For most of real-world applications, the JSON AUTO will not give you the control you could need over your file format, for having more control over it, you must use the JSON PATH option, along with the ROOT option as follows:


SELECT TOP 10
 id,
 dataVarchar,
 dataNumeric,
 dataInt,
 dataDate
FROM [dbo].[MyTestTable]
FOR JSON PATH, ROOT('TestTable')


This will generate the following output:

JSON PATH output


And if we want to group similar items (for example for queries with joins), just rename the fields as element.field as in this example:


SELECT TOP 10
 id,
 dataVarchar as [group1.D1],
 dataNumeric as [group1.D2],
 dataInt as [group2.E1],
 dataDate as [group2.E2]
FROM [dbo].[MyTestTable]
FOR JSON PATH, ROOT('TestTable')

Will generate the following output:

JSON PATH with grouped items

Of course, if you have SQL Server Operations Studio you can do it from the IDE:



If you want to learn more about the FOR JSON option, please read Microsoft official documentation here

Saturday, August 18, 2018

Performance Basics: Indexed views

Views help our query writing by simplifying writing the same sentences and/or aggregations over and over again, but it has a drawback, the views just store our query definition, but the performance is not improved by using them.

Since SQL Server 2008, the option to create an index over a view was introduced, of course, there are some limitations, but if your view can use them, the performance improvement could be great!

I will show you how to create a simple index over a view

Base query


I have created a test table called Aggregate_tbl1 with over 12,000,000 rows of random "money" data and random texts as well, as you can see in the following images:






with this table, let us proceed to make a simple aggregation query and create it inside a view:


CREATE VIEW [dbo].[Aggregate_tbl1_VI]
AS
SELECT 
 SUM(ValueMoney) AS total, 
 [Description] AS descr
FROM dbo.Aggregate_tbl1
GROUP BY [Description]

GO

If we execute this view with a single where and check the execution plan we will see something like this (no indexes):



we can see that an index scan and some computes are performed, so what happens if we just add a simple index in the base table over description and include the valueMoney field?

If we execute the query again, we can see some improvement, and index seek is performed now, but still, there is an aggregation operation costing 7% of the total operation and please take a look on how many rows are read (22397) to compute the total









Creating the view and the related index


We proceed to create an index over the view we created previously, of course, we have to drop it first, and then recreate it with the SCHEMABINDING option, and since we are using a GROUP BY, we also must include the COUNT_BIG(*) sentence:


DROP VIEW [dbo].[Aggregate_tbl1_VI]
GO

CREATE VIEW [dbo].[Aggregate_tbl1_VI]
WITH SCHEMABINDING
AS
SELECT
 COUNT_BIG(*) as [count_rows], 
 SUM(ValueMoney) AS total, 
 [Description] AS descr
FROM dbo.Aggregate_tbl1
GROUP BY [Description]

GO

Now we proceed to create an index over the view (as any other index)
Just note that the index must be a unique clustered one, that is because a group by is used in our example, to guarantee the uniqueness of the rows of the view:

CREATE UNIQUE CLUSTERED INDEX IX_IV_Aggregate_tbl1_VI_DESCR  
    ON [dbo].[Aggregate_tbl1_VI] (descr)
GO  

Now, let us proceed to execute the query again and see how it behaves:



We can see that now the newly created index is executed, and the aggregation operation lowered its cost, and from reading 22397 rows it decreased to 1 row, as we can see in the details:



With this simple example, we could see that we had a big improvement on the performance of the query, but as I always tell you, test any change on a dev server first!

If you need more information about the special requirements to create an index over a view, please visit the Microsoft official documentation here


Thursday, August 16, 2018

Announcing new Guatemala SQL Server user group



I am very happy to announce that after a long time struggling and filling all the requirements, we were able to create the SQL Server local user group for Guatemala.

The purpose of creating this group was to empower the local database administrators and developers by creating a community where all of us can benefit from the knowledge or personal experiences from any particular topic, this will become us better professionals and why not? have better jobs and salaries in the future.

We want to focus our community as a roundtable so any of the members (old and new) could be an assistant or speaker so we will encourage the participation of all the members.


What are the contact details?


You can access the main site at gtssug.pass.org and we encourage to register now!
The meetings and related content will be published in there.

Also follow us on social Media for news and database related content.

Twitter: @gtssug
Facebook: facebook.com/groups/gtssug

Who is leading this group?


This group was initiated by great professionals I have the privilege to met (and me!).
these are Carlos Robles, Christian Araujo, Carlos Lopez, and myself. You can learn more about us here.

Please note that we only are the coordinators, in fact, anybody can participate as a volunteer or speaker!


Where will be the meetings?


We want to thank Atos Guatemala for providing us a place to have our meetings, you can learn more about this awesome company here

Atos is located in a convenient place at Distrito moda in Ciudad Cayala, so there are no excuses for not assist to any meeting of your interest!

Paseo Cayalá Oficina 401,
Edificio H Distrito Moda,
Zona 16, Guatemala


And remember, please register here to receive the latest news and related material.
We want you to be part of this rising community!