Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What version of SQL server are you using ? –  Kin Jul 14 at 0:53
1  
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 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 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 Jul 14 at 14:39

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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

share|improve this answer
2  
Welcome, Erin! Great to have you on board! –  Max Vernon Jul 14 at 15:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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