SQL TempDB Related

Sachin K

I have Three stored Procedures A,B,C C is a common procedure which derives data into a common table depending on parameters from respective procedures A or B and returns to a or b for further processing on one sql 2019 environment its working normally but on another if Proc A is executed first then later if procedure B is executed procedure b would return error after lot of debugging finally i tried calling proc C from A or B with recompile option then its working fine need a solution other than recompile any help appreciated. Please guide if any Sql Server settings can resolve as it works on sql 2019 server but on two different environments but failing on one.

Procedure C basically is a collection of dynamic sql which returns table required with data according to the caller, like If A calls C with Parameter ColumnName Distance C will process and return a table with Say DynamicMaster with column Distance added along with respective Data,and if B calls C with Parameter cloumn Time, C will process Data and Return a common table only with Column Time Instead Of Distance which is called from A but whats happening is if A is called earlier it returns DynamicMaster Table with Column Distance ,but after that if B procedure is executed expecting DynamicMaster Table with Column Time it gives error Column Time does not exist the DynamicMaster is created in Proc C and data collated in C itself this is working fine with one environment but failing in another environment of sql 2019

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jayraj Chhaya2y ago
Accepted answer
Hi Sachin K,

If you are experiencing an error when executing stored procedures in a SQL Server 2019 environment, specifically when Procedure A is executed before Procedure B, and you have already tried using the "WITH RECOMPILE" option to resolve the issue, there may be other solutions to explore.

One possible solution is to check the SQL Server settings in the environment where the error occurs. Here are a few settings that you can review and potentially modify to resolve the issue:

1. Compatibility Level

Check the compatibility level of the database where the stored procedures are executed. Ensure that the compatibility level is set to SQL Server 2019 or a higher version. You can use the following query to check the compatibility level:
```
SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'YourDatabaseName';
```
If the compatibility level is lower than SQL Server 2019, you can change it using the following command:
```
ALTER DATABASE YourDatabaseName
SET COMPATIBILITY_LEVEL = 150; -- Replace 150 with the appropriate compatibility level for SQL Server 2019
```
2. Query Store

Check if the Query Store feature is enabled for the database. The Query Store can help identify and troubleshoot performance issues related to query execution. You can enable the Query Store using the following command:
```
ALTER DATABASE YourDatabaseName
SET QUERY_STORE = ON;
```
Once enabled, you can monitor the queries executed by the stored procedures and analyze their performance using the Query Store reports.

3. Execution Plan Caching

By default, SQL Server caches the execution plans of stored procedures to improve performance. However, in some cases, cached execution plans can cause issues when the same stored procedure is executed with different parameters. You can try disabling the execution plan caching for the specific stored procedures by using the following command:
```
EXEC sp_recompile 'YourStoredProcedureName';
```
This will clear the cached execution plan for the specified stored procedure, allowing SQL Server to generate a new plan based on the current parameters.
Sachin K2y ago
1 Compatibity level, i checked for both 2019 sql server environments its same set as 100

3 Sp_Reompile i tried but its giving performance issue