November 20, 2012 at 10:19 am
Bhuvnesh (11/20/2012)
Robert Davis (8/7/2012)
Read my tempdb whitepaper: http://www.idera.com/Action/RegisterWP.aspx?WPID=37its not working
Sorry. Try this link: http://www.idera.com/Action/RegisterWP.aspx?WPID=43
November 20, 2012 at 12:15 pm
Wow! Nice thread ... 😛 .. just reading was fun!
I've seen both sides of the coin (IO and page contention), when I was working for HP. So I know 1st hand how important tempdb configuration is. I even discussed with a MS engineer about why MS shares tempdb across all databases and the differences with Oracle. But that's part of another thread, I guess.
Kind of off topic, but related. Bolton and Ozar wrote an awesome book where they discuss this in detail: SQL Server 2008 Internals, Chapter #7.
November 21, 2012 at 8:31 am
I would like to add to this thread that the number of clients I have had in 15 years of consulting that actually had tempdb allocation issues is TWO, and one of them was a stupid client trick (98% of their 1000+ sprocs had table variables in them). This is a non-issue for the VAST VAST MAJORITY of SQL Installations in the world, IMHO. Fortunately when it does occur multiple tempdb files is a nice magic bullet fix.
Now, on the other side of the coin, I have had more than a few clients that have REDUCED their tempdb IO throughput/response by having TOO MANY FILES (i.e. the ridiculous one-file-per-core "rule") on TOO FEW SPINDLES. They get KILLED by the access/seek times of the disk(s) and head thrashing throttles IO badly.
YMMV - this is just one man's observations of many real-world environments.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 21, 2012 at 10:10 am
TheSQLGuru (11/21/2012)
I would like to add to this thread that the number of clients I have had in 15 years of consulting that actually had tempdb allocation issues is TWO, and one of them was a stupid client trick (98% of their 1000+ sprocs had table variables in them). This is a non-issue for the VAST VAST MAJORITY of SQL Installations in the world, IMHO. Fortunately when it does occur multiple tempdb files is a nice magic bullet fix.
Kevin, i couldnt agree more.
TheSQLGuru (11/21/2012)
Now, on the other side of the coin, I have had more than a few clients that have REDUCED their tempdb IO throughput/response by having TOO MANY FILES (i.e. the ridiculous one-file-per-core "rule") on TOO FEW SPINDLES. They get KILLED by the access/seek times of the disk(s) and head thrashing throttles IO badly.YMMV - this is just one man's observations of many real-world environments.
Having too many files is worst than too few in my book.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply