December 2, 2011 at 8:44 am
Hello! thanks for looking. 🙂
I have recently added the temporary table step to the below sproc in an effort to speed up the import of data. It now runs more quickly, however the tempDb starts growing on the order of 5GB+ in a day. Previous to the addition of the temporary table usage I do not believe we were seeing such tempDb growth. I must restart the SQL Server 2005 32-bit (Win2003) which 'shrinks' the tempDb. Why does tempDb grow? How can I stop this/accomplish my task more efficiently/correctly? All help is sincerely appreciated.
--Setup temporary MSSQL table #glxRDCount
CREATE TABLE #glxRDCount (
id bigint
,scn varchar(15)
,LUISTimeStamp datetime
,sort varchar(20)
,sort_desc varchar(20)
,ct int
)
--Load temporary table #glxRDCount from MySQL table
INSERT INTO #glxRDCount (id, scn, LUISTimeStamp, sort, sort_desc, ct)
SELECT SDM.id, SDM.scn, SDM.LUISTimeStamp, SDM.sort, SDM.sort_desc, SDM.ct
FROM OPENQUERY(GALAXY, 'select id, scn, cast(concat(left(ts,4), ''/'',mid(ts,5,2),''/'',mid(ts,7,2),'' '',mid(ts,9,2),'':'',mid(ts,11,2),'':'',mid(ts,13,2)) AS Datetime) LUISTimeStamp, sort, sort_desc, ct from sdm.rd_count order by id desc') SDM
LEFT JOIN dbo.tblRDCount SQL
ON SDM.id = SQL.GlxyRDID
AND SDM.LUISTimeStamp = SQL.LUISTimeStamp
WHERE SQL.GlxyRDID is null
AND SQL.LUISTimeStamp is null
--Use temporary table to write into SQL table
INSERT INTO dbPOGGalaxy.dbo.tblRDCount (
[GlxyRDID] --1
,[FK_IP_ID] --2
,[LUISTimeStamp]--3
,[SortDate] --4
,[FKSortID] --5
,[SQLTimeStamp] --6
,[count] --7
,[FKSortDescID] --8
)
SELECT
ISNULL(id,0) GlxyRDID -- 1 to GlxyRDID (bigint, not null)
,ISNULL(PK_IP_ID,0) FKIPID -- 2 To tblIP.IPAddress and take FK_IP_ID
,ISNULL(RD.LUISTimeStamp,0) LUISTimeStamp -- 3
,ISNULL(RD.LUISTimeStamp,0) SortDate -- 4
,ISNULL(PKSortID,6) SortID -- 5 link to left(tblSort.SortAbbr,1) and take FKSortID
,GetDate() AS SQLTimeStamp -- 6 insert to SQLTimeStamp
,ISNULL(ct,0) Cnt -- 7 insert to Count
,ISNULL(PK_sort_desc_ID,0) SortDescID -- 8 link to tbl_sort_desc.sort_desc and take PK_sort_desc_ID
FROM #glxRDCount AS RD
JOIN [dbPOGGalaxy].dbo.tblIP
ON scn = IPAddress
JOIN [dbPOGGalaxy].dbo.tblSort
ON LEFT(sort,1) = LEFT(GalaxySortName,1)
JOIN [dbPOGGalaxy].dbo.tblSortDesc
ON RD.sort_desc = dbo.tblSortDesc.sort_desc
LEFT JOIN [dbPOGGalaxy].[dbo].tblRDCount
ON RD.id = GlxyRDID
AND RD.LUISTimeStamp = [dbPOGGalaxy].[dbo].tblRDCount.LUISTimeStamp
WHERE
[dbPOGGalaxy].[dbo].tblRDCount.GlxyRDID IS NULL
AND [dbPOGGalaxy].[dbo].tblRDCount.LUISTimeStamp IS NULL
--Drop temporary table #glxRDCount
DROP TABLE #glxRDCount
December 2, 2011 at 8:47 am
Which table in this script is your temp table?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 2, 2011 at 9:27 am
Jason, thanks and sorry, I posted the wrong sproc! I've edited to the correct one now.
December 2, 2011 at 10:13 am
So the reason this is occurring is due to the number of records being ported over. The temp table (created in tempdb) is receiving many records and the database needs to grow to accommodate the needs of the query.
Are you stopping sql server service everyday to shrink tempdb back down?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 2, 2011 at 10:26 am
Yes, I've been restarting SQL just since this has started happening - obviously, not an ideal work-around right now. I originally noticed there was a problem (somewhere) when other software in use showed that the server's drive freespace was decresing. There are still hundreds of gig available, but I'd like to 'nip this in the bud' so to speak.
December 2, 2011 at 10:27 am
Is it possible that the tempDb will now just need to 'hover' at a larger size, and there is nothing really 'wrong' with the sproc?
December 2, 2011 at 10:46 am
junk.jjk (12/2/2011)
Is it possible that the tempDb will now just need to 'hover' at a larger size, and there is nothing really 'wrong' with the sproc?
you need to have a larger tempdb.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 2, 2011 at 11:38 am
Alright, thanks for looking at my code. I'll try letting the tempDb grow for a few days and see if it 'levels-out' at a new, larger size.
The other problem (doh! that I forgot to state :blush:) is that the page file gets maxed out as well. I assumed that was from the tempDb issue, but the pf is going over 10GB. (btw the server has 12GB RAM, 1TB RAID-5, 8x1.8 xeon if any of that matters).
December 2, 2011 at 12:06 pm
junk.jjk (12/2/2011)
Alright, thanks for looking at my code. I'll try letting the tempDb grow for a few days and see if it 'levels-out' at a new, larger size.The other problem (doh! that I forgot to state :blush:) is that the page file gets maxed out as well. I assumed that was from the tempDb issue, but the pf is going over 10GB. (btw the server has 12GB RAM, 1TB RAID-5, 8x1.8 xeon if any of that matters).
How are you determining the page file is going over 10GB? SQL Server should not be using the page file - and if it is, you most likely have memory issues that need to be addressed.
My guess is that you are looking at Task Manager - which is not reporting the memory usage correctly. What you are really seeing is SQL Server using 10GB of memory which is normal depending on your max memory settings.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 2, 2011 at 12:17 pm
Yes, I was looking at the Task Manager Performance tab's "Page File Usage History" graph. When I restart SQL Server, it drops back down to normal levels (about 3-4GB). This also seems to coincide with the inability to connect to a few MS Access Linked Servers (local .mdb files on the server) - restarting SQL fixes this problem as well.
December 6, 2011 at 12:59 am
junk.jjk (12/2/2011)
Alright, thanks for looking at my code. I'll try letting the tempDb grow for a few days and see if it 'levels-out' at a new, larger size.The other problem (doh! that I forgot to state :blush:) is that the page file gets maxed out as well. I assumed that was from the tempDb issue, but the pf is going over 10GB. (btw the server has 12GB RAM, 1TB RAID-5, 8x1.8 xeon if any of that matters).
As the server is a 32-bit server you must have enabled AWE on this so that SQL Server can use the extended RAM. In that case Task Manager will not show the true details of memory usage by SQL Server.
As for as TempDB is concerned:
1. Try creating multiple files for TempDB (say 4 data files on separate drives), that will increase the I/O performance.
2. Increase the default file size to 5 GB for TempDB. So that, it never has to autogrow resulting in poor I/O performance in runtime.
3. You can avoid restarting the server by just shrinking TempDB with DBCC SHRINKFILE ('FileName',0)
For a detailed understanding please click here.
December 6, 2011 at 1:51 am
Put an index on your temp table. SQL Server will jump through fewer hoops creating the INSERT source.
CREATE --UNIQUE
CLUSTERED INDEX [CXscn] ON #glxRDCount (scn ASC)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply