Are there alternatives to tempdb

Question

Are there alternatives to tempdb

kkapferer

SSChasing Mays

Points: 621
More actions
April 5, 2010 at 9:20 am

#231379

We are in the midst of a website redesign project, and have come across a scenario where the consultants brought in have recommended that we set up a new instance of SQL Server for a component of the project, rather than creating a new database in our existing instance, citing this particular component is rather I/O intensive, and tempdb will get swamped and we will take performance hits.
My question is, could we just define a different temporary db for this extra component to do its work and leave tempdb alone for the other databases to use. I know Oracle supports this type of functionality, and I was looking for something similar to this in the MS universe.
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

a new, seperate instance would have a greater impact on a machine than simply making tempdb starting with more than enough room to handle expansion needed by your apps calculations. two versions of Sqlserver.exe, seperate memory allocations, seperate everything, and for what?

except for developer machines testing more than one version, i do not think a production machine should have more than one instance.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Toby White SSCertifiable Points: 6187 More actions · Answer 2

One option would be to spread tempdb over more spindles. Another alternative would be to install an additional instance on the server and place the tempdb for that instance on it's own disk array.

Tempdb is used for some operations whether you use temp tables or not, but you can consciously minimize it's use from a design and programmatic perspective. If it is I/O bound I suggest simply using better disk subsystem. More cache on controller, more/faster spindles etc. The vendor/contractors are always going to want the "best case" scenario even if a good case would be more than sufficient.

Regards,

Toby

kkapferer SSChasing Mays Points: 621 More actions · Answer 3

Lowell,

Thanks for your reply. I reread my original post and I see I was unclear. When I wrote separate instance I was implying that this was a separate machine. Of course you are correct in your statement that a separate instance on the machine would provide inferior performance.

We are trying to make a decision on whether we want to license another SQL Server instance or take the performance hit the Microsoft consultants have warned us about. My original post was to see if there was a third option where we could just define another database and declare that a temporary database for this extra components' database to do work in.

Thanks again for responding so quickly to this thread.

kkapferer SSChasing Mays Points: 621 More actions · Answer 4

kkapferer

SSChasing Mays

Points: 621

April 5, 2010 at 9:51 am

#1145810

Thanks Toby,

I'll research your suggestions.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

kkapferer (4/5/2010)
We are in the midst of a website redesign project, and have come across a scenario where the consultants brought in have recommended that we set up a new instance of SQL Server for a component of the project, rather than creating a new database in our existing instance, [font="Arial Black"]citing this particular component is rather I/O intensive, and tempdb will get swamped [/font]and we will take performance hits.
My question is, could we just define a different temporary db for this extra component to do its work and leave tempdb alone for the other databases to use. I know Oracle supports this type of functionality, and I was looking for something similar to this in the MS universe.
Thanks in advance.

I believe that everyone is concentrating on the wrong problem and that they're throwing hardward at the problem in a vain attempt to overcome it and it will eventually become a problem even on the new hardward. The real problem is in the code and the design of the database and until someone fixes those things, you won't have a fix for long.

--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)

Garadin One Orange Chip Points: 29613 More actions · Answer 6

kkapferer (4/5/2010)
Lowell,
Thanks for your reply. I reread my original post and I see I was unclear. When I wrote separate instance I was implying that this was a separate machine. Of course you are correct in your statement that a separate instance on the machine would provide inferior performance.
We are trying to make a decision on whether we want to license another SQL Server instance or take the performance hit the Microsoft consultants have warned us about. My original post was to see if there was a third option where we could just define another database and declare that a temporary database for this extra components' database to do work in.
Thanks again for responding so quickly to this thread.

SQL Licenses are spendy. Before you bought another one, I'd look at buying additional drives / arrays that you could spread TempDB onto. It all depends on your situation of course, but it should at least be considered.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 7

make sure tempdb has enough spindles to handle the IO - once you have that you should have 1 tempdb data file for every cpu you have up to 8. It has be determined that after 8 no performance gain is found. The tempdb data files should also all be the same size with the same growth. typically having the files start at about 2 GB each will work for most systems. And the tempdb log file should be on a different set of disk completely (only 1 of those is needed)

I would also suggest getting clarification of how intense it actually will be. You could use the SQLIO.exe to see the capacity of your disks and use that to plan how much disk will be needed to get the IO they say it will need

hope that helps 🙂