SQL Server Performance Forum – Threads Archive
Network Attached Storage (NAS) OptimizationWe are in the process of migrating a single server SQL Application to a configuation that maintains the SQL Application on one server (Gateway 9415) and the SQL tables on a (Gateway 9515) containing 1.6 TBytes They are currently connected via a gigswitch utilizing a 1G NIC Cards in each of the servers. No other networking is on the LAN other than these two devices. The SQL Database files and logs are connected via UNC File name. The Disk storage is RAID 5. The application design is a series of batch updates, which in its original configuation (a single server) took 2.5 hours. In the NAS configuation it takes approximately 3.5 hours. Is NAS inherently slower when utilizing a GigiSwich than a single server approach? We are just starting to configure this setup, and we are new to this approach. We beleive that the hardware configuation maybe the issue. Any suggestions would be helpful. Thanks. MULINE
Hi ya, Assuming that the NAS is capable of keeping the Gig ethernet connection busy, and you have the NAS on its own network, and not the same as what the two servers use, then I would ahve thought that it would be a similar speed to a DAS single scsi disk… However another major change you’ve made is to go from one server to two servers. Now depending on how many sql calls are made from the application, going from a local sql database to one over the network is going to add significant amounts of latency to each call. Generally speaking a single server with everything on it is great for pure performance, but lousy for capacity. So adding more users will cause the single server solution to get saturated, but single user performance would more than likely be better on a single server… This is the same as COM. An two-tier application (app/db) is substantially faster when used with a single user than a three-tier one, but will not scale to large numbers of users Cheers
Further Clarification: There is only one user that interacts with the Application Server. The Data server is strictly used by the application server and there are no other applications running on either server. The User only interacts with the Application Server once the Batch Processing (updating of records) is complete. Generally, the batch process is started nightly, and the single user logs in the morning following the processing. The Database is quite large, requiring that the data be placed in a large storage aray architecture. The number of records that are added to the database grows at 2.5 million a day.
and how are the batch processes written, is it a single SQL job, a small number of bcp/dts jobs or individual statement inserts/updates/deletes? For optimum performance with batch jobs you really don’t want any interaction between the database and the front-end application. The batch jobs should be done in sql procedures or dts jobs for optimum performance. Cheers
The NAS bandwidth that you have right now is not the same with the databus connected to the CPU and thus, you will always have the additional overhead with NAS. However, to maximize your NAS productivity, there is an optimization overhead setting that you might want to take advantage as well as the MTU capacity (that is, if the server/network is dedicated). Check this out, then the memory and parallel CPU settings. I’d establish a baseline if I were you to see if the actual difference.