using dm_exec_sessions to count reads?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » using dm_exec_sessions to count reads?

using dm_exec_sessions to count reads?

Rate Topic

Display Mode

Topic Options

Author

Message

Eric Mamet

Posted Wednesday, January 30, 2008 4:26 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:41 AM
Points: 1,129, Visits: 685

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?

Post #449271

colin.Leversuch-Roberts

Posted Thursday, January 31, 2008 12:31 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:10 PM
Points: 2,668, Visits: 688

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/

Post #450129

GilaMonster

Posted Thursday, January 31, 2008 12:50 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

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 2008, MVP
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

Post #450139

Eric Mamet

Posted Friday, February 01, 2008 7:40 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:41 AM
Points: 1,129, Visits: 685

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 Mark

I 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...

Post #450482

« Prev Topic | Next Topic »

Permissions