T-SQL Ninja #28

SHOWPLAN

What does SHOWPLAN mean?

If you’ve ever wondered what SQL Server is doing between the time you pressed F5 in Azure Data Studio with a cold sweat on your forehead and the time you got your query results, then SHOWPLAN is the solution for you. One warning first: reading execution plans is a science in itself and there are many who have devoted themselves to this science with great dedication and who can do it much better than your Shogun here (we bow our heads in awe towards the great Ricken-San). So don’t expect great insights here, but rather an overview of what is available. So to speak the entrance of the rabbit hole.

How can you work with SHOWPLAN?

To see the different plans and plan options, we first write a query on our WideWorldImporters database:

SELECT 
       [StockItemID]
      ,[StockItemName]
      ,[SupplierName]
      ,[Brand]
      ,[Size]
      ,[LeadTimeDays]
      ,[QuantityPerOuter]
      ,[IsChillerStock]
      ,[TaxRate]
      ,[UnitPrice]
      ,[RecommendedRetailPrice]
      ,[TypicalWeightPerUnit]
FROM [Warehouse].[StockItems] i
    LEFT JOIN [Purchasing].[Suppliers] s 
    ON i.SupplierID = s.SupplierID
WHERE StockItemID = 200

This query returns exactly one line. Now let’s find out how the SQL Server finds this row. For this we want to have a look at the execution plan of the query. This can be obtained by activating the SHOWPLAN_XMLoption (and ideally deactivating it immediately after the query), which looks like this

SET SHOWPLAN_XML ON;
GO

SELECT 
       [StockItemID]
      ,[StockItemName]
      ,[SupplierName]
      ,[Brand]
      ,[Size]
      ,[LeadTimeDays]
      ,[QuantityPerOuter]
      ,[IsChillerStock]
      ,[TaxRate]
      ,[UnitPrice]
      ,[RecommendedRetailPrice]
      ,[TypicalWeightPerUnit]
FROM [Warehouse].[StockItems] i
    LEFT JOIN [Purchasing].[Suppliers] s 
    ON i.SupplierID = s.SupplierID
WHERE StockItemID = 200;

GO
SET SHOWPLAN_XML OFF;
GO

In the results window you will now see a graphical representation of the execution plan (in SQL Server Management Studio you will first see a result with an XML cell, if you click on it the graphical representation will open, in Azure Data Studio the graphical representation will open immediately. This representation shows that a Clustered Index Seek operation is executed on both tables, that is, that the system searches for rows in the Clustered Index of the respective table and that these rows are then merged in a Nested Loop. So far so good, but if you want to view this plan in more detail or save it (in whole or in part), the XML representation is very unwieldy despite the supported XML datatype. So let’s see what other options we have. Instead of outputting the plan as XML, we can also output it textually in table form by enabling SHOWPLAN_ALL instead of SHOWPLAN_XML:

SET SHOWPLAN_ALL ON;
GO

SELECT 
       [StockItemID]
      ,[StockItemName]
      ,[SupplierName]
      ,[Brand]
      ,[Size]
      ,[LeadTimeDays]
      ,[QuantityPerOuter]
      ,[IsChillerStock]
      ,[TaxRate]
      ,[UnitPrice]
      ,[RecommendedRetailPrice]
      ,[TypicalWeightPerUnit]
FROM [Warehouse].[StockItems] i
    LEFT JOIN [Purchasing].[Suppliers] s 
    ON i.SupplierID = s.SupplierID
WHERE StockItemID = 200;

GO
SET SHOWPLAN_ALL OFF;
GO

This form of representation is better in applications where the XML cannot be converted directly into a graphical representation as in SSMS or Azure Data Studio. You can activate an even simpler representation with fewer columns in the result set using the SHOWPLAN_TEXT option. Here you will receive the original statement in addition to the textual representation of the individual steps in the execution plan, which can be very helpful under certain circumstances.

So far this is quite nice, but what does it all mean? To get an idea of what is happening here, we change our query a bit and search for a part of the article name instead of an ID:

SET SHOWPLAN_XML ON;
GO

SELECT 
       [StockItemID]
      ,[StockItemName]
      ,[SupplierName]
      ,[Brand]
      ,[Size]
      ,[LeadTimeDays]
      ,[QuantityPerOuter]
      ,[IsChillerStock]
      ,[TaxRate]
      ,[UnitPrice]
      ,[RecommendedRetailPrice]
      ,[TypicalWeightPerUnit]
FROM [Warehouse].[StockItems] i
    LEFT JOIN [Purchasing].[Suppliers] s 
    ON i.SupplierID = s.SupplierID
WHERE StockItemName like '%USB%';

GO
SET SHOWPLAN_XML OFF;
GO

Here you can see that the execution plan has changed, where before there were two Clustered Index Seeks (a direct search in the B-tree of the index) there are now Clustered Index Scans, the index is scanned (searched) in its full width. To detect this, we activate the IO statistics output (as shown in week 9) and compare the output of both statements:

SET STATISTICS IO ON

SELECT 
       [StockItemID]
      ,[StockItemName]
      ,[SupplierName]
      ,[Brand]
      ,[Size]
      ,[LeadTimeDays]
      ,[QuantityPerOuter]
      ,[IsChillerStock]
      ,[TaxRate]
      ,[UnitPrice]
      ,[RecommendedRetailPrice]
      ,[TypicalWeightPerUnit]
FROM [Warehouse].[StockItems] i
    LEFT JOIN [Purchasing].[Suppliers] s 
    ON i.SupplierID = s.SupplierID
WHERE StockItemName like '%USB%';

SELECT 
       [StockItemID]
      ,[StockItemName]
      ,[SupplierName]
      ,[Brand]
      ,[Size]
      ,[LeadTimeDays]
      ,[QuantityPerOuter]
      ,[IsChillerStock]
      ,[TaxRate]
      ,[UnitPrice]
      ,[RecommendedRetailPrice]
      ,[TypicalWeightPerUnit]
FROM [Warehouse].[StockItems] i
    LEFT JOIN [Purchasing].[Suppliers] s 
    ON i.SupplierID = s.SupplierID
WHERE StockItemID = 200;

SET STATISTICS IO OFF

In this case, we see that the first query performs a scan with 42 or 16 read pages on our tables, while the second query performs a sea with two read pages each.

For the details about Seeks, scans and execution plans, we refer to many very good lectures of very good Shoguns and their books. There is so much to learn here, even for ninjas like us.

References

Ninja-Notebooks @ GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *