Developer Friendly T-SQL Enhancements in SQL Server 2016 and vNext

Both SQL Server 2016 and vNext versions have introduced many new features. T-SQL language too gets new features in each SQL Serer versions. Below two enhancements to T-SQL language is very important for DBAs and Database Developers who writes T-SQL heavily.

1. CREATE OR ALTER

You can create SQL Server objects like below by using CREATE OR ALTER statement.

    CREATE OR ALTER PROC dbo.TestProc

    AS

    BEGIN

       SELECT @@VERSION

END

This way, you can create or alter stored procedure, function, view, and trigger.  However, this does not apply for table object.  This saves developers time tremendously   because it avoids numerous validation steps.

2. DROP IF EXISTS

The new DROP EXISTS statements also avoids some conditional logic that you need to include to check the existence of the object before it drops.

DROP PROC IF EXISTS dbo.TestProc

This statement works with many SQL Server objects. See the below figure;

Source: MSDN

So if you’ve SQL Server 2016 or vNext in your environments, you can start using these new features right away.

Cheers!

R Integration in SQL Server 2016

R is a popular programming language that is used in data science to analyse data. R has capabilities for data analytics. Data analytics needs special data structures such as arrays, vectors, matrix. Traditional RDBMS products do not have capabilities for data analytics because they do not have such data structures stated above. 

The latest release of MS SQL Server 2016 has R integration so that data processing and data analytics can be done in the same data management platform. 

This blog post highlights R integration in SQL Server 2016 by using a simple example. 

Let's assume you have a requirement to generate sequence of numbers. There are various methods you can do in SQL Server using T-SQL.  The typical solution would be using a WHILE loop and few control variables which is mentioned below;

There is an interesting  msdn forum question which has many alternative ways to achieve this. It is worth to read the discussion. 

The same thing can be accomplished using R in a very simple way. R has a function named seq, and below code snippet shows R seq to generate a sequence of numbers from 1 to 10. You can use RStudio to use full R functionalities. 

> seq(1, 10)

 [1]  1  2  3  4  5  6  7  8  9 10

Now, lets see how we can integrate the above simple R function with SQL Server 2016. First you need to enable R integration in SQL Server 2016. 

I had to restart SQL Server service after executing the above code which should not be the case. Below is the version of the SQL Server that I'm using for this demo. 

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)

I had to restart SQL Server Launchpad service too which is a new service introduced in MS SQL Server 2016. 

Then you can use sp_execute_external_script programming interface to call an external script written in R or in other language which recognized by SQL Server. 

Below is the output of the above code. 

There are many complex data analytics work you can do in R and the same thing you can do inside SQL Server by combing the data stored in SQL Server. 

Cheers!

ALTER PARTITION FUNCTION Causes Blocking?

Recently I’ve experienced a situation where one of the partition maintenance jobs created a blocking. At this point the database had high t-log usage and backup log was also executing. It was clear some kind of large transaction has occurred during a partition maintenance operation. All inserts into the table which was being partitioning, got blocked too. 

MSDN states:

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

ALTER PARTITION FUNCTION (Transact-SQL)

So it is important to select a proper partition key so that when achieving using sliding window concept, it works without impacting users.

Cheers.

How To Find Object Name For “wait_resource”

When querying sys.dm_exec_requests dmv, you can see the waiting requests and the resource those requests are waiting for. Sample of such values are shown in Figure 1 below.

Figure 1 – List of wait resources

How do you interpret these values? It has the following format in this case: (It is not always the same format. Depending on the values you see in wait_resource the interpretation would be different)

[database id] : [file id] : [page id]

However still the information is not sufficient because knowing the page id does not give much details related to the issue your troubleshooting. So we need to figure it out the table related to the above page. For that you need to use some undocumented DBCC commands. (Please be careful when running them in production system)

DBCC TRACEON (3604)
GO
DBCC PAGE (5, 20, 56792898)
GO
DBCC TRACEOFF (3604)

You get an output similar to below;

Figure 2 – Output of DBCC PAGE command

Refer the objectId value. That is the object Id of the page belongs to. After that it is simple to find the object name using OBEJCT_NAME() T-SQL function.

Cheers.

Identifying the root blocker and the blocking chain

As a DBA, identifying blockings and waitings is one of the primary tasks. I searched in the Google but did not find a useful script. So I came up with one and thought to share it with others.

;WITH requests (session_id, start_time, status, blocking_session_id, 
      database_name, 
      command, sql_text) 
     AS (SELECT session_id, 
                start_time, 
                status, 
                blocking_session_id, 
                Db_name(database_id), 
                command, 
                sql_text = Cast(text AS VARCHAR(max)) 
         FROM   sys.dm_exec_requests WITH (nolock) 
                CROSS apply sys.Dm_exec_sql_text (sql_handle) 
         WHERE  status <> 'Background'), 
     blocking (session_id, start_time, status, blocking_session_id, command, 
     sql_text, rownum, levelrow) 
     AS (SELECT r1.session_id, 
                r1.start_time, 
                r1.status, 
                r1.blocking_session_id, 
                r1.command, 
                r1.sql_text, 
                Row_number() 
                  OVER ( 
                    ORDER BY r1.session_id), 
                0 AS LevelRow 
         FROM   requests r1 
                INNER JOIN requests r2 
                        ON r1.session_id = r2.blocking_session_id 
         WHERE  r1.blocking_session_id = 0 
         UNION ALL 
         SELECT r3.session_id, 
                r3.start_time, 
                r3.status, 
                r3.blocking_session_id, 
                r3.command, 
                r3.sql_text, 
                b.rownum, 
                b.levelrow + 1 
         FROM   requests r3 
                INNER JOIN blocking b 
                        ON r3.blocking_session_id = b.session_id 
         WHERE  r3.blocking_session_id > 0) 
SELECT * 
FROM   blocking 
ORDER  BY rownum, 
          levelrow 

Alternatively you can write the output of this to a table as a schedule job and the results can be analyzed later. This is really useful to identify any blocking patterns of the database server and fix them if they persist.

Stored procedure taking long time to execute

This can happen due to many factors like lack of indexes, out of dates statistics, if it returns a large number of records, etc.

I observed a similar behavior recently in a production environment. The stored procedure had the code similar to the following;

    SELECT Col3   
    FROM dbo.Tab1  
    WHERE  Col1 = @Col1 
    AND Col2 = @Col2  
    AND RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(CAST(Comments AS VARCHAR(2000)),'<br />',''),CHAR(10),''),CHAR(13),''),'&nbsp;',''))) <> ''  

Let me give you some background information about this table.

• Col2 and Col3 are UNIQUEIDENTIFIER
  
• Comments column is ntext and does not have indexes
  
• All three columns Col1, Col2 and Col3 has indexes
  
• Col2 and the PK is the clustered index of the table
  
• Table has 825,452,256 records (825 million)

The FORMAT function

Recently one of colleagues asked, how to format a date value according to the style he wants. His date style is as below;

10/15/13 - 04:11 pm

The immediate solution that came into my mind was the CONVERT function which you can specify the conversion style. He had already worked out a solution which using that function and the code is as below;

SELECT LEFT(Lower(CONVERT (VARCHAR, CURRENT_TIMESTAMP, 1) + ' - ' + RIGHT( 
       CONVERT ( 
       VARCHAR, CURRENT_TIMESTAMP, 100), 8)), 17) 
       + ' ' 
       + Lower(RIGHT(CONVERT (VARCHAR, CURRENT_TIMESTAMP, 100), 2)) AS 
       "Date/Time" 

However it is bit of complex string concatenation. Wouldn’t there be any simple way of doing it? So after bit of research I found the following code which does the exact same thing that my colleague wants.

DECLARE @d DATETIME = CURRENT_TIMESTAMP; 

SELECT Lower(Format(@d, 'MM/dd/yy - hh:mm tt')) AS 'Date/Time' 

Using FORMAT function, you can format numeric and date values. It also supports the culture. The syntax is as below;

FORMAT ( value, format [, culture ] )

Identifying slow running queries

This post is for SQL Server Database Administrators who have find difficulty in some situations when identifying slow running queries. The DBAs who work in production environment specially might have faced situations where users or other production monitoring teams complaining that suddenly they see a slowness in their systems. The “slowness in the system” a very generic term. This could be due to various reasons. Such as;

• Network issue
• Application server issue
• Database issue

As a DBA, it is a responsibility to make sure everything is clear in database perspective. Before start troubleshooting the broader phenomena, it is necessary to ask few questions to shed some light into the dark area. Below questions might be useful;

“Table Alias”, How it behaves?

One of my colleagues asked me a question about a simple T-SQL query which uses table alias. See the below T-SQL code;

USE AdventureWorks2012
GO

SELECT E.loginID,HumanResources.Employee.JobTitle FROM HumanResources.Employee E

The above query used a table alias “E” and in SELECT list one column refers with table alias while the other column referring  full table name.  Seems like technically correct query. However the query returned the following error.

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "HumanResources.Employee.JobTitle" could not be bound.

As per the error message, use of table name, “HumanResources.Employee” to refer JobTitle column is incorrect. When you remove HumanResources.Employee in the SELECT list then the query works fine.

The theory behind this is when you have a table alias, it logical rename the table to table alias. So if you want to refer the table in the query, it needs to use table alias instead of the actual table name.

Relational algebra explains this more clearly.

The relevant operator for table alias in relational algebra (RA) is RENAME. As SQL derived from RA it is always better to learn RA before learning SQL.

How to get database sizes

There are several ways to get database sizes in a server. Following three system tables has the information to get database sizes.

sys.sysfiles

sys.database_files

sys.dm_db_file_space_usage

You also can use following system stored procedure.

exec sp_spaceused

However the easiest way is to use SP_HELPDB system stored procedure. Below script used that SP to get the database sizes in a server.

create table #spdbdesc

(
    dbname sysname,
    dbsize        nvarchar(13) null,
    owner sysname null,
    dbid    smallint primary key,
    created nvarchar(11),
    dbdesc    nvarchar(600)    null,
    cmptlevel    tinyint
)


INSERT INTO #spdbdesc
exec SP_HELPDB

SELECT *
FROM #spdbdesc

Test data generator using T-SQL

Some times DBAs want to generate test data to test some applications designed and developed by themselves. I have faced many situations like this in past. Recently also I had to generate some test data to test one of my research projects. Below is the ERD of the application. It is just four tables.

1. Customer ( master data)
2. Item (master data)
3. SalesOrder (Sales order header data)
4. SalesOrderDetail (Sales order detail data)

Currently executing queries

It is important to know what is currently executing in SQL Server at any given time. You may need this for troubleshooting purposes. The below query is useful in such occasions;

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
s.login_name,
s.host_name,
req.wait_type,
req.wait_resource,
SUBSTRING(sqltext.text, (req.statement_start_offset/2)+1,
        ((CASE req.statement_end_offset
          WHEN -1 THEN DATALENGTH(sqltext.text)
         ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) AS statement_text,
req.plan_handle,
sqltext.text
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions s
    ON req.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
ORDER BY req.cpu_time DESC

sp_attach_db / sp_detach_db are deprecated

These two system stored procedures are used frequently by DBAs in general administrative work. However these are marked as deprecated so they will be removed from future versions of SQL Server. I checked in MSDN and from SQL Server 2005 these are marked as deprecated. Interestingly they still available in SQL Server 2012 release as well. However it recommends not use them for any development work and use the new method using CREATE DATABASE statement. 

sp_attach_db, sp_detach_db


Attaching a database using CREATE DATABASE;


CREATE DATABASE Archive
      ON (FILENAME = 'D:\SalesData\archdat1.mdf')
      FOR ATTACH ;
GO

SQL Server 2012 Tutorials and Sample Databases

Microsoft SQL Server 2012 has released RTM recently. No doubt that you're interested to learn new features of the new version. If so then it is useful to have the tutorials and sample databases installed on your server. You can get them using below links;

Tutorials here

Sample Databases here

Note that sample databases are just data files (No log file). You need to attach it to your server to create the databases. Use the following T-SQL script.

CREATE DATABASE AdventureWorks2012

ON (FILENAME = 'D:\SQL Server\DATA\AdventureWorks2012_Data.mdf')

FOR ATTACH_REBUILD_LOG

Enjoy with the new version of SQL Server.

How to get the execution plan of a stored procedure?

Think about a situation where you have identified a badly performing stored procedure in Production environment and now you want to see the execution plan of it for further analysis. Please note, executing the procedure with sample parameter values in Production environment should be done with extra care because if the procedure is only SELECT then no issue but if it has DML operations, then issue.

You can use following DMVs to get the execution plan either XML format or graphical one.

USE AdventureWorks2008R2

EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID=60

Note: I executed the SP first so that plan will be created. In actual environment this is not necessary.

select Plan_handle from sys.dm_exec_procedure_stats ps

where ps.database_id=DB_ID('AdventureWorks2008R2')

              and object_id=object_id('dbo.uspGetEmployeeManagers')  

--Copy and paste the plan handle to the below query

select * from sys.dm_exec_query_plan(0x05000500EAE8807F40E13287000000000000000000000000)

Cheers for reading this blog post.

Parameter Sniffing

What is it? First we will go through the scripts below;

Create the following index;

USE [AdventureWorks2008R2]

GO

/****** Object:  Index [NNI_SalesOrderDetail_ModifiedDate]    Script Date: 03/06/2012 15:09:41 ******/

CREATE NONCLUSTERED INDEX [NNI_SalesOrderDetail_ModifiedDate] ON [Sales].[SalesOrderDetail]

(

     [ModifiedDate] ASC

)

Then create the below stored procedure. It is a very simple SP which has single SELECT statement for Sales.SalesOrderDetail table. There are two parameters for two date values.

Geospatial data support in SQL Server

I wanted to do some research on geospatial data support in SQL Server after attending Refresh Colombo July meeting.

There Janith did a small presentation on how to finding a bus route in Colombo. Here is his blog explaining how he has done it. 

He has implemented the solution based on MYSQL database and I’m not quite sure about the database design used for the application.

This tended me to look at how we can implement such a thing in MS SQL Server and did some googling around the geospatial data support.

How to identify objects used in a stored procedure

Recently there was a requirement from a project team to identify all objects belong to a stored procedure. Actually they wanted only the tables used in a stored procedure.You can use sp_depends with the SP and then you will be able to get all the dependent objects. But the actual requirement was much more complex than that because they wanted to analyze a trace they created for all the SPs captured in the trace.

Sp_depends and sys.sysdepends are provided in SQL Server 2008 for backward compatibility purpose only. So not recommending to use in new developments.

Refer below links for more information.


sp_depends


sys.sysdepends

Query best practices

In this blog I'm stating several best practices when writing SQL queries in MS SQL Server. They are;

• Choose the most appropriate data type
• Write correctly formed queries
• Return only the columns/rows needed
• Fully qualified object names
• Avoid long actions in triggers
• Avoid expensive operators such as NOT LIKE
• Minimize cursor use
• Limits query and index hints
• Avoid implicit or explicit functions in WHERE clause
  

More about ORDER BY clause

Every one knows the purpose and the use of ORDER BY clause in SQL Server, but most may not know how ORDER BY works, in-side SQL Server. The implementation of T-SQL is based on set theory. Each table is considered as a set. Let’s take an example of Customer and Order tables. They are actually considered as two sets. The records of the table are identical to members or elements in sets. The T-SQL statements will not work as sequential language execution. Consider the T-SQL statement below.

use AdventureWorks

GO

SELECT TOP 100 SH.CustomerID,SH.SalesOrderNumber,SD.OrderQty,SD.UnitPrice FROM Sales.SalesOrderHeader SH

INNER JOIN Sales.SalesOrderDetail SD

ON SH.SalesOrderID=SD.SalesOrderID

ORDER BY SH.CustomerID

When you execute the query above, it will not execute sequentially within the SQL Server. Even though the SELECT statement is appeared first it will not execute as the first statement. Instead it will execute at a later part of the query (The execution order of a T-SQL query is beyond the scope of the article). During execution, T-SQL query will go through several virtual tables. The final result set of the query also displays as a virtual table.

But when you include the ORDER BY clause in your query the final result will not be a table, instead it returns as a cursor output. Because, a set does not have predetermined order of its members. It is a logical collection of members and the order of the members should not matter. In other words you can not perform an ORDER BY in a set simply by using set concepts. This is the reason the SQL Server uses cursors for this operation. ORDER BY is a costly operation to SQL Server (The use of cursors is anyway a costly operation).

Consider the T-SQL query below.

--Query1

USE AdventureWorks

GO

SELECT SalesOrderID,OrderQty,ProductID,UnitPrice FROM Sales.SalesOrderDetail

ORDER BY SalesOrderID

GO

Look at the execution plan of this query.

You may wonder why the execution plan does not contain the SORT operator. The obvious reason is SalesOrderID is the clustered index of the table, Sales.SalesOrderDetail which means the data is physically ordered by using the key of SalesOrederID column. So there will not be any additional cost for this sorting.

Let’s look at the query below;

--Query2

USE AdventureWorks

GO

SELECT SalesOrderID,OrderQty,ProductID,UnitPrice FROM Sales.SalesOrderDetail

ORDER BY UnitPrice

GO

Look at the execution plan of this query.

Now you can see the SORTing is happening here and it consumes 71% percent from the total cost of the query.

Consider this query;

--Query3
USE AdventureWorks

GO

SELECT SalesOrderID,OrderQty,ProductID,UnitPrice FROM

(SELECT SalesOrderID,OrderQty,ProductID,UnitPrice FROM Sales.SalesOrderDetail

ORDER BY UnitPrice) A

I have used ORDER BY clause withing the sub-query, let’s execute this and see the result.

What do you expect as the result of this query?

Think before it executes.

Below is the output;

Msg 1033, Level 15, State 1, Line 3

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Were you able to figure out why we are getting this error?

As I already explained, the query will execute by creating several virtual tables, keeping the intermediate results and final result also a virtual table. But ORDER BY clause works quite differently. It returns a cursor. So the sub-query is returning a cursor result while outer query is implementing a virtual table. This is the reason why you can not use the ORDER BY clause in sub-queries, views, inline functions, etc (as mentioned in the error message).

The error message gives a hint to resolve the issue.

Consider the query below;

--Query4

USE AdventureWorks

GO

SELECT SalesOrderID,OrderQty,ProductID,UnitPrice FROM

(SELECT TOP 100 PERCENT SalesOrderID,OrderQty,ProductID,UnitPrice FROM Sales.SalesOrderDetail

ORDER BY UnitPrice) A

Now you can see the result of the query and the execution plan.

The difference of query 3 & 4 is the TOP 100 PERCENT statement. When you use the TOP operator (which is not a relational operator rather it is a T-SQL specific) the ORDER BY clause will not use a cursor when implementing. It uses a virtual table thus it prevents the above error and also the cost is very low with compared to the query 2.

Conclusion

As a rule of thumb it is a good practice to avoid ORDER BY clause unless you really need the rows sorted. Use ORDER BY clause in sub-queries with TOP 100 PERCENT if you really need it. A query with ORDER BY clause can not be used as a table expression that is, a view, inline table-valued function, sub query, derived table, or common table expression (CTE).

SQL version used for this article is, Microsoft SQL Server 2005 - 9.00.3073.00 (Intel X86) Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)