SQL 2005: Increase performance by using additional files?

[kapplah]

Hi there,

yesterday we've (finally) moved successfully our VC database to our new dedicated SQL server for VC.

But we are "slightly" disappointed by the performance of our dual CPU box. A little google-search gave me the hint to add at least one more file to the SQL database to give the database engine the chance to let a second thread access the databases files.

Before we want to do this, I'll ask this forum if anyone has done this before and if it solved the performance lags? Are there any issues I don't know yet?

After the desastrous movement of the database yesterday, I am EXTREMELY careful regarding thoise thing.

regards,

Alex

[VirtualNoitall]

Hello,

Have you done any performance monitoring on the server to see where the bottleneck is? I would have perfmon run for a day or so and then analye the results. Is CPU, Memory, Disk or?? the issue?

[LarsLiljeroth]

I agree with VirtualNoitall , you need to find you whether or not it is a disk issue.

So if you'll find you it is so here is what i suggest.

I havent heard about a solution that involved adding another disk for the database files.

If you need to add anything i think it is different disks for Temp.db ( heavy used in sql2005) and offcource the Log files. ( .trn)

This should give you a perf lift, i hope.

/lars

[Argyle]

Number one thing to do when you move a database is to reindex the database again on the new server so index and statistics are updated or you can get aweful performance (set up a maintenance plan to do this weekly).

Secondly, inrease the size of the VC database to accommodate future growth and change the autogrow setting. A good start for the VC database is 2 GB data file, 500 MB log and set the files to grow with 250 MB. If you let it be the default 10 MB? with autogrow set to 10% it will be very fragmented over time. As an example we manage around 140 VMs and the vpx_hist_stat table that contains performance data is about 6 million rows. The size of this database is 800 MB.

It is also a good idea to set a max limit on the SQL Server RAM. Even if it's a dedicated SQL Server set the max ram to 500 MB lower than whats installed in the server to leave some RAM for OS and other services.

When it comes to having SQL files on seperate disks its good to seperate data and log files on its own disks. It has to be seperate physical disks or array channels to make any difference though (putting files on different partitions on same disk do nothing). On the other hand its not a major performance factor unless you have lots of activity in the database. Again as an example, we run our VC database above on a virtual machine with one single data disk.

[kapplah]

Hi,

my problem is not the performance of the SQL server ITSELF my problem is that SQL server with this single database seems to run only on one SMP node.

Some people suggest to add a second database file to let SQL server run the second CPU.

The question hereby is: Has anybody done that und are there any issues regarding VC?

And yes: We have NO HDD, Memory and other bottlenecks - the only limiting factor for now is CPU usage. The second CPU node is idling ...

We have a maintainance plan making all the stuff needed.

cu,

Alex

[VirtualNoitall]

Hello,

Is your single CPU being maxed out? What is your avg processor queue length?

VC won't care how many files or in what config those files are in with regards to the database. It just connects to the database. Database files are typically split up to improve disk performance. Any benefit to CPU would be minimal.

Is your server setup with a multiprocessor HAL?

[Argyle]

Ok, wasn't clear to me that you had a CPU issue. As long as you have all CPUs marked as available to SQL Server under SQL properties and has enabled the parallellism option to make use of all processors it should be fine. But only queries that SQL find can benefit from multiple CPUs will be run on multiple CPUs.

[LarsLiljeroth]

So can the SQL see that you have the 2 Cpu's and has the check mark " Automaticly set processor affinity mask for all processors" this option should be default thou . Is the SQL running other databases ?

Our VC runs on a server with 2 x dual core and the SQL is using almost equal CPU ressources on them.

[LarsLiljeroth]

What SQL 2005 version is it that you use ?

The Express version only supports 1 CPU, but then again can you install VC on that ?

[spex]

We use our central SQL server (MSSQL 2005) also for vc_db.

Performance is not as good as i would like. I think the vc queries (especialy the perfomance stats) are not optimal.

What helped was to reorg the index for the 2 stats tables every day.

Regards Spex