SQL Profiler: Interprete CPU, Reads, Duration

Jun 2, 2008

I ran Sql profiler and got the following results for a stored procedure

CPU of 1078;
Reads of 125464
writes of 0
Duration of 1882

how do i interpret the above results.
also what CPU and Duration is considered high and indicating a poor performing query.

I am using SQL Server 2005. Thanks

View 4 Replies


ADVERTISEMENT

Number Of Reads In Profiler

Jul 27, 2007

Hi,

Can any of can explain, what the "Reads" column in Profiler exactly mean ? I'm not comfortable with the explanation given in BOL.


"The number of read operations on the logical disk that are performed by the server on behalf of the event. These read operations include all reads from tables and buffers during the statement's execution"

For the same procedure with same parameters, if the server is not loaded much, the Reads are in a few hundreds, but when there are more than 1000 concurrent users, why it is going to millions ? What other parameters affecting this reads ? And how can I reduce it ?

Environment: SQL Server 2005 64-bit Enterprise Edition on Windows Server 2003 R2 Server x64 Enterprise Edition SP2


Thanks in Advance.

Regards

Babu

View 4 Replies View Related

Duration In Sql Profiler

Jan 20, 2005

If monitoring for duration with sql profiler, what does the number represent ie 2733906 is it milliseconds, thousandths, looked in BOL no clear definition

View 2 Replies View Related

SQL Profiler - Duration

Aug 1, 2002

Can someone please tell me what unit of measurement the Duration column is in when running SQL Profiler? I assume milleseconds, but am not sure.

I'm trying to filter on this and want to be sure I'm not missing anything.

Thanks!

View 1 Replies View Related

Profiler Not Reporting Reads Accurately

Jul 23, 2005

I am running a profiler trace against a database and noticed that thereads column always shows 0. When running the same trace againstanother machine I get back values in the reads column. I took a querythat profiler reported as having 0 reads and ran in in query analyzerwtih STATISTICS IO on and confirmed that there are in fact reads:Table 'tt_cawardalloc'. Scan count 1, logical reads 8, physical reads0, read-ahead reads 1.Table 'tt_clineitem'. Scan count 10, logical reads 125208, physicalreads 1540, read-ahead reads 2995.Table 'tt_contractitem'. Scan count 32, logical reads 676, physicalreads 0, read-ahead reads 0.Table 'tt_contract2'. Scan count 3, logical reads 121, physical reads4, read-ahead reads 0.I am on SQL 2000 sp3a. Any help appreciated.Thanks!

View 8 Replies View Related

Number Of Reads In Profiler Is Not The Same When Running The Same SP On Different PCs

Apr 17, 2007

I'm running the same query on two different PCs and tracing results in Profiler on my PC. When executing the query on PC1 - the total number of reads is 200000. When executing the same query on PC2 - the toal number of reads is 13000. It is almost 15 times more reads when executing query on PC2. The executed query is same on PC1 and PC2. Any reason for this?



I'm trying to analyse that query and reduce the number of logical reads as it's is too high but then I get completly different result on different PC.



Thanks.

View 5 Replies View Related

SQL Profiler Duration And CPU Baseline

Jan 9, 2007

Sarfaraz writes "I have captured SQL Profiler data. I was reviewing top running CPU intensive SQL statements. The Duration (in seconds) 1.39, 1.09, 0.16 and CPU (in seconds) 0.97, 0.95, 0.16 respectively for some SQL statements. How do I know what is the normal baseline for duration and CPU in order to determine the CPU intensive SQL statements.

Secondly same question for long running procedure duration 0.14, 0.11. What is the normal baseline here. Is this normal or too long.

Thanks,
Sarfaraz"

View 5 Replies View Related

Profiler Duration Column Value.

Jul 12, 2007

Hi Experts,

We have a VLDB ( few table with above 200 million records ). This database is used for performance testing by simulating for 150 users and executing all necessary functional flows.

When I examined the profiler results , I could see some very high values as shown below in the duration column for many events.

1521729
3462142
1624325
3211255
1248276
3903998

Does it mean that that SP or the T-Sql statement is taking this much time in milliseconds to execute and give the output ?

Any help would be greatly appreciated.

Thanks & Regards,

DBLearner

View 1 Replies View Related

Duration In Profiler Traces

Jul 20, 2007

Hi



I have a procedure in a history database that does insert into 3 tables inside a transaction. users complaint that the proc sometimes takes too long during heavy usage. I did some traces to see what is taking up the time, I found that the rpc duration was averaging > 500 ms (should only take 50ms). I checked to see if one of that statements were taking too much time, but only see the commit transaction statement taking around 500 ms). I check the avg disk queue to be around 30. ( this is on a single local disk) .



So is this definitely a disk issue, or is there something else I need to check



thanks



P



View 1 Replies View Related

Total Number Of Reads = 48000000 In SQL Profiler!?

Apr 17, 2007

I'm using an application that produce 48000000 reads for one stored procedure and 170 seconds to complete. The same procedure when executed in SQL Analyzer takes only one seconds and 10000 reads.



What is happening here? Where should I look to solve this problem?



Thanks

View 1 Replies View Related

High Duration SQL 2005 Profiler

Jun 19, 2007



Hello everybody,

I have a very stranger problem that I need to understand...
In last days I executed a plan SQL 2005 Profiler to review TSQL Duration. When reviewing the results encounter that a SP displays a value of 4037312 in field DURATION which are not normal.
Could to help me to identify why passes this?

View 5 Replies View Related

SQL 2012 :: Is Profiler Reads Column Incorrect For Parallel Plans

Aug 19, 2015

Is the SQL Server Profiler Reads Column Incorrect For Parallel Plans?

I often use profiler as one tool to identify bad plans. The reads column gives me a good indication of excessive IO to dig into and correct if necessary. I often use it with Showplan so I can see what a query does, replicate it and fix it.

However I have just lost some faith in it. I am looking at a poorly performing query joining five tables. A parallel plan has been generated and one table is being scanned (in parallel) due to a missing index. This table had in excess of 4 million rows in it. The rest hitd indexes well. However the entire query generates ONLY 12 READS.

Once corrected, a single processor plan is used. This looks really efficient and uses 120 reads. That looks the right figure to me.

Does the profiler only display one thread of a parallel plan perhaps? Or something else?

View 9 Replies View Related

Setting Duration Druing SQL 2k Profiler Trace

Dec 9, 2004

I've set the Duration of my trace to "Greater than or Equal to: 1000". However when I start my trace the Duration column is now empty. Prior to the setting, there were values showing in this column. Any ideas on how to fix this?

View 1 Replies View Related

Profiler, Duration And Start/Stop Times

Aug 14, 2007

Profiler was run against a database looking for "long running" queries. I used the Duration column to filter out the queries that I didn't want. When reviewing the output, I noticed that for some queries the StartTime was equal to the EndTime even though the Duration was set higher.
My question is, "What can account for this discrepancy and what inferences should I draw about the difference?"
Does the difference represent a resouce being locked or some other type of blocking (Duration) and once the query was allowed to run, it completed quickly?
TIA

=======================================
If Tyranny and Oppression come to this land, it will be in the guise of fighting a foreign enemy. -James Madison, fourth US president (1751-1836)

View 2 Replies View Related

SQL Server 2014 :: Profiler Reads Column Incorrect For Parallel Plans?

Aug 19, 2015

I often use profiler as one tool to identify bad plans. The reads column gives me a good indication of excessive IO to dig into and correct if necessary. I often use it with Showplan so I can see what a query does, replicate it and fix it.

However I have just lost some faith in it. I am looking at a poorly performing query joining five tables. A parallel plan has been generated and one table is being scanned (in parallel) due to a missing index. This table had in excess of 4 million rows in it. The rest hitd indexes well. However the entire query generates ONLY 12 READS.

Once corrected, a single processor plan is used. This looks really efficient and uses 120 reads. That looks the right figure to me.

Clearly 12 reads is wrong. Does the profiler only display one thread of a parallel plan perhaps? Or something else?

View 1 Replies View Related

Transact SQL :: Get Total Request Duration From Multiple Task Duration?

Jun 4, 2015

I have the following SQL query

SELECT
[Req_ID]
,[Service_Name]
,[Req_Started_Date]
,[Task_Name]
,[Task_Status]
,[Performer_Full_Name]

[code]....

Which works fine, but what I need to calculate the total duration of a request based on the duration of the tasks completed in the request based on Req_ID. I would like to use the CASE statement I have to determine the SLA_Mins for each task and add them together to get total request SLA_Mins.

Below is the create table schema and data

GO
/****** Object: Table [dbo].[MidrangeOtherSourceControl] Script Date: 06/03/2015 18:13:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MidrangeOtherSourceControl](
[Req_ID] [float] NULL,
[Service_Name] [nvarchar](255) NULL,

[code]....

View 9 Replies View Related

How To Interprete Execution Plans For Queries

Dec 16, 2004

Pls tell me where i will be able to find a good material on interpreting the Execution plans................how do i compare 2 diff plans for Quries written in 2 diff ways...giving same output

View 2 Replies View Related

Many Reads

Mar 25, 2008

Hi!
I was assigned to solve performance problems for an application. I fired up Sql Server profiler and started a trace. Downloaded Sql Server Trace Analyzer. It's a trial version so it's very limited. What I found is that one stored procedure generates almost 400 000 reads everytime it's used and it's used everytime the user wants to see his orders. I've tried to translate the t-sql to english from swedish, it looks something like this:


select top 100
o.orderid,
o.name,
o.latestdeldate,
os.name as OrderStatus,
os.orderstatusID,
p.placeID,
p.name as place,
p.address,
p.city,
a.name as worktype,
noOfActions=(select count(*) from actions a where a.order_orderid=o.orderid),
noOfServiceObjects = (select count(*) from Serviceobject s, Actions a where s.Place_PlaceID = o.Place_PlaceID and a.order_orderid = o.orderid and a.Serviceobject_serviceobjectid = s.serviceobjectid),
...
...
...


It has 8 select count(*) in the select statement then in the where statement it has 2 more select count(*).

I know it's very difficult for you to come up with a solution but do you know a better way than to use select count(*) everywhere? The count is used for to show different status flags on the website.

/Magnus

Jesus saves. But Gretzky slaps in the rebound.

View 19 Replies View Related

Dirty Reads

Aug 1, 2001

If I'm doing a dirty reads and a someone updates a record when I'm trying to read it is it possible to read both the old and new records thereby retrieving two records?

View 2 Replies View Related

Reads / Writes Per Second.

Oct 30, 2006

How can You find the reads and writes per second of your hard drives in sql. I am reading my SQL book and it says that your average disk should have 125 or less i/o's. And it gave the forumal but as mentioned I don't know how to find the reads and writes.

View 4 Replies View Related

Reads, Clustering, Etc

May 1, 2008

server: QAT on clustering server ----> 23 seconds
----------------------------------------------------
SS 2000 developer edition SP4
win NT 5.2 (3790) SP4
MeM 7935 MB
processors 4
root directory C:program files...
use a fixed memeroy size 640 MB

reserve physical memory for sql server
minimum query memory 1024 kb

use all available processors
minimum query plan threshold for considering 5

PROFILER READS = 5234




server: MILLER ----> 3 seconds
----------------------------------------------------
SS 2000 developer edition no service pack
win NT 5.2 (3790) SP4
MeM 2047 MB
processors 4
root directory f:MSSQL$INAQAT

dynamically configure sql server memory

use all available processors
minimum query plan threshold for considering 5
PROFILER READS = 598





----------------------------------------------------
Making story short. I got an application that hits only 1 database called RECORDS. I'm getting different duration when running an application. 23 and 3 seconds.
Same database, same objects and same application.
SERVER QAT is our staging server, means lots of databases
SERVER MILLER is just a server i just assembled, means just one database (RECORDS).

Not sure if it's because it's a clustering server that is causing the issue nor the reads. If its the reads, what is causing it? Do you think is the how the memory is configured?. Will the experts pls stand up?

View 20 Replies View Related

More Reads Then Expected

Jul 18, 2006

So I€™m at a dead-end looking for the reason behind the following behavior. Just to make sure no one misses it, the 'behavior' is the difference in the number of reads between using sp_executesql and not.

The following statements are executed against a SQL 2000 database that contains >1,000,000 records in the act_item table. They are run using Query Analyzer and the Duration and Reads come from SQL Profiler

SQL 1:
exec sp_executesql N'update act_item set Priority = @Priority where activity_code = @activity_code', N'@activity_code nvarchar(40),@Priority int', @activity_code = N'46DF335F-68F7-493F-B55E-5F9BC6CEBC69', @Priority = 0

Reads: ~22000
Duraction: 250-350 ms

SQL 2:
DECLARE @Priority int
DECLARE @Activity_Code char(36)

SET @Priority = 0
SET @Activity_Code = '46DF335F-68F7-493F-B55E-5F9BC6CEBC69'
update act_item set Priority = @Priority where activity_code = @activity_code

Reads: ~160
Duration: 0 ms

Random information:

Activity_code is an indexed field on the table, although it is not the primary key. There are a total of four indexes on the table, none of which include the priority as one of the fields.
There are two triggers on the table, neither of which is executed for this SQL statement (there is an IF UPDATE(fieldname) surrounding the code in the trigger)
There are no foreign relationships
I checked (using perfmon) to see if a compilation/recompilation was happening. No it's not.
Any suggestions as to avenues that could be examined would be appreciated.

TIA

View 3 Replies View Related

Sqldatareader Reads From Second Row Skip The First Row.

Jun 20, 2007

Hello,
im using sqldatareader to read my data and whenever time i loop through the reader it starts from second row why is that?
here is my code:while (reader.Read()){hinfo.Name = reader["_name"].ToString();hi.Add(hinfo);}
i look at the database and i have two rows but its reading only the second row, skiping the first row 
 

View 2 Replies View Related

Log Reads In SQL Server 2005

May 31, 2006

I have a set of triggers that log the history of changes to a table - i.e. I record inserts, updates, deletes (pretty standard audit stuff I suppose). I want to also log reads on that data. If I were using sprocs for reading data, this would be relatively painless, but I am using an O/R mapper to handle my data access, which writes dynamic sql at runtime (and I don't want to use sprocs with it) and then sends it down to the DB. Is there a way I can intercept reads and log them to the same table I am logging other actions? I know very little about the new capabilities of SQL Server 2005, but I would think I could somehow, maybe via the new CLR capabilities or similar, get access to these types of events within the database? Anyone? I know I could always do this higher up in the application layers, but I would like to keep all of this at the database level if possible....Thanks,

View 1 Replies View Related

High Page Reads

Jan 17, 2002

SQL 6.5 - 5.5 Gig
NT

Hello,

Throughout the day our Document Management application generates high busts of physical page reads when users query the database.

What SQL configuration parameter(s) should I check/modify to insure that the database is performing at it's optimun during these bursts?

Thank You in advance.

View 1 Replies View Related

COUNT Of READS And WRITES On A 6.5 Db.

Jul 21, 2000

Is there a way to get a total count of all SELECT, UPDATE, DELETE and INSERT statements to a SQL Server 6.5 database during a 12 hour period? I'm thinking maybe someone knows of a software that reads the log or monitors the server... I've been looking at the performance monitor and, although it has good information, it doesn't capture DML's.

FYI - it's for capacity planning.

TIA,
Mike

View 1 Replies View Related

Reducing Reads Question

Aug 24, 2007

I'm trying to insert all the rows from a table to a new table.
(insert A select * from AA)
The reads on Profiler shows ar really high value (10253548).

First I created a unique clustered index and the reads shows (3258445), then I created a non clustered index expecting to have lower reads. Instead the reads shows (10253548).

I read creating indexes helps reduce reads. But it's not happening.
Any ideas what is going on?

=============================
http://www.sqlserverstudy.com

View 6 Replies View Related

Track Reads And Writes

Mar 5, 2008

GUys,

Is there any way track tables which have most no of reads and writes from a database of 400 tables.

Thanks

View 9 Replies View Related

Transaction Lockout Of Reads

Aug 28, 2006

Hi,

I have been seeing a basic scenario of a write transaction appearing to unexpectedly lock-out reading.

The database has isolation set to "READ COMMITTED".

The scenario is:

1.) Start a transaction (for doing a write)

2.) Do a read before the transaction (for doing the write) is committed (e.g. sqlCommand2.ExecuteReader()).

--> the code will appear to lock-up (then time out).

I see the same behavior if I step through the "write" code with the debugger (to a point after the transaction is started, but before it is committed), and run a "SELECT * FROM" type query from Microsoft SqlServer Management Studio.

Following is the code sample demonstates the issue.

Thoughts on how to resolve the issue (to let me do "read committed" reading of the database table)?

Thanks!

Andy







Module Transaction

Sub Main()

Dim exception1 As Exception

Try

' Create/Open Database Connection

Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("Server=GRB-AB;Database=Transaction;Trusted_Connection=True;")

sqlConnection1.Open()

' Start transaction

Dim sqlTransaction1 As System.Data.SqlClient.SqlTransaction = sqlConnection1.BeginTransaction()

' Set Parent record

Dim sqlCommand1 As New System.Data.SqlClient.SqlCommand("INSERT INTO Parent (Name) VALUES ('ParentValue');", sqlConnection1)

sqlCommand1.Transaction = sqlTransaction1

sqlCommand1.ExecuteNonQuery()

' Get Id from parent record (note: this code assumes the table was empty when this program starts)

sqlCommand1 = New System.Data.SqlClient.SqlCommand("SELECT Id FROM Parent;", sqlConnection1)

sqlCommand1.Transaction = sqlTransaction1

Dim parentId As Integer = CType(sqlCommand1.ExecuteScalar(), Integer)



'

' Do reading test to test concurrently reading table being written to

'

' Create/Open Database Connection for reading test

Dim sqlConnection2 As New System.Data.SqlClient.SqlConnection("Server=GRB-AB;Database=Transaction;Trusted_Connection=True;")

sqlConnection2.Open()

Dim sqlCommand2 As New System.Data.SqlClient.SqlCommand("SELECT Id FROM Parent;", sqlConnection2)

sqlCommand2.ExecuteReader()

Dim i As Integer

While (sqlCommand2.ExecuteReader.Read = True) ' <===== LOCKS UP HERE **************

i = i + 1

End While

'

' End reading test

'



' Set child record

sqlCommand1 = New System.Data.SqlClient.SqlCommand( _

"INSERT INTO Child (Name, ParentId) VALUES ('ChildValue', " & parentId.ToString & ");", sqlConnection1)

sqlCommand1.Transaction = sqlTransaction1

sqlCommand1.ExecuteScalar()

' Either 1.) commit transaction OR 2.) rollback transaction

Dim test As Boolean = False

If test = False Then

sqlTransaction1.Commit()

Else

sqlTransaction1.Rollback()

End If

sqlConnection1.Close()

sqlConnection2.Close()

Catch ex As Exception

exception1 = ex

End Try

End Sub

End Module

View 1 Replies View Related

SQL CLR Stored Proc Reads

Sep 19, 2006

I have written a same stored proc in TSQL and SQL CLR which basically takes an input xml and returns xml document. In SQL Profiler, I am getting reads value about five times more for the CLR. Does anyone has any idea why the CLR is doing more reads than TSQL? Thanks in advance.

View 5 Replies View Related

Query Logical, Scan Reads?

Dec 22, 2000

Hi Everybody,

One of my friend asked me "How do we reduce the query logical, scan reads
in SQL Server?".

I really don't know, how to answer him.

Can anybody explain me regarding this.

thanks,
Srini

View 2 Replies View Related

SQL 2012 :: Deadlocking Under Repeatable Reads

May 5, 2015

Just migrated application from Oracle to SQL and we are seeing alot of deadlocking and blocking. I did notice that app seems to be passing isolation level of repeatable read. Attached is a .doc of one of the deadlocks, is there a way to avoid these in the repeatable read isolation level? This example is a select with two tables, using NCI's that cover the where, and a insert doing just a clustered index insert. Is this simply try to get rid of the repeateable read if not needed, guess have to check with vendor on that or is there a way to get this to not deadlock using repeatable read?

View 2 Replies View Related

Audit Logout, High Reads

May 2, 2007

Hi,

I'm trying to figure out why my sqlserver is flatlined on the CPU. I'm doing a trace and can't help but notice this, with crazy high reads. I'm not sure what this is? It doesnt look good to me, altho maybe its nothing. Any info is much appreciated.

Thanks again!
mike123



Event Class/ TextData/ApplicationName/ LoginName/ CPU/ Reads/ Writes/ Duration

Audit Logout.Net SqlClient Data ProviderloginName3764129784 3146 156

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved