Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 Speed up SQL server

Author  Topic 

acik
Starting Member

4 Posts

Posted - 2012-08-03 : 09:58:28
I am a systems analyst and SQL is not "my daily bread".

A client asked me to "speed up" his PC.
Especially a small management software written in VB6, which relies on a SQL 2005 Express database.
Windows XP SP3 32bit.

I increased the RAM from 1 to 4GB.
I replaced the disks with most performing.
I put a Quad Core i5 CPU 3400MHz.
The computer become fast, but the management software remained slow.

Some time ago I readed that more RAM can be assigned to the SQL server. How it works ?
Is possible to load and use the entire database in RAM ?
Can I shrink/optimize the database ?

Thanks

acik
Starting Member

4 Posts

Posted - 2012-08-03 : 10:02:36
More informations:

SQL is installed locally, the computer also acts as a server for other two old PCs:
One old Pentium 4 PC and an very old Pentium3.

Locally, a search takes 20 seconds.
The Pentium 4 takes the same time.
The Pentium3 takes the same time.
That is the reason that made me think of intervening on the SQL server, because that is the only part in common between the three PCs.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-03 : 11:17:45
As mentioned in your earlier post, SQL Express Edition is limited to 1 GB of RAM and 1 CPU (or 1 core if multi-cored). You'd have to upgrade to at least Workgroup Edition to use more resources (and you might as well go to SQL Server 2008 R2). You're also limited with 32 bit Windows XP, you should upgrade to Windows 7 and 64 bit.

You can certainly do index and statistics maintenance, I recommend Ola Hallengren's script: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html I'm not sure how much it will matter though if the database is large enough.

The only way to put the entire database in RAM is to use a RAM disk software, which takes away from the RAM SQL Server can use. Since you're using Express this may not matter, as it will only use 1 GB, but it may not be feasible to do on a 32 bit operating system.
Go to Top of Page
   

- Advertisement -