June 19, 2013 at 7:07 am
Hi
I need some clarification on this topic.
what is tempdb mdf files used for vs ldf files used for?
I understand tempdb mdf needing space and ldf not being used.
In the event of ldf being used, what would this be used for?
June 19, 2013 at 7:17 am
Although crash recovery is never required for TempDB, transactions can be rolled back, just like in any other database. Therefore, although the logging is optimised for never requiring a roll forward, it still needs to log the before image for all transactions until a commit is issued.
June 19, 2013 at 7:20 am
100% but that's not what i am asking, my question is what is it used for.
file type specific.
June 19, 2013 at 7:24 am
What do you mean by "file type specific"? The file with the ldf extension is the log file and it's used for exactly what I explained above. Perhaps if you could elaborate on what you're asking?
June 19, 2013 at 7:53 am
cool, from how i understand it is.........
tempdb works a little different as opposed to normal db's, the mdf is used in processing of query's, hash tables, union joins etc.
the ldf is not used. in the event of the ldf growing why would that be?
i am currently in this situation. For the past year odd, one of our instances make use of 6 X 10GB mdf tempdb files. (naturally directly matched to number of CPU's and placed on different LUNS for speed). There has been no changes in SQL queries/jobs or data BUT
for some reason now our normal daily SQL job imports are making the tempdb LDF grow. I have run the below query which shows weights of out current jobs using tempdb, but that still does not explain why the ldf grows? I have also identified the job, but why is the job using the ldf and NOT the mdf?
SELECT es.host_name , es.login_name , es.program_name,
st.dbid as QueryExecContextDBID, DB_NAME(st.dbid) as QueryExecContextDBNAME, st.objectid as ModuleObjectId,
SUBSTRING(st.text, er.statement_start_offset/2 + 1,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE er.statement_end_offset
END - er.statement_start_offset)/2) as Query_Text,
tsu.session_id ,tsu.request_id, tsu.exec_context_id,
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time, er.cpu_time, er.total_elapsed_time, er.reads,er.writes,
er.logical_reads, er.granted_query_memory
FROM sys.dm_db_task_space_usage tsu inner join sys.dm_exec_requests er
ON ( tsu.session_id = er.session_id and tsu.request_id = er.request_id)
inner join sys.dm_exec_sessions es ON ( tsu.session_id = es.session_id )
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count)
DESC
June 19, 2013 at 8:06 am
Most internal operations (spills into TempDB in execution plans etc.) either log very little, or nothing at all, but explicitly created temporary objects (table variable, temp tables etc.) do. Are there any temp tables created/updated in the job?
June 19, 2013 at 8:10 am
yes, temp tables which also makes use of Unions... which is fine. (that will use up tempdb space but on the mdf only if i am right?)
Now any idea why the LDF is growing? It jumped to operational 2GB to 120GB in size.
June 19, 2013 at 8:12 am
Modest DBA (6/19/2013)
tempdb works a little different as opposed to normal db's, the mdf is used in processing of query's, hash tables, union joins etc.the ldf is not used.
The ldf most definitely is used. All of those operations, if they make any changes in TempDB (temp tables, spills, etc) are logged operations and hence are written to the transaction log.
TempDB is never recovered, but it must be able to roll back any changes, including hash tables or spills otherwise it would have to be taken offline when any active connection is stopped or killed. That's clearly not an acceptable option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 8:14 am
Modest DBA (6/19/2013)
yes, temp tables which also makes use of Unions... which is fine. (that will use up tempdb space but on the mdf only if i am right?)
No, you're not right
Now any idea why the LDF is growing? It jumped to operational 2GB to 120GB in size.
Because modifications to temp tables, sort spills, hash spills, etc are logged operations and hence are written to the transaction log and the log cannot be reused until the transaction commits.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 8:26 am
so why is the best practice to have multiple mdf's and keep a constant ldf if the log is the one with processing in it? Sorry for the questions but i am trying to best understand this?
June 19, 2013 at 8:43 am
Because TempDB is prone to allocation contention (contention on the allocation pages in the data file), a contention that is alleviated by having more than one file and because there is never any benefit to having multiple log files for any database.
While TempDB does have a whole bunch of optimisations, it's core behaviour is pretty much the same as the same as for user databases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 8:53 am
so what you telling me is that due to contention my set up should be as follows...
6 CPU machine and 6 LUNS allocated for mdfs.
I can reduce the size of my mdf files to 1024MB from 10GB and increase my ldf to 120GB from existing small size. And performance will stay the same but i won't have any growth problems due to operations being spooled on the log?
Very interesting!!!
June 19, 2013 at 8:53 am
Multiple tempdb files help to relieve latch contention.
See this link: http://sqlblog.com/blogs/adam_machanic/archive/2011/04/21/analyzing-tempdb-contention-a-month-of-activity-monitoring-part-21-of-30.aspx
Sorry... got pulled away from my desk as I was replying. I see you've gotten past this question now... 🙂
June 19, 2013 at 8:58 am
Modest DBA (6/19/2013)
so what you telling me is that due to contention my set up should be as follows...6 CPU machine and 6 LUNS allocated for mdfs.
No, I'm not necessarily telling you that.
I'm saying that the reason multiple data files are created is for contention. Whether or not you need multiple files or multiple entirely separate LUNs depends on how much load you have and whether you are experiencing contention and of what type.
And performance will stay the same but i won't have any growth problems due to operations being spooled on the log?
Err... what?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply