4

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?

  • 1
    What version of SQL server are you using ? – Kin Shah Jul 14 '14 at 0:53
  • 2
    A 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. – Shawn Melton Jul 14 '14 at 2:35
  • 1
    Hi,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 – Shanky Jul 14 '14 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 Shah Jul 14 '14 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 Chambers Jan 19 '18 at 9:56
4

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.

10

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 2 – Collecting metrics and reporting

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

9

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
    Welcome, Erin! Great to have you on board! – Max Vernon Jul 14 '14 at 15:39
  • It's really great to see you on dba.stackexchange.com – cat_minhv0 Apr 5 '18 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.