adding files to tempdb

Last Post 14 Aug 2007 02:55 AM by oshaw. 11 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
oshaw
New Member
New Member

--
13 Aug 2007 04:04 AM

To ease tempdb contention issues, I plan to add files to tempdb and spread them across disks. First, I added them in our test environment. (I followed Microsoft's instructions and added the files using enterprise manager. The files appeared in EM prior to stopping and starting sql server.) When I quiry sys tables, the added files are there. However, after stopping and restarting sql server, enterprise manager shows tempdb reintialized to it's state when first installed. EM does not show the added files??
What does this mean?
oshaw
New Member
New Member

--
13 Aug 2007 08:48 AM
Sorry - sql server 2000 sp 4
oshaw
New Member
New Member

--
14 Aug 2007 02:55 AM
Yes - There is an error message for a log file that I did not explicitly create:

"CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'D:\Program Files\Microsoft SQL Server\MSSQL\DATA\templog1_Log.LDF'."

So, it seems the system tried to automatically create an additional .ldf when I created the additional .ndf files.

I ran this query against table sysaltfiles:

select
substring(name,1,13)
,substring(filename,1,70)
from sysaltfiles
where dbid =2

- the results show these files defined:

tempdev E:\sql server\Data\tempdb.mdf
templog E:\sql server\Data\templog.ldf
templog1 D:\Program Files\Microsoft SQL Server\MSSQL\DATA\templog1_Log.LDF *********
TEMPDEV1 E:\sql server\Data\TEMPDEV1_Data.NDF
tempdev2 E:\sql server\Data\tempdev2_Data.NDF
tempdev3 D:\MSSQL\Data\tempdev3_Data.NDF

*********** I did not define the templog1 file??

When you attempted to replicate the problem:

1) Did you use Enterprise manager?
2) Did you explicitly define additional log files?
3) Can these files be removed without causing a problem. (I've been testing the system and so far, get no error messages)

Please tell me exactly how you defined the additional files.

Thank You


oshaw
New Member
New Member

--
14 Aug 2007 04:46 AM
was your additional file(s) secondary with the .ndf extension? Is there "anyway" to either undo the "bad" log file created by the system?
oshaw
New Member
New Member

--
14 Aug 2007 07:40 AM
THank You!!!!
oshaw
New Member
New Member

--
10 Sep 2007 07:26 AM
Thank You!!
TRACEYSQL
New Member
New Member

--
16 Sep 2007 02:09 AM
Hi there i was about to post the same thread.
I have just done the same thing here.

I have a brand new 14 disks just for SQL Server three Drives all dedicated. All RAID 10.
Performed IOSTRESS tests on Old and New 200 IOPS old and now 10,000 IOPS fantastic i thought.

Now got SQL Server with 4 CPU even better.

Created additional files
tempdev
templog
tempdev2
tempdev3
tempdev4
all same size

Ran one application within our ERP system.
CPU starts at 0% and goes to 25% and stays there until it finishes. Captured profiler and yes i can see the code its doing some massive inserts and updates and build Z_ tables and truncates etc.

I look in TEMPDB and i see probrably 200 entries for EXT X in here.


Now with or without the additional TEMP files same thing 25%.

Stuck - cannot change code of ERP .

All indexes are updated all statistics .


How do i fix or is there a fix. We are on build 8.00.2039 SQL 2000 SP4
Is there a later build for SQL

I see this T1118 (But im not sure if this is for SQL 7 or not).


Any ideas or suggestions
TRACEYSQL
New Member
New Member

--
16 Sep 2007 10:22 AM
The problem is when ERP Application is running the CPU goes from 0 to 25 % and remains there until the process is complete. I see the Exclusive Exents .

The program writes to Z_ Tables (which is blank with data) it does some updates to other main tables from these Z_ Tables and then does truncate from the Z_ Tables once it populates the main tables.

I definately see high CPU when it does this. Then 300 locks EXT in TEMPDB .

So i wondering how to help speed it up, or if thats just what it has to do.

On our normal production we may see CPU goe to 100% and just sit there for a while.....(now we trying to narrow it down).
TRACEYSQL
New Member
New Member

--
16 Sep 2007 10:25 AM
Re Read your thread......adding inserts updates will generate the EXT in TEMPDB but it should not consume high CPU as its IO related.

This is the only thing that runs nothing else.....no other connections brand new setup and only myself on SAN doing this testing.....

So im confused on high CPU too
TRACEYSQL
New Member
New Member

--
16 Sep 2007 11:36 AM
As soon as process application got to 25 % i ran
command dbcc sqlperf(waitstats)

MISCELLANEOUS 1.0 0.0 0.0
LCK_M_SCH_S 0.0 0.0 0.0
LCK_M_SCH_M 0.0 0.0 0.0
LCK_M_S 0.0 0.0 0.0
LCK_M_U 0.0 0.0 0.0
LCK_M_X 0.0 0.0 0.0
LCK_M_IS 0.0 0.0 0.0
LCK_M_IU 0.0 0.0 0.0
LCK_M_IX 0.0 0.0 0.0
LCK_M_SIU 0.0 0.0 0.0
LCK_M_SIX 0.0 0.0 0.0
LCK_M_UIX 0.0 0.0 0.0
LCK_M_BU 0.0 0.0 0.0
LCK_M_RS_S 0.0 0.0 0.0
LCK_M_RS_U 0.0 0.0 0.0
LCK_M_RIn_NL 0.0 0.0 0.0
LCK_M_RIn_S 0.0 0.0 0.0
LCK_M_RIn_U 0.0 0.0 0.0
LCK_M_RIn_X 0.0 0.0 0.0
LCK_M_RX_S 0.0 0.0 0.0
LCK_M_RX_U 0.0 0.0 0.0
LCK_M_RX_X 0.0 0.0 0.0
SLEEP 39013.0 659203.0 639006.0
IO_COMPLETION 3152.0 998.0 0.0
ASYNC_IO_COMPLETION 0.0 0.0 0.0
RESOURCE_SEMAPHORE 0.0 0.0 0.0
DTC 0.0 0.0 0.0
OLEDB 1693.0 3.6554145E+9 0.0
FAILPOINT 0.0 0.0 0.0
RESOURCE_QUEUE 18541.0 2211715.0 613071.0
ASYNC_DISKPOOL_LOCK 0.0 0.0 0.0
UMS_THREAD 0.0 0.0 0.0
PIPELINE_INDEX_STAT 0.0 0.0 0.0
PIPELINE_LOG 0.0 0.0 0.0
PIPELINE_VLM 0.0 0.0 0.0
WRITELOG 1975.0 1525.0 0.0
LOGBUFFER 0.0 0.0 0.0
PSS_CHILD 0.0 0.0 0.0
EXCHANGE 26.0 0.0 0.0
XCB 0.0 0.0 0.0
DBTABLE 0.0 0.0 0.0
EC 0.0 0.0 0.0
TEMPOBJ 0.0 0.0 0.0
XACTLOCKINFO 0.0 0.0 0.0
LOGMGR 0.0 0.0 0.0
CMEMTHREAD 48.0 0.0 0.0
CXPACKET 19926.0 114706.0 2891.0
PAGESUPP 84.0 0.0 0.0
SHUTDOWN 0.0 0.0 0.0
WAITFOR 10.0 600126.0 600126.0
CURSOR 0.0 0.0 0.0
EXECSYNC 2.0 0.0 0.0
LATCH_NL 0.0 0.0 0.0
LATCH_KP 0.0 0.0 0.0
LATCH_SH 0.0 0.0 0.0
LATCH_UP 3.0 0.0 0.0
LATCH_EX 10624.0 59348.0 346.0
LATCH_DT 0.0 0.0 0.0
PAGELATCH_NL 0.0 0.0 0.0
PAGELATCH_KP 0.0 0.0 0.0
PAGELATCH_SH 0.0 0.0 0.0
PAGELATCH_UP 266.0 16.0 0.0
PAGELATCH_EX 0.0 0.0 0.0
PAGELATCH_DT 0.0 0.0 0.0
PAGEIOLATCH_NL 0.0 0.0 0.0
PAGEIOLATCH_KP 0.0 0.0 0.0
PAGEIOLATCH_SH 3364.0 24193.0 31.0
PAGEIOLATCH_UP 13.0 48.0 0.0
PAGEIOLATCH_EX 25.0 137.0 0.0
PAGEIOLATCH_DT 0.0 0.0 0.0
TRAN_MARK_NL 0.0 0.0 0.0
TRAN_MARK_KP 0.0 0.0 0.0
TRAN_MARK_SH 0.0 0.0 0.0
TRAN_MARK_UP 0.0 0.0 0.0
TRAN_MARK_EX 0.0 0.0 0.0
TRAN_MARK_DT 0.0 0.0 0.0
NETWORKIO 215.0 266.0 0.0
Total 98981.0 3.6590871E+9 1855471.0


Profiler
cpu read write duration
2033 338831 28027 2783
6672 1328759 156 7766
2594 427102 339 2640

On SQL has all 4 processors selected


This wait_stats run for 10 minutes
***total*** 156906.0 100.0
CXPACKET 89532.0 57.1
LATCH_EX 46961.0 29.9
PAGEIOLATCH_SH 18303.0 11.7
WRITELOG 1068.0 .7
IO_COMPLETION 717.0 .5
NETWORKIO 187.0 .1
PAGEIOLATCH_EX 121.0 .1
PAGEIOLATCH_DT .0 .0
TRAN_MARK_NL .0 .0
TRAN_MARK_KP .0 .0
TRAN_MARK_SH .0 .0
TRAN_MARK_UP .0 .0
TRAN_MARK_EX .0 .0
TRAN_MARK_DT .0 .0
PAGEIOLATCH_UP .0 .0
LATCH_DT .0 .0
PAGELATCH_NL .0 .0
PAGELATCH_KP .0 .0
PAGELATCH_SH .0 .0
PAGELATCH_UP 16.0 .0
PAGELATCH_EX .0 .0
PAGELATCH_DT .0 .0
PAGEIOLATCH_NL .0 .0
PAGEIOLATCH_KP .0 .0
PAGESUPP .0 .0
SHUTDOWN .0 .0
CURSOR .0 .0
EXECSYNC .0 .0
LATCH_NL .0 .0
LATCH_KP .0 .0
LATCH_SH .0 .0
LATCH_UP .0 .0
ASYNC_IO_COMPLETION .0 .0
RESOURCE_SEMAPHORE .0 .0
DTC .0 .0
OLEDB .0 .0
FAILPOINT .0 .0
ASYNC_DISKPOOL_LOCK .0 .0
UMS_THREAD .0 .0
PIPELINE_INDEX_STAT .0 .0
PIPELINE_LOG .0 .0
PIPELINE_VLM .0 .0
LOGBUFFER .0 .0
PSS_CHILD .0 .0
EXCHANGE .0 .0
XCB .0 .0
DBTABLE .0 .0
EC .0 .0
TEMPOBJ .0 .0
XACTLOCKINFO .0 .0
LOGMGR .0 .0
CMEMTHREAD .0 .0
MISCELLANEOUS .0 .0
LCK_M_SCH_S .0 .0
LCK_M_SCH_M .0 .0
LCK_M_S .0 .0
LCK_M_U .0 .0
LCK_M_X .0 .0
LCK_M_IS .0 .0
LCK_M_IU .0 .0
LCK_M_IX .0 .0
LCK_M_SIU .0 .0 <
TRACEYSQL
New Member
New Member

--
16 Sep 2007 11:47 AM
sp_configure reports this
affinity mask -2147483648 2147483647 0 0


Has 4 processors ...is this correct.

TRACEYSQL
New Member
New Member

--
17 Sep 2007 03:21 AM
25% because its the only application process that runs. On old one its the same 25% and thats on disks that are not on SAN.

When run this with probrably 10 other main apps on production goes to 100% and just waits.

I was hoping that on new SAN it be incredible now im stuck
You are not authorized to post a reply.

Acceptable Use Policy