Tag Archives: sql

Resolve SQL Server Fatal error: cannot create ‘R_TempDir’

I was trying to test an install of R in SQL Server 2016 and when running a script I received this error: Fatal error: cannot create ‘R_TempDir’

Following the instructions here, I enabled external scripts, restarted the sql server service, and then tried to run the following test script:

This is when the fatal error occurred.  As the error suggests, R is having some issues creating a temporary directory.  After some internet searching and trial and error I got past the issue.

Enable  8dot3 File Names

R configuration uses the 8dot3 file name convention, also known as “short names”.  To enable this on windows 10, run the following command in CMD (command prompt):

For more options and information look here: https://support.microsoft.com/en-us/kb/121007

Give access to the working directory to R

Locate and open “rlauncher.config” file in a text editor.  This file will be under the “<sqlserver_instance>\binn” directory.  Take a look at the location of WORKING_DIRECTORY.  This should have a “short name” file path.  The path should be something like “<sqlserver_instance>\EXTENS~1”, and “\EXTENS~1” is equivalent to “\ExtensibilityData”.  We need to give access to R to this folder. I did this by changing the permissions to full control to everyone.  You may want to be more restrictive here, but in my case this did not matter.

  • Right click folder > Properties > Security tab > Advanced > Add
  • Select a principal (I entered “Everyone”)
  • Tick “Full control” under basic permissions and click “OK”
  • Tick “Replace all child object and permissions entries with inheritable permissions entries from this object” and click “OK”

Now if you rerun the script above you should get a result of “hello, 1”.

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.”

Get Database Sizes in PestgreSQL

Returns the database name, owner, and size on a given server.  Tested in PostgrSQL 9.2 and 9.3.

Source: https://wiki.postgresql.org/wiki/Disk_Usage

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

2016 Learning Goals

MCSA & MCSE SQL Server Certification
  • MCSA: SQL Server
  • MCSE: Data Platform
    • 70-464 – Developing Microsoft SQL Server Databases
    • 70-465 – Designing Solutions for SQL Server
  • MCSE: Business Intelligence
    • 70-466 – Implementing Data Models and Reports with Microsoft SQL Server
    • 70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server
Neural Networks and Machine Learning

Training Sources:

Other Interests
  • Internet of Things Development
  • Mobile Application Development
  • Python
  • Ruby on Rails
  • Node.js, MongoDB, and Express
  • Quartz Composer
  • Actuary Exams
Learning Websites

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.

 

Helpful SQL Server Links

icrosoft White Papers:
https://msdn.microsoft.com/en-us/library/ee410014(v=sql.105).aspx

Microsoft Books Online:
https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx

Pragmatic Works Training Resources:
http://pragmaticworks.com/Training/Resources

SQL Server Database Engine Permission Posters:
http://social.technet.microsoft.com/wiki/contents/articles/11842.sql-server-database-engine-permission-posters.aspx

TechFunda SQL Server Tutorials:
http://techfunda.com/howto/sql-server

Microsoft SQL Server Tutorials:
https://msdn.microsoft.com/en-us/library/ms167593

Microsoft SQL Server Community Projects & Samples:
http://sqlserversamples.codeplex.com/

SQL Pass DBA Fundamentals – Web Presentation Archives
http://fundamentals.sqlpass.org/MeetingArchive.aspx

Blogs and Other Great Resources:
https://blogs.msdn.microsoft.com/sql_server_team/ – MSSQL Tiger Team
https://www.mssqltips.com/
http://blog.sqlauthority.com/
http://www.brentozar.com/
http://www.edwinmsarmiento.com/
http://kevinsgoff.net/
https://www.sqlskills.com/
http://blog.datainspirations.com/
 – BI blog by Stacia

A better list put together by New Horizons:
http://nhlearningsolutions.com/Blog/TabId/145/ArtMID/16483/ArticleID/1186/Free-Resources-for-SQL-Server.aspx

SQL Injections:
http://www.unixwiz.net/techtips/sql-injection.html