SQL Server Performace Problems

Question

SQL Server Performace Problems

Matt Allpress-274863

Old Hand

Points: 395
More actions
November 8, 2006 at 12:37 pm

#114178

Hi,
I am running SQL Server 2005 SP1 on Windows Server 2003 SP1 on an Intel Xeon 3GHz CPU, with 2GB of RAM, and a 100GB hard disk.
The database engine, analysis services, and reporting services are all on the same server. I now have a number of databases and cubes on the server, and am starting to run into performance problems. When a spreadsheet tries to query the database engine the same time that an Analysis Services cube is being processed or an SSIS package is updating some database tables, the query will often timeout. I realise that the best solution is probably to place Analysis Services on its own server, although the extra cost means that this is not a possibile solution.
Are there any recommendations for improving the performace of this server - maybe from hardware upgrades or system configurations? My guess is that an extra 1GB of RAM would help. If anyone could share their similar experiences/solutions it would be much appreciated.
Thanks, Matt

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

EdVassie SSC Guru Points: 60472 More actions · Answer 1

You probably need a lot more than 1 GB of RAM. Try going to 8GB and using AWE for your database buffer pools. Also consider moving the RS workload to a different server. However, depending on your workload you may remain memory-constrained in a 32-bit environment.

My view is that SQL2005 is a 64-bit product that tolerates running in 32-bit mode. The only item in 32-bit SQL2005 that can use memory above the 4GB line is the DB buffer pool, but in 64-bit the whole memory range can be used.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

I realize that there are financial and physical constraints that make this difficult, but your first goal in configuring a SQL Server is to add all the memory in the world (and 64-bit software that can use it), and the second is to add all the disks in the world. We all have to live with the frustration of not being able to attain these goals, but you should be able to do better than 2GB RAM and a 100GB disk. Faster processors and more cores are nice also, but in this case with a limited budget I would start with RAM and disk.

I can understand not being able to afford another server, as it involves extra expense for another copy of Windows and SQL Server, but there is no reason for living with 2GB of RAM. Given the price of RAM these days you should get all your server can handle. If you can get over 4GB, the 64-bit version of SQL is a huge improvement. The only reason not to go to 64-bit is if there is some insurmountable hardware or software incompatability.

SQL Server generates different types of disk I/O and will work much better if these are segragated to different physical drives (not just partitions of the same disk or RAID group that are assigned different letters). You want to place the Windows OS + paging file, SQL log files (.LDF), and SQL data files (.MDF) on separate disks. If you have a fourth disk, move the tempdb data file there. The SQL drives should not be used for anything else, keep the file shares and MP3 collections off them. Obviously the faster these disks are, the better.

If your SQL Server is running ANYTHING other than SQL, move it or kill it. The SQL Server box should not be a domain controller, web server, FTP server, file server, print server, or any other kind of server.

Matt Allpress-274863 Old Hand Points: 395 More actions · Answer 3

Thanks for both of your replies. From your recommendations I will push for an additional 2GB of RAM and more hard disk. The reason the server has low specs is that the budget allocation was low seeing since the SQL Server is only being used for reporting applications. Aside from common sense and experience, are there any studies that would show the types of increases that an additional 2GB of RAM would produce?

I forgot to mention that the server is also acting as a temporary IIS server for a couple of ASP.NET intranet applications, so that will be slowing it down as well.

Thanks, Matt