TempDB CPU
-
Yeah, huh? Still can't get used to that...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
someone go off on vacation? or worse?
-
Not sure what you mean, Steve
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Not sure what you asked noeld Sorry.....
Thank you all for your help on this.
This is only for 2005 i cannot do this in 2000?
-
Tracey,
No reason you can't do this in SQL 2000 that I'm aware of. I just added a file to the TempDB on my local PC version of the database via SSMS and had no problem. You might want to check FILES in Books Online for SQL Server 2000 though to verify the T-SQL code hasn't changed between versions.
-
Thanks i will give it a try....
-
the multiple files work in sql2000 and sql2005 - you may want to enable a trace flag, especially in 2000, can't remember which off the top of my head.
You ideally want tempdb on a fast array, preferably raid 10, and I've posted about this before concerning splitting tempdb out to its own array - if your data array has, say 14 disks arranged in raid 10 - that's 7 spindles effectively available, so 7 x xxx io / sec. If you're now going to put tempdb out on its own disks the raid should be as fast or faster than the array you taken it from, otherwise you're going to introduce a bottleneck as spindles x io still equals throughput. You'd never want to allocate files to individual disks ( spindles ) introduces failure points, always use a raid array and stripe across the array.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Let me re read the last post
First we had the first 5 slots on SAN which is used by the operating system so thats 5 disks (this is raid 5) has all my disks on it tempdb, all databases etc.
We took one database and moved this to two disks (raid 1).
Now for the tempdb (if i had 5 (event if raid 5) then this is 5 * io i can have so i want to have aleast that number for the new tempdb files.
otherwise i get bottleneck as spindles x io still equals throughput.
Saying about the bottleneck ( i have noticed the disk load is still high on the two database i just moved to raid 1)....(Is that cause i went from 5 disks (raid 5) to 2 disks (raid 1)....
-
Tracey,
Load is going to be high on Raid 1. Higher for writing than for reading, because the disks are essentially making two copies of every transaction on the mirror. Colin was suggesting Raid 10 which is mirror & striping.
First thing you should do is check the I/O speed (not the actual spin speed) of your disks and the I/O speed of the controllers. Is the speed of the disks & controllers on your Raid 1 equivlant to or greater than the speed of the disks & controllers on your Raid 5? Greater is preferable, especially since you've put tempdb on a plain mirror.
The math Colin's talking about comes in (correct me if I'm wrong, Colin) when you look at the load your databases will be putting on the tempDB. Then you have to consider how much data could be coming in from each of the databases and whether or not your I/O speed is going to be able to handle all other disks sending and retrieving data at their maximum capacity.
As far as moving 2 databases to the same Raid array as the tempdb is on, that actually defeats the purpose of splitting out your tempdb for I/O optimization. If your tempdb can't have a monopoly on the disk and disk controller, than you've killed any chance you have at speeding up your processes.
-
Thank you for the replies...gosh this is hard
I/O speed (not the actual spin speed) of your disks and the I/O speed of the controllers. I have to re ask our consultants this...
Our data reads are hugh i worked this out to 3gig or sometimes 8 gig in an hour.
This is fun but a lot to learn
-
Tracey,
What's going to be really hard is when you get all of this figured out and realize that all the load balancing you're trying to do with TempDB is nothing more than a temporary patch... the real problem is the code.
If you take a look at it, I'll just bet it has more Cursors, While Loops, Views and Views-of-Views, and Correlated Sub-Queries than you can shake a stick at. The ONLY long term fix for that is to rewrite the code correctly 
... and better to do it now because the problem will continue to get worse as the data grows. If you also have triangular joins (<=, <, >= >, <>
in the Correlated Sub-Queries, consider the facts in the following thread and that a lousy 10,000 row table join can cause more than 50 MILLION internal rows to be spawned. If you double that to 20,000 rows, you now spawn well over 200 MILLION internal rows! Now, just how much TempDB tuning are you going to have to do to handle that??? 
Read the thread below and you will understand why you're getting an overwhelming number of reads...http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=359124&p=2
... and you STILL owe us the link for the article you said you read in your first post
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
absolutely - crap apps are always the problem. all the balancing and splitting of files, filegroups, arrays and databases only helps to a certain extent - I'm tuning an ERP app and although the hardware needs some serious work, removing the 100 million+ io queries will achieve far more far quicker.
btw. dump raid 5 that'll make things better.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Can't you tell Jeff. Apparently all of your posts are invisible to him.
Agree with your point completely. On the note of the tempdb issue. This isn't the origional post, but a really good one on the issue.
Basically the way it goes is IF you are having CONTENTION issues with tempdb, they can be reduced by doing 2 things. And the performance advisor in 2005 also makes similar recommendations.
Basically do 2 things.
1) create a temp file for every CORE. FIXED SIZE. If they are different it throws the whole thing off.
2) Add trace 1118 to force a round robin on the files.
-
Good information... thanks, Bob. Tracey may not be acknowledging my requests but I hope (s)he is getting something out of this...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Now. THAT's what I'm talking about! Thanks, Colin!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply