I've been struggling a little bit around Performance Troubleshooting including baseline and troubleshooting of SQL Performance.
Could anyone help with this or point me to where I can possibly get a helpful information around this topic?
It is all about the queries. You need only three bits of information about your queries: CPU, Duration & Reads.
SELECT TOP 50 qs.creation_time , qs.execution_count , qs.total_worker_time as cpu , qs.total_elapsed_time as duration , qs.total_logical_reads as reads , t.[text] FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t ORDER BY qs.total_worker_time DESC
CPU should roughly equal Duration, or C = D
CPU * 100 should roughly equal Reads, or *C*100 = R*
If C < D, then we have a Waiter
If C = D, but C*100 > R, then we have Computation
If C = D and C*100 = R, then we have a Runner
Waiter means we are waiting on something: I/O, blocking, latches, CPU.
Computation means we are doing something other than Reads: CPU bottleneck, spinlock, query compilation, UDF/function - computation, calculation, SQLCLR/XP, SQL Server code
Runner means nothing without a baseline. You would have to know that the query normally takes less time to run: Outdated stats, missing indexes, poorly designed query, suboptimal plan, parameter sniffing, optimizer timeout
Waiter: wait stats, query_plan, perfmon, profiler, blocker script/per_stats script, DMVs, Xevent
Computation: spinlock stats, query plan, profiler, set statistics time, statistics IO, query plan XML, Trace flags, Xperf, Kernrate, F1 Visual Studio, Query text, perfmon
Runner: query plan, schema, query text, statistics info, missing indexes info, index fragmentation
There are many DMVs, 3rd party software, and scripts that will help you gather this data.
Here are some good articles with some practical examples that you can find here:
While Part 1 will provide you some basic knowledge about what the baseline is, in Part 2 you can find info how to do that on your own using "poor man" method
Part 3 provides some examples on how you can establish baselines and how to use baselines in troubleshooting some issues via ApexSQL Monitor
I wrote a series on SQLServerCentral about baselines that might be of interest to you:
And as Shawn so kindly mentioned, I also have a Pluralsight course. If you have more questions, feel free to contact me (erin at sqlskills dot com).