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.



Did You find this page useful?

Yes No



Last Modified: 18 February 2017

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - Unsigned int
2011-02-20 - MSSQL - All temp tables are global
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-29 - MSSQL - TRIM Function