Performance - check whether it is affecting application or system, don't blame SQL Server alone

Its a general assumption that whenever performance is degraded, the finger will be pointed to SQL Server, pretty easy eh!? If you look at any of the SQL Server related forums then 3 in 10 questions asks same question and nothing but shove blame on SQL Server.

It is always better to be proactive than reactive, when it comes to identifying and eliminating SQL Server performance issues. So next question will be how do I monitor the  SQL Server performance?

Troubleshooting performance issues a series of analysis to isolate the issue and moreover determine the root cause of that corresponding issue, possible causes are Blocking (poorly written queries), System resources (not an effective planning on capacity), Application design & code(a big issue where most of the developers think problem is with database only, not their code).  When you talk about Application performance it means all about waits and queues you need to monitor on SQL Server side using DBCC SQLPERF(WAITSTATS). Bear in mind analyzing such performance exercise with workloads may vary, shifting waits & queues. So you talk about system resources means it is all about identifying (& resolving) bottlenecks, maximizing system capabilities.

Ideally, it is best to capture the SQL Profiler, Performance Monitor, and blocking output during the same timeframe. This timeframe must encompass a time when application performance goes from good to bad. The combination of this information will help you to get a clearer picture of where the performance slowdown is occurring. Using both of these combinations will help to narrow down the issues, general issues such as high CPU problems may indicate large numbers of stored procedure recompilations, ad-hoc query compilations, or intensive use of hash and merge joins. Using above DBCC statements you would get information such as high disk queue lengths that purely indicate the need for more system memory or an improved disk subsystem. 

The minimum design considerations resulting from the Waits and Queues where high IO waits refers to database design, memory pressure means bad query plans with improper usage of indexing and high CPU usage  means heavy usage of recompilation and missing correct plan re-use, parameterization, re-compilation. Finally if the blocking is the issues that means the concurrency is lacking with the transaction management from the application you need to have correct isolation level with smaller batches of execution.

When it comes to the hardware ensure to set controller or IO Subsystem cache for log as 100% write through with a goal to take 1 or 2 milliseconds to write (based on Avg Disk sec/write). If you are using the SAN based storage then ensure test the throughput prior to depolying the live database, using IOMETER(shareware) is best to simulate the load or event simply copy large files between drives while monitoring the usage with SYSMON, in this case if you get ~ 120mb/sec throughput per channel that means your SAN is healthy. For OLTP systems such as heavy updated system then on the disk controller make cache more write through vs read and for better data readability ensure to spread data files across as many drives as possible.  Finally if your application is receiving timeout errors, stops responding (hangs), or experiences other events that cause the problem statements to never complete better to check the blocking SPIDs execution and use relevant locking hints.