edited tags

Link

edited Jan 14, 2016 at 17:47

Aaron Bertrand

181.5k
28
402
619

added 8 characters in body

Source Link

edited Nov 18, 2014 at 16:17

Geoff Bowden

105
1
1
8

I have a database which is getting a lot of calls to the following procedure:

exec dbo.SM_SP_MAILEVENTNAMES 167260

In Profiler using the TSQL_Duration trace these calls show up as:

EventClass: RPC: Completed
Duration:   900 to 1500 ms
Reads:      8818

Running the same query in Management Studio with Statistics I/O:

set statistics io on
exec dbo.SM_SP_MAILEVENTNAMES 167260

Runs instantaneously and has the following I/O output:

Table 'SM_MAIL_CONTACTS'. Scan count 0, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SM_MAIL_EVENT_CONTACTS'. Scan count 13, logical reads 68, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SM_MATTER_EVENTS'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The query itself returns about 3 rows. Why the disparity in what I am seeing in Profiler and Management Studio?

I have a database which is getting a lot of calls to the following procedure:

exec SM_SP_MAILEVENTNAMES 167260

In Profiler using the TSQL_Duration trace these calls show up as:

EventClass: RPC: Completed
Duration:   900 to 1500 ms
Reads:      8818

Running the same query in Management Studio with Statistics I/O:

set statistics io on
exec SM_SP_MAILEVENTNAMES 167260

Runs instantaneously and has the following I/O output:

Table 'SM_MAIL_CONTACTS'. Scan count 0, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SM_MAIL_EVENT_CONTACTS'. Scan count 13, logical reads 68, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SM_MATTER_EVENTS'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The query itself returns about 3 rows. Why the disparity in what I am seeing in Profiler and Management Studio?

I have a database which is getting a lot of calls to the following procedure:

exec dbo.SM_SP_MAILEVENTNAMES 167260

In Profiler using the TSQL_Duration trace these calls show up as:

EventClass: RPC: Completed
Duration:   900 to 1500 ms
Reads:      8818

Running the same query in Management Studio with Statistics I/O:

set statistics io on
exec dbo.SM_SP_MAILEVENTNAMES 167260

Runs instantaneously and has the following I/O output:

Table 'SM_MAIL_CONTACTS'. Scan count 0, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SM_MAIL_EVENT_CONTACTS'. Scan count 13, logical reads 68, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SM_MATTER_EVENTS'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The query itself returns about 3 rows. Why the disparity in what I am seeing in Profiler and Management Studio?

added 12 characters in body; edited title

Source Link

edited Nov 18, 2014 at 16:13

Aaron Bertrand

181.5k
28
402
619

procedure Procedure showing in profiles as taking a second but runs instantaneouslydifferent duration / reads in MS SQL StudioProfiler vs. SSMS

I have a database which is getting a lot of calls to the following procedure:

exec SM_SP_MAILEVENTNAMES 167260

exec SM_SP_MAILEVENTNAMES 167260

In MS-SQL Profiler using the TSQL_Duration trace these calls show up as:

EventClass: RPC: Completed
Duration:   900 to 1500 ms
Reads:      8818

Running the same query in Management Studio with statistics io onStatistics I/O:

set statistics io on exec SM_SP_MAILEVENTNAMES 167260

set statistics io on
exec SM_SP_MAILEVENTNAMES 167260

runsRuns instantaneously and has the following traceI/O output:

Table 'SM_MAIL_CONTACTS'. Scan count 0, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SM_MAIL_EVENT_CONTACTS'. Scan count 13, logical reads 68, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SM_MATTER_EVENTS'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The query itself returns about 3 rows. Why the disparity in what I am seeing in Profiler and Management Studio?

procedure showing in profiles as taking a second but runs instantaneously in MS SQL Studio

I have a database which is getting a lot of calls to the following procedure:

exec SM_SP_MAILEVENTNAMES 167260

In MS-SQL Profiler using the TSQL_Duration trace these calls show up as:

EventClass: RPC: Completed
Duration: 900 to 1500 ms
Reads: 8818

Running the same query in Management Studio with statistics io on

set statistics io on exec SM_SP_MAILEVENTNAMES 167260

runs instantaneously and has the following trace:

Table 'SM_MAIL_CONTACTS'. Scan count 0, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SM_MAIL_EVENT_CONTACTS'. Scan count 13, logical reads 68, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SM_MATTER_EVENTS'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The query itself returns about 3 rows. Why the disparity in what I am seeing in Profiler and Studio?

Procedure showing different duration / reads in Profiler vs. SSMS

I have a database which is getting a lot of calls to the following procedure:

exec SM_SP_MAILEVENTNAMES 167260

In Profiler using the TSQL_Duration trace these calls show up as:

EventClass: RPC: Completed
Duration:   900 to 1500 ms
Reads:      8818

Running the same query in Management Studio with Statistics I/O:

set statistics io on
exec SM_SP_MAILEVENTNAMES 167260

Runs instantaneously and has the following I/O output:

Table 'SM_MAIL_CONTACTS'. Scan count 0, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SM_MAIL_EVENT_CONTACTS'. Scan count 13, logical reads 68, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SM_MATTER_EVENTS'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The query itself returns about 3 rows. Why the disparity in what I am seeing in Profiler and Management Studio?