Normally when people are trying to put a point across about using stored procedures in an application. The most common thing that is normally pointed out is about network bandwidth is normally presented. Some other things like memory saving are normally missed. This is an example on how to lower the memory footprint in sql server when using stored procedures instead of sql queries being stored in the application and being passed.

If you have looked into this before then you will be aware that using a stored procedure will save sql server compiling execution plans when using stored procedures. This also saves some memory. The following example will show how and
just how much memory is being saved for a single query.

In the following example I have a service running collecting netflow packets from a cisco router and saving these in an sql table that has the following format.

CREATE TABLE [dbo].[Flow](
        [FlowID] [uniqueidentifier] NOT NULL,
        [Router] [varchar](15) NOT NULL,
        [Source] [varchar](15) NOT NULL,
        [SourcePort] [int] NOT NULL,
        [Destination] [varchar](15) NOT NULL,
        [DestinationPort] [int] NOT NULL,
        [InterfaceIn] [int] NOT NULL,
        [InterfaceOut] [int] NOT NULL,
        [Packets] [bigint] NOT NULL,
        [Bytes] [bigint] NOT NULL,
        [Protocol] [int] NOT NULL,
        [LoggedAt] [datetime] NOT NULL,
 CONSTRAINT [PK_Flow] PRIMARY KEY CLUSTERED
(
        [FlowID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Flow] ADD  CONSTRAINT [DF_Flow_FlowID2]  DEFAULT (newsequentialid()) FOR [FlowID]
GO

The data is being stored with the following insert statement which is being called from the service each time.

INSERT INTO Flow (Router, Source, SourcePort, Destination, DestinationPort, InterfaceIn, InterfaceOut, Packets, Bytes, Protocol, LoggedAt) VALUES(@Router, @Source, @SourcePort, @Destination, @DestinationPort, @InterfaceIn, @InterfaceOut, @Packets, @Bytes, @Protocol, @LoggedAt)

This looks straight forward enough. However if you examine the query plan cache
by using the following sql.

SELECT
        P.usecounts AS TotalCount,
        P.size_in_bytes,
        SqlText.text
        FROM sys.dm_exec_cached_plans AS P
                CROSS APPLY sys.dm_exec_sql_text(P.plan_handle) AS SqlText
        WHERE SqlText.text like '(@Router%'
        ORDER BY P.usecounts DESC

It will show how many times the sql has been compiled to an execution plan and cached. It will also give usage counts for each of the plan caches. The following has been trimmed. There was originally about 30 rows of output for a single query.

TotalCount      size_in_bytes   (No column name)
56192   65536   (@Router nvarchar(12),@Source nvarchar(10),@Sourc
32482   65536   (@Router nvarchar(12),@Source nvarchar(11),@Sourc
15267   65536   (@Router nvarchar(12),@Source nvarchar(10),@Sourc
14742   65536   (@Router nvarchar(12),@Source nvarchar(11),@Sourc
11676   65536   (@Router nvarchar(12),@Source nvarchar(10),@Sourc
10953   65536   (@Router nvarchar(12),@Source nvarchar(10),@Sourc
10612   65536   (@Router nvarchar(12),@Source nvarchar(10),@Sourc
9512    65536   (@Router nvarchar(12),@Source nvarchar(12),@Sourc

A number of cached plans have been created because the passed sql from the program has a number of differences. In particular as the length of the strings change the sql is using different types because the data length of the type has
changed and sql server considers this to be a different type being passed. This causes sql server to create a new execution plan for every different length of string. This can result in a single query producing multiple execution plans which is using more memory. In my example above the total memory usage for all the execution plans was around 5MBytes. This may not sound much but I was only passing 3 strings which are only variable in length between 8 and 15 long.

When using a stored procedure the above issue with the execution plans does not occur.

When changing the insert statement to a simple stored procedure.

CREATE PROCEDURE [dbo].[FlowInsert]
        @Router VARCHAR(15),
        @Source VARCHAR(15),
        @SourcePort int,
        @Destination VARCHAR(15),
        @DestinationPort int,
        @InterfaceIn int,
        @InterfaceOut int,
        @Packets bigint,
        @Bytes bigint,
        @Protocol int,
        @LoggedAt datetime
AS

INSERT INTO Flow
(Router, Source, SourcePort, Destination, DestinationPort, InterfaceIn, InterfaceOut, Packets, Bytes, Protocol, LoggedAt)
VALUES(@Router, @Source, @SourcePort, @Destination, @DestinationPort, @InterfaceIn, @InterfaceOut, @Packets, @Bytes, @Protocol, @LoggedAt)

GO

Then looking into the execution plan cache again using the sql above it will be reduced to a single entry using a total of 57kbytes of memory. That is saving around 4-5MBytes of memory which sql server can use else where. This may not sound like much but considering that it is a single query and it can be common for even small applications to have 100 or more quries it can be saving quite significant amounts of memory.