Dynamic SQL Going Haywire and Wreaking Havoc!

DZone 's Guide to

Dynamic SQL Going Haywire and Wreaking Havoc!

In this article, see how Dynamic SQL wreaked havoc for one user.

· Database Zone ·
Free Resource

Those who work on database, or even those who do not, must have come across the term Dynamic SQL or at least heard of it. Dynamic SQL is basically a programming technique that enables you to build SQL statements dynamically at run time. It allows you to create a more general-purpose, flexible SQL statement because the full text of the SQL statements may be unknown at compilation. For example, you can use the dynamic SQL to create a stored procedure that queries data against a table whose name is not known until run time.

Even though there are benefits, it could also bring in some real negative issues, and I would like to share how, in one of those occasions, it wreaked a havoc! 

I came across a situation where a client stored procedure was not just running long but was failing from the application with the following error;

SQLSTATE = 42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'

It was not that the problem was being reported everyday, the same procedure was running fine some days.

So, what was happening on those days? 

The problem was detected to an insert statement within this procedure that was populating a temp table through a Dynamic SQL using 'Exec(@SQL)'. On those exception days, due to differences in data pattern, the optimizer was generating a plan that was simply not good enough. We know that with 'EXEC', plan reuse is always a concern.

So, while evaluating the code block, I felt functionally dynamism not needed, and whatever logic was being implemented, the same could be done without the dynamic version.

However, it was a still a question of what was happening and why. 

The dynamic code was a combination of 5 UNION statements, which was fetching data from multiple tables based on join logic with a date filter in all the UNIONs.

On the day the problem was reported, an abrupt amount of data was qualifying in the temp table insert section.

When I checked all the UNION queries with some of the timestamps, I saw that for some of the queries, operation was getting spilled into tempdb.

This would mean that that the query was not granted enough memory to finish the operation and spilled over into the tempdb to complete the operation. 

The query read as much as it could from memory before moving over to the TempDB disk, however, with multiple spills, it started consuming space too.

Below is the data comparison between With Dynamic vs Without Dynamic part of the code.

Prior to running the insert portion of the code, the free space available was around 62161 MB, and once the insert completed, below is what the picture looked like.

Without Dynamic

Free space (MB)      Internal objects (KB)          User objects (KB)                 Version store (KB)

61251                         1216000                                   3008                                       2176

With Dynamic

While the procedure was running, tempdb free space went as low as 160 KB and eventually become 0, resulting in the above error message.

Free space (MB)        Internal objects (KB)         User objects (KB)                      Version store (KB)

160 *                                 65382208                                  34112                                       256

* later became 0.

Query used for the above:



(SUM(unallocated_extent_page_count)/128) AS [Free space (MB)],
SUM(internal_object_reserved_page_count)*8 AS [Internal objects (KB)],
SUM(user_object_reserved_page_count)*8 AS [User objects (KB)],
SUM(version_store_reserved_page_count)*8 AS [Version store (KB)]
FROM sys.dm_db_file_space_usage
WHERE database_id = 2

Once the code was modified to be pulled out of the dynamic execution, the problem got resolved, and it never came back in. 

So, to conclude, there are times when an improper coding approach of using Dynamic SQL could cause a very dangerous impact on a system, hence you should evaluate the type of code that is being written. This would reduce the possibility of a situation like this one to a very large extent.

database, dynamic sql, performance, sql

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}