| Author |
 Topic  |
|
|
viperbyte
Posting Yak Master
USA
103 Posts |
 Posted - 12/03/2012 : 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, CPU
FROM trace_table_name
WHERE EventClass = 12 -- SQL:BatchCompleted events
AND CPU < (Duration * 1000)
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
viperbyte
Posting Yak Master
USA
103 Posts |
Posted - 12/03/2012 : 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.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)?
|
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
viperbyte
Posting Yak Master
USA
103 Posts |
Posted - 12/03/2012 : 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
USA
103 Posts |
Posted - 12/04/2012 : 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
USA
35007 Posts |
|
| |
Topic |
|
|
|
Interpreting a Trace
