Doug here.  The article below offers some highly relevant advice, with a few caveats.

Performance tuning is a necessity these days but is difficult to achieve for most SQL Server developers.  The reasons vary, but typically involve:

  • Lack of permissions to utilize tuning tools (often reserved for DBAs only)
  •  Lack of knowledge on performance tuning methodologies (as opposed to techniques)
  •  Lack of availability of DBA or someone with permission and knowledge required for success
  •  Inefficient code being generated by their development environment
  •  Lack of understanding of computing environment, application internals, normal v. non-normal workloads
  •  Lack of experience overcoming the above mentioned roadblocks

Performance tuning is often a typical catch-22 situation.  The developer(s) understand the application (hopefully) and the DBA understands performance tuning. Therefore, it is by necessity a collaborative effort.  But each party has their own priorities and often the DBA’s manager is not willing to devote their staff member to solve ‘someone else’s problem’.  Also, relevant parties are often less than motivated to share what they know due to concerns about exposing ‘soft spots’ within their environment or operations.

That’s where an experienced SQL Server professional can assist.  They usually have both the developer and DBA experience required to understand ‘both sides’ of the issue.  They also should have skills in crossing functional boundaries in an organization and can bring the relevant issues together for analysis and resolution.

At Simplex-IT we are often asked by clients to ‘take a look at’ a performance issue that is hampering their production environment.  In nearly every case it involves overcoming the typical roadblocks shown above as well as utilizing related skills in networking, server configuration, people management, etc.

However, developers can perform ‘Level 1′ performance tuning by focusing on the basics of data retrieval:

  • Retrieve only what is needed at that moment
  •  Ensure indexes exist and are used by the application

Both of those items are typically well within the knowledge domain of the developer.

After those items are addressed, engage a competent consultant to overcome the skills that may be lacking in the situation.  And learn from them.  Many performance issues are caused by the same, predictable recipe which can be overcome with some solid documentation, sound design patterns and common sense.

This article excerpt, by Andrew Pruski, originally appeared here:
Performance tuning often gets called an art as people feel that a certain knack or innate talent comes into play. And while I don’t disagree that a certain level of knowledge is involved, I completely disagree that only certain people can performance tune.
Given the correct approach, anyone should be able to learn to effectively performance tune.
But how should performance tuning be approached? I’d like to take a step back from delving into specifics and define the process of performance tuning, a set of guidelines that can be used in any circumstance.
Performance tuning should follow the scientific method, which is defined as: “A set of principles and procedures for the systematic pursuit of knowledge involving the recognition and formulation of a problem, the collection of data through observation and experimentation and the formulation and testing of hypotheses.”
In practice, this can be broken down into the following steps:
•Declaration of the end goal or issue
•Forming a hypothesis
•Testing the hypothesis
•Analysis of results
•Further research
This way of approaching performance tuning comes into its own, particularly when investigation is required to respond to issues in a live environment. The trick is to follow the method and not to go with “gut” feelings or guesses. Stick to what you know.
For example, your production server has started responding slowly. Queries are taking longer than expected to complete. This could be due to memory pressure, but you are not sure. It would be better to start off by declaring “Performance of the server is poor,” which you know to be 100 percent correct, rather than declaring “Performance of the server is poor due to memory pressure.”
So the steps to follow would be:
•Observation: Queries on production running for longer than expected
•Declaration: Performance of the server is poor
•Hypothesis: The poor performance of the server is due to memory pressure
•Testing: Running an extended events session on the server to catch incoming queries
•Analysis: Several queries performing scans of large tables
•Conclusion: Creation of a covering non-clustered index to prevent the table scans
•Further Research: Are there any other queries being executed that are performing table scans?
These steps may seem obvious, but using the scientific method will prevent you from being led down blind alleys and potentially missing the actual problem. It provides a structure for the investigation.