Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using dm_exec_sessions to count reads? Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2008 4:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:42 AM
Points: 1,191, Visits: 790
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
Posted Thursday, January 31, 2008 12:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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
Posted Thursday, January 31, 2008 12:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:10 PM
Points: 42,986, Visits: 36,141
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
Posted Friday, February 1, 2008 7:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 8, 2014 5:42 AM
Points: 1,191, Visits: 790
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.

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 »

Add to briefcase

Permissions Expand / Collapse