This article is part of an Essential Guide, our editor-selected collection of our best articles, videos and other content on this topic. Explore more in this guide:
2. - Trends, perspectives and advice on enhancing SQL Server skills: Read more in this section
- Expert highlights Microsoft SQL Server trends
- Ways that application developers unwittingly hurt performance
- SQL Server certification: Is it worth it?
Explore other sections in this guide:
- 1. - Basics and benefits of SQL Server training
- 3. - Essentials for DBAs in 2014
- 4. - SQL Server Glossary
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.