Monthly Archives: July 2018

RECOMPLE behavior the difference between OPTION ( RECOMPILE ) AND WITH RECOMPILE

An interesting problem was posted on #SQLHelp the other day about using the QueryHint OPTION(RECOMPILE). If you are not sure about how recompile works here is a quick primer. Stored procedures can be expensive to run so SQL Server database engine takes a moment when running the procedure the first time to come up with a plan. Ideally the plan takes into account the current situations and arrives at what it thinks would get the job done in the quickest way with the least resources consumed. This plan is saved in the plan cache. Because a stored procedure is something that gets executed frequently it makes sense to reuse the plan over and over again. But like all things in life situations change and the plan that was working before might not be the best anymore. A simple example would be when the number of rows in the table have changed significantly.

In such cases it is better to RECOMPILE the procedure so that it always takes into account the current state and comes up with a new plan each time. To allow us to do this we have a few option.

Here is the question that was being asked.


This hint allows us to recompile the entire stored procedure (not just bits and pieces of it) each time it is executed. The below screenshot show the usage. When used this way we see that the Execution plan is discarded as soon as the query finishes execution. Since we need to recompile it every time the procedure executed there is no sense wasting memory on it.

Result of querying the plan cache can be seen below:-

If the hint was removed in the procedure we would end up with something like below:-

Another way to achieve RECOMPILES without actually modifying the procedure would be to call the hint at run time as shown below

Notice in the above screenshot we don’t have cacheobjtype = CompiledPLan Proc

Another way to recompile procedures is something called statement level RECOMPILE


Sometimes we arrive at a use case where the entire stored procedure seems to be fine except for a single block of code. If the procedure is sufficiently complex it doesn’t make sense to RECOMPILE the whole thing. In these cases we can go for statement level recompile. This is achieved by adding the hint OPTION( RECOMPILE) against the statement we want recompiled as shown below.

There is an important distinction that happens at this time. Since we are not RECOMPILING the entire procedures from scratch we end up with a plan in the plan cache. SQL Server still Recompiles the block that has the hint but the rest remains untouched.

Notice that we have use count of 10 and our cache objtype is Compiled plan type Proc.

In summary to answer the question OPTION(RECOMPILE) will result in plan being cached because it does statement level recompile while WITH RECOMPILE will not cache the plan since the entire procedure will get recompiled.



Smart Log backups SQL Server 2017

Smart log backups is a feature introduced in SQL 2017 to allow DBAs to manage the transaction log growth better. Before we go into details it might be better to understand what problem it was trying to solve.

In any typical production workload we often have uneven usage on the database system, often the majority of the work is performed during office hours so we see the T-log grow significantly during this time and in order to control the disk usage we take frequent backups. However during non-peak hours we see that taking such frequent backups is an overhead since there are hardly any changed being logged in the transaction log file when no users are connected.

Sometimes due to sudden spikes in usage we can also see the TLOG grow significantly before the next backup run is scheduled as a result it grows to a larger size before the log backup can be taken unfortunately this excess size is not reclaimed automatically after the backup completes resulting in wasted disk space.

I have seen a few implementations where DBAs have used this feature to reduce the number of T log backups taken during off peaks hours and I don’t agree with the approach. Here is why



Changing Default port number for SQL on Windows/Linux Install

Changing the default port number of a SQL installation is one of the most basic post install steps performed. It is mainly used to make intrusion more difficult by preventing users from taking advantage of the default settings which are known in the public domain. SQL Server when install as a default instance uses port 1433. Since everyone knows this it becomes easier to target this port to try and connect to the database server.

MS SQL Server on Windows


We can change the port number by opening the configuration manager and changing the port or the TCP/IP network configurations in SQL Server Configuration manager.

Note: – this change only makes sense if connecting via TCP/IP.

First enabled TCP/IP Connections, as you can see its disabled. Once enabled you need to restart SQL Server services in order for the changes to take effect.

After the restart double click the TCP/IP setting again and navigate to the IP Addresses tab and scroll to the IP address from which you want to accept connections. Most servers will have multiple network cards and it is a good idea to accept connections from specific NIC instead of all of them. To assign port number to all IPs in one go you ca navigate to the bottom of the tab and set TCP Port Value under IPAll section.

In the above screenshot I have set 6001 as the default for all NICs with the IP assigned (IPS are always static for database servers). Make sure the firewall has these ports open else they will block connections anyway. Next restart the SQL Server Service and try connecting via SSMS as shown below. Notice the comma between the server name and the port number.

If you tell SQL server to listen to some NIC that doesn’t actually exists like maybe a Virtual IP your SQL Server instance won’t be able to restart and you will see the below error message in event log


MS SQL Server on Linux


SQL on Linux doesn’t have the same UIs as we see in windows OS so most of the work when it comes to configurations are done via terminal and mssql-conf utility

To begin you need to launch the terminal (equivalent of command prompt in windows)

Next in the shell we need to enter the below command. Which basically says go to the location bin/mssql-conf and edit the configuration file by looking for a key called Network.tcpport and setting its value to 6020

Press enter, after which you will be prompted to restart SQL Server service much like we did for windows. As you can see from the below screen the command is mentioned in the prompt you simply need to enter it as is and press Enter.

You will be prompted to enter the admin password after which the service will restart. Next open SSMS on a remote system (not available in Linux desktop, if you want to check from within Linux use SQLCMD)

Once you press connect you can check if it works properly by running @@version like the screenshot below:-

And that is all there is to it, as you can see in many ways it is much simpler to change the port number in Linux compared to Windows.

NOTE:- You can also make changes in bulk by navigating to the path /var/opt/mssql/mssql.conf and editing the file directly provided you have the required permissions, the file will look something like below