Those who work on various databases, or even those who do not, must have come across the term, Dynamic SQL. Dynamic SQL is basically a programming technique that enables you to build SQL statements dynamically at run time. It allows you to create more general purpose flexible SQL statement because the full text of the SQL statements may be unknown at compilation. For example, you can use Dynamic SQL to create a stored procedure that queries data against a table whose name is not known until run time. Even with the benefits, there could also be problems and concerns. I would like to share how this programming technique caused a big problem in my environment.
I came across a situation where a client stored procedure was not just running long, but, was failing due to this 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.
The same procedure was running fine without an exception on some days. So,what was happening on those days where an error was returned?
After some analysis I detected that an insert statement within this procedure, that was populating a temp table through Dynamic SQL, was the root cause of the issue. On those exception days, due to a difference in the 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. While evaluating the code block, I felt functionally dynamic code was not needed, and whatever logic was being implemented could be done without the dynamic version. However, it was a still a question of what was happening and why.
On the day when the problem was reported, a large volume of data was getting qualified in the temp table insert section. This INSERT was a combination of 5 UNION statements, fetching data from multiple tables based on join logic with a date filter in all the UNIONs. On checking all the UNION queries, I saw that for some of the queries, the operation was spilling into tempdb.
This would mean that that the query was not granted enough memory to finish the operation and, spilled over into tempdb to complete. The query read as much as it could from memory before moving over to the tempdb disk, however, with multiple spills, it started consuming lots of space in tempdb.
In order to recreate the issue, I pulled the insert statement from the procedure and executed it with and without Dynamic SQL. Below is the data comparison between the 'With Dynamic' vs 'Without Dynamic' part of the insert code. Prior running the insert portion of the code, the free space available was around 62161 MB. Below I'll show how I tested things.
For the purpose of understanding I have used demo code. The Dynamic SQL part of the insert is shown here:
Exec ('
insert into #Temp
select f.objid
from table1
where f.lastchngdtime > ' + @lstChngDt + '))
UNION
select f.objid
from table2 f
join table3 d1 on
f.objid= d1.objid
where f.lastchngdtime > ' + @lstChngDt + '))
UNION
select f.objid
from table3 f
join table4 d1 on
f.objid= d1.objid
where f.lastchngdtime > ' + @lstChngDt + '))
UNION
select f.objid
from table4 f
join table5 d1 on
f.objid = d1.objid
where f.lastchngdtime > ' + @lstChngDt + '))
UNION
select f.objid
from table5 f
join table6 d1 on
f.objid= d1.objid
where f.lastchngdtime > ' + @lstChngDt + '))')The tempdb free space status after execution was 160MB. This later went down to 0.
The insert code without using Dynamic SQL is here:
insert into #Temp select f.objid from table1 where f.lastchngdtime > @lstChngDt UNION select f.objid from table2 f join table3 d1 on f.objid= d1.objid where f.lastchngdtime > @lstChngDt UNION select f.objid from table3 f join table4 d1 on f.objid= d1.objid where f.lastchngdtime > @lstChngDt UNION select f.objid from table4 f join table5 d1 on f.objid= d1.objid where f.lastchngdtime > @lstChngDt UNION select f.objid from table5 f join table6 d1 on f.objid= d1.objid where f.lastchngdtime > @lstChngDt
The tempdb free space status after execution was 61251MB
The free space signifies the sum of unallocated_extent_page_count for tempdb, and it was obtained by using the below query:
SELECT (SUM(unallocated_extent_page_count)/128) AS [Free space (MB)] FROM sys.dm_db_file_space_usage WHERE database_id = 2
Based on the results from the above test, the insert part of the procedure was modified so that no longer uses Dynamic SQL.
The situation described here shows that there are times when the coding approach of using Dynamic SQL could have serious impact on a system, so a developer needs to evaluate the code before implementing it with Dynamic SQL. This would reduce the possibility of a situation such as the one reported here.
