Sql-server – In a DacPac deployment, if a stored procedure that is called by another stored procedure is updated, is the calling stored proc also redeployed

dependenciessql serverssdtvisual studio

I noticed that, in my DacPac, after a deployment, two stored procs were given a modified date on sys.objects, almost to the same millisecond. However, I would only expect one of them to have this, since I only updated one proc. The other proc that was updated calls the one that was updated, but I didn't modify it directly.

Has anyone seen this behavior? Is it expected with DacPac, that the calling stored proc gets "modified" even though there is no actual code change, or could it be something else?

My concern is for a number of reasons, mainly related to QA people questioning this, and ultimately it isn't a good idea to see a proc has been modified when you can't explain why.

Any help appreciated.

Best Answer

Yes, it's expected.

If StoredProcA calls StoredProcB, and StoredProcB is modified, then the DacPac deployment will:

deploy the changes to StoredProcB
update the metadata for StoredProcA

What that looks like in the generated deployment script is:

GO
PRINT N'Altering [dbo].[StoredProcB]...';


GO
ALTER PROCEDURE [dbo].[StoredProcB]
/*
your new stored proc body here
*/
GO
PRINT N'Refreshing [dbo].[StoredProcA]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[StoredProcA]';

It's the call to sp_refreshsqlmodule that updates the modified date on StoredProcA. The procedure hasn't been redeployed, but it's underlying metadata may have.

In your case, nothing has really changed. The whole sp_refreshsqlmodule thing would be necessary if you updated a user-defined type that was used a parameter to StoredProcA, for example. It's not necessary in this situation, but SSDT doesn't have complex rules about when to refresh dependent modules - it just updates them all if any have changed.

Related Solutions

Sql-server – SQL Server 2012 Very Slow to Consume Memory

My first question where are you looking for SQL Server memory consumption. Please don't use task manager or process monitor, these do not show correct value when SQL Server service account has Locked pages in memory privilege. just use below query to see memory consumed by SQL Server

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

There was notable change in SQL Server memory code in SQL Server 2012 but no where its related to what you are seeing. SQL Server memory allocation is dynamic in nature when SQL Server boots up it would grow its memory notification till the highMemoryResourceNotification resource notification is revoked.SQL Server will keep allocating memory based on its need as long as there is memory available I.e. as long as MEMPHYSICAL_HIGH (HighMemoryResourceNotification )notification is signaled in widows and will scale down its usage when there is MEMPHYSICAL_LOW (LowMemoryResourceNotification) signaled in windows. When available memory is between the low memory and high memory SQL Server will try to maintain the memory usage stable( RESOURCE_MEM_STEADY) with some exceptions.

With 48G memory you have set max server memory to 44G and only given 4 G to OS. IMO this is not correct you must give at least 6 G OS. You should give enough memory to OS so that it would have enough to allocate otherwise it would start signaling low memory notifications and SQL Server would have to trim its memory consumption.

Does SQL Server service account has Locked pages in memory privilege. If not you should give it, but before doing that please leave 6-7 G for OS( I am assuming here that this is dedicated system for SQL Server)

If you want SQL server to consume more memory create a dummy table and then start inserting large number of records in it at same time start running multiple Select * statements you would see SQL Server memory consumption rising. If you want to check SQL Server memory consumed by SQL Server use query posted above

SQL Server – Executing Dynamic SQL Generated from SELECT Statement

While I would prefer to generate true dynamic SQL statements with strongly-typed parameters, concatenation is probably ok here because the proper way will be a lot more cumbersome. I do, however, prefer sp_executesql over EXEC(), always.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N'EXEC [sim].[ROI_Save] ' 
        + CONVERT(varchar(6), VSL.PathId)           + N', '
        + CONVERT(varchar(6), @ROIResultId)         + N', '
        + CONVERT(varchar(6), RT.routeTypeResultId) + N', '
        + CONVERT(varchar(6), RT.ROI_RouteTypeId)   + N'; '
FROM  @routeResultIds AS RT
JOIN sim.ROI_Result_Vehicle AS VSL 
ON RT.ROI_Result_VehicleId = VSL.ROI_Result_VehicleId;

EXEC sys.sp_executesql @sql;

I still don't like that the parameters to the procedure aren't named, and if any of your parameter values were strings or dates, the build-up of single quotes would quickly get very annoying.

Best Answer

Related Solutions

Sql-server – SQL Server 2012 Very Slow to Consume Memory

SQL Server – Executing Dynamic SQL Generated from SELECT Statement

Related Question