Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
viperbyte
Posting Yak Master
132 Posts |
 Posted - 2012-12-03 : 16:05:43
|
| 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, CPUFROM trace_table_nameWHERE EventClass = 12 -- SQL:BatchCompleted eventsAND CPU < (Duration * 1000) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
viperbyte
Posting Yak Master
132 Posts |
Posted - 2012-12-03 : 17:42:56
|
| 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.MethodDescriptionFROM 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.RepricedMethodWHERE (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.BatchNoThis 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)? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
viperbyte
Posting Yak Master
132 Posts |
Posted - 2012-12-03 : 20:32:31
|
| 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:) |
|
|
|
viperbyte
Posting Yak Master
132 Posts |
Posted - 2012-12-04 : 08:45:24
|
| 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? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Interpreting a Trace