October 1, 2010 at 9:27 am
We are contemplating adding a solid state PCI disk drive to one of our servers and moving tempdb to the new drive.
Does anyone have advice re this type of configuration?
Thanks for any input.
October 1, 2010 at 9:56 am
I've been dying to try this myself... something to keep in mind though, analyze the io stats of your db files first (see sys.dm_io_virtual_file_stats) http://msdn.microsoft.com/en-us/library/ms190326.aspx .
It could be that you might see better performance gain by moving actively used db tables to it instead.
Also, keep in mind if you are using sql server in a cluster configuration, the tempdb must exist on the same device as your database.
October 1, 2010 at 12:32 pm
getoffmyfoot (10/1/2010)
I've been dying to try this myself... something to keep in mind though, analyze the io stats of your db files first (see sys.dm_io_virtual_file_stats) http://msdn.microsoft.com/en-us/library/ms190326.aspx .It could be that you might see better performance gain by moving actively used db tables to it instead.
Also, keep in mind if you are using sql server in a cluster configuration, the tempdb must exist on the same device as your database.
I'm pretty sure that's not a requirement. As a matter of fact, I wouldn't recommend it as it would create an i/o bottleneck. I've never had to do that and I've configured a number of clusters. As for "actively used db tables", I typically find that TempDB sees as much or more activity than any user database. There's a reason it is suggested that TempDB sit on it's own, preferably RAID 10, device.
"Beliefs" get in the way of learning.
October 1, 2010 at 12:53 pm
we are trying this at the moment. I'll get back to you early next week. post here if you don't hear from me.
Incidentally we have already tried moving heavily used data files and then log files to an SSD and saw no improvements in ETL process durations. Still trying to get my head around that one.
---------------------------------------------------------------------
October 1, 2010 at 1:29 pm
I sure don't have any, but you might search through Brent Ozar's blog[/url]. He's talked about them pretty frequently.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 1, 2010 at 1:48 pm
Robert Frasca (10/1/2010)
getoffmyfoot (10/1/2010)
I've been dying to try this myself... something to keep in mind though, analyze the io stats of your db files first (see sys.dm_io_virtual_file_stats) http://msdn.microsoft.com/en-us/library/ms190326.aspx .It could be that you might see better performance gain by moving actively used db tables to it instead.
Also, keep in mind if you are using sql server in a cluster configuration, the tempdb must exist on the same device as your database.
I'm pretty sure that's not a requirement. As a matter of fact, I wouldn't recommend it as it would create an i/o bottleneck. I've never had to do that and I've configured a number of clusters. As for "actively used db tables", I typically find that TempDB sees as much or more activity than any user database. There's a reason it is suggested that TempDB sit on it's own, preferably RAID 10, device.
It is when you are running sql server in a windows failover cluster configuration (which is what I was referring to, sorry that wasn't very clear) - http://connect.microsoft.com/SQLServer/feedback/details/532759/support-local-disk-location-for-tempdb-in-failover-cluster-installation
the term "cluster" gets used for so many things...
October 1, 2010 at 1:55 pm
getoffmyfoot (10/1/2010)
Robert Frasca (10/1/2010)
getoffmyfoot (10/1/2010)
I've been dying to try this myself... something to keep in mind though, analyze the io stats of your db files first (see sys.dm_io_virtual_file_stats) http://msdn.microsoft.com/en-us/library/ms190326.aspx .It could be that you might see better performance gain by moving actively used db tables to it instead.
Also, keep in mind if you are using sql server in a cluster configuration, the tempdb must exist on the same device as your database.
I'm pretty sure that's not a requirement. As a matter of fact, I wouldn't recommend it as it would create an i/o bottleneck. I've never had to do that and I've configured a number of clusters. As for "actively used db tables", I typically find that TempDB sees as much or more activity than any user database. There's a reason it is suggested that TempDB sit on it's own, preferably RAID 10, device.
It is when you are running sql server in a windows failover cluster configuration (which is what I was referring to, sorry that wasn't very clear) - http://connect.microsoft.com/SQLServer/feedback/details/532759/support-local-disk-location-for-tempdb-in-failover-cluster-installation
the term "cluster" gets used for so many things...
I went back and read myself and that is confusing - let me clarify - You have to have tempdb on shared storage, not local storage, when using sql server in a windows failover cluster configuration. Yes, definitely put tempdb on separate spindles from the other databases.
October 1, 2010 at 2:01 pm
getoffmyfoot (10/1/2010)
Robert Frasca (10/1/2010)
getoffmyfoot (10/1/2010)
I've been dying to try this myself... something to keep in mind though, analyze the io stats of your db files first (see sys.dm_io_virtual_file_stats) http://msdn.microsoft.com/en-us/library/ms190326.aspx .It could be that you might see better performance gain by moving actively used db tables to it instead.
Also, keep in mind if you are using sql server in a cluster configuration, the tempdb must exist on the same device as your database.
I'm pretty sure that's not a requirement. As a matter of fact, I wouldn't recommend it as it would create an i/o bottleneck. I've never had to do that and I've configured a number of clusters. As for "actively used db tables", I typically find that TempDB sees as much or more activity than any user database. There's a reason it is suggested that TempDB sit on it's own, preferably RAID 10, device.
It is when you are running sql server in a windows failover cluster configuration (which is what I was referring to, sorry that wasn't very clear) - http://connect.microsoft.com/SQLServer/feedback/details/532759/support-local-disk-location-for-tempdb-in-failover-cluster-installation
the term "cluster" gets used for so many things...
I understood what you meant. I think you might have misunderstood the article. The article says that TempDB must be on "shared" storage" not on the same device as the database. When you configure a cluster you designate storage resource groups that are shared by all of the nodes on the cluster. That resource group can contain any number of logical "devices". Your database can be spread across one or more of these logical devices and your TempDB should be on it's own device whether it's solid state or not. The requirement is that the device be shared between all of the nodes on the cluster.
The question they were responding to was asking about having TempDB stored on a drive that was local to each node on the server. That would be like using the C: drive for each server for TempDB. Since the C: drive for Server A is not shared by Server B it is not allowed.
"Beliefs" get in the way of learning.
October 1, 2010 at 2:03 pm
getoffmyfoot (10/1/2010)
getoffmyfoot (10/1/2010)
Robert Frasca (10/1/2010)
getoffmyfoot (10/1/2010)
I've been dying to try this myself... something to keep in mind though, analyze the io stats of your db files first (see sys.dm_io_virtual_file_stats) http://msdn.microsoft.com/en-us/library/ms190326.aspx .It could be that you might see better performance gain by moving actively used db tables to it instead.
Also, keep in mind if you are using sql server in a cluster configuration, the tempdb must exist on the same device as your database.
I'm pretty sure that's not a requirement. As a matter of fact, I wouldn't recommend it as it would create an i/o bottleneck. I've never had to do that and I've configured a number of clusters. As for "actively used db tables", I typically find that TempDB sees as much or more activity than any user database. There's a reason it is suggested that TempDB sit on it's own, preferably RAID 10, device.
It is when you are running sql server in a windows failover cluster configuration (which is what I was referring to, sorry that wasn't very clear) - http://connect.microsoft.com/SQLServer/feedback/details/532759/support-local-disk-location-for-tempdb-in-failover-cluster-installation
the term "cluster" gets used for so many things...
I went back and read myself and that is confusing - let me clarify - You have to have tempdb on shared storage, not local storage, when using sql server in a windows failover cluster configuration. Yes, definitely put tempdb on separate spindles from the other databases.
Sorry, I was typing my response while you were clarifying yours. They crossed in the mail, so to speak.
"Beliefs" get in the way of learning.
October 5, 2010 at 4:30 am
well we tried a run of our ETL process with tempdb on SSD and elapsed times were comparable to the standard disks, i.e. no real improvement.
What this shows (to me anyway) is that if you are not IO bound spending money on SSD will not necessarily provide improvements to justify the cost (it won't make it worse either). Our IO paths to the SAN were only running at 25% capacity so we had no IO backlog and therefore a faster disk bought us nothing.
Disk configuration if you are interested:
---------------------------------------------------------------------
October 6, 2010 at 7:08 am
I'm interested in your disk configuration.
October 6, 2010 at 7:21 am
its in the attachment
---------------------------------------------------------------------
October 6, 2010 at 7:24 am
duh, thanks. Time for another double shot + energy.
It looked the text was missing from your post.
October 6, 2010 at 7:40 am
I found your test results very interesting. Thanks for posting. My guess is that the controller for the SSD isn't any faster than the controller for a "regular" disk, i.e. you can still only pass X number of I/O's per second through the controller. If the SSD is part of a SAN you would have network latency on top of that. (That latency could be rendering the value of the SSD moot, i.e. acting as a governor so to speak.) That being said, I'd love to have some time to play with it a little, i.e. put some serious stress on the SQL Server and see how the SSD performs.
If you're not seeing any measurable gain, where is the value add for an SSD? Seems kind of strange to me. Any chance you could post the name of the SSD manufacturer? I'd like to go to their web site and check out the technical specifications for the drive.
"Beliefs" get in the way of learning.
October 6, 2010 at 7:42 am
One other thought on your test. It could be that your ETL processes aren't using TempDB in any significant way so you might not see a gain. Easy enough to figure that out with Perfmon while you're running your test.
"Beliefs" get in the way of learning.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply