This is the fifth article in a series that examines the output of the Brent Ozar Unlimited sp_Blitz™ script run against the SQLServerCentral database servers.
Managing a SQL Server is an ongoing job. While the SQL Server platform will run itself for a long time, as your workload evolves, your applications are patched, and your staff experiences changes, it's easy for configuration and security issues to creep into your environment. The best way to prevent issues and problems is with regular monitoring and auditing of your instances.
The SQLServerCentral servers are regularly monitored, using SQL Monitor from Red Gate Software, and you can see the data at monitor.red-gate.com. We expose that as a demonstration of what the monitoring software can do. This covers alerts and problems that occur in real time, but it doesn't catch some changes which may occur over time.
This is where some type of auditing becomes very important. One of the ways in which you might think about auditing your instances is with a standard script that examines various potential places where your configuration isn't optimal.
sp_Blitz™
Brent Ozar Unlimited has released a fantastic script for examining the configuration, health, and performance of your SQL Server instances. This script is known as sp_Blitz™ and is currently at v16. You can download it and it builds a stored procedure that you can run on any of your instances.
A tremendous amount of information is returned. If you are interested, you can read about the script on the Brent Ozar Unlimited site.
We used this script to perform a quick audit of our database server instances and learned a few things. This series of articles examines the results and mitigation strategies we've taken. The other articles are:
- Security
- Reliability
- Performance - Part 1
- Performance - Part 2
- Query Plans
- Informational and other lower priority items
This article examines those items related to the query plans captured by your database server.
Query Plans
The fourth Findings Group output by the sp_Blitz script is the Query Plans section. These items can affect how your instance responds to client requests, warning you of items that may or may not cause your server to run slower. We received the following sections in our result set:
- Cursor
- Implicit Conversion
- Scalar UDFs
Each of these items is discussed below with regard to our database servers.
Cursor
We had two instances of this alert appearing in our result set. The alerts were from a query being one of the more resource intensive ones being run on the instance. The result set shows a showplan of the query. I've included these as two attachments to this article (plan 1 and plan 2).
Both items are calls to RenderTodaysForumPosts. This function is called for the newsletter, to build the list of forum topics you receive every day at the bottom of the email. We do this at the point in time that the newsletter is sent, and the value is stored in the database, and then sent out after being merged with the rest of the newsletter code.
This uses a cursor because there's some string manipulation that isn't pretty. Could we do this in a set based way? I bet we could, but I'm not sure it would be more efficient as the string building requirements add code when the forum changes, and this results in an algorithm that is easier to understand (for developers) as it's written. We only send the newsletter once a day, so it wouldn't make sense to spend much time optimizing this.
However.
We do send constant emails out for forum notifications, password resets, sign ups, and more. Unfortunately the code that builds these emails also calls RenderTodaysForumPosts as well. This means this code is being called unnecessarily, over and over again. I've emailed with our developers, and they aren't sure why this decision was made. Since I've had 6 or 7 developers across the last six years working on this project, it's hard to know when this code was added.
In any case, I've filed a bug to refactor this code, but it's not a high priority item. The site has been working fine, albeit inefficiently because of this. We'll get this corrected.
You can read more about this alert at BrentOzar.com.
Implicit Conversion
Implicit conversions are bad. These are some very costly operations that occur inside of your instance and dramatically affect the scalability of your server. It's hard to upgrade a SQL Server quickly when load increases and the more implicit conversions you have, the more difficult it is for your server to perform well as the calls increase to operations that require implicit conversions. You might even get errors, depending on what conversion is chosen.
We have a number of rows in the result set dealing with implicit conversions, 6 to be exact. Looking through these showplans, what we find is that these functions/stored procedures are the problem:
- Email_GetEmailsReadyForSending
- TopScores
- UpdateStatsCaches (twice)
- A dynamic query for security
- One of our scripts queries
Each of these functions has to be analyzed separately and the code refactored to avoid the implicit conversions.
The first procedure, Email_GetEmailsReadyForSending, has a variable declared inside it as a varchar(50). However this variable is compared to a column with an nvarchar(50) datatype. Top scores has a similar issue. A parameter is declared as a datetime type, but compared to a smalldatetime type in a table. I've sent notes to our developers to correct these.
The UpdateStatsCache has a conversion between an int parameter multiplied by 1.0 and a float value that's stored in the table. This isn't the best solution, since there are a lot of rows being inserted. I asked a few people on Twitter for the best way to change this and got a few answers. We'll do some testing, but I suspect that using CAST might be the best way to alter this code for readability. After asking a few people, I'm not sure this will result in any better performance, but I think it's a good habit to explicitly code your conversions.
We have a security code query, which came with our forum software. Essentially it runs a "SELECT @i = COUNT() from ...". However the return parameter being used is an nvarchar(1000), resulting in another conversion. I've asked this be changed.
The script query is pulling back counts from the scripts table, where the count values are smallints, and the result is an int. Again, a conversion issue. I've asked this be looked at.
These are all small issues in development, but they turn into big ones in production when the number of conversions becomes significant. Each one puts an unnecessary load on your database server. This is an area that all developers should be
You can read more about this alert at BrentOzar.com.
Scalar UDFs
Scalar UDFs were highly anticipated when they were added to T-SQL. However over time we've discovered there are performance problems with these programming constructs in set based queries.
The function that is causing this is listed in a procedure: sp_get_composite_job_info. This is the procedure that is being run from some front end code, gathering up information for all jobs from MSDB as well as a few of our applications. It rarely runs, just a few times a day. While we should revisit this later as Scalar UDFs can be problematic, we will add this to our list of development items as a lower priority fix.
You can read more about this alert at BrentOzar.com.
Moving Forward
This is the fifth article looking at the very helpful and popular sp_Blitz™ script, and it examined the query plans section of the output. The next article will examine the Reliability section of the script as run against the SQLServerCentral database servers.
The other articles in this series are:
- Security
- Reliability
- Performance - Part 1
- Performance - Part 2
- Query Plans
- Informational and other lower priority items
This is a great script to run on your instances, but you need to run it periodically to catch changes, some of which might potentially be causing you problems, or will cause you problems in the future. Saving your output from previous runs and comparing the results and looking for changes is a good idea to save during future analysis.