| Author |
Topic |
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
 Posted - 2008-06-26 : 14:52:43
|
| On SQL 2005 - Got the standard TEMPDEV AND TEMPLOG.I have a process that consumes 100% CPU - Indexes are all good.I see there are about 1689805 locks that occur on this process.I saw the programs are PAGEIIO LATCH_EX CXPACKET.So if im correct this is TEMPDB issue? So i was going to add more temp files for the 8 CPU.On TEMPDB do you add another FILEGROUP or just add FILES.templog1, templog2, so i should add 7 more...so i have 8 temp log and 1 tempdata.Then set autogrowth to NONE and size DATASIZE 10024 and TRANSACTION LOG 2048Any advice would be appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-06-26 : 15:10:36
|
| That look interesting - Do you recommend keeping the AUTO GROWTH set to automatic or NONE.If i do on production now do i have to reboot or providing the size is lower than the 1024 which is what i was going to set it to for the cpu.Cheers. |
 |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-06-26 : 15:21:45
|
| I ran on my laptop version of SQL 2005 i have two CPUSo it creates a TEMPDEV DATA TEMPDEV_2 DATA TEMPLOG LOGSo its the DATA file is per CPU so if i run on 8 CPU i end up with TEMPDEV DATA TEMPDEV_2 DATA TEMPDEV_3 DATA to TEMPDEV_8 DATA TEMPLOG LOGAnd it is set to autogrowth --- Is that ok to leave as this kind of prefer so it not restricted by growth. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-26 : 15:22:22
|
quote:
That look interesting - Do you recommend keeping the AUTO GROWTH set to automatic or NONE.If i do on production now do i have to reboot or providing the size is lower than the 1024 which is what i was going to set it to for the cpu.
I don't recommend setting it to none as what happens if it needs the space for some reason and you do have enough space on disk for it to grow some?Mine are set to grow by 200MB and unrestricted growth.My script does modify the grow by option, so if you use my script make sure to pass in the MB value to @growth.My blog entry for that script shows what my recommedation is for when to run it. You don't need to reboot/restart, but it depends on your current data file size and what you'll be setting them all to.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-26 : 15:23:57
|
quote:
Originally posted by TRACEYSQL
I ran on my laptop version of SQL 2005 i have two CPUSo it creates a TEMPDEV DATA TEMPDEV_2 DATA TEMPLOG LOGSo its the DATA file is per CPU so if i run on 8 CPU i end up with TEMPDEV DATA TEMPDEV_2 DATA TEMPDEV_3 DATA to TEMPDEV_8 DATA TEMPLOG LOGAnd it is set to autogrowth --- Is that ok to leave as this kind of prefer so it not restricted by growth.
Yes it's the data files to add. It's all explained in the MS link in my blog entry.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-06-26 : 15:30:30
|
| Cheers thanks for the help....I do some more reading then run it in production. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-06-26 : 15:32:34
|
| What kind of scripts report do you run after the effect to ensure that problem is not tempdb related....I kind of did it the other way around by seeing PAGE_IO and LATCH_EX .... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-26 : 15:38:09
|
| To be honest, I don't know. The MS link in my blog entry says to add data files when tempdb is found to be a performance problem, but it doesn't say how to determine that. We've just made it a standard practice to add tempdb data files according to how many CPUs we have since it won't hurt you to have this config but it may hurt you if you don't. We had MS come out to help us with our performance problems a few years back. One of the fixes was this tempdb thing, which is why we've made it a standard practice for all of our prod systems on not just ones having issues.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-06-26 : 15:42:49
|
| No problem...Last thing ran on dev box and the tempdev remained at 8 MB and the tempdev2 tempdev3 set to 1024.But first tempdev is 8 ...i cannot go into this and set to 1024 so they are all equal. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-06-26 : 15:53:18
|
| Says specified file size is less than current. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2008-06-26 : 16:20:59
|
| Cheers will do thanks.I just ran my large application and still at 636,460 locks seconds pusshes the CPU to 100% so back to the drawing board on analysis |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-28 : 00:39:50
|
| Adding more files in tempdb will not solve your issue if those locks are held in user db. |
|
|
|
|
TEMPDB