SQL Server Recompile Hint saves the day

It’s not often that I have the opportunity to use an SQL Server technique for the first time. Recently I was able to dramatically improve the query performance of a stored procedure by using the WITH RECOMPILE option. It started out as a real head-scratcher. Even though I just revealed The Spoiler, let me lay out how it unfolded…

Client presents a performance problem with a stored procedure that sources data for a report. The report used to run in 3-4 minutes, but now takes an unacceptable amount of time. I don’t know how long because they have cancelled it after 10 minutes thus far. Next, the usual suspects:

  1. Its running SQL Server 2019, so not an old code issue.
  2. Stored procedure execution is only long-running activity on the server. SP_WHOISACTIVE shows other queries taking a second or less to complete, so its not overall server activity.
  3. CPU is pinned at 100% which I interpret as the data being in memory and not I/O bound. It also shows that this query alone is pinning the needle.
  4. Ozar Blitz reveals nothing remarkable.
  5. The query has at least a 20 table join, so the query optimizer is going to have trouble creating a plan that optimally uses indexes and so forth. Big red flag for some query redesign and tuning.
  6. The query does have some # type temp tables as intermediate result stores, so its broken up into chunks to some degree–could be a good thing.

Leaving us with nothing remarkable in the environment. There is also some thought from the group that the problem may have started after windows/SQL Server patching the previous week. I did not pursue this, as it did not seem a fruitful line of inquiry and the task is to get things working in the current environment, not kick the can down the road by regressing some maintenance that will need to be applied later.

My immediate recommendation is to have the client engage in some query tuning, but there is a Plot Twist!—the client has been able to copy the query code from the stored procedure and when its run as a script in a SMSS query window, it takes 10 seconds to run… As Sherlock Holmes would say, this takes the mystery from the mundane to the interesting.

That is the head-scratcher… We know that in general stored procedures run faster because they have pre-compiled query plans. There is something on the edge of my memory along the lines of the query optimizer not being able to predict the cardinality of temp tables, but I can’t quite put my finger on it. At this point, it is quittin’ time and we agree to restart discussion the next morning. The query authors will look into query tuning—but we keep getting back to the part where it runs fast outside of a stored procedure. One option is to see if the reporting technology can use raw SQL as a query source rather than a stored procedure—not a terribly satisfying solution. I keep thinking “What is it about a pre-compiled query plan that is slower than a script?

So, about 10PM that night, as my head hits the pillow, I start thinking about dynamic query optimization. A few years ago at Ignite, I had the privilege of watching a presentation on new capabilities added to the query optimizer—the ability to take intermediate results of a query and change the query plan based on this information.

I start imagining that the pre-compiled query plan in a stored procedure might not have access to dynamic query optimization. Another vague and ancient memory pops into mind—the WITH RECOMPILE option. I have only read about it, never saw it in the wild.

The next morning I suggest the use of WITH RECOMPILE to the query authors. They report back that the performance of the stored procedure is extremely fast. This seems to me a bit like a brute-force approach, but it did the job and the next task presses, so I left it with that.

The only concern about this sort of solution is that once developers see it making a huge improvement in one query, they will add this to every stored procedure they touch for the rest of their lives, and in this respect, losing the normal advantage of the pre-compiled query plan in a stored procedure

To the extent my assumptions here are wrong, glad to be educated. The only thing I can say for sure is WITH RECOPILE solved the problem.

MSDB system tables: a Treasure Island for the intrepid administrator. Part 1, SQL Agent.

There are times when coding up a query using MSDB system tables or views can save an enormous amount of time for some administrative tasks. Now a thoughtful administrator could reasonably ask “Why would I want to query and update system tables? Surely Microsoft has provided tools to handle any tasks that one might need to perform.

My answer is that in computer years, MSDB and its supported functionality such as SQL Agent, date back to the Ming Dynasty. I don’t recall that SQL Agent has gained much functionality since SQL Server 2000—at least to the best of my recollection—and there does not appear to be much activity in this area on the horizon. So, the intrepid administrator’s kit bag of queries can be a wonderful thing once stocked with MSDB helpers.

So, given that moment when one faces the prospect of using Management Studio to right click over 100 Agent jobs in order to disable them, and some sort of script starts looking attractive, how does one proceed? There are two paths, and I normally follow both. First, there are lots of internet posts on just about any question one has. When I do an internet search, I always start the query with ‘sql server’ followed by the specifics. The second path is to get on a test system with Management Studio and start exploring the system tables and views—looking at names, and selecting top 1,000 rows to see what the data looks like. Let’s turn our attention now to how SQL Agent has been exposed to us in MSDB. Many of these objects may have started life as system tables, but evolved into system views. For querying, the distinction is not important, but when it comes to updating, of course it will be tables we want to target.

I will be using SQL Server 2017 for my examples here. Earlier releases may have slight name changes, but the functionality is very similar. I will indicate the high points such as join columns, and encourage the reader to do some selects from the tables to get their own ideas as they prepare data shopping lists for their own queries.

For agent, a great place to start is the table dbo.sysjobs. There is no primary key designated for this table, but I believe job_id performs that role and contains a guid-like value. The other columns expose much of what you will see when you use Management Studio to view the properties of a job. There is name, descripton, start step and others that may be of interest depending on the situation.

Job steps are implemented using dbo.sysjobsteps. It joins to job on job_id, and its unique key is job_id and step_id. A good way to get a feel for its data is to get the job_id from a job you are familiar with and do a select * for that job_id and order it by step_id. Now on to some example queries:

SELECT [name],SUSER_SNAME(owner_sid) AS owner
FROM msdb.dbo.sysjobs
where SUSER_SNAME(owner_sid) != ‘sa’

It’s a good practice to ensure that all system jobs are owned by SA. The query above lists non-compliant jobs. SUSER_SNAME is a function which gets the user name from the sid. Keep an eye out for functions like this which can eliminate the need to do a join to another system table in order to get the name for a value.Note the user of brackets—not strictly necessary for the query to work, but a reminder that many of the column names in system tables are SQL Server reserved words.

select [name],[description] from msdb.dbo.SysJobs
where [enabled] = 1
order by [name]

The query above filters on the enabled status of the job. I recently used this as part of a system migration to ensure that all jobs had been disabled on a server after its databases and jobs had been migrated to a new system. This can be changed into an update query to disable a large number of jobs, with perhaps a where clause based on job name.

j.[name] as jobname

from msdb.dbo.sysjobs j

left join msdb.dbo.sysjobsteps js
on j.job_id = js.job_id

where j.[name] like ‘%backup%’
order by j.[name], step_id

The query above provides an example of joining the job and jobstep tables together. Note the use of a left join, as jobs can be set up with no steps.

j.[name] AS [JobName]
when j.notify_level_email = 0 then ‘Never’
when j.notify_level_email = 1 then ‘When job succeeds’
when j.notify_level_email = 2 then ‘When job fails’
when j.notify_level_email = 3 then ‘When job completes (success or fail)’
end as notify_level
,isnull(o.[name],’none’) as notify_operator_name
FROM dbo.sysjobs j
LEFT JOIN dbo.sysoperators o
ON j.notify_email_operator_id = o.id

The query above introduces the dbo.sysoperators table and shows how it can be joined to the jobs table to get information about job notifications. Notice the need to use a case statement to translate the notification integer into a meaningful description. The meaning of these values is found in the Microsoft documentation of the dbo.sysjobs table which can be found with an internet search.

use msdb
with a as (
sj.[name] as jobName,
dbo.agent_datetime(jh.run_date,jh.run_time) as run_date,
jh.message as step_message,
ROW_NUMBER() over (partition by sj.name, jh.step_id
order by dbo.agent_datetime(jh.run_date,jh.run_time) desc) as run_seq,
FROM dbo.sysjobs sj

join dbo.sysjobhistory jh
on jh.job_id = sj.job_id

where jh.step_id = 0
select jobName,step_id,run_date,step_message
from a
run_seq = 1 and run_status = 0
order by jobName, step_id

The above query brings in the dbo.sysjobhistory table. This table contains job and step run history. One aspect of this table that is not intuitively obvious is that it uses job step 0 to contain the overall job run history. This query checks the job history to identify jobs there the most recent run has failed. This can be mailed to developers to indicate which jobs have failed and may need to be fixed.Note also the use of an SQL Server function, dbo.agent_datetime which takes the separate date and time columns in the job history and converts them into a datetime format.This is an undocumented function in SQL Server, so it may be removed in a future release. Were this to happen, one would need to write one’s own function to do the same sort of conversion.

I hope these examples and discussion help you harness the SQL Agent system tables when a tedious or automated task needs to be performed by a script.