Pau Lo
asked on
tempDB and single data files - risks
Can I ask for a laymans interpretation (management jargon free summary) of the following issue in SQL Server. By reviewing a recent healthcheck report, they flag it as a medium severity issue that the "tempDB" only has 1 data file. Can anyone elaborate on the risks here, and what exactly the tempDB does in relation to MSSQL?
basically I am asking what is the risk in having only 1 data file for tempDB? How many should you have? Is this the same for every database?
basically I am asking what is the risk in having only 1 data file for tempDB? How many should you have? Is this the same for every database?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The things I've read about most seem to indicate that it's mainly a tempdb issue, but it depends upon your workload and what the system is used for.
You mainly OLTP ? Then short in and out transactions probably aren't going to make a huge difference.
You have a data warehouse with huge volumes of data ? Then maybe partitioning is a better idea.
Paul Randal ran a scenario where he did some testing (for what it is worth in this discussion), but might be worth a read: http://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/
You mainly OLTP ? Then short in and out transactions probably aren't going to make a huge difference.
You have a data warehouse with huge volumes of data ? Then maybe partitioning is a better idea.
Paul Randal ran a scenario where he did some testing (for what it is worth in this discussion), but might be worth a read: http://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/
ASKER
Yes mainly OLTP info systems driven by our SQL database apps.
ASKER