SG SQL Server Usergroup

Hello Everyone,

I was reading some of the SQL Server books and they recommend to have multiple number of data files for an database. The number recommended should be equal to the number of CPU's in the box.

So, if I have an database and my host is having 4 physical CPU, then I will have 4 data files of equal size for the database to improve performance. The reason I read is that, SQL Server will maintain separate thread for these files which will enhance performance as work can be done simultaneously.

Now, my question:

Let's say if I have 100+ database in an single sql server instance a host, and if I decide to add more data files to all the database; will this action cause any overhead to Windows/SQL server as there will be 400+ threads to maintain.

Any comments and insights will help.

Cheers,
Ethiraj

Hello Ethiraj,

Yes what you have read is correct.

Regarding instanciating 100+ database in a single instance of SQL server, this will definitely have its effect on performance not only when it comes to the threads but also to cpu, memory and io utilization. What I would recommend is you use system monitor in conjunction with profiler to understand whats going on with your database server. As long as you are not exceeding any of the recommended counter values, then it should be ok.

How about you guys? What are your views on this? I do encourage everyone to just drop your recommendations or queries about this topic.

Thanks!

Hello Pom Figueroa,

Thanks a lot for your suggestion and inputs. Much appreciated.

Cheers,
Ethiraj

You might have read about having the number of database files for the tempdb database.  Technically, the database files for user databases should be based on a lot of factors, not just performance - manageability, operations, etc.  For backup and recovery of VLDB, you might want to create multiple database files and group them according to filegroups so you can do filegroup backups. Plus, storing LDF and MDF files on different spindles (not just logical drives) can improve IO

Bass Player,

Thanks for the inputs ... Also I heard that if you have many database files for user databases, the bandwith of the fibre channel to SAN can be effectively used as there will be multiple threads to the disks.

Any windows guru here who can comment ?

Cheers,

Ethiraj

Hi Ethiraj,

This may not be entirely true. To effectively use your SAN, you have to run on x64 or Itanium. If you are using an x86, then the performance of the SAN may be limited to the capability of the x86 processor.

Pom

I'd check with the hardware vendor on their best practices to improve performance on their hardware.  Vendors like EMC will recommend stuff like PowerPath for certain applications like SQL Server

I hope we have members from EMC who can share their expertise on this :)

Did several SQL 2005 geo-clusters a few years back with the help of EMC and it was awesome :)

Thanks to everyone for their inputs.

Best regards,
Ethiraj