0

We have a new local server that processes data using sql server 2008 and ssis. in this server i have dedicated drive to do different things. the c drive is for the os and software. the d drive is for db storage , and the ssis . The e drive is a ssd drive that we restore each database that is being used by the ssis.

our idea was that we process allot of data and since the ssd drive is only 500gb( because of the cost) we would have everything on a regular drive and transfer the databases in use to the ssd drive to have the process run faster.

when i run the ssis without the ssd drive it takes about 8 hrs and when i run the process restoring the databases on the ssd drive the process takes about the same amount of time to process( in fact if i include the restoring of the data bases the process takes longer) As of right now i cannot move the os and software to the ssd drive to test to see if that would help the process.

is there a way to utilze the ssd drive to process the data and to speed up the process.

user2615302
  • 194
  • 2
  • 14

1 Answers1

1

If you want to speed up a given process, you need to find the bottleneck.

Generally speaking (since you give no details of the SSIS-Process) at each point in the operation, one of the systems components (CPU, RAM, I/O, Network) is operating at maximum speed. You need to find the component that contributes the most to your run time and then either speed that component up by replacing it with a faster component or reduce the load on it by redesigning the process.

Since you ruled out already the I/O to the user database(s), you need to look elsewhere. For a general ad hoc look, use the systems resource monitor (available through the task manager). For a deeper look, there are lots of performance counters available via perfmon.exe, both for OS (CPU, I/O), SSIS and SQL Server.

If you have reason to believe that DB-I/O is your bottleneck, try moving the tempdb to the SSD (if you generally have lots of load on the tempdb that is a good idea anyway). Instructions here.

Unless you give us some more details about the SSIS process in question, that's all I can say for now.

TToni
  • 9,145
  • 1
  • 28
  • 42
  • do you think if i moved the ssis to the ssd drive it would process faster. As far as the ssis being a efficient process i know it is not. but i want to have the process in the correct spot on the server before i start to change the process using the ssd drive to its fullest ability. dose that make sense? – user2615302 Jan 07 '14 at 15:38
  • 2
    @user2615302: A process lives in the RAM, not on the disk. A SSIS package is just a bunch of code that gets loaded into RAM once and is then executed, regardless where it came from. Unless the package itself is multiple GB in size it won't make any difference where you load it from. – TToni Jan 07 '14 at 15:50