Where did it go???

Have you have ever looked for something and expected it to be there…and it wasn’t? If so, you will understand my day today. I have a SQL Server Agent job that uses VBScript in one of it’s job steps and we tried to move it to a SQL Server 2016 server.  It unexpectedly failed because the ActiveX Script job step type is no longer an option.

Below you will find a screenshot from a pre-2016 server.  As you can see, the job step type is there.

Now here is a screenshot of the job step properties from a SQL Server 2016 server.  Notice, NO script job step type.  However, you will see a PowerShell job step type that we can use instead.

Here is the link to Microsoft that describes other features that were removed from SQL Server 2016.


You can query the sysjobsteps table to find the jobs that have these types of job steps.

SELECT     j.name
, s.step_id
, s.step_name
, s.subsystem
FROM dbo.sysjobsteps s
         INNER JOIN sysjobs j
                 ON s.job_id = j.job_id

I have been telling my students for a number of years that as DBAs we will need to learn PowerShell someday. That day actually arrived a few years ago.  This is a great example of why we should know PowerShell.

I really try to keep up on the features that are removed from versions of SQL Server, however this one slipped through the cracks and I missed it.  I just really never expected it to be removed, although I do understand why MS did remove it.


Max Degree of Parallelism vs Cost Threshold for Parallelism

The title sounds like something that you would see for a boxing match.  However, in reality it is a misleading title.  The Max Degree of Parallelism and the Cost Threshold for Parallelism SQL Server settings actually work more together than they do against each other.

These two settings actually define the how many and the when in regards to parallel execution plans.  The Max Degree of Parallelism(MDop) simply defines the number of processors\cores that SQL Server will use when the optimizer determines parallelism is needed.  The Cost Threshold for Parallelism is cost threshold of when SQL Server will use parallelism.  The cost is the overall cost the optimizer determines for each query and SQL Server will use parallelism if the cost is above the threshold value.

The recommended settings for MDop is the number of cores not to exceed 8.  However, when setting this, it is important to continue to monitor the system to see if the change has caused an improvement. The default value is 0.

The recommended setting for the Cost Threshold for Parallelism is 25 to 50 and has a default value of 5.  Yes, the default setting is too low.  By changing this, SQL Server will reduce the number of smaller queries that may use parallelism.  Just like the MDop setting, it is important to monitor the server after making a change to see if an improvement is make.  There have been many times where simply changing this setting from the default, I have seen the CPU utilization drop from close to 100% to less than 10%.  This in no way means you will see the same improvement, just what I have seen.

Neither one of these settings require a reboot of the service when changing.  It is also important to keep in mind that if the MDop is set to 1, SQL Server will ignore the Cost Threshold for Parallelism setting.

Kendra Little has a very nice video that explains this more in depth.

MAXDOP of Confusion (Dear SQL DBA Episode 8)


This snippet of code can be used to query server configuration settings

, value
, description
FROM sys.configurations

Thanks for reading!!!!!


Anatomy of a SELECT Statement – Part 1

Well here it is, my first blog post.  In writing this I realized I can talk for hours on a topic and struggle when writing a blog post.  Writing blog posts is much harder than I had thought.  My plan is to start with something simple and work my way into more complex topics.  Luckily there are many great blogs out there in the SQL Server community that I can use as a model of how blogs should be done.

The Select statement is one of 4 Data Manipulation Language(DML), the others being INSERT, UPDATE and DELETE. As expected, the SELECT statement is used to extract data from a relational database such as SQL Server.  This statement works in most database systems, although there may be some differences in different products. These differences are not the focus of this or future posts, we will concentrate on the use of the SELECT statement in Microsoft SQL Server.  Over the years the SELECT statement has not changed much as versions of SQL Server are released, making this one of the statements that will work in older versions as well as the new ones.  This is the first of several that will dissect the SELECT statement and provide information on each.  I will start with the order of the commands in a SELECT statement.

Order of the Commands in the SELECT Statement

The SELECT statement consists of multiple parts. Below you will see the order in which the statements must appear.

If they are not in the proper order, an error will be raised and the query will not be allow to process properly.

Below you will find the error you will see if the statements are not in the proper order:

Msg 156, Level 15, State 1, Line 9

Incorrect syntax near the keyword ‘FROM’.

Understanding this order is vital to help explain why column aliases cannot be referenced in any statements besides the ORDER BY.  Just a quick reminder, column level aliases can be used on all columns, however calculated columns and columns that use functions will not have a column name unless an alias is used.

If you run the statement below you will receive this error:

Msg 207, Level 16, State 1, Line 2

Invalid column name ‘NewPrice’.

SELECT TOP 20 [ProductID]
,ListPrice * 2 AS ‘NewPrice’
FROM [AdventureWorks2014].[Production].[Product]
WHERE NewPrice > 100

The reason the error happens is because when the WHERE statement runs, the alias doesn’t exist yet.  With the above statement, the SELECT statement will actually run after the WHERE, therefore causing an error.

Well, how do I make it work you may ask?  The small snippet below shows how.  Rather than reference the alias in the WHERE clause, you will need to repeat the formula.  In this case the formula is the ListPrice Column times 2.

WHERE ListPrice * 2 > 100

However, you can reference the alias in the ORDER BY clause because it processes after the SELECT clause and the alias exists when the ORDER BY processes.

Hopefully you have made it this far!!!  As you can see in some situations, understanding the processing order of the commands in a SELECT statement is important.  I will cover the SELECT command in my next post.

Thanks for visiting my blog!!!

Welcome to my blog

I really enjoy teaching SQL Server and I am hoping my blog will allow me to share knowledge with more people.  I hope you enjoy and more importantly, you learn SQL Server and how great it is!!!