The performance issue of the SQL Server can be anything from the connectivity issue of the application to Query running slow or query timeouts. So the first thing that we need to find out during the performance troubleshooting is to find what is the exact issue that we will troubleshoot. Finding the exact issue is the first step to find the solution of the issue.
Here are a few documents which would help you find out the bottleneck and start with the performance troubleshooting. Also these documents are helpful while designing the database architecture for your organization.This white paper on Troubleshooting Performance Problems in SQL server 2005 takes you through the steps to resolve the typical performance issues with the SQL Server.This document on Physical Database Storage Design, details the basic considerations with regards to the storage.This document on SQL Server 2005 Waits and Queues describes in detail the sql server performance from the perspective of Waits and Queues. This one describes the options that can be used to investigate performance issues and improve the SQL Server performance.This white paper on Comparing Tables Organized with Clustered Indexes versus Heaps, summarizes the advantages and disadvantages, the difference in performance characteristics, and other behaviors of tables that are ordered as lists (clustered indexes) or heaps.
SE, Microsoft SQL Server