SQL Clone
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
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4849 Visits: 954
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
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21831 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 Guru
SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)

Group: General Forum Members
Points: 411385 Visits: 47113
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
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4849 Visits: 954
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