| Subject: | Memory leak when using table variables in MSSQL via DBD-ODBC |
I have identified what looks like a substantial memory leak that occurs
under a somewhat complicated set of circumstances. I am guessing the
leak occurs in DBD::ODBC but don't know for sure. I was unable to
reproduce it using DBD::ADO as an alternate driver.
I have attached a test script for reproducing the leak. The details of
the leak and the usage of the script are documented in the script, but I
will also also describe the behavior in detail here.
Overview:
If you perform a particular type of query using MSSQL via DBI and
DBD::ODBC, a substantial amount of memory seems to get leaked on each
repetition of the query. The amount of memory leaked varies with the
number of long text columns that you are selecting.
Details:
In order to reproduce the leak, you must meet four criteria:
1) In your query, you must create a table variable.
2) You must insert at least one row into the table variable. After
that, you can completely ignore the table variable!
3) You must select from a table that contains 'text' columns. The more
text columns you select, the larger the leak. Note that you don't
actually have to select any rows to cause the leak.
4) After executing the query, you must do something which triggers the
lookup of column information. I've found that either $sth->{NAME} or
$sth->fetchrow_hashref() will cause the leak, for instance.
In addition, the 'LongReadLen' property of the database handle is
somehow related because the higher this number, the greater the leak.
Modules involved: DBI 1.50, DBD::ODBC 1.13
Perl version: ActiveState ActivePerl 5.8.7
Database: MSSQL 2000 SP3.
OS: Windows XP SP2.
| Subject: | mssql_leak.t |
############################################################################
# mssql_leak.t
#
# Script for demonstrating substantial memory leak with DBI, DBD::ODBC, and
# a MSSQL 2000 database.
#
# To use this script you must have DBI 1.50, DBD::ODBC 1.13, and owner
# access to an MSSQL database. A table called 'leak_test' will be created
# and destroyed in the database. See the CONFIGURATION section below to set
# up the desired connection.
#
# In order to reproduce the leak, you must meet four criteria:
#
# 1) In your query, you must create a table variable.
# 2) You must insert at least one row into the table variable. After that,
# you can completely ignore the table variable!
# 3) You must select from a table that contains 'text' columns. The more
# text columns you select, the larger the leak. Note that you don't
# actually have to select any rows to cause the leak!
# 4) After executing the query, you must do something which triggers the
# lookup of column information. I've found that either $sth->{NAME} or
# $sth->fetchrow_hashref() will cause the leak, for instance.
#
# In addition, the 'LongReadLen' property of the database handle is somehow
# related because the higher this number, the greater the leak.
#
# Once you've verified that the leak is occuring using the code below, you
# can try commenting out various lines to see how it affects things.
# The two clearest examples are commenting out the insertion into the
# table variable, and commenting out the $sth->{NAME} line.
#
# I wasn't able to figure out how to get at the memory usage of perl
# programmatically on Windows, so rather than writing tests to test the
# memory usage, I just made a loop that demonstrates the leak. It pauses
# after the first iteration and then again at the end so you can observe the
# memory used by perl using the task manager or 'Process Explorer' from
# sysinternals.com.
#
# Author: David Brewer, Second Story Interactive
# Date: June 7, 2006
############################################################################
use strict;
use warnings;
############################################################################
# CONFIGURATION
############################################################################
# Database connection
my $dsn = "DBI:ODBC:driver={SQL Server};server=localhost;database=test;";
my $user = "test";
my $pass = "test";
# number of times to perform the leaky query in the loop
my $leak_iterations = 1200;
# seconds to pause at beginning and end of loop to permit memory observation
my $pause = 7;
# LongReadLen value to use on the database handle; this is somehow related
# to the leak because the greater this value, the greater the leak.
my $LongReadLen = 20000;
############################################################################
# SETUP
############################################################################
use Test::More tests => 8;
# Verify we have the correct versions of the modules
BEGIN {
use_ok( 'DBI 1.50' );
use_ok( 'DBD::ODBC 1.13' );
}
# Prepare the database by creating the test table. Drop it first if it
# already exists.
my $create_sql = q{
IF OBJECT_ID('leak_test','U')IS NOT NULL DROP TABLE leak_test;
CREATE TABLE [dbo].[leak_test] (
ID int NOT NULL,
LongText1 text,
LongText2 text,
LongText3 text,
LongText4 text,
LongText5 text,
PRIMARY KEY (ID)
)
};
my $dbh = DBI->connect($dsn, $user, $pass);
isa_ok($dbh, 'DBI::db');
ok($dbh->do($create_sql), 'Created leak_test table');
ok($dbh->disconnect, 'Disconnected from database');
############################################################################
# THE TEST
############################################################################
my $leaky_sql = q{
DECLARE @table_variable TABLE (
TestInteger int DEFAULT(0)
);
-- Commenting the line below prevents the leak!
INSERT INTO @table_variable (TestInteger) VALUES (1);
SELECT TOP 0
*
FROM
leak_test;
};
my $iterations = 1200;
print(qq{
We will run the leaky query $leak_iterations times, pausing $pause seconds
after the first run so you can observe the memory usage.
});
$dbh = DBI->connect($dsn, $user, $pass);
$dbh->{LongReadLen} = $LongReadLen;
for my $i (1..$leak_iterations) {
# print a dot for each run through the query
print "\n" if ($i % 60 == 1);
print ".";
# run our leaky query...
my $sth = $dbh->prepare($leaky_sql);
$sth->execute();
# Getting column names triggers link; you can comment out to verify.
my $names = $sth->{NAME};
# clean up
$sth->finish;
undef $sth;
# pause after the first run so you can observe the memory usage
sleep($pause) if ($i == 1);
}
$dbh->disconnect();
undef $dbh;
print(qq{
\nPausing $pause seconds so you can observe final memory usage.
});
sleep($pause);
############################################################################
# CLEANUP
############################################################################
# Remove the leak_test table
my $cleanup_sql = q{
IF OBJECT_ID('leak_test','U') IS NOT NULL DROP TABLE leak_test;
};
$dbh = DBI->connect($dsn, $user, $pass);
isa_ok($dbh, 'DBI::db');
ok($dbh->do($cleanup_sql), 'Removed leak_test table');
ok($dbh->disconnect, 'Disconnected from database');