2

I'm working on a website project which has also a back-end application. Both projects rely on one instance of sql server 2008 . If both application are running sql-server becomes a sever bottleneck. Unfortunately it is not possible for us to scale-up (it is not possible to add more horse power on computer that is running sql-server) and now we a looking for other ways to improve performance.

We are using windows 2008 with sql-server 2008 enterprise edition on multiple virtual box.

5 Answers 5

2

If the current server can't scale up, then you may need to move to a bigger server. It sounds like you are running SQL server under a VM, which can be done, if done correctly. If it isn't done correctly then your life will be hell.

Before you look at the major project of scaling out the database, you need to check your database performance and make sure that everything is properly indexed, and that all your queries are running the best that they can.

Assuming that they are, look to getting a newer server. If you currently have 2 vCPUs on the machine, then you need 2 SQL CPU licenses. Look at a server with 2 quad or 6 core CPUs in it. This way your licensing doesn't change, and you only have to pay for the new hardware.

How have you determined that your SQL Server is a bottleneck and that you need to scale out your deployment?

2

This whitepaper is a good starting point:

SQL Server 2008 Performance and Scale

Included in This Document:

  • Introduction
  • Optimizing Performance with SQL Server 2008
  • Scaling Up with SQL Server 2008
  • Scaling Out with SQL Server 2008
  • Conclusion

http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-performance-scale.aspx

1

Scaling out typically involve deep refactoring of the original application. Usually, it's a subtle operation that comes with quite a few perf measurements.

If you have one app that is only "read-only" on the DB, then you can setup an SQL Server mirror that would serve "read-only" queries to lower the pressure of the primary server.

Then, you can also look for missing indexes. A single missing index can make your performance miserable.

Finally, if you have non-critical heavy queries you can add some NO LOCK statements to massively speed-up query time (but this method should be used with caution as the returned results could be partially inconsistent).

1
  • 1
    "deep refactoring". I love it...! :)
    – Luke
    Sep 7, 2009 at 22:22
1

As others have suggested, then the cheapest and easiest way to make SQL Server perform better is to performance tune it. IMHO the additional SQL Server licenses involved in scaling out disqualifies it as an option unless your database is a VLDB and running on the latest & greatest hardware.

Assuming you've optimized SQL Server as much as you can, then you have the following options:

Here is a excellent article from Microsoft which discusses the pro's and con's of using each of these options.

0

Optimize your SQL queries.

Here's a Microsoft article that will step you through it -

http://msdn.microsoft.com/en-us/library/ms979196.aspx

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .