I have seen this over and over again with third-party applications that use SQL Server as their backend. There seems to be two main reasons, based on my personal experience, that third party applications have performance problems when using SQL Server.
First, the designers of the applications may be good coders in C++, VB, ASP, or whatever language they are developing the front-end part of the application in, but they don’t know much about how to design applications to get the most out of SQL Server.
Second, the designers want to support multiple database programs (not just SQL Server), and because of this, they write their data access code very generically, not taking full advantage of the power of Transact-SQL and SQL Server.
Unfortunately, many vendors don’t care much about writing applications that make the most out of SQL Server’s abilities. If you complain about performance, they will blame SQL Server or your hardware, but never blame their own code, even though it is the guilty party.
As a DBA, you don’t have many options to speed applications that you cannot change. For the most part, here’s all that you can do to speed badly written third-party applications.
- Purchase the fastest hardware your company can afford.
- Tweak the hardware, operating system, and SQL Server the best you can using the tips you find on this website.
- Add or modify current indexes on tables to help out performance as much as possible. Use Profiler and the Index Wizard (or the Database Tuning Advisor) to help you out.
- If you are using SQL Server 2000 or 2005 Enterprise Edition, consider using clustered indexes to speed queries. Although the application won’t be able to use clustered indexes directly, they can be used by the query optimizer to help boost the performance of some queries.
- If you are running SQL Server 2005, you have the option of creating Plan Guides to provide hints to poorly-performing queries created at the application.
I would almost add to this list “complaining to the third-party application vendor,” but I know from experience that most of them could care less.
]]>