January 30, 2008 at 4:26 am
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?
January 31, 2008 at 12:31 pm
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 http://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.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 31, 2008 at 12:50 pm
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
February 1, 2008 at 7:40 am
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.
:exclamationmark: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...
[font="Courier New"]
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
[/font]
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...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply