SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Interpreting a Trace
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 12/03/2012 :  16:05:43  Show Profile  Reply with Quote
Hello everyone,

In learning how to interpret a trace from profiling I came accross this code from msdn. Is this good to help figure out why an applicatiion is running slugishly occaisonaly? If not, can someone help modify the code to help me figure out why we're having slow response times on the server?

SELECT TextData, Duration, CPU
FROM trace_table_name
WHERE EventClass = 12 -- SQL:BatchCompleted events
AND CPU < (Duration * 1000)

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 12/03/2012 :  16:38:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
I look for high reads, high CPU and high duration. But that's if I'm not really sure what's going on yet.

Have you looked at PerfMon? What about wait stats? Out-of-date stats?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 12/03/2012 :  17:42:56  Show Profile  Reply with Quote
Thanks.

Would a select statement that selects 32 columns from the below view be costly?

SELECT TOP (100) PERCENT dbo.tblFinHdr.ClaimKey, dbo.tblFinHdr.ClaimType, dbo.tblFinHdr.ClaimMode, dbo.tblFinHdr.ClaimStatus, dbo.tblFinHdr.ClaimPending,
dbo.tblFinHdr.PatientId, dbo.tblPatDemo.LastName, dbo.tblPatDemo.FirstName, dbo.tblPatDemo.MiddleInitial, dbo.tblFinHdr.ClientNo, dbo.tblMastClient.ClientName,
dbo.tblFinHdr.ClaimTaxId, dbo.tblFinHdr.ProvNo, dbo.tblMastProv.ProvType, dbo.tblMastProv.FacilityName, dbo.tblMastProv.ProvLastName,
dbo.tblMastProv.ProvFirstName, dbo.tblMastProv.ProvMiddleInitial, dbo.tblFinHdr.ClaimKeyImage, dbo.tblFinHdr.ClaimEdited, dbo.tblFinHdr.BatchNo,
dbo.tblFinHdr.ClaimChargeAmt, dbo.tblFinHdr.UsrLogin, dbo.tblFinHdr.ReceivedDate + dbo.tblFinHdr.ReceivedTime AS DateTimeEntered,
dbo.tblFinHdr.ClaimPendingReason, dbo.tblFinHdr.ClaimRepriced, dbo.tblFinHdr.RepricedMethod, dbo.tblBatches.BatchNo AS Expr1, dbo.tblBatches.ArDate,
dbo.tblBatches.NoClaimsReceived, dbo.tblBatches.NoClaimsEntered, dbo.tblBatches.NoClaimsReceived - dbo.tblBatches.NoClaimsEntered AS PendingToEnter,
dbo.tblRepriceMethod.MethodDescription
FROM dbo.tblFinHdr INNER JOIN
dbo.tblMastClient ON dbo.tblFinHdr.ClientNo = dbo.tblMastClient.ClientNo INNER JOIN
dbo.tblMastProv ON dbo.tblFinHdr.ProvNo = dbo.tblMastProv.ProvNo INNER JOIN
dbo.tblPatDemo ON dbo.tblFinHdr.PatientId = dbo.tblPatDemo.PatientId INNER JOIN
dbo.tblBatches ON dbo.tblFinHdr.BatchNo = dbo.tblBatches.BatchNo LEFT OUTER JOIN
dbo.tblProviderContract_Office ON dbo.tblMastProv.ProvNo = dbo.tblProviderContract_Office.ProvNo AND
dbo.tblFinHdr.OfficeNo = dbo.tblProviderContract_Office.ProvOfficeNo FULL OUTER JOIN
dbo.tblRepriceMethod ON dbo.tblProviderContract_Office.RepricedMethod = dbo.tblRepriceMethod.RepricedMethod
WHERE (dbo.tblFinHdr.ClaimMode = 2) AND (dbo.tblFinHdr.ClaimStatus = 1) AND (dbo.tblFinHdr.ClaimPending = 0) AND (dbo.tblFinHdr.CancelledRepricing = 0) AND
(dbo.tblFinHdr.ClaimRepriced = 0) AND (dbo.tblFinHdr.ClaimPendingReason IS NULL) AND (dbo.tblFinHdr.ClaimKey NOT IN
(SELECT DISTINCT ClaimKey
FROM dbo.tblFinDet
WHERE (RecType = 2)))
ORDER BY dbo.tblFinHdr.BatchNo

This was flagged as an event 36 times in half an hour and each time the duration was around 285000 and the reads was about 34038 and cpu around 281.
In learning how to read this, is 285000 read as 285 seconds(4.75 minutes)?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 12/03/2012 :  18:09:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
285000 is in microseconds, so that's a decent duration (just 285 milliseconds). 34000 reads probably means you need to add an index.

Returning 32 columns isn't a concern.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 12/03/2012 :  20:32:31  Show Profile  Reply with Quote
Hi Tara,

No I haven't tried PerfMon, wait stats, Out-of-date stats yet. I'll start getting familiar with all this asap. Thanks for all the help:)
Go to Top of Page

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 12/04/2012 :  08:45:24  Show Profile  Reply with Quote
Tara do you have a general guidline rule on the number of reads when you decide it's time to add an index if one doesn't exist?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 12/04/2012 :  14:30:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
I wouldn't say that number of reads is what determines it, but rather reads+duration and checking the plan before/after is what determines it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000