i am an IT Consultant and take up projects for my clients for setting up complete IT infrastructure. I have a client who has been facing problem with their DATABASE, he is a retail distributor for foods and has a database size of 10GB, the server is an IBM x3100 M4 Xeon Quad Core Processor 8 MB Cache, with 16 GB Ram and 300 GB SAS, OS is Windows Server 2008 Enterprise, with sql server 2005 standard. The client complaints that the whole process of billing and other activities on the DATABASE is too slow. When i check the resource monitor on server 2008 i can see that sqlserver process is hitting 70 - 80% every few seconds, memory usage is at 50 to 60% and disk usage keeps varing between 1 to 10mbps.

Any ideas, where the problem might be.


If the database is too slow to reply to queries, either the database server is underprovisioned (perhaps you should consider setting up a cluster), or the queries being sent to it are badly designed and execute slowly. Obviously these are additive problems.

Given the numbers you set out, it might not be a bad idea to add additional database servers or significantly upgrade the one you have. Although I have no idea what your disk bandwidth is like, it's quite possible that it's slow due to disk access though; there are things you can do to upgrade the speed of that, such as using a fibre channel connected SAN, or better disks or a different RAID level, but when disks are the problem it's usually not a bad idea to add additional database servers (backed by different disks).

If you can, consider looking at the queries that are being done against the database and optimizing them so they create less temporary tables, or smaller temporary tables. For instance, selections and projections (SELECT statements) should pretty much always happen before joins and sorts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.