The biggest cause of poor performance in systems accessing SQL Server is poor application design. The biggest design error, the one I encounter more than any other, is to make too many round trips to the server.

I have just looked at a poorly performing application for a client. The application enabled a customer to be searched for by surname, and the list of matching customers were displayed to the user.

This should have been just one call to a stored procedure or SELECT statement, returning all columns in one hit. What the application actually did was a simple search on surname followed by one additional query for each matching customer.

What should have been one query taking a fraction of a second was actually 95 separate queries taking over a second in total.

In addition to the negative impact for the user, these extra round trips also increase load on CPUs and network.

So remember, if you are currently designing or coding an application with a SQL Server database, keep individual queries to a minimum, or performance and scalability will suffer.