May 7, 2008 at 12:19 pm
I actually uninstalled and reinstalled and left all default settings. Next week, I will be modifying the procedure to not use a cursor. I'll post the results here when I'm done.
May 7, 2008 at 1:09 pm
Check hyper-threading and the number of tempDB files you have.
You may want to check that hyper-threading is turned off for your machine. Hyper-threading slows performance on SQL Server.
Also, you want to make sure that you have one tempDB file for every logical core in the machine which in the case of your dual quad Xeon is 8 tempDB files. This will help your parallelism.
--Dave
May 8, 2008 at 6:21 am
I'm pretty sure Xeon quad-cores do not support Hyper-Threading, so no problem there. Eight tempdb files seems excessive, and I don't have the disk space to span them out over eight volumes. I can create eight files, but they would have to be split between two volumes. Also, the second volume is for log files, so throwing in a random access data file on it may actually decrease performance. I have room for another SATA drive, so maybe I can dedicate it to the tempdb data file.
I started to work on fixing the procedure last night, and am pretty sure I can remove the cursor. All other queries run extremely fast, so once I solve this issue I'll be happy.
I appreciate the suggestions, keep them coming if you think I've missed something.
May 8, 2008 at 12:42 pm
From what I have read, creating multiple files for tempdb helps even if they are on the same drive. I have a production server setup that way and the activity on the drive with tempdb is minimal.
😎
May 8, 2008 at 2:07 pm
Lynn Pettis (5/8/2008)
From what I have read, creating multiple files for tempdb helps even if they are on the same drive. I have a production server setup that way and the activity on the drive with tempdb is minimal.😎
I believe that guidance is for situations where you are seeing blocking on the SGAM page due to heavy temp object creation/drop activity.
You can actually cause poorer performance by having too many files on a drive due to head thrashing.
YMMV.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 8, 2008 at 2:25 pm
to see if you have IO problems regarding the tempdb you can offcourse always use:
/* track sqlserver IOstall time*/
set nocount on
SELECT ' use ['+ name +']
go'
+ case dbid when 1 then ''
else char(10) + ' insert into #tmp '
end
+ char(10) + ' SELECT db_name() as dbname, * '
+ case dbid when 1 then char(10) + ' into #tmp '
else ''
end
+ char(10) + 'FROM ::fn_virtualfilestats(' + cast(dbid as varchar(15)) + ', -1)
order by DbId, FileId
;'
FROM master..sysdatabases
order by dbid
;
print 'Select * from #tmp order by dbname, FileId '
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 8, 2008 at 3:15 pm
Since this is a 2005 specific forum, I think it is best to use this: sys.dm_io_virtual_file_stats
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 8, 2008 at 11:58 pm
TheSQLGuru (5/8/2008)
Since this is a 2005 specific forum, I think it is best to use this: sys.dm_io_virtual_file_stats
Indeed I posted the "old" script (grabbed it from the sql2k lib) :blush:
Using the dmv-version is advised for sql2005.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 9, 2008 at 2:11 am
If only there was some code, imagine what we could do to help.
--Dave
May 9, 2008 at 4:36 am
fn_virtualfilestats is fine with sql 2005 , think you're being too picky!
I sense a vast lack of knowledge in dealing with the original post - quad core procs do support HT and yes you should disable hyperhtreading in general for SQL Server.
I find that multi core procs have actually enhanced my employment prospects as whilst one might think the increased use of multi cores would actually cut the number of sockets used - e.g. save license costs - I find 4 way quads are often the de facto sql box - that's a 16 way box in old money - and it's probably running an app that would happily run on a single quad and save you lots of money. There have been just so many microsoft blogs and posts about the danger sof too many processor cores ( each core requires extra resource ) I'd suggets some serious research on the matter - for a 4 x quad core ( x64 ) I'd probably suggest assigning around 6gb ram for the o/s only
.
So many cores = performance problems due to lack of resource / scheduler problems / paralleism etc. you can run a server out of threads very quickly with 16 cores and if you leave HT enabled ( wow 32 procs !! ) very very quickly.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 9, 2008 at 5:59 am
I still disagree about the hyper-threading thing. It disappeared when the first dual-core procs were released. It will apparently make a comeback this year in Intel's Nehalem processor, and will be called "simultaneous multi-threading".
http://www.intel.com/pressroom/archive/releases/20070328fact.htm
http://www.tgdaily.com/content/view/31408/135/[/url]
I did go into the BIOS, and disable the cores, however, it had no effect. There were no settings for Hyper-Threading (because it doesn't exist). 😉
I saw a request for my procedure code earlier. I may post it if I'm having issues removing the cursor from it, but I will start a new thread. Don't want to get too far off topic.
Thanks for the I/O script.
May 9, 2008 at 8:03 am
True, YMMV, but for our PeopleSoft application where I have tempdb on its own drive with multiple files, nary a blip. It's big enough that it doesn't grow, and all the disk stats are great.
😎
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply