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?
-
1What version of SQL server are you using ?– Kin ShahCommented Jul 14, 2014 at 0:53
-
2A great training video is on Pluralsight for just this topic by Erin Stellato. pluralsight.com/training/Authors/Details/Erin-Stellato It is worth the small fee for 30 day access but you can get other topics on SQL Server that will greatly help your learning. I am an avid user of their site.– user507Commented Jul 14, 2014 at 2:35
-
1Hi,Question is wide and there can be many reasons for slowness of the system so I would suggest you to have a look at SQL server whitepaer on how to troubleshoot performance problems in SQL server 2008 and above : technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx use this and first try to filter down cause for problem and then move accordingly towards troubleshooting– ShankyCommented Jul 14, 2014 at 8:27
-
Suggest you to take a look at Performance Data Collector by collecting data throughout your business cycle and then comparing it overtime. I recon with Shawn about Erin's course on Pluralsight .. SQLSkills is one of the best out for anything related to SQL Server (note: I have done IE1 and IE2 from SQLSkills and hence my recommendation.)– Kin ShahCommented Jul 14, 2014 at 14:39
-
@ShawnMelton Please could you confirm which Pluralsight course it was you were recommending? (There are 8 courses listed on the link). Was it "SQL Server: Analyzing Query Performance for Developers"?– Steve ChambersCommented Jan 19, 2018 at 9:56
|
Show 1 more comment
3 Answers
Troubleshooting Performance
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
Data to collect
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:
How to detect SQL Server performance issues using baselines – Part 1 – Introduction
How to detect SQL Server performance issues using baselines – Part 3
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:
http://www.sqlservercentral.com/Authors/Articles/Erin_Stellato/351331/
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).
Erin
-
3
-