Essential Guide

Microsoft SQL Server training: What you need to know

A comprehensive collection of articles, videos and more, hand-picked by our editors

Eight ways that SQL Server developers hurt performance

Application developers who access SQL Server can affect server performance without knowing it. Here are eight things SQL Server developers often do wrong.

Ah, developers. You can't live with them, you can't get any new applications written without them. SQL Server developers and DBAs have a long, honorable history of disagreement, wherein developers like to take certain approaches to application development and DBAs wish they wouldn't.

For more on SQL Server developers

Top five tools for SQL Server developers

Tips and tricks for SQL Server developers

The SQL Server basics that developers need to know

Application developers are most able to positively or negatively impact SQL Server performance. Tune indexes and storage subsystems all you want, but the impact you have as a DBA is minimal compared to what a well-trained developer can do, who knows how to wring the maximum performance from SQL Server. But not all developers are that well trained. Here are examples of things some developers might do to negatively impact SQL Server performance:

Run everything on the server. With stored procedures that can be written in .NET framework languages like C#, some developers get the idea to run nearly their entire application's logic on SQL Server. Unfortunately, that's not what SQL Server is best at. There's a balance that needs to be struck, where the stuff SQL Server needs to do is run on the server, and where other, especially long-running, processes run elsewhere.

Write ad-hoc queries. SQL Server hates to run ad-hoc queries, because it has to compile a query plan for each one, each time. Instead, developers should code queries into stored procedures, which enable SQL Server to more easily cache execution plans. Stored procedures can also help provide a valuable protection against certain kinds of security attacks.

Design indexes in development. Development environments never reflect the actual on-the-ground conditions of a production environment. While SQL Server developers' made-during-development indexes are a good first guess, you should work with them after an application goes into production to retune the indexes for real-world conditions -- and then repeat that exercise regularly as production demands evolve.

Ask for too much data. Nothing drags a server's performance down like a developer who queries a million rows of data when he only needs one of them. Instead, developers can implement paging in client applications, querying just the data that needs to be displayed right then, and going back for more only when the user actually needs it.

Using the sysadminor "sa" account. Security people hate this, but for developers it's an easy way to give a client application access to SQL Server without needing to take the time and understand how security should work. SQL Server offers numerous better options, and SQL Server developers who hardcode the "sa" account into an application should be flogged.

Cursors. Experienced DBAs know that cursors are sometimes a necessary evil, and also know that developers often think "necessary" comes more often than is, well, necessary. Cursors are memory- and processor-intensive for SQL Server, requiring it to batch up large amounts of data in memory and act on one row at a time. They're best avoided whenever possible.

Not using views. Views are another way that SQL Server can gain a performance boost because, like stored procedures, they can cache their execution plans. Views also help abstract what a client application "sees" from the actual back-end database schema, making it easier to make schema changes over time with less client-end disruption.

Over-normalizing. While SQL Server can tolerate pretty massive table joins (I have clients for whom 9- and 10-table joins are common), there is such a thing as going too far. If commonly run queries are joining 20 tables that's too much, and is likely a sign that the database design has been over-normalized. Normalization is good for reducing redundancy, but it's a bear on performance. Denormalization is the process of backing off on the normalization ethic for the sake of better performance.

What can you do if you discover these faux pas? Not much: They're typically embedded in code that's out of reach for the DBA. But you can call them to SQL Server developers' attention, and ask them to focus on them in future releases.

This was first published in October 2012

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Essential Guide

Microsoft SQL Server training: What you need to know

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close