Eight ways that SQL Server developers hurt performance
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in October 2012
approaches to
application development and DBAs wish they wouldn't.
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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation