Showing posts with label Basics. Show all posts
Showing posts with label Basics. Show all posts

Wednesday, January 13, 2021

Install Python on Windows


Python is the most famous development language today.

This is because is open-source, scalable and robust. It can run in almost any device with a processor (or microprocessor, we will see that in coming posts) and is very simple to use and learn, even if you don't have any programming backgroud or experience.

Start using it is very simple and you can learn the basics in the official site.

 

Installing Python

For windows systems, you can download the latest version here.

Once downloaded, open the executable file as administrator.

Then, for the uses we will give on the articles on this site, please install it this way:

  • Make sure you have selected the add Pyton to PATH option - This will allow us to run the Python engine from multiple sources (As is intended for use on multiple applications).
 
  • Make sure you install Python for all users - This will allow usto run the script commands.
 
  • Select PIP feature - This is used to install adittional libraries and functionalities, one of the main reasons for using Python and benefit from it.

 

Run the Installer as administrator and select the Customize installation option:

 
 
In my case since I am just upgrading, the install for all users option cannot be changed, but make sure to select it the first time you install it.

 
 
If everything is ok, you will see this message after the setup completes.


Test it!

Ok, but how do we actually use it? 

There are plenty of integrated development environments (IDE) for Python, depending on what are you planning to do with it.

But just to make sure is working ok, just open a CMD window and type python, then you can start using it right away.



Ok, once you have checked everything is working as expected, a good IDE to start working with Python right away is to use VSCode, you can download it here.

Then, add the Python extension.


 

Python files ends with the .py extension, so for VScode, just create any new file, and then save it as .py extension, the IDE automatically will load the Python extension and you can start working with it right away!

 


Happy coding! Stay tuned for the next things we will buid using Python.

 



Monday, March 30, 2020

Administration Basics: Point in time recovery

Nowadays, data is a precious asset for companies today. If you are a database administrator (by decision or by mistake) or simply you are the "IT guy," you have the mission of guarantee all the data is backed up and accessible for recovery.

Trust me, even when you could think you have the more reliable hardware on the planet, or you have multiple database replicas around the globe, anything can happen (a user deleting an entire schema by mistake, an application updating the wrong records, some process crashing, a lot of things can happen).
So trust me and don't question me, just backup all your databases regularly.

The Backup/restore strategy is a broad topic, so for this post, we will focus on the restore activity, so if you don't know how to back up a database yet, you can start here.


What do we need to know?

I like to think of the SQL backups like a pyramid, like this:

Pyramid of backups

For restoring your database to a particular time, you must perform a series of restores:
  • Full backup BEFORE the date you want. 
  • If available, the closest differential backup (this can be omitted if your diff backup is after the date you want or you don't have it).
  • Each T-log backup in order until the required date (we will see an example later).
It is essential to know that only the FULL database recovery model supports point-in-time recovery; with Bulk-logged, you are only allowed to restore the entire transaction log backup.

Set up our example

For this example, I have created a database named DB_RestoreDemo in SQL 2019. On it, I have a table called dates_demo with no relevant information, just a date and a varchar field for this example.

USE [DB_RestoreDemo]
GO

CREATE TABLE [dbo].[dates_demo](
 [id] [bigint] IDENTITY(1,1) NOT NULL,
 [date] [datetime2](7) NOT NULL,
 [text] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_dates_demo] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]

GO

I populate the table with some records (save it as we will use the same insert later):

USE DB_RestoreDemo
GO

INSERT INTO dbo.dates_demo
VALUES(GETDATE(),replicate('|',rand()*100))

GO 50

Then take a full backup:

BACKUP DATABASE [DB_RestoreDemo] TO  
DISK = N'C:\SQLServer\DB_RestoreDemo_full.bak' 
WITH COMPRESSION
GO

After that, execute the INSERT statement above, but replace the number after the GO with a significant number, so it takes a lot of time to complete, in my case I will execute it 10 million times:



While it is running, open a new query window and perform some Differential and Transaction log backups as you prefer (in any order and as many times you want), just remember to change the filenames each time to have them quickly identified:


-- Differential backup
-- Remember to change the filename each time

BACKUP DATABASE [DB_RestoreDemo] 
TO  DISK = N'C:\SQLServer\DB_RestoreDemo_diff_1.bak' 
WITH  DIFFERENTIAL, COMPRESSION
GO

-- Transaction Log backup
-- Remember to change the filename each time

BACKUP LOG [DB_RestoreDemo] 
TO  DISK = N'C:\SQLServer\DB_RestoreDemo_1.trn' 
WITH COMPRESSION
GO

Once you are happy with the backups taken, you can stop the INSERT query or let it run until it finishes... depends on you.

We have what we need for our example now.


Let's do it!


Open the table and select a random record, we will try to restore the database as close as possible to the date of that record.

SELECT * 
FROM dbo.dates_demo
ORDER BY [date];

I have selected this random record; we must guarantee that the last record of the table is this one (or very close in case the millisecond resolution include other records):
Id: 613834, date: 2020-03-30 01:49:50.5366667



Then, go to the folder where your backups are located, and check the modified date of the files, you have three options depending on which record you have selected:


  • Option 1: The time you want to restore is before the full backup taken: Congratulations! You know what data loss is now, good thing this is a demo. You cannot go to that point in time if you don't have the required backups.
  • Option 2: The date you want to restore is after any backup was taken: You can make another transaction log backup.
  • Option 3: The date you want to restore is in range, just locate the file with the closest creation date after the date you want.

In my case, is this file:

 

Based on the pyramid we saw before, this must be my restore strategy:


  1. Restore 1 FULL backup - DB_RestoreDemo_full.bak
  2. Restore 1 DIFF backup - DB_RestoreDemo_diff_1.bak
  3. Restore 1 Transaction Log backup - DB_RestoreDemo_2.trn

We will restore these backups to a new database DB_RestoreDemo_NEW, so we don't overwrite existing.

Please note that except for the last one, all restore operations must be done with the NORECOVERY option.

For the last transaction log file restored, we will use the STOPAT option; this option allows us to restore a database to a specific date (our example), a particular LSN, or a transaction mark (we will see that on another post).

NOTE: for this example, we used only INSERT statements without explicit transactions, but in a real-life scenario, the STOPAT option with a date parameter will restore the database to the latest committed transaction to that point in time.

 Once we have our restore strategy in place, we create the restore scripts:

USE [master]
GO

RESTORE DATABASE [DB_RestoreDemo_new] 
FROM  DISK = N'C:\SQLServer\DB_RestoreDemo_full.bak' 
WITH  FILE = 1,  
MOVE N'DB_RestoreDemo' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\DB_RestoreDemo_new.mdf',  
MOVE N'DB_RestoreDemo_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\DB_RestoreDemo_new_log.ldf',  
NORECOVERY;

GO

RESTORE DATABASE [DB_RestoreDemo_new] 
FILE = N'DB_RestoreDemo' 
FROM  DISK = N'C:\SQLServer\DB_RestoreDemo_diff_1.bak' 
WITH  FILE = 1, 
NORECOVERY;

GO

-- Because of the resolution on the decimals in datetime type
-- the increments are .003, .007 and .000, so the closest one
-- to the date we want .537 is .540
RESTORE LOG [DB_RestoreDemo_new] 
FROM  DISK = N'C:\SQLServer\DB_RestoreDemo_2.trn' 
WITH  FILE = 1, STOPAT = N'2020-03-30T01:49:50.540';

GO

 We navigate to the restored database, and if everything is ok, our selected record should be the last one (or very close to):



We have successfully restored a new database to the required point in time. A few things to have in mind:

  • You can specify the STOPAT option with a LSN or a transaction mark; this is useful if you have a critical process you can write your application to "mark" it for easy restore.
  • You just need the latest FULL and DIFF backup to the required date, but need all the TLog backups after that DIFF (or full if you don't have it).
  • In case the date you want to restore is included on a DIFF backup, you will need to ignore that DIFF backup and restore all the TLOG backups since the last FULL, including the first one after the DIFF backup.
  • For Bulk-logged recovery mode, you cannot perform point-in-time recovery, but you can perfectly restore log backups, but the entire content is restored.

And remember to always test your backups by restoring them regularly, with this you can achieve three things:
Confidence in the restore process, this will help you to remain calm under a stressful situation.
You can prepare the scripts you need to do it before anything bad happens.
You can estimate the time you need to do it, and check if that aligns with your RPO and RTO.

Friday, January 3, 2020

T-SQL Basics: using OUTPUT

You are working on a database development project, and you need to obtain the records before/after an INSERT, DELETE, UPGRADE or MERGE Statement to present it to the user or do any work with that data.


A common approach that I see a lot and involves a lot of code to obtain the before/after information can be the following (maybe some IF and TRY...CATCH also) :


DECLARE @id INT = 1;

SELECT PostalCode
    , ModifiedDate
FROM Person.Address a
WHERE a.AddressID = @id;

UPDATE Person.Address
SET PostalCode = 95012, 
    ModifiedDate = 'Jan 02 2020'
WHERE AddressID = @id;

SELECT PostalCode
    , ModifiedDate
FROM Person.Address a
WHERE a.AddressID = @id;

The results will be something like this (before and after):


And the execution plan will look like this, each extra SELECT statement adds more workload to our server (independently of the resources used) :



Even when the code is easy to read, but if you use this pattern over all your codebase, maintain it can become difficult if you have to change object names or implement it on another system.

T-SQL language provides the OUTPUT clause, that allows you to retrieve information from a DML statement in the same batch.

NOTE: OUTPUT clause will return rows even when the statement presents an error and is rolled back, so you should not use the results if any error is encountered.

Sample usage is as follows:

-- Values After INSERT INTO --
INSERT INTO <MyTable>
OUTPUT INSERTED.*
VALUES (@a, @b);

-- Values After INSERT FROM --
INSERT INTO <MyTable>
OUTPUT INSERTED.*
SELECT * FROM <MyOtherTable>;

-- Values Before/After UPDATE --
UPDATE <MyTable>
SET <myField> = @value
OUTPUT 
 DELETED.<myField> as [ValueBefore],
 INSERTED.<myField> as [ValueAfter]
WHERE id = @key;

-- Deleted records After a DELETE --
DELETE <MyTable>
OUTPUT DELETED.*   
WHERE id = @key;


If we refactor the first example to use OUTPUT we obtain this code:

DECLARE @id INT = 1

UPDATE Person.Address
SET PostalCode = 95012, 
    ModifiedDate = 'Jan 02 2020'
OUTPUT 
 DELETED.PostalCode, 
 DELETED.ModifiedDate, 
 INSERTED.PostalCode, 
 INSERTED.ModifiedDate
WHERE AddressID = @id

Less code and also easy to follow and maintain, and the results will be something like this:


What if we take a look at the execution plan? we can see that this time just one sentence is executed (as expected):


Of course, performance can vary depending on how many rows are you retrieving back to the user, current workload, index and table design that is very own to your environment, but the more you know, the better you can adapt each situation to achieve the optimal performance.

Speaking of which, what if we have a lot of fields or records to work with, it is possible to store the output results on another table? of course, you can, as we can see in this other example:

-- To review the correct data has been deleted
-- we can output to a temp table
DELETE
FROM Person1
OUTPUT DELETED.*
INTO PersonBackupInfo
WHERE EmailPromotion = 0;

-- we can review/recover data if needed
SELECT * 
FROM PersonBackupInfo;

We can implement a deleted data review/rollback logic to our application to protect it from user errors (the typical Ooops queries) and protect our databases from an unnecessary point in time restores.

Like everything in life, nothing is perfect, and there are some remarks you should consider to implement it on your applications, so be sure to read it before using it.

Thursday, July 4, 2019

SQL Engine basics: Caching data

SQL Server engine does an excellent job to optimize query performance and it has different methods to achieve this goal, one of these methods is the data caching.

Data caching consist of putting 8KB pages from storage into memory as they are needed, so the database engine can access the required data again in a much faster way.

At a glance, the way SQL Engine caches the data is the following:


When a request is made the engine checks if the data is already cached in memory (or buffer pool), if the data is already available, then it is returned. If the data is not available, then it is retrieved from storage and put into memory until the buffer pool is full.

How long the data is stored in the buffer pool depends on various aspects: the memory allocated to SQL Server, size of your database, workload, type of queries executed, In-memory OLTP.. etc.

When the buffer pool does not have enough space to allocate the desired memory, then a data spill occurs, it consists of additional data stored on tempdb, this is usually caused by inaccurate memory grants. Fortunately, in SQL Server 2019 Memory Grant feedback is being implemented, that can help to reduce this problem, you can read more about it here.

Since SQL Server 2014, you can also configure Buffer pool extensions, as the term suggests, this consist on an extension of the memory by using faster storage (usually SSD or Flash memory), this is an advanced feature and you can learn more about it in the Microsoft documentation.

Related T-SQL Commands


You have now a concept on what is the data caching and how it works, now let us see it in action.

we will use WideWorldImporters test database for this example, first we run some queries, does not matter what you execute for this example, can be any SELECT statement:

DECLARE  @RawValue decimal(10,2) = 25000

SELECT SUM(UnitPrice * Quantity) as RawValue
FROM Sales.InvoiceLines
GROUP BY InvoiceID
HAVING SUM(UnitPrice * Quantity) > @RawValue;

SET @RawValue = RAND()*1500

SELECT InvoiceID, InvoiceLineID, UnitPrice,Quantity
FROM Sales.InvoiceLines 
WHERE TaxAmount > @RawValue
OPTION(FAST 100);

After that we use the DMO sys.dm_os_buffer_descriptors to check what data is into memory, after making the query more readable we can use it like this:

--DETAILS BY DATABASE:
SELECT 
    IIF(database_id = 32767,'ResourceDB',DB_NAME(database_id)) AS [Database],
    file_id, -- This File is at database level, it depends on the database you are executing the query
    page_id,
    page_level,
    allocation_unit_id, -- can be joined with sys.allocation_units (database level DMO)
    page_type,
    row_count,
    read_microsec
FROM sys.dm_os_buffer_descriptors BD
ORDER BY [Database], row_count DESC;

Depending on the workload, we obtain something like this:


If you want something more compact, you can execute the query grouping it by database:

-- TO OBTAIN TOTAL BY DATABASE:
SELECT 
    IIF(database_id = 32767,'ResourceDB',DB_NAME(database_id)) AS [Database],
    COUNT(row_count) AS cached_pages
FROM sys.dm_os_buffer_descriptors 
GROUP BY database_id
ORDER BY cached_pages DESC;

We obtain the results grouped by database:



In order to release the data from the cache, you run the DBCC DROPCLEANBUFFERS command:

DBCC DROPCLEANBUFFERS;

NOTE: Avoid running this command in production environments, since all the data will be released from memory and must be pulled from the storage again, incurring in additional IO usage.


If you have Buffer pool extension enabled and want to check their options, or if you want to determine if the instance has it, use the sys.dm_os_buffer_pool_extension_configuration DMO as follows:

-- Check if you have a buffer pool extension enabled:
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

The results are something like this (I don't have a buffer pool extension enabled on my machine):



If you want the script for the commands we discussed, you can download it from my GitHub repository.

Wednesday, March 13, 2019

Quick tip: Zoom in Azure Data Studio

If you finally have given a try to Azure Data Studio, and if you use it on a regular basis, maybe you want to customize it to suit your needs.

Among the huge customization options it has, you can control the text size in form of zoom. To do change it, just use the following keyboard combinations:

  • (Ctrl + = ) For Zoom in.
  • (Ctrl + - ) For Zoom out.
  • (Ctrl + 0) For Zoom reset.
 You can see it in action:


Also, as with everything on this tool, you can access this functionality from command palette (Ctrl + Shift + P), and type "zoom" (you can fine-control the editor zoom from here):


If you haven't tried it yet, you can download Azure Data Studio here.

Tuesday, February 5, 2019

I am speaking at SQLSaturday Guatemala 2019




I’m very thrilled to announce that I will be participating as speaker in this year’s SQL Saturday #828 event in Guatemala city!
This will be my first time as a speaker on a SQLSaturday.

Event will take place on February 16 at Universidad Francisco Marroquin, Calle Manuel F. Ayau (6 Calle final), zona 10, Guatemala

Here are the details of the session I will be presenting (at 3:15 PM CST in Dev Room)

T-SQL Basics: Coding for performance


It is very common in the IT field for a developer to switch to database developer or administrator, even when the programming concepts are the same, the skillset required to code T-SQL is different.
In this session, we will learn some basic tips to improve our code and improve database performance from early application planning stages to already deployed applications.

We will also see some demos about:
  • Compatibility level and deprecated features
  • Filtering basics: SARGABLE arguments
  • Covering indexes
  • Indexed views
  • Implicit conversions
  • Memory Grants
  • Joining records with NULL 
  • DMOs to find top resource intensive queries
  • Collation: considerations when working with multiple databases

I will show you execution plans using an excellent tool called Plan Explorer from SentryOne, best thing is that is free.
You can download it from here


As any SQL Saturday event organized by SQLPass you can register for free, it takes less than 5 minutes to get in and sign up.:

https://www.sqlsaturday.com/828/registernow.aspx


Hoping to see you there!

Wednesday, January 30, 2019

Understanding and working with NULL in SQL Server

Graphic representation of the difference between 0 and NULL
Image taken from 9gag.com
According to database theory, a good RDBMS must implement a marker to indicate "Missing or inapplicable information".

SQL Server implements the lack of value with NULL, that is datatype independent and indicates missing value, so the logical validations are different, this is better known as Three-Valued Logic, where any predicate can evaluate to True, False or Unknown.

I see a common error, referring to null like "Null values" but the correct definition of null is "Lack of value" so you must refer to it as null, in singular form.

On this post, we will learn how to work with null in SQL Server.

Declaring and assigning NULL


For working with null, SQL Server engine uses the reserved word NULL to refer to it.
It is datatype independent so you just have to assign it to any variable or field, using the equal operator =, as you can see on this example:

DECLARE @NVi as int = NULL;
DECLARE @NVc as nvarchar(30) = NULL;
DECLARE @NVv as sql_variant = NULL;

SELECT @NVi, @NVc, @NVv;

If we run the statement, we will obtain these results, the same for each data type, as expected.



For inserting and updating fields with NULL we do it like on this example:


-- For inserting values
INSERT INTO test1..Table_1 (column_char,column2)
VALUES(NULL, NULL);

-- For updating values
UPDATE test1..Table_1
SET column2 = NULL;

Be careful when working with null, the equal operator = is only used for assignment.

Operations and comparison against NULL


As we stated earlier, any predicate or comparison can evaluate to TRUE, FALSE or UNKNOWN, so when a value is unknown we don't know if it is true or false, so comparing or working any value with unknown is also unknown.

For example, the following operations result is NULL in all cases:

--Arithmetic operations
SELECT NULL+5,NULL-3.47, NULL*3.1416, NULL/0; 

SELECT SQRT(NULL), POWER(NULL,2);

--String operations
SELECT 'HELLO ' +NULL + 'WORLD';

SELECT QUOTENAME(NULL);

SELECT LTRIM(NULL);

--Date operations

SELECT DATEADD(m,1,NULL);

SELECT DATEDIFF(m,GETDATE(),NULL);

When comparing to null, we also obtain null as well, as in those examples, as you can see, even comparing null to null is unknown, and when we execute below code, we obtain NO for all:


--comparing to 0
IF(0= NULL) OR (0 <> NULL)
 SELECT 'YES'
ELSE
 SELECT 'NO' 

--Comparing to empty string ''
IF(''= NULL) OR (''<> NULL)
 SELECT 'YES'
ELSE
 SELECT 'NO' 

--Even comparing to another null
IF(NULL= NULL) OR (NULL<> NULL)
 SELECT 'YES'
ELSE
 SELECT 'NO' 

So, if we want to compare value or column and check if is null or not what must we do?
SQL Server implements the IS NULL and IS NOT  NULL to compare against null, usage is as follows:


-- IS NULL usage

SELECT *
FROM test1..Table_1
WHERE column2 IS NULL;


-- IS NOT NULL usage

SELECT *
FROM test1..Table_1
WHERE column_char IS NOT NULL;


-- Using on IF construct

DECLARE @NVi as int = NULL;

IF(@NVi IS NULL)
 SELECT 'YES'
ELSE
 SELECT 'NO';


-- For Replacing NULL you can use
-- ISNULL Value since SQL 2008

SELECT ISNULL(@NVi,0);


With these tools we are ready to work with null in our databases, so now you should follow some considerations to not impact your database performance.

Special considerations for good performance


As the last point, I would like to give you some tips for dealing with NULL

Prefer IS NULL over ISNULL()


When possible, try to compare predicates using IS NULL, before casting NULL to default values using ISNULL(), because casted values are not SARGABLE.

Take as an example of these two queries, they are equivalent, but the first one has better performance over the second:


-- First query uses an index seek :)
SELECT FirstName
      ,MiddleName
      ,LastName     
FROM Person.Person
WHERE MiddleName = N'' 
 OR MiddleName IS NULL;

-- Second query uses an index scan :(
SELECT FirstName
      ,MiddleName
      ,LastName     
FROM Person.Person
WHERE ISNULL(MiddleName,N'')=N'';

These are the execution plans:

First query execution plan, an index seek is used :)

Second query execution plan, an index scan is used :(

We get a warning on the second execution plan
You can see the 2 differences on the 2 plans, so for this case, we prefer to stick to the first one, even if you must write more code.

Be careful with aggregations over nonexistent data


When you perform aggregations, be extra careful with no existent data, even when columns do not allow null, aggregate data that does not exist on the table returns null, contrary to what one could think can be the usual (a 0 value), as you can see on this example:


-- Even when TotalDue field does not allow NULL, 
-- the SUM of noexistent values is NUll, not 0 as one could think

SELECT SUM(TotalDue) as [Total Due]
FROM Sales.SalesOrderHeader
WHERE DueDate > GETDATE();

And the query results:


For those cases, you should use the ISNULL() function after SUM.

As I always recommend: test anything before going live, and use default values and not null columns when possible, to make your life easier.


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

How I Built RAG for My Accounting App on Bedrock and pgvector

The first version of my accounting assistant could not find a single purchase of "comida" in a database full of lunches, misse...