I use PowerShell quite a lot to manage servers, especially SQL Servers. So I need to be able to run PowerShell commands remotely. Fortunately I don't have to worry about this much on Windows Server 2012 and up, because the PowerShell remoting is enabled by default. On the other hand, if an organization deems PowerShell, and PSRemoting in particular, as a security risk and decides to disable it on purpose. that obivoulsly is a different matter.
And, any earlier versions of Window Server and all versions of the Windows client operating systems (Home Pro etc.), PowerShell remoting is turned off by default.
To enable PSRemoting:
Connect / RDP into the computer.
Launch the PowerShell with elevated privileges (Run As Administrator).
Run the command:
Enable-PSRemoting–Verbose and –Confirm
I added the -Verbose and -Confirm parameters because by default, the Enable-PSRemoting runs silently with no output whatsoever.
Here is a link to the Microsoft article for more information on Enable-PSRemoting command:
And, if you’re working in a workgroup environment, for example, at home, with no domain controller to handle the security and identity, you need to add list of computers you trust. Here is the article that explains how-to, and troubleshooting other potential issues:
That's all nice. But how do you check whether it is already enabled or not? PowerShell doesn't provide a direct way to check whether PSRemoting is enabled or not. Indirectly, you can send a test command to the remote computer.
If errors out, it will display an ugly error message. The error per se could be due to a completely different reason, maybe you typed in wrong server name, WinRM service is not running, issues with your credentials, firewall, trusts etc.
Another, indirect method, is to run the Test-WSMan command to see whether WinRM service is running there or not.
Test-WSMan -ComputerName Servername
This too doesn't guarantee to tell if issue is with PSRemoting setup.
May be some future version of PowerShell will add a command to check status of PSRemoting locally as well as remotely, in secure ways of course. I am not too hopeful though because it has been enabled by default since Windows 2012, so Microsoft may not see a need for it or deem it a security risk.
Get Configuration Change History From the SQL Server Error Logs
There are several options if and when you need to see if any configuration change was made in SQL Server.
The simplest maybe to use the SSMS GUI, using its built-in (aka standard) reports:
The good thing about this standard report is that it reads from the default trace, which being the "default" setting, would have been already running, that means you don't need to setup and enable it. It is just there, unless of course it was purposely disabled/stopped.
The bad is that the default trace is of limited size, I think the size limit is 20 MB with up to 5 rollover files. You cannot change the size of default trace, you can however create your own trace, in which case better to look at setting up Extended Events instead.
You can also use the SQL Server Auditing and Extended Events features as well, which gives you lot more control on what to capture and to what size. The downside is that you need to set it up and it should be already running by the time there is a need to check if any configuration change was made.
SQL Server also logs anytime there is a configuration change, which you can then view and filter to find the configuration changes by searching for string "Configuration option". You can obviously use the SSMS to open and filter the log file, or even your favorite text editor. And better, you can programmatically read and filter using the sys.xp_readerrorlog:
Example 1: Search for entries containing Configuration option
USE master;
EXEC sys.xp_readerrorlog
0, -- 0 for the current log file, 1 = archive file #1 etc1, -- 1 or NULL for SQL Server error log, 2 for the SQL Agent log
N'Configuration option', -- Search/filter by this stringNULL, -- Further refine the search/filter conditionNULL, -- Start timeNULL, -- End time
N'desc'; -- Sorts the results by asc for ascending order or
-- desc for descending order
Example 2: A second filter for the Start Time
USE master;
EXEC sys.xp_readerrorlog
0, -- 0 for the current log file, 1 = archive file #1 etc1, -- 1 or NULL for SQL Server error log, 2 for the SQL Agent log
N'Configuration option', -- Search/filter by this stringNULL, -- Further refine the search/filter condition'2023-09-08 16:00:00', -- Start timeNULL, -- End time
N'desc'; -- Sorts the results by asc for ascending order or
-- desc for descending order
Example 3: Further refine/search into the results
USE master;
EXEC sys.xp_readerrorlog
0, -- 0 for the current log file, 1 = archive file #1 etc1, -- 1 or NULL for SQL Server error log, 2 for the SQL Agent log
N'Configuration option', -- Search/filter by this stringN'Optimize', -- Further refine the search/filter condition
NULL, -- Start time NULL, -- End time
N'desc'; -- Sorts the results by asc for ascending order or
-- desc for descending order
So, what is the catch here? Every time SQL Server is restarted, it starts a new log file and renames the previous log file to errorlog.1, and the previous errrolog.1 gets renamed to errorlog.2 and so forth, up to 7 logs, which you can increase up to 99. Stored procedure sp_cycle_errorlog can be used to start a new log file in between the SQL Server restarts. That means 1) The older configuration change entries would get moved to the so called archived error logs, which sys.xp_readerrorlog does allow to read from as well, and 2) Eventually the oldest archived error log files will get removed, unless of course you setup something to store the log entries somewhere.
Of course, you can also use sys.xp_readerrorlog for other purposes as well, for example to see if there have been any errors in last 24 hours.
USE master;
declare@start_time datetime = getdate() -1EXEC sys.xp_readerrorlog
0, -- 0 for the current log file, 1 = archive file #1 etc1, -- 1 or NULL for SQL Server error log, 2 for the SQL Agent log
N'Severity: 16', -- Search/filter by this string
N'41145', -- Further refine the search/filter condition@start_time, -- Start timeNULL, -- End time
N'desc'; -- Sorts the results by asc for ascending order
Emergency Access to SQL Server: A DBA's Guide to Regaining Control
A word of caution:
I am a bit apprehensive here, so I would like to make this clear from the beginning: This is really intended for responsible and cautious DBAs who have a legitimate need to access SQL Server but there is no one around with DBA (i.e., sysadmin) role or any access at the instance level for that matter. Yes, there can still be loopholes in your SQL Servers that, if left open, can be leveraged by "ethical" as well as rogue actors alike. Here is a relatively old article discussing some of the ways SQL Server security might be compromised:
How do you manage and troubleshoot a SQL Server if you don't any access to it at all? You can't. Even if you have full administrative rights on the server or even in the entire IT network, you cannot connect to the SQL Server if you don't have valid credentials with sysadmin rights.
Unless...... it is a very very old SQL Server version, like SQL Server 7 or older. Back then SQL Server would automatically add the local administrators (BUILTIN\Administrators) as sysdba during the installation. You could go back and remove that access, but I don't think most people bothered. But back then there was even a bigger security issue with SQL Server, blank password for the almighty sa user by default!!!!!
Fortunately those dark days are long gone, SQL Server has come a very long way since then and so have we, in our mindsets regarding the security.
But that is not what I am here to talk about.
So back to the topic, this problem per se is not new and neither is the solution. Most long time DBAs would know the solution already, as they would have run into this issue at some point in their career.
Let me just describe the solution briefly and get it out of the way as this article is not about that.
The solution requires that you are a member of the local administrators group on the computer where SQL Server instance is installed. You log into the server, stop and then restart the SQL Server in a single user mode, that temporarily grants local administrators sysadmin permissions. You then connect to the SQL instance with Windows Authentication, and make yourself or any login you like a member of the sysadmin role. Then stop and restart the SQL service in normal mode. VOILÀ!
Below is a link to a how-to document on this very topic from Microsoft:
Recently, I got a new gig and had to solve this problem on more than 200 SQL Servers, over a single weekend! Given the short amount of time and a relatively large number of SQL Servers, I decided to write a PowerShell script that I can then call remotely on each SQL Server. At first the PowerShell code was a relatively short one, without any validation or error checking. It basically restarts the SQL instance in single user mode, adds a AD domain group for DBAs to the sysadmin role then restarts back the SQL Instance and the SQL Agent services:
# INPUTS$sql_instance_name = 'MSSQLSERVER'$login_to_be_granted_access = 'Contaso\Group-MSSQL-DBAs'# GET THE NAME FOR THE WINDOWS SERVICE AND THE SQL CONNECTIONif($sql_instance_name-eq"MSSQLSERVER")
{
$service_name = 'MSSQLSERVER'$sql_server_instance = "."
}
else
{
$service_name = "MSSQL`$$sql_instance_name"$sql_server_instance = ".\$sql_instance_name"
}
$sql = "CREATE LOGIN [$login_to_be_granted_access] FROM WINDOWSGOALTER SERVER ROLE sysadmin ADD MEMBER [$login_to_be_granted_access]GO"$check_permission = "IF EXISTS (SELECT * FROM sys.server_role_membersWHERE member_principal_id = SUSER_ID('$login_to_be_granted_access')AND role_principal_id = SUSER_ID('sysadmin')) PRINT 'VERIFIED'ELSE RAISERROR('ERROR: Verification failed.', 16, 1)GO"# STOP/START SQL SERVER IN SINGLE USER MODEStop-Service -Name $service_name -Force
net start $service_name /f /m"SQLCMD"
Start-Sleep 2
sqlcmd.exe -E -S $sql_server_instance -Q $sql
sqlcmd.exe -E -S $sql_server_instance -Q $check_permission# Stop the serviceStop-Service -Name $service_name -Force
# RESTART SQL SERVICES IN NORMAL MODE$service = Start-Service -Name $service_name -PassThru
Start-Service -Name $service.DependentServices[0].Name
Note: Restarting a Windows service requires administrator privileges, including for the process from where the service is being restarted from, which in this case is PowerShell and therefore it needs to be launched as “Run As Administrator”:
Later, I added more code for validation, error checking, and an option to create a SQL Login instead of a Windows Login. Most importantly, I added the ability to run this script remotely. As a result, the script is now longer than it perhaps should be.
##### CAUTION: THE SCRIPT WILL STOP AND RESTART YOUR SQL SERVER INSTANCE!!!!!!!!! <#THIS SCRIPT IS INTENDED TO GET ACCESS TO SQL SERVER ONLY IF YOU DON'T HAVE SYSADMIN PERMISSION. USE ONLY IN EMERGENCY.#><#.NOTATIONThis script is intentionally lengthy for several important reasons:- It must handle multiple complex steps safely to grant sysadmin access to SQL Server- It needs to validate environment prerequisites such as elevated rights and service state- The script carefully stops, starts, and manages dependencies of SQL Server services- Confirmation prompts and detailed error handling are included to prevent unintended disruptions- To avoid automation mistakes, explicit validations and user prompts are mandatory- The overall complexity reflects the sensitive operation of forcibly gaining sysadmin accessPlease read and understand each section of the script carefully before using it,and always run this script in a controlled environment with proper permissions.#><#REQUIREMENTS:1. Local Administrator rights on the server2. Run locally or via Invoke-Command with PSRemoting enabled (default on Windows Server 2012+)3. Elevated PowerShell session (Run as Administrator)PARAMETERS:- $login_to_be_granted_access (string, required): Windows or SQL login to grant sysadmin access- $sql_instance_name (string, optional): SQL instance name (default instance if omitted)- $confirm (bool, optional): Prompt for confirmation before stopping SQL service. Default: $true- $sql_login_password (string, optional): SQL login password required if SQL login#><# Examples:Example 1: Running the Script Locally- Save the script on your local device, for example as C:\Scripts\Gain-SqlSysadminAccess.ps1- Open PowerShell with Administrative privileges (Run as Administrator).- Navigate to the folder containing your script, for example: cd C:\Scripts- Run the script with required parameters. For example, to grant sysadmin access to a Windows login named "DOMAIN\User1" on default instance with confirmation prompt:.\Gain-SqlSysadminAccess.ps1 -login_to_be_granted_access "DOMAIN\User1" -confirm $falseExample 2: Running the Script Remotely Using Invoke-Command- From your local machine with PowerShell launched as Administrator, run the script on a remote computer (e.g., RemoteServer01). Make sure PSRemoting is enabled on the remote server.- Use the following command to invoke the script remotely:# Run the local script on a remote computer, passing parametersInvoke-Command -ComputerName RemoteServer01 ` -FilePath "C:\Scripts\Gain-SqlSysadminAccess.ps1" ` -ArgumentList "DOMAIN\User1", "SQL2022AG01", $false- Replace parameters accordingly for your target environment#>param (
[string]$login_to_be_granted_access = 'sqladmin',
[string]$sql_instance_name = 'SQL2022AG01',
[bool]$confirm = $true,
[string]$sql_login_password = 'WA1!!1P7JRjN7F4eibEES&IxU%Elgw6b#'
)
# Set default preferences$ErrorActionPreference = 'Stop'$WarningPreference = 'Continue'$InformationPreference = 'Continue'# Assume default instance if sql_instance_name not specifiedif (-not$sql_instance_name) { $sql_instance_name = 'MSSQLSERVER' }
if ($null-eq$confirm) { $confirm = $true }
Write-Information"Computer Name: $env:COMPUTERNAME"Write-Information"SQL Instance Name: $sql_instance_name`n"# Confirm prompt if requiredif ($confirm) {
$valid_responses = 'Yes', 'yes', 'No', 'no'do {
Write-Warning"##### CAUTION: THE SCRIPT WILL STOP AND RESTART YOUR SQL SERVER INSTANCE!!!!!!!!!"$response = Read-Host"Are you sure you want to continue (Yes/No)?"if (-not$valid_responses.Contains($response)) {
Write-Host"Please enter Yes or No"
}
} until ($valid_responses.Contains($response))
if ($response -in @('No', 'no')) { return }
}
else {
Write-Warning"Confirmation prompts are disabled."Write-Information""
}
# Validate mandatory parametersif (-not$sql_instance_name-or-not$login_to_be_granted_access) {
throw"Error: Both `\$sql_instance_name` and `\$login_to_be_granted_access` are required."
}
if (-not$login_to_be_granted_access.Contains('\') -and-not$sql_login_password) {
throw"A password must be provided for SQL Login."
}
# Check for elevated privileges$isAdmin = ([System.Security.Principal.WindowsIdentity]::GetCurrent()).Owner -eq'S-1-5-32-544'if (-not$isAdmin) {
throw"Error: Powershell must be launched in elevated privileges mode (Run as Administrator)."
}
# Determine service and SQL Server instance namesif ($sql_instance_name-eq'MSSQLSERVER') {
$service_name = 'MSSQLSERVER'$sql_server_instance = '.'
}
else {
$service_name = "MSSQL`$$sql_instance_name"$sql_server_instance = ".\$sql_instance_name"
}
Write-Information"SQL Server Instance: $sql_server_instance"Write-Information"Service Name: $service_name`n"# Get SQL service and dependent services$sql_service = Get-Service -Name $service_name -ErrorAction Stop
$dependent_services = $sql_service.DependentServices
if (-not$sql_service) {
throw"Error: SQL instance '$sql_instance_name' or service '$service_name' not found."
}
Write-Information"Service Status: $($sql_service.Status)"Write-Information"Service Startup Type: $($sql_service.StartType)`n"# Re-enable if disabledif ($sql_service.StartType -eq'Disabled') {
Write-Warning"SQL instance '$sql_instance_name' is currently disabled."if ($confirm) { Set-Service -Name $service_name -StartupType Manual -Confirm }
else { Set-Service -Name $service_name -StartupType Manual }
$sql_service.Refresh()
if ($sql_service.StartType -eq'Disabled') {
throw"Error: Cannot continue while SQL instance is Disabled."
}
}
# Stop the service if runningif ($sql_service.Status -eq'Running') {
Write-Warning"Stopping service: $service_name and its dependent services..."if ($confirm) { Stop-Service -InputObject $sql_service -Confirm -Force }
else { Stop-Service -InputObject $sql_service -Force }
Start-Sleep -Seconds 1
$sql_service.Refresh()
if ($sql_service.Status -ne'Stopped') {
throw"Error: SQL instance service '$service_name' did not stop as expected."
}
}
# Start the service in single-user mode if appropriate$sql_service.Refresh()
if ($sql_service.Status -ne'Running'-and$sql_service.StartType -in @('Manual', 'Automatic')) {
Write-Warning"Starting SQL Server service in single user mode..."Write-Information""
net start $service_name /f /m"SQLCMD" | Out-NullStart-Sleep -Seconds 1
$sql_service.Refresh()
if ($sql_service.Status -eq'Running') {
if ($login_to_be_granted_access.Contains('\')) {
$sql = @"CREATE LOGIN [$login_to_be_granted_access] FROM WINDOWS;GOALTER SERVER ROLE sysadmin ADD MEMBER [$login_to_be_granted_access];GOSELECT @@ERROR AS [ErrMsg];GO"@
}
else {
$sql = @"CREATE LOGIN [$login_to_be_granted_access] WITH PASSWORD=N'$sql_login_password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;ALTER SERVER ROLE sysadmin ADD MEMBER [$login_to_be_granted_access];SELECT @@ERROR AS [ErrMsg];"@
}
Write-Information"Adding login '$login_to_be_granted_access' to SYSADMIN role..."Write-Information$sql
sqlcmd.exe -E -S $sql_server_instance -Q $sqlWrite-Information""$check_permission = @"IF EXISTS ( SELECT * FROM sys.server_role_members WHERE member_principal_id = SUSER_ID('$login_to_be_granted_access') AND role_principal_id = SUSER_ID('sysadmin')) PRINT '****** VERIFICATION SUCCEEDED ****************'ELSE RAISERROR('ERROR: Verification failed.', 16, 1);GO"@Write-Information"Verifying sysadmin permissions..."Write-Information$check_permission
sqlcmd.exe -E -S $sql_server_instance -Q $check_permissionWrite-Information""Write-Information"Restarting SQL instance in normal mode..."
net stop $service_name | Out-Null
net start $service_name | Out-NullWrite-Information""Write-Information"Restart dependent services if they were running previously"Write-Information""$dependent_services | Format-Table -Property DisplayName, Status, StartType
Write-Information""foreach ($dependent_servicein$dependent_services) {
$dependent_service_name = $dependent_service.Name
if ($dependent_service.Status -eq'Running') {
if ((Get-Service -Name $dependent_service_name).Status -ne'Running') {
Write-Information"Starting dependent service: $dependent_service_name"$dependent_service.Start()
}
}
}
}
else {
throw"Error: SQL instance did not start as expected."
}
}
Now what? How do you use it then? If it is just one or two SQL Servers, you can simply copy/paste it into an elevated PowerShell session, and edit the param block with the appropriate values.
Note: You can give a Windows login for the $login_to_be_granted_access parameter, as long as it contains a "\" in it, the script will know it is a Windows login.
Then click the green Run Script button or hit F5 key:
It will display a warning and ask if you want to continue:
It will also ask to confirm again before stopping the SQL Service.
You can disable the confirmation prompts by setting $confirm = $false, which is what I would do when running this in non-interactive mode, especially in a batch mode when running it against multiple SQL Servers at the same time. In the following example, the script is saved in a file "Gain-SqlSysadminAccess.ps1, then using the Invoke-Command, I can run it on a remote server (highlighted):
# Add integrated/windows authenticated login or group
To run the script on multiple SQL Servers, you can save the list of SQL servers, instance names and login name in a CSV file. For integrated/Windows logins, the LoginName needs to be in "ServerName\UserName" or "Domain\UserName" format and must contain a "\" as that is what my script is using to dynamically determine whether is is Windows login or a SQL login. For example:
Then use the following PowerShell code to execute the script against all servers listed in the CSV file:
We often think of ad hoc query plans, perhaps due to my own dismissive attitude towards it, as single-use plans that won't be reused frequently or at least not in the very near future. By that time, the plan might have already been evicted from the cache by SQL Server. However, what if a significant portion of our overall workload consists of such queries? Let's say, for the sake of argument, more than 50%. In this scenario, caching these execution plans would simply waste SQL Server's memory, and the server's memory in general. Some might argue this can bloat the plan cache.
As an illustration, consider a real-world scenario where the memory consumed by these query plans is a staggering 30GB!
SELECT
instance_name AS name,
cntr_value /128AS pages_mb,
cntr_value /128/1024AS pages_gb
FROM sys.dm_os_performance_counters
WHERE counter_name ='Cache Pages'ORDERBY pages_mb DESC;
Next, I fire up a slight variation of the query to see the type of workloads using the most memory:
;WITH cte_plan_cache_usage_by_obj_type AS
(
SELECT COALESCE(objtype, '-- ALL Plans') objtype,
Sum(Cast(size_in_bytes ASBIGINT)) /1024/1024 size_mb
FROM sys.dm_exec_cached_plans
GROUPBYrollup ( objtype )
)
SELECT objtype,
size_mb,
'percent'=
( size_mb *100 ) /
(SELECT size_mb
FROM cte_plan_cache_usage_by_obj_type
WHERE objtype ='-- ALL Plans'
)
FROM cte_plan_cache_usage_by_obj_type
ORDERBY size_mb DESC
Wow, the ad hoc queries are using a staggering 94%!
This workload is predominantly ad hoc. And no, I am not in a position to instruct the application team to modify their queries, at least not in the short term.
So for now, any improvements must be made on the backend SQL Server.
Fortunately, for these types of workloads, SQL Server offers a configurable setting called “optimize for ad hoc workloads”. This setting helps limit or reduce the memory used by single-use ad hoc queries. The key here is "single-use". How does it work? When this setting is enabled, the optimizer doesn't cache the full execution plan for an ad hoc query initially. Instead, it caches only a much smaller plan "stub" along with its query hash and plan hash. This plan "stub" isn't a full execution plan, so it's not reusable for subsequent runs of the same query. However, if that same query is executed again soon after, the optimizer will then cache the reusable, full execution plan. Now if you have a very long running analytical query that runs once a day, should its full execution plan be cached? My qualified answer would be no, because it is very likely that the cached plan would need to be recompiled anyway due to manual or automatic updates of index statistics, index rebuilds, or other reasons—or it may simply be evicted from the cache by then.
In short, when optimize for ad hoc workloads is turned on:
The first execution creates a small compiled plan stub.
On a repeat (exact) query, SQL Server promotes the stub to a full, reusable plan.
Plans with low use counts, or not used for many hours, may be best candidates for removal to further free memory.
In environments where single-use, ad hoc queries constitute a large portion of the workload, this setting can save a significant amount of memory, freeing it up for other tasks.
Note: For two ad-hoc queries to be reused in SQL Server, they must have an exact text match. This match is sensitive to both case and spacing, even on servers that are case-insensitive.
By default, this setting is turned off. However, I occasionally encounter situations where I find it beneficial to enable it. Why? Because memory in SQL Server is valuable. Perhaps an even more compelling reason is that many SQL servers have a limited memory allocation. As much as I'd like to, I can't convince myself, let alone my manager, to allocate 2TB of RAM to every server.
In the example I presented, the 30GB plan cache might appear excessive without understanding the context. Heck, most of my SQL servers don't even have that much total memory. To shed some light, here's some context regarding this SQL Server:
SQL Version:
Microsoft SQL Server 2019 (RTM-CU18)
(KB5017593) - 15.0.4261.1 (X64)
Enterprise Edition: Core-based
Licensing (64-bit)
Windows Server 2019 Standard 10.0
<X64> (Build 17763: ) (Hypervisor)
While a MAXDOP of 8 is the recommended best practice, I'm skeptical whether it's the optimal setting for this SQL Server, particularly considering its primarily analytical workload and 112 cores. It might be worthwhile to adjust this value dynamically based on the time of day and expected workload. For example, if there's an extensive ETL job running every night for 5 hours, designed for exclusive use of the entire server, thereby blocking or prohibiting other users, then that ETL job might benefit from a higher MAXDOP value. I'll explore this in more detail later on.
In this context, where the SQL instance has more than 2TB of RAM available, the 30GB cache size represents less than 1.5%. This percentage is, on average, lower than that of most other SQL Servers. However, in absolute terms, it's still significant, so it's worth investigating.
Let's first check what the current setting is for 'optimize for ad 'hoc workloads'.
It's not enabled, which is the default, so it remains in the turned-off state. Ordinarily, I wouldn't spend more time investigating this; I would simply enable the setting. However, in this case, I've been hired solely to investigate and make recommendations. I'm not authorized to implement any changes myself.
Therefore, I need to delve deeper into my investigation. But before proceeding, let's discuss: what precisely defines an execution plan as 'ad hoc'?
As I highlighted at the outset, some of us often view an ad hoc plan or query as something executed only once. This perception is only partially accurate, especially when considering how SQL Server categorizes a query plan as 'ad hoc' or otherwise in its cache:
“Adhoc: Ad hoc query. Refers to Transact-SQL submitted as language events by using osql or sqlcmd instead of as remote procedure calls”
That description is straight from this MS article:
Even though it doesn't explicitly mention SSMS, queries executed from SSMS are also labeled as ad hoc by the optimizer.
It's not the frequency of a query's execution that determines its classification as an ad hoc query. However, the number of query executions does influence whether the "optimize for ad hoc workloads" setting caches the full execution plan or just a stub.
In practical terms, ad hoc queries are those that either lack parameters or cannot be auto-parameterized by the optimizer and are not part of a database object (like stored procedures, functions, views, etc.). Consider, for instance, this simple query:
Observe that the query utilizes a constant value 'tempdb' in the WHERE clause, rather than a variable or parameter. Even so, the optimizer can—and often will—attempt to parameterize this query if it's included within a stored procedure or sent as a prepared SQL query. Interactive client tools like sqlcmd, osql, SSMS, and others don't execute such queries as prepared statements. As a result, the optimizer labels them as ad hoc queries and caches their execution plans accordingly. If you have processes that frequently run certain queries using such tools, consider using sp_executesql. This way, they're cached as prepared plans, which can be reused to enhance query performance.
Now, let's examine the subsequent example where the same SQL statement is executed in three distinct manners to obtain identical results:
-- Ad hoc querySELECT NAME FROM sys.databases dbs WHERE NAME ='tempdb';
GO-- Prepared SQL statementEXEC Sp_executesql N'SELECT NAME FROM sys.databases dbs WHERE NAME = @db_name',
N'@db_name sysname',
N'tempdb'GO-- Stored Procedure
IF Object_id('sp_test_adhoc_plans_cache', 'P') ISNOTNULLDROPPROCEDURE sp_test_adhoc_plans_cache
GOCREATEPROCEDURE sp_test_adhoc_plans_cache
(@db_name SYSNAME ='tempdb')
ASSELECT NAME FROM sys.databases dbs WHERE NAME =@db_name;
GOEXEC sp_test_adhoc_plans_cache
GO
Now, observe how the optimizer processed and cached those queries:
SELECT cp.plan_handle,
cp.objtype,
cp.cacheobjtype,
cp.size_in_bytes,
cp.usecounts,
qt.text,
qs.query_hash,
qs.query_plan_hash
FROM sys.dm_exec_cached_plans cp
innerjoin sys.dm_exec_query_stats qs on qs.plan_handle = cp.plan_handle
CROSS apply sys.Dm_exec_sql_text(cp.plan_handle) qt
WHERE qt.textLIKE'%SELECT NAME FROM sys.databases dbs WHERE NAME =%'AND qt.textNOTLIKE'SELECT cp.plan_handle%'
For clearer visibility of its content, I've divided the results into two screenshots:
Observe that the query hash and plan hash values for them are identical. However, the plan handles and objtype differ, even for the same input value, 'tempdb'. While the cached plan size for the ad hoc query is considerably smaller than for the other plans, it remains larger than it would be if 'optimize for ad hoc workloads' were enabled. Let's confirm that:
EXECsp_configure'optimize for ad
hoc workloads', 1;
Rerun the same three queries and examine the cache information:
Note that the plan_handle values differ from those in the previous screenshot, as I cleared the earlier plans from the cache.
Observe the size of the ad hoc plan: merely 456 bytes, with the cacheobjtype listed as 'Compiled Plan Stub'. That's correct – it's only a stub. It doesn't contain an execution plan, so technically, there's no cached plan available for reuse. However, should the same ad hoc query be executed again in the near future, the optimizer will generate a full execution plan for it, which can be reused by subsequent invocations of the same query. Let's validate that:
Here is the info from sys.dm_exec_cached_plans after running the ad hoc query the second time:
The cacheobjtype for the Adhoc plan is no longer a tub.
Given this information, would I recommend enabling the 'optimize for ad hoc workloads' setting? Given that over 90% of the workload is ad hoc, the risks seem minimal. However, I'd like to examine one more piece of data. The following query displays the aggregate memory usage for:
Ad hoc query plans
Plans that are not stubs
Plans with a use count of 2 or fewer
Plans not used by a query in over 5 hours
;with cte_cached_adhoc_plans as
(
SELECT plan_handle,
MAX(last_execution_time) last_execution_time,
SUM(execution_count) execution_count
FROM sys.dm_exec_query_stats
GROUPBY plan_handle
)
SELECTCOUNT(*) [Plan Count],
SUM(CAST(size_in_bytes ASBIGINT)) /1024/1024 [Size MB],
MAX(cte_cached_adhoc_plans.execution_count) [MaxExecCount],
SUM(cte_cached_adhoc_plans.execution_count) [Total ExecCount]
FROM sys.dm_exec_cached_plans cp
INNERJOIN cte_cached_adhoc_plans ON cte_cached_adhoc_plans.plan_handle = cp.plan_handle
WHERE cp.objtype ='Adhoc'AND cte_cached_adhoc_plans.last_execution_time < DATEADD(HOUR, -5, GETDATE())
AND cp.objtype !='Compiled Plan Stub'AND cte_cached_adhoc_plans.execution_count <=2
That's nearly 28GB. In this case, I would recommend enabling the 'optimize for ad hoc workloads' setting. However, like any recommendation involving configuration changes, it comes with caveats. Exercising caution is essential, as any change can have potentially unintended and sometimes adverse effects. The organization should have a robust change control procedure in place, complete with a back-out plan.
As an alternative, the following query can be used to generate a script to remove individual ad hoc query plans from the cache. Please tailor the filter conditions to fit your needs. This script could be scheduled to run at appropriate times, perhaps after peak business hours and just before nightly ETL or maintenance tasks commence.
;with cte_cached_adhoc_plans as
(
SELECT plan_handle,
MAX(last_execution_time) last_execution_time,
SUM(execution_count) execution_count
FROM sys.dm_exec_query_stats
GROUPBY plan_handle
)
SELECT TOP 1000
[Remove Cached Plan] ='DBCC FREEPROCCACHE (0x'+convert(varchar(max), cte_cached_adhoc_plans.plan_handle, 2) +');'FROM sys.dm_exec_cached_plans cp
INNERJOIN cte_cached_adhoc_plans ON cte_cached_adhoc_plans.plan_handle = cp.plan_handle
WHERE cp.objtype ='Adhoc'AND cte_cached_adhoc_plans.last_execution_time < DATEADD(HOUR, -5, GETDATE())
AND cp.objtype !='Compiled Plan Stub'AND cte_cached_adhoc_plans.execution_count <=2
In conclusion, as we're aware, SQL Server will evict cached plans and cached data pages from the buffer cache as necessary, using its continually evolving algorithm. So, why not trust its judgment? I generally concur, but with a caveat. The default settings and behaviors are effective for many SQL installations in a majority of scenarios. However, they don't cater to every SQL Server instance or every situation.
