Performance Zone is brought to you in partnership with:

I’ve been a Windows developer since 3.0 and caught the Visual Basic wave early with v1. I’ve released a “production” application in every version of VB since then (except VB for DOS). Focusing on enterprise, line-of-business development I’ve built Call Center Applications, Mortgage finance systems, Customer Relationship Management tools and more recently I’ve been in the Litigation Support/Electronic Data Discovery/Electronically Stored Information space. Greg is a DZone MVB and is not an employee of DZone and has posted 476 posts at DZone. You can read more from them at their website. View Full User Profile

"The database is slow!" Here's a SQL Server Performance Crib Sheet [well almost book] that might help you...

  • submit to reddit
  • Introduction
  • Overview
  • Measuring Performance
    • Perfmon
      • Perfmon Counter Set
      • Using perfmon
      • Evaluating perfmon data
    • Server health
      • Memory
      • Memory Grants Pending
      • Lock Requests/Sec
      • Deadlock/Sec
    • Extended Events
    • Dynamic Management Objects
    • Third Party Tools
  • Tuning Performance
    • Server Performance
    • Database Performance
      • Indexing
      • Files and FileGroups
      • Normalization
      • Data Types
      • Other Issues
    • T-SQL Performance
      • Client Access
    • Testing Performance
  • Suggested Reading


How much performance is enough? Where do you start tuning? When do you stop tuning? Each application being developed will answer these questions in a different way. The important thing is not to establish a single mechanism for answering them. Your goal is to establish best practices and guidelines that will lead to the answers in the right way for the application under consideration.

First, and most important, the SQL Server system itself needs to be configured correctly. It also needs to be running on a correctly configured Windows server. This is the foundation on which the databases will be built. These same requirements do not exist when dealing with an Azure SQL Database. After the server is configured, you need to design and build the database to perform optimally. That's assuming you're building a new database. If you're trying to tune an inherited database, then you'll want to know what a good database looks like. Appropriately designing the database is even more important in Azure. Once the server and database are out of the way, you need to be concerned with the code running against it. This means the views, triggers, functions and, on local copies of SQL Server, the CLR code. It doesn't stop there because you need to be sure that the development staff is accessing the database correctly either in their general use of the database and it's code, or in their own use of ADO or whatever other client they might be using to access the system

In order to address all these concerns you need to understand how to measure and test performance. Once you've measured the performance and found it wanting, you'll need to know what to do about it. After addressing structural changes to the database or changes to the server or refactoring the T-SQL code, you'll need to have a mechanism in place to test the results in order to be sure your work is accurate

After all this, you should have a correctly functioning system that performs and scales well.



Should be enough to get you stared at least in looking into your DB performance "issues".

Published at DZone with permission of Greg Duncan, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)