Category Archives: MS SQL Server 2008 R2

Backup Transaction Logs to Nul for All Online Databases

First of all, never use this in a production environment!   This script is to backup your transaction logs to a “nul device” for all online databases which are not using simple recovery model.  In windows it is indeed spelled “nul” with one “L”.  The only reason you would want to do this is if you have a non production environment using full recovery model and this server architecturally mirrors your production environment.  For example, we have a “staging” server that is used for testing our code changes before they go into production.  We require the staging environment to be as close to production as possible and have scheduled scripts that sync them weekly.  In this scenario, we have many databases in the staging server that are using full recovery model but we do not want to backup the t-logs, we would rather just throw them away.

To learn more about NUL devices, here’s a link to the wikipedia page: https://en.wikipedia.org/wiki/Null_device

Collapse and Concatenate Rows

I had a need to concatenate  and comma separate some multi-row data into an array of values with each having an unknown number of elements, in other words, take a many to one parent-child relationship and collapse the many child rows into the one parent record and separate the child record values with a comma.  In the past, my default method to solve this problem was to build a temporary  table  and then use a loop to iterate through a data set and append the elements by updating rows in the temporary table, or use a common table expression with anchor and recursive members.  Recently I stumbled upon the “stuff” and “for xml” functions.  I had seen these functions  before but never took the time to understand their potential use.  These function can be used to solve the problem mentioned.

T-SQL For XML (Path Mode)

Function Description: A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries. The top-level FOR XML clause can be used only in the SELECT statement. In sub queries, FOR XML can be used in the INSERT, UPDATE, and DELETE statements. It can also be used in assignment statements.

In a FOR XML clause, you specify one of these modes: RAW, AUTO, EXPLICIT, PATH.  We will only use PATH for this exercise.

The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.

The EXPLICIT mode allows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This rowset format is then mapped into XML shape. The power of EXPLICIT mode is to mix attributes and elements at will, create wrappers and nested complex properties, create space-separated values (for example, OrderID attribute may have a list of order ID values), and mixed contents.

I will not list the syntax for this function because it can get pretty complex very quickly for all of the options.  Instead, you can see in the example I just use it to concatenate the rows into a comma separated array.   Any other XML is basically ignored by passing  in the argument “Path (”)”.

(More information on For XML)

T-SQL Stuff Function

Function Description: The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Function Syntax: STUFF ( character_expression , start , length ,character_expression )

(More Information on the Stuff function)

Example Problem

List database users and include all roles of which a user is a member, separated by comma.

Solution using Stuff and For XML

  • “For XML” is used to collapse and concatenate the row data into a single array.
  • “Stuff” is used to remove the leading comma.
  • Note that we are using a correlated subquery when we reference “dp.principal_id” in order to limit our roles and role members to our database principals in the main outer query.

Output:

principal_id type_desc create_date modify_date Roles
5 SQL_USER 11/13/2002 6/19/2010 db_owner
7 SQL_USER 11/13/2002 6/19/2010 db_owner,db_datareader,db_datawriter
8 WINDOWS_USER 3/26/2009 3/26/2009 db_datareader
10 WINDOWS_USER 3/16/2008 3/16/2008 db_datareader
11 SQL_USER 7/11/2008 6/19/2010 WebViews
13 SQL_USER 7/25/2014 7/25/2014 role_proxyusers
14 WINDOWS_USER 3/12/2009 3/12/2009 db_datareader
16 SQL_USER 7/25/2014 7/25/2014 role_proxyusers
18 SQL_USER 8/26/2005 6/19/2010 db_owner
19 WINDOWS_USER 6/2/2009 6/2/2009 MSReplPAL_9_1,db_datareader
20 WINDOWS_GROUP 10/3/2005 10/3/2005 db_datareader
21 SQL_USER 7/14/2009 5/2/2016 role_proxyusers,db_datareader,db_denydatawriter

Microsoft SQL Server Developer Edition is now free

https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/

“Exciting news! Starting today, SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition). We are making this change so that all developers can leverage the capabilities that SQL Server 2014 has to offer for their data solution, and this is another step in making SQL Server more accessible. SQL Server Developer Edition is for development and testing only, and not for production environments or for use with production data.”

“SQL Server 2016 Developer Edition, when released later this year, will also be free. To learn more about the exciting new features in SQL Server 2016, read the datasheet.”

SQL Server Job to Capture Reads

We needed to capture physical reads over time from our SQL server.  One method we decided to deploy was to check sys.dm_exec_query_stats periodically and capture the differences between each check.  The way I am doing it below is not 100% accurate because sys.dm_exec_query_stats is based on cached plans and the server will let clear the plan data over time.  This means that when the differences are calculated any plan that was cleared from cache would not be included in the current check, but would still be in the comparison record.  This could be solved by storing and comparing at the plan level but for my purposes I prefer to store summary information and do not mind this issue because I am running my comparison every hour and then will be averaging the results over time, and the result does not need to be exactly precise, just close enough to give us a general understanding.

This is the query inside the job:

This is what the table looks like with a few records.  Note that this is a result of running the job multiple times with a few seconds between each interval.

SQL Server Job to Capture Reads result

Disable then Enable SQL Agent Jobs

This script has three actions:
1) Put Current Job Information Into Temp Table
2) DISABLE All SQL Server Agent Jobs
3) ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling
Note: If you need to disconnect from the server after step 1 then step 3 will not be able to read from the temp table. Instead, you would have to feed a list of job IDs to the query. I have created a place for this just in case this is what you have to do.

Test Results:
Put Current Job Information Into Temp Table: 47 row(s) affected
DISABLE All SQL Server Agent Jobs: 33 row(s) affected
ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: 33 row(s) affected

 

Get and Kill Executing Processes of Job

This query will identify the current SQL processes or executions which belong to a specific job.

We had a job process that was getting blocked for long periods, and in turn was causing a lot of blocking.  The best solution here is to fix the root blocker, however, as a short term fix we want to allow the job to run but then kill it a specific time if it is still running.  To do this, we create another job that looks for all of the SPIDS associated to executions which belong to the job getting blocked, and execute a kill commend for each SPID.

 

Performance of Like vs Left/Right for Indexed and Non Indexed Columns

I have have heard and read many times over the years that Left or Right should always be used, where possible, instead of Like.  On the surface, this does sound logical since the Like command is often used for pattern searching and one could assume that the Like command is causing some extra work to be performed by the SQL server engine even though the predicate expressions are essentially equivalent. I personally have always assumed that they operated equivalently, however, I had never performed a performance analysis to infer the true answer.  Also, was curious to find if there was a difference in performance when applied specifically to an indexed column vs non-indexed column.

The names and data structures in this example have been changed to protect the innocent. For this test, assume [SOME_TABLE] has 530 million records and contains two columns: [INDEXED_COLUMN], and [NONINDEXED_COLUMN].  Indexed_Column has a clustered index and Nonindexed_Column does not have an index.  We will run a test using like and left against each column using a basic count(*) command.

Test 1 – Non Indexed Column

Output for queries 1 and 2 = 63 million.

  • Query 1 used like and  averaged 102 seconds over 5 measurements.
  • Query 2 used left and averaged 103 seconds over 5 measurements.

Execution plans for queries 3 and 4:like vs left no index

Test 2 – Indexed Column

Output for queries 1 and 2 = 87 million.

  • Query 1 used like and averaged 3 seconds over 5 measurements.
  • Query 2 used left and averaged 14 seconds over 5 measurements.

Execution plans for queries 1 and 2:like vs left index

Conclusion

You can see from the results that Like is actually considerably faster than Left when applied to an indexed column.  This is because Like is treated as a computed column, which can utilize indexes, while Left is a function which can not utilize indexes.  If an index is not involved then Like and Left perform the same.

There might be additional factors that impact performance differences which I am not considering such as other index types, and other data types.  I came across another blog (http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex) where a similar test was done.  Surprisingly, their results showed that Chardindex is the most efficient out of Like, Substring, Left/Right, and Charindex, and their results between Like and Left/Right are quite different than mine.  At some point I’d like to go through their code and identify exactly what is different.

Edit: The author reached out to me and mentioned that the main difference between our results is likely due to my analysis being ran on SQL Server 2008 R2 vs theirs being done on SQL Server 2014 and there have been changes to the query engine between versions.  I encourage anyone seeing this to check out Dave’s blog.

More about Indexes on Computed Columns: https://msdn.microsoft.com/en-us/library/ms189292.aspx

Check SQL Server Version Using T-SQL

If you are building some code that would be different depending on the version of SQL Server then it may be useful to check the SQL server version using T-SQL instead of going through the object explorer in SSMS.  For example, I have some code that checks for SSIS package execution status across multiple servers and the database design is slightly different between server versions so I need to check the version and then execute the proper block of code.