I am trying to estimate how much the memory usage of a temp table being used on query
againts SQL server.
For example I have a query stated below:
Select ColumnA, ColumnB, ColumnC, ColumnD, ColumnE
Into #TempTable
From BigTable
--Finalset
Select ColumnA, ColumnB, ColumnC, ColumnD, ColumnE
From #TempTable
Drop table #TempTable
My concern is, I wanted to make sure I know how much the memory being used if I execute and run
query from my Temp table againts my SQL server. This estimation will help me to decide whether
or not I should use my temp table if alot of memory will be used in the production which will
impact to other issues.
What is the query I can use to see how much memory being used with my temporary table
after the execution?
You could have googled and find an answer ... here is the query below you can use, It will provide information about all tables in tempdb. ... but the main question is how this will affect your decision whether to use temp table or not. you can estimate the size of the table without putting in tempdb
anyway here is the query
SELECT TBL.name AS ObjName
,STAT.row_count AS StatRowCount
,STAT.used_page_count * 8 AS UsedSizeKB
,STAT.reserved_page_count * 8 AS RevervedSizeKB
FROM tempdb.sys.partitions AS PART
INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT
ON PART.partition_id = STAT.partition_id
AND PART.partition_number = STAT.partition_number
INNER JOIN tempdb.sys.tables AS TBL
ON STAT.object_id = TBL.object_id
ORDER BY TBL.name;
Well, I wanted to make sure my queries don't cost too much memory using the temp tbl so by having memory usage query, it will give me an alert how much memory will be used when my stored procedure(s) or queries run in the production.
Is there a way I can set an alert so I can get warning if my queries used more than 300 MB? I know temp tbl will drop when the execution is finished on session or in SP but it would nice if I would depends alot on Temp Tbls and I can control the memory usage and keep a good performance in the production environment.
300MB is only 38,440 pages of data. A whole lot of people write inefficient queries that frequently use more than that even for single row returns. I hope you're set to receive a gazillion alerts if you setup for 300MB query alerts.
And, Temp DB is frequently hit with more than that even if you don't use a Temp Table because, behind the scenes, SQL Server uses them a lot.
Now, if temp table being in query session, if the execution finished and session still open but on "sleeping" mode, is the temptbl still stay in SQL server memory? The reason I asked because if I ran large dataset and takes lot of SQL memory, I want to make sure, right after the execution of qeury, temptbl is deleted.
... here is the query below you can use, It will provide information about all tables in tempdb. ... but the main question is how this will affect your decision whether to use temp table or not. you can estimate the size of the table without putting in tempdb