How can I tell if Statistics are created by the system

Column statistics are used by the optimizer to help create the best possible execution plan for a query.  There are a few ways these statistics get created.  The first method is when an index is created, which are updated when the index is optimized. The second method of creating statistics is by using the CREATE STATISTICS statement.  This statement allows the DBA to create statistics on a column that doesn’t have an index on it and allows the creator to provide a name of the statistics object.  The last method is to let SQL Server create the statistics when the column is read.

Using the AdventureWorks2014 database, if you run the code below you will see that there are not any statistics on the MakeFlag column in the Production.Project table.

SELECT OBJECT_NAME(object_id) AS [ObjectName]
,[name] AS [StatisticName]
,STATS_DATE([object_id], [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats
WHERE OBJECT_NAME(object_id) = ‘Product’

I get the result set below.  But in looking at it, how can I tell that there really isn’t statistics on the MakeFlag column?

You can run the statement below that will list all the columns that have statistics.  This statement can be found at here on Microsoft’s web site.

SELECT s.name AS statistics_name
,c.name AS column_name
,sc.stats_column_id
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c
ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID(‘Production.product’);

Now that I have been able to check the columns, what are the objects that have the funny names?  Those are system generated statistics.  Let’s break down the name.

Using this name as an example: _WA_Sys_00000007_75A278F5

The WA really just stands for Washington…of course we know where that came from

The sys just means system

00000007 means it is the 7th column in the table. in this case the SafetyStockLevel column.

Below is a list of the column in the Production.Product table.  Notice that the SafetyStockLevel is the seventh column.

But what about this statistics object, _WA_Sys_0000000A_75A278F5.  Where is column A?  The means it is the 10th column as indicated below.  The A means that it is a hexadecimal value.

That takes care of the first three parts of the name.  The last item, 75A278F5 is a hexadecimal number of the object ID.  The image below you can see that the object ID is the same as 75A278F5 converted to a decimal number.


Now that we have broken down the name, let’s talk about how these actually get created.

If you run the statement below, it will create the statistics object on the MakeFlag column. Before showing you the results, let’s predict the name.

SELECT Name
, MakeFlag
FROM [Production].[Product]
WHERE Makeflag = 2

We know it will start with _WA_SYS_.  Since MakeFlag is the 4th column, we know the next part of the name is going to be 00000004.  This just leaves the last part, the hexadecimal version of the object id.  Using a converter at https://www.rapidtables.com/convert/number/decimal-to-hex.html.  we can take the object ID and convert it to 75A278F5.

Put all the parts together and you get _WA_Sys_00000004_75A278F5 as confirmed by the screenshot below.

After all that, the easiest way to find the system generated statistics is the run this statement:

SELECT *
FROM sys.stats

This statement will return a column named, auto_created.  This is another method of finding the system generated statistics.

Hope this helps clear up the names of system generate statistics.  Thanks for stopping by and hope you learned a little something.

Anatomy of a Select Statement Part 2

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 the different products. These differences are not the focus of this post, 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 part 2 of the series.

The SELECT clause is just one of a few clauses in the SELECT statement.  The others are FROM, WHERE, GROUP BY, HAVING and ORDER BY.  In part 1 I discussed the order these clauses are processed.  As a reminder, the SELECT clause is either last or second to the last in the processing order.  If there is an ORDER BY, the SELECT is processed just before the ORDER BY.  If there is not an ORDER BY, then the SELECT is the last clause processed.  The focus of this post is just the SELECT clause.

The SELECT clause can easily be described as the place where you identify what columns you want to be included in the result set.  These columns can be columns from the table or view, an expression or the results of a scalar function.  In addition to what columns you want included in your result set, the SELECT clause is also where the order in which those columns appear in the result set is determined.

When deciding what columns from the tables to be included in the SELECT clause, it is important to only return the columns that are needed.  Avoid using “SELECT * FROM….”.  Sometimes it is simply easier to use SELECT *, however there are consequences to using it, mostly the potential impact on the performance of the query.   When listing the columns they need to separated by a comma.  Below you will find an example.  The “[” and “]” are used when the column name contains a space.  You will also see them if you right click on a table and click “Select Top x rows” in SSMS.

In addition to actual column names you can also use calculated columns in the SELECT clause. However, when this is done the column name in the result set will be (No Column Name) unless a column alias is used.

Result Set without a column alias:

The example below show how a column alias can be used.  The “AS” keyword isn’t required.  The single quotes surrounding “New Price” are also not required as long as the alias name does not include spaces.  Personally I also like to include them regardless if there is a space or not.  There really aren’t a lot of requirements for column aliases although I would try to avoid using keyword and actually column names.

Scalar Functions

Scalar functions can also be used in the SELECT clause.  These functions can be system supplies functions or user defined functions. Really the only requirement is that it must be a scalar function, not a table valued function. Just a reminder, a Scalar function is a function that returns a single value.  So when one is included in a SELECT statement, it will run the same number of times as there are rows returned.

Here are some examples of a few native SQL Server built-in functions. More information can be found here: More Info on Functions

LEFT RIGHT LEN
UPPER LOWER REVERSE
TRIM GETDATE DATEDIFF
UPPER LOWER TRY_CONVERT

When using these functions, many times the arguments will be populated with column names.  Although that is in no way a requirement.

Below you will find an example of using two Built-In functions. One, UPPER, requires an argument to be used while the second, GETDATE(), does not.  However, both would need a column alias.  This would be the same if using User Defined Functions(UDF).

Example using a UDF.  In this case it is the ufnGetSalesOrderStatusText UDF in the AdventureWorks database.  It accepts TINYINT value as an input parameter and will return the text description of the status.

If you run the above query without the TOP clause, you will see that this query returns over 31,000 records.  This means that the UDF will execute over 31,000 times. This is important to know in case there are performance issues.

TOP

We have discuss adding columns, column aliases as well as the use of scalar functions in the SELECT clause. Now lets talk about the TOP key word. This appears immediately after the SELECT key word.  This is used to limit the number of rows to a specific number, TOP(10) or percent of rows, TOP 10 PERCENT.  The TOP key word is usually used in conjunction with the ORDER BY clause.  If no ORDER BY clause is included, then SQL Server will per Microsoft,  “it returns the first N number of rows in an undefined order”.

In the example below, only 10 rows are return based on the oldest 10 records when sorted by the OrderData.  Remember, the default sort order is ascending.  Notice that there are not parenthesis around the number 10.  Again, not required at this point, but will be in the future.

Notice above that the OrderDate is the same.  That is because it only returned the top 10 records.  Well what if there are more than 10?  This is where WITH TIES comes in.  If you look at the example below you will see that there are 11 rows.  This is because the 11th row match the value of the 10th row.  WITH TIES will return if there are additional matches with the value in the last row.

$ROWGUID and $IDENTITY

These two functions are used with tables that have an Identify column or a column using the ROWGUIDCOL property.  With both $ROWGUID and $IDENTITY you actually don’t need to know the name of the columns.  If you attempt to use either of these on a table that does not have the proper column types will result in an invalid column error.

Notice in the example below, the first column and the last column in the result set are the same.  The $ROWGUID column, the first column, will also assume the column name of the source column as well. $Identify works in the same way.

DISTINCT

The last topic in the post is the use of the key word DISTINCT.  DISTINCT is used to eliminate duplicates in the result set.  Per Microsoft, NULLS are considered equal for the DISTINCT keyword.

If you look below you will see that the number of rows returned is 316.  If you add the keyword DISTINCT the number of rows returned drops to 89.  With this query, all duplicates based on Gender and Rate are removed.


During the post we have discussed many of the items that can be included in the SELECT clause. This includes column aliases, user defined functions, built-in functions, TOP and DISTINCTS keywords as well as a few other topics.

Hopefully you leaned something and thank you for visiting my blog. In the next post I will dissect the FROM clause.

Previous parts of the Series

Part 1 – Order of the Clauses in the SELECT statement

 

 

 

 

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.

https://docs.microsoft.com/en-us/sql/database-engine/discontinued-database-engine-functionality-in-sql-server-2016?view=sql-server-2017

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

SELECT CONVERT(VARCHAR(60), name) AS ‘Name’
, 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]
,[Name]
,[ProductNumber]
,[MakeFlag]
,[FinishedGoodsFlag]
,[Color]
,ListPrice
,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!!!