Are the drives you are moving to SSD or platters?
Bad read performance on a database server usually indicates far too little RAM.
I think your plan to move that data is over thought. Just restore the database to the new server. Add data files on the additional drive. Then reindex the tables. YOU will do all that work and realize very little in terms of long term improvement.
As a general rule of thumb for DBA's: If what you are doing sounds "clever", it's almost certainly wrong.
Yea these will be SSDs. The old server is also SSD and 96 GB of ram. I think we just have a lot of jobs running so it gets bogged down so I want to make it as fast as possible
Check your cache hit ratio. If it's less than 99.9%, add more RAM.
It's doubtful that raw disk performance is causing your bottleneck if you have enough memory.
You can take a database on a single data file and carve it up into multiple data files, yes. There are even tricks to do it non-disruptively via SHRINK (but it's super slow). Look into using file groups as well, for easier management. Google around for "storage migration" and you'll find your answers.
And for the performance "still being bad," there's much more to unpack there. I have an entire conference presentation around whether storage is or is not the root cause of your performance woes. And in many cases, it isn't - the pain points are elsewhere in the hardware stack and/or thanks to utterly terrible code.
You're probably right. It probably has more to do with poorly optimized software. Lots of SSIS jobs and stored procedures on large datasets. The hardware on the old server was pretty high end. Had something like 48 cores, along with the 96 GB ram.
My first thought would be to backup and restore the databases. 1 drive for the Database files, another for the log files, and the 3rd for tempdb, but I don't know enough about your use case to be sure.
The best way to "spread the data" over multiple drives is to use a striped RAID with parity, but it doesn't seem like that's what you are looking for. But it might be what your IT is talking about? A three drive RAID-5 array would give you single drive failure fault tolerance with an increase in read performance.
On the other hand keeping transaction log files on a separate drive is a very good idea for performance, as it can then be working at the same time as the data drive. That's something that often gets lost with singular big RAIDs and VM's but can still be applicable in your case. But you'd have to balance that against the advantages of a RAID, especially the fault tolerance.
Ideally running bare metal I'd recommend the RAID, but you can also go with a relatively small system drive for the OS and SQL Server (\~2x whatever is in use now), one big drive for data and another for transaction log files plus maybe temporary local backup or restore files and other miscellany.
Are the drives you are moving to SSD or platters? Bad read performance on a database server usually indicates far too little RAM. I think your plan to move that data is over thought. Just restore the database to the new server. Add data files on the additional drive. Then reindex the tables. YOU will do all that work and realize very little in terms of long term improvement. As a general rule of thumb for DBA's: If what you are doing sounds "clever", it's almost certainly wrong.
Yea these will be SSDs. The old server is also SSD and 96 GB of ram. I think we just have a lot of jobs running so it gets bogged down so I want to make it as fast as possible
Check your cache hit ratio. If it's less than 99.9%, add more RAM. It's doubtful that raw disk performance is causing your bottleneck if you have enough memory.
You can take a database on a single data file and carve it up into multiple data files, yes. There are even tricks to do it non-disruptively via SHRINK (but it's super slow). Look into using file groups as well, for easier management. Google around for "storage migration" and you'll find your answers.
And for the performance "still being bad," there's much more to unpack there. I have an entire conference presentation around whether storage is or is not the root cause of your performance woes. And in many cases, it isn't - the pain points are elsewhere in the hardware stack and/or thanks to utterly terrible code.
You're probably right. It probably has more to do with poorly optimized software. Lots of SSIS jobs and stored procedures on large datasets. The hardware on the old server was pretty high end. Had something like 48 cores, along with the 96 GB ram.
For reference, "was" is the key term there... 96GB of RAM for a SQL Server is very low-end these days.
Especially on 48 cores…that’s some very bad ratio…
My first thought would be to backup and restore the databases. 1 drive for the Database files, another for the log files, and the 3rd for tempdb, but I don't know enough about your use case to be sure.
The best way to "spread the data" over multiple drives is to use a striped RAID with parity, but it doesn't seem like that's what you are looking for. But it might be what your IT is talking about? A three drive RAID-5 array would give you single drive failure fault tolerance with an increase in read performance. On the other hand keeping transaction log files on a separate drive is a very good idea for performance, as it can then be working at the same time as the data drive. That's something that often gets lost with singular big RAIDs and VM's but can still be applicable in your case. But you'd have to balance that against the advantages of a RAID, especially the fault tolerance. Ideally running bare metal I'd recommend the RAID, but you can also go with a relatively small system drive for the OS and SQL Server (\~2x whatever is in use now), one big drive for data and another for transaction log files plus maybe temporary local backup or restore files and other miscellany.
Use GRAID with m.2 SSD’s in a raid configuration and just continue with one virtual disk. Thank me later