August 27, 2021 at 1:01 pm
Is there any way to identify which stored proc is creating a table variable in tempDB?
I am looking at the forward_fetch_count in sys.dm_db_index_operational_stats and we have a few tables that have a very large count (the biggest is over 133 million). The table name comes out in tempDB as a # followed by 8 hex characters. Is there a way to track that back to the originating process so we can fix it?
We are running SQL 2019 on Linux.
Thanks, Evan
August 27, 2021 at 3:57 pm
Are the table names consistent? What I mean is are they always the same 8 hex characters? If so, then you could use a tool like SQL Search (RedGate free tool for SSMS) to search the database for stored procedures that contain that text.
You may be able to look through the plan cache to see what created the table. See this link if you want to look through the plan cache to get the query text to find the query that is creating the table variable or temp table:
https://www.sqlshack.com/searching-the-sql-server-query-plan-cache/
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 27, 2021 at 5:14 pm
The name in the tempDB database is not the same as the @table variable name. I tried SQL Search and it didn't return anything.
August 27, 2021 at 6:04 pm
I was actually just experimenting with this and I am mistaken on how you would find it.
Turns out the plan cache doesn't map this up either... I am going to do a bit more digging and see if I can figure something out...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 27, 2021 at 9:23 pm
I did find one way to do this, but it relies on the query still being in the plan cache and is not a very "automated" approach and has potential for incorrect results depending on how the query that created the object was written.
What I did was look at tempdb.sys.tables to find the table in question and look at the creation time. Hypothetically, lets say the creation time was 2021-08-27 15:10:41.500.Using the creation time, we then look at the plan cache with the following query:
SELECT
creation_time, query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
WHERE query_plan IS NOT NULL
AND [creation_time] > '2021-08-27 15:10:31.500'
AND [creation_time] < '2021-08-27 15:10:51.500'
In my case, I have 3 plans that fall into that time range, and 2 of them were created at the time I specified above exactly. Looking at both of them, I can guess which one created the temporary table.
Now, why this MAY be problematic - if the query that generated the table variable does a bunch of calculations prior to creating the table variable, you may need longer than a 20 second window to find it. With ALL of the scripts I create that get deployed to live, I have my table creation and variable declaration portion of the script as the first step. So, in my case, a 20 second window is overkill and I could probably get by with a 1 or 2 second window. But that may not be the case for you.
The above is also guesswork in that if you find 10 different queries that are potential candidates for that one table variable, it MAY be that one of them is the correct one or it may be that your time window was too narrow.
I have been trying to think of another way to find that information, but so far I am coming up empty.
EDIT - thought of another potentially good way to verify that you found the correct execution plan - when looking at tempdb.sys.tables, join it to tempdb.sys.columns. This way even though you don't know the table name, you know the columns in the table so you can verify that the table you are looking for in the plan cache matches the table in tempdb.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 30, 2021 at 12:42 pm
Isn't the table variable deleted when the process completes ?
August 30, 2021 at 1:54 pm
It does not seem to go away.
August 30, 2021 at 5:12 pm
I actually thought the same thing as homebrew01 - when the query completes, the table should go away. The exception is with temporary tables, which would go away when the session ends, or global temporary tables, which would need manual cleanup if I remember right... I tend not to use them.
BUT since it is just a hex string for the table name, that looks to me to be a table variable. This leads me to believe that there is some query that is running (possibly a begin transaction without an commit/rollback transaction) or a long running query that is creating the table variables and not cleaning them up as fast as you would like.
Now, with my comment above, I am not saying that what you are seeing isn't possible - I've seen all sorts of strange behavior that in theory shouldn't happen but does.
A different approach that you could do, depending on the number of stored procedures you have to work with, would be to convert your table variables over to temp tables. Doing this may impact performance and cause other side effects, so be cautious doing it and test it carefully. But the advantage to temp tables is that they will show up with the temporary table name, followed by a random hex value so it is easy to find the temp table name and search for it in stored procedures. Plus, you can create indexes on temp tables just like you would a normal table. I do recommend dropping the temp table when you are done with it, but it should auto-cleanup when the session is terminated as well.
Mind you, if you have thousands of stored procedures to scrape through to do the above, it may not be realistic to change them all. In that case, it may be more beneficial to look in the plan cache to see which ones are run frequently and see if any of those would benefit from temp tables instead of table variables.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 30, 2021 at 5:19 pm
Believe me, I would love to change them to temp tables, but the stored procs are written by a 3rd party. We were able to find most of them checking the sys.columns table and then searching sys.sqlmodules for the name of one of the columns. It is not 100% but from the sounds of all of the answers I have received, nothing will be.
Since then we have engaged with the 3rd party vendor to fix the ones they can find.
Thanks for the help.
August 30, 2021 at 5:22 pm
Are you referring to Temporary Tables in TEMPDB ?
I see I have quite a few also, with names like
#BF1C9DD8
#AC5C13E1
#AC7698F6
#AC11FB1E
#AC444A0A
#AAA61A5B
#AAC09F70
Some of mine have a few records , some have 0, according to this:
SELECT substring(o.name,1,50) as 'Table Name',
ddps.row_count , used_page_count, (used_page_count * 8)/1024 as M_bytes, (used_page_count * 8)/1048576 as G_bytes, convert(varchar(25),create_date,107) as 'Created'--,o.*
,i.index_id
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id--ORDER BY used_page_count desc
WHERE i.index_id < 2-- Omits Indexes, which can also be significant.
AND o.is_ms_shipped = 0 -- 1 for system databases
ORDER BY used_page_count desc , o.name
August 30, 2021 at 5:27 pm
Scroll Down to "Local Temporary Table"
I have some created 2 months ago. The above article says
"You get housekeeping with Local Temporary tables; they are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE. "
August 30, 2021 at 6:16 pm
We have found that at least some of them are table variables with the # and 8 hex digits. What we are looking at is the forward fetch number (received from Brent Ozar's BlitzFirst) that are very high. We have at least 1 table that has 133 million forward fetches. We tracked it down to a stored proc and found the after the table variable creation, the vendor is doing an update on the table causing each to grow too big for the page it resides on. SQL then moves the data and creates the forward pointer to the new page.
I am not sure how the back end works on these, but something doesn't add up. It is claimed that they disappear after the stored proc exits, but there are other factors I have found that makes it seem like they are reused. Like in the above explication, the stored proc is called, it inserts values into the temp table variable, then the proc exits. The are not enough rows of actual data to make it come up to high of a number.
August 30, 2021 at 6:37 pm
I believe SQL caches table variables the same way it does for temp tables. If so, an entries for them would still be in the system tables. It would be some time before the cached objects expired or memory pressure caused the objects to be removed.
If you want to prevent caching of table variables / temp tables, then do one of the following:
Add a named constraint;
Add using WITH RECOMPILE to the proc.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 30, 2021 at 6:41 pm
Thank you. The vendor has decided to just not update the table in a separate statement. That seems to have done the trick.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply