New Online Course: Understanding Statistics In SQL Server

I’m pleased to announce my new online course is now up and available: Understanding Statistics in SQL Server. This course is almost 2 hours long and will teach you everything you need to know about statistics in SQL Server – what they look like, how they are used, and how to maintain them. I also go over common problems caused by out of date statistics and how to find and fix them. It includes demos and sample scripts.


Use this link to sign up (or the coupon code BLOG15OFF) to get a 15% discount.

Don’t forget my other courses are still available:

Microsoft SQL Server 101. Use this link for a 15% discount.

Understanding Data Compression in SQL Server. Use this link for a 15% discount.

SQL Server Maintenance Plans. User this link for a 15% discount.



The Certification Shuffle

MCSA_SQL12_BlkLast weekend, I passed the last test needed to upgrade my MCTS: SQL Server 2008 certification to the MCSA: SQL Server 2012 certification. I still need to take one more exam to upgrade my MCITP: Database Administrator 2008 to the MCSE: Data Platform certification. I’ll  probably do this, but I’m not sure I’ll continue with Microsoft certifications after this. Here’s why:

Back in 2012, when Microsoft changed the (then still fairly new) MCTS and MCITP certifications to the MCSA and MCSE certs, I wrote what I liked about the change and also about several reasons why I didn’t like it.  As mentioned then, the new MCSE cert will no longer be tied to a specific version of SQL Server and will require re-certification every 3 years. At the time, I wrote “I have a strong suspicion that the re-certification exams are going to focus on the latest version of SQL Server that is out at the time. This puts people who work for companies that do not like to be on the “bleeding edge” at a disadvantage.

Sure enough… 3 years later, this appears to be exactly what has happened. The MCSE cert stated out with SQL 2012. In April 2014, the exam was updated with SQL 2014 topics. (See this and this (PDFs)). In January 2016, the MS 70-459 exam, which let you upgrade your MCITP: Database Administrator 2008 to MCSE: Data Platform will be retired. This is right around the time SQL Server 2016 will be out. So you can bet the exam for MCSE:Data Platform will be updated to include SQL 2016 features at that point.

And to make matters worse, Microsoft has announced that the MCSA level certification is ending with SQL 2012. Or, more specifically, that no MCSA: SQL Server 2014 will be offered – which more or less implies that there won’t be one for any other versions of SQL Server as well. (See the first question in the Q & A section in the link for more info.)

Taken altogether, let’s look at what this means for the SQL Server database professional in 2016 and beyond. No MCSA cert will be around, so we’ll all be forced to get the MCSE certification. This exam will focus primarily on the shiny new features of the latest version of SQL Server. SQL Server is now on a 2 year release cycle. This cert requires re-certification every 3 years. This means we will always be tested on the latest features of the latest version of SQL Server. OK, I can see why Microsoft wants to emphasize that. The big problem? Odds are, we won’t have any experience actually using those features.

Most vendors I deal with are just now starting to support SQL 2012 – and it’s been out for 3 or 4 years already. They simply don’t have the manpower to re-test their products on a new database version every two years. As a result, the day-to-day work of a DBA tends to be more with older versions of SQL Server, because that is all our vendors will allow us to use.

Can you see the disconnect? Microsoft is asking up to get certified on products we don’t use. I thought certifications were supposed to demonstrate that DBAs had experience and proficiency with certain software. This new policy will simply demonstrate the DBAs have “book learning” about a product. There will be no guarantee that they actually have experience using a product.

I’m already not a big fan of certifications. I think they are expensive and of dubious worth. In my opinion, they primarily are useful when you are looking for a new job and need to get past that first batch of resume screeners looking to see if a candidate meets the minimum requirements for a position. With the new changes, I think their worth drops even more. I’m not sure I’ll bother with any more certifications going forward.


The Only Constant Is Change

8044372740_3ddac1850d_mMonitoring the health of your SQL Servers is a standard part of a DBA’s job. One of the most important parts, to tell you the truth. It’s tempting to think of databases as unchanging. You can check them once and, if they are healthy, your job is done. There is no need to check it again. But, just like people, their health can change over time.

I run my own health check scripts on my SQL Servers every quarter. I am amazed at what I find. I typically spent 2 to 3 weeks going through all my servers and correcting all the issues the script turns up. But what is even more amazing is how often I find problems cropping up again the next time I perform the health check.

One of the more common things I find are untrusted or disabled foreign keys and check constraints. Each quarter, these pop up in my checks and, each quarter, I re-enable them. They always seem to get disabled somewhere down the line. Sometimes it’s an in-house process that disables them. In that case, I can talk to the developers and correct the problem at the source. Sometimes, it’s a third party app that I have no control over.

I also find a lot of issues regarding jobs. One common one is jobs without error notifications. Often times, third party products install SQL jobs and they almost never set up a failure notification. I also find vendor jobs that perform index maintenance. Because I have my own index maintenance routines on my servers, I disable these jobs in favor of my own (after verifying my own will run as frequently as the vendor’s did). Because I don’t always know when new software is installed or upgraded, these issues come up each time I run my health checks, even if I have corrected the issue the last time I ran the check.

Bottom line: your SQL Servers are living things and they change over time. Even if you have them configured and running exactly they way you want them to right now, over time, they will change. Monitor them on a regular basis.

FYI, my health check script is a variation on Brent Ozar Unlimited’s sp_Blitz script. If you don’t have your own script to use, theirs is a great one to start with.


Using PowerShell For More Readable Log Files

Most of the scheduled jobs on my SQL Servers write some sort of output to a log file. Most of the time, it’s just the output of a SQL statement. However, for some of my longer, more complicated routines, the procedures I write include PRINT statements to output various statuses or progress messages while the procedure is running.

When these are running on SQL Server Management Studio, they appear in the Message tab of the results pane. If the procedure is run as a scheduled job and the option to output the results to a file is enabled, the text output by the PRINT statement gets written to the text file. But it’s not pretty.

If you’ve ever tried this, you’ll quickly discover that SQL Server appends the text [SQLSTATE 01000] to the end of each line output by a PRINT statement. I find this really annoying. It renders the log file much harder to read.

There was a Microsoft Connect item created to change this, but Microsoft has stated they will not change this behavior. Apparently, their code cannot tell the difference between a PRINT statement and an error and SQLSTATE is a vital piece of information that needs to be included in error messages.

Enter Powershell7551668880_4446c12657_m

Because SQL Agent offers a PowerShell step type, it’s very easy to use PowerShell to remove these unwanted additions to your wondrous PRINT statement messages. For my jobs that output verbose log files populated by such messages, I just add an additional step at the end of the job that calls a PowerShell script to replace the string “[SQLSTATE 01000]” with a carriage return and line feed.

Here’s the script:

Get-Content $file | ForEach-Object { $_ -replace "\[SQLSTATE 01000\]", "`r`n" } | Set-Content ($file+".tmp")
Remove-Item $file
Rename-Item ($file+".tmp") $file

What the script does is export the contents of your file to a temp file, replacing the offending string with a carriage return-line feed as it does so, deletes the old file, and renames the temp file back to your original file name. The script needs to escape the brackets, as they are special characters in PowerShell. Additionally, the `r`n combinations are PowerShell special characters that represent a carriage return and a line feed.  The script works well if the filename the job writes to is always the same. If it changes, you’ll need to add logic to set the $file variable correctly.


Implicit Conversions Can Be An Unseen Danger

I got an email from one of my developers regarding a process they had developed in-house. The process has been taking longer and longer to run and it had reached the point where the application was timing out while waiting for a response from the database. Some investigating revealed that the process was timing out after 30 seconds. This process was developed in .NET and the default timeout for .NET applications is 30 seconds, so at least we identified why the application was timing out.

The next step then was to look at the code that was being run and determine why the database was taking more than 30 seconds to return data. To make a long story short, there were stored procedures calling stored procedures calling views and the whole thing was basically a mess. But the root cause, believe it or not, was implicit conversions, mainly with dates. The views were joining several tables and the stored procedures were referencing several of the columns in WHERE clauses. In some of the tables, dates were stored as date datatypes, but in others they were stored as varchar datatypes. As a result, virtually every join required an implicit conversion and almost no WHERE clauses were SARGable.

A common way of troubleshooting performance issues is to look at the execution plan using SSMS. Unfortunately, implicit conversions are easily overlooked in an execution plan because they don’t have their own little symbol – you have to mouse over a symbol and read the pop up text to see them.

Let’s look at a simplified example. We have the following table:

Implicit Conversion Table

For demonstration purposes, I’m not going to join to any other tables, but the concept is the same. Let’s look at this SELECT statement:

FROM    [dmPCOMBDaysNoHolidays]
WHERE	CONVERT(VARCHAR(5), YEAR(calendarDate)) = '2014'

If we execute this statement and include the execution plan, we get the following:


Nothing exciting there. Standard stuff.  But let’s hover the mouse over the clustered index scan icon and look at the text that pops up:


You can see two conversions – let’s look at them in reverse. The second is the implicit conversion that changes the varchar column from the table into a date datatype to feed into the YEAR function.  Because the YEAR function returns an integer, we explicitly convert this into a varchar(5) to compare to ‘2014’, which is the first conversion listed. (Don’t ask me why it’s varchar(5) and not varchar(4). That’s just another example of how bad this code is.)

Note the subtree cost. We’ll come back to that.

Now let’s re-write this query and try to eliminate that explicit conversion. We can use this:

FROM    [dmPCOMBDaysNoHolidays]
WHERE	YEAR(calendarDate) = 2014

Because the YEAR function returns an integer, we can compare the result to the integer value of 2014, instead of the string value ‘2014’. Our execution plan looks exactly the same:


But the mouseover text now has some differences:


Our explicit conversion is gone – as expected since we were the ones who got rid of it. We still have the implicit conversion of the column data to a date datatype for use in the YEAR function. So we’re doing one less conversion, which means SQL Server has less work to do. You would expect the estimate subtree cost would drop.  But, surprisingly, it did not. Hmm.

Now let’s make one more change. If we actually look at the data, we see it looks like this:


The data values all are in the format YYYY-MM-DD. Knowing this, we can now write our query as follows, keeping everything as character data:

FROM    [dmPCOMBDaysNoHolidays]
WHERE	LEFT(calendarDate,4) = '2014'

Again, the resulting execution plan looks identical to our other two:


Looking at the mouseover text, we see we no longer have any conversions at all. Yet, the subtree cost remains the same.


In summary, our original query did the following:

  1. Convert varchar datatype to a date datatype
  2. Run this value through the YEAR function, which returns an integer datatype
  3. Convert the integer back into a varchar for the comparison to the criteria

Because this is a non-SARGable query, these three steps have to be done for each and every row in the table.

Our re-written query does the following:

  1. Compares the leftmost 4 characters of the calendarDate field with the criteria

We’ve cut the work down by 66%. That’s an improvement, but it’s a rather small one. Unfortunately, without changing the database schema, it’s all we can do.

Now in these examples, the table only has a few thousand rows, so we’re not seeing changes in cost or noticeable changes in performance. However, in the actual application I was troubleshooting, these conversions were happening for multiple tables with hundreds of thousands of rows, in multiple places, in WHERE clauses, and on joining columns. It all added up to very slow performance.

Unfortunately, for situations like these, there is very little a DBA can do to dramatically improve performance. All of the WHERE clauses in these examples are non-SARGable and will require an index scan of all rows rather than an index seek of a subset of rows. To fix performance issues caused by this type of problem, the only answer is a code re-write, schema re-design, or, more likely, both

As a test, I reproduced the tables in the process using correct and consistent datatypes, created the same indexes on them, loaded the exact same data, and ran the queries (modified only to not perform all the no-longer necessary datatype conversions) against the new schema. Performance was improved by an order of magnitude. If you only looked at execution plans, it might look like the changes did not make any difference, but in reality, the difference was significant.