Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using dm_exec_sessions to count reads?


using dm_exec_sessions to count reads?

Author
Message
Eric Mamet
Eric  Mamet
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1227 Visits: 869
I am trying to use sys.dm_exec_sessions to check how many reads I just did in TSQL...

I can use STATISTICS IO but it's quite "hard work" to evaluate the total damage in terms of reads (adding numbers scattered around the screen).

I thought I could collect the logical_reads from dm_exec_sessions before I run something (like one or several stored procedures) and then read the same again at the end.
Surely this would give me the total of reads...

DECLARE @OldReads BIGINT, @NewReads BIGINT
SELECT @OldReads = logical_reads FROM sys.dm_exec_sessions WHERE session_id = @@SPID
SELECT * FROM Whatever...
SELECT @NewReads = logical_reads FROM sys.dm_exec_sessions WHERE session_id = @@SPID
PRINT 'Cost: ' + CAST(@NewReads - @OldReads AS VARCHAR(9)) + ' reads';

Zut Alors! It does not work...

I seem to always get the same figure before and after.
Is there a trick I could use to force a "refresh" of that DMV?
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2725 Visits: 715
in general dmv's are cumulative so you need snapshots into a table - but I'm not quite sure what you're trying to achieve so if you want to drop me a mail ( via www.grumpyolddba.co.uk ) or as a private message I may be able to help as I have a mass of scripts and reports which do this sort of thing - I just need to understand what it is you're trying to do.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47165 Visits: 44346
One of the easiest ways to count reads is using profiler. Start up a session, trace T-SQL:Stmt_completed and/or T-SQL:BatchCompleted and filter on your login name and/or machine name.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Eric Mamet
Eric  Mamet
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1227 Visits: 869
Well, I have a little stored procedure that, given another stored procedure name, generates a simple test harness.
Nothing rocket science: begin tran, declare all required parameters, set values (NULL by default) to all parameters, fire the stored procedure, check the return status and finally roll back.
Simple but extremely useful.

Exclamation MarkI suddenly thought that it might be even more useful if my test harness told me, out of the box, how many database reads my tested stored procedure generated.

From tests I just did, it looks like the DMV only gets updated between batches, hence I tried to store the result in a temporary table.
The only little glitch left is that the very fact of looking for the number of reads, itself generates reads...
About 230/240 pages in my tests.

So far, it looks like this...

IF EXISTS( SELECT 1 FROM tempdb.sys.tables WHERE object_id = OBJECT_ID('tempdb.dbo.#Stats') )
    DROP TABLE #Stats;

CREATE TABLE #Stats(Id INT NOT NULL PRIMARY KEY, LogicalReads BIGINT NOT NULL)

INSERT INTO #Stats(Id, LogicalReads)
   SELECT 1, logical_reads
      FROM sys.dm_exec_sessions
      WHERE session_id = @@SPID
GO

-- call anything here
SELECT * FROM AnyTableOrProcedureWouldDo

GO
INSERT INTO #Stats(Id, LogicalReads)
   SELECT 2, logical_reads
      FROM sys.dm_exec_sessions
      WHERE session_id = @@SPID

SELECT 'You have read ' + CAST(S2.LogicalReads - S1.LogicalReads AS VARCHAR(20)) + ' page(s)' AS Performance
   FROM #Stats S2
   INNER JOIN #Stats S1 ON S2.Id = 2 AND S1.Id = 1


I think it's almost there... I might just remove a ballpark figure, like 230 reads...

PS: I know I can get reads through a trace or SET STATISTICS IO but I wanted to improve my test harness generator...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search