August 31, 2020 at 5:11 pm
I have a procedure that is trying to pass in a table name and outputs #rows and #logical reads.
create procedure test_output @tbl_name varchar(50), @num_rows int = 0 OUTPUT, @num_reads int = 0 OUTPUT
AS
begin
select *
from @tbl_name
set @num_rows = @@ROWCOUNT
set @num_reads = SELECT ???
I tried using something like this:
SELECT TOP 1
databases.name,
dm_exec_sql_text.text AS TSQL_Text,
dm_exec_query_stats.total_logical_reads
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE dm_exec_sql_text.text LIKE ????
Can I save the sql handle inside the procedure and use it here?
Is there a better way to accomplish returning these two stats?
Thanks
DB
August 31, 2020 at 5:27 pm
DB,
your procedure fails because it is expecting a table variable. You need to implement dynamic sql to pass table names.
create or alter procedure test_output @tbl_name varchar(50), @num_rows int = 0 OUTPUT, @num_reads int = 0 OUTPUT
AS
begin
declare @local_rowcount as int, @local_readcount as int
DECLARE @ActualTableName AS NVarchar(255)
SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tbl_name
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = 'SELECT * FROM ' + @ActualTableName + ';'
EXEC(@SQL)
set @num_rows = @@ROWCOUNT
set @num_reads = ???
end
hopefully someone else can shed light on the logical reads.
regards
jason
Microsoft Certified Professional: MCSE Data Management and Analytics
August 31, 2020 at 8:09 pm
First - to get row count you can do this instead of count:
Declare @schemaName sysname = 'dbo'
, @tableName sysname = 'Tally';
Select ddps.row_count
From sys.dm_db_partition_stats ddps
Where object_name(ddps.[object_id]) = @tableName
And object_schema_name(ddps.[object_id]) = @schemaName;
I am not sure what you are trying to identify as logical reads for a table - that really doesn't make sense. Can you explain that requirement in further detail?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
September 1, 2020 at 2:17 pm
Thanks Jason and Jeff. Yes I didn't explain it very clear.
Basically, I am trying trying to return the number of logical reads (from the sys.dm_exec_query_stats.total_logical_reads) for the last query that was executed. So the procedure is passed in a table name, which is used to execute query 'abc', and I want to return the logical reads for that query.
September 1, 2020 at 2:37 pm
If you query the table to get the row count, your query will be the one that gets measured for logical reads. I'm pretty sure you need to use Jeff Williams code to get the row count. Also, you will probably find multiple queries in the table with the logical reads, so you'll have to figure out which one was the last one to run...
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
September 2, 2020 at 12:13 am
Are you looking for something like the following?
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_ShowWorst]
/**********************************************************************************************************************
Purpose:
This store procedure returns the "worst" performing queries in descending order by "CPU" time (worker time), logical
"Reads", total "Duration", or most used (execution "Count") for whatever is currently cached.
Programmer's Notes:
1. This stored procedure should live in the Master database.
2. After building the stored procedure, you need to run the following code to convert it into a "system" stored
procedure that can be executed from database.
USE MASTER;
EXEC sp_ms_marksystemobject 'sp_ShowWorst';
SELECT name, is_ms_shipped
FROM sys.objects
WHERE name = 'sp_ShowWorst'
;
-----------------------------------------------------------------------------------------------------------------------
Usage:
sp_ShowWorst --(from any database, defaults to "Reads" and "10")
... OR ...
EXEC sp_ShowWorst --(from any database, defaults to "Reads" and "10")
sp_ShowWorst [@pSortType][,@pRows][,@pDebug] --Both parameters are option. Logical "Reads" and "30" will be presumed.
Valid values for @pSortType:
Reads - Logical Reads (current default) since last compile date
CPU - Total worker time in micro seconds since last compile date
Duration - Total elapsed time in micro seconds since last compile date
Count - Total number of executions since last compile date
Physical - Total number of physical reads since last compile date
Recent - Adding the word "Recent" to any of the above limits the output to only those things that have executed in
the last 60 minutes.
Valid values for @pRows:
Any positive integer (current default is 10)
Valid values for @pDebug
0 - Debug is off (default) and the code will be executed
1 - Debug if on and the code will be displayed instead of being executed.
-----------------------------------------------------------------------------------------------------------------------
Example Usage:
--===== Example usage for things that have run in the last hour
-- regardless of when they were compiled.
EXEC sp_ShowWorst 'Recent CPU' , 10;
EXEC sp_ShowWorst 'Recent Reads' , 10;
EXEC sp_ShowWorst 'Recent Duration' , 10;
EXEC sp_ShowWorst 'Recent Writes' , 10;
EXEC sp_ShowWorst 'Recent Physical' , 10;
EXEC sp_ShowWorst 'Recent Count' , 10;
--===== Example usage for things since they were last compiled.
EXEC sp_ShowWorst 'CPU' , 10;
EXEC sp_ShowWorst 'Reads' , 10;
EXEC sp_ShowWorst 'Duration', 10;
EXEC sp_ShowWorst 'Writes' , 10;
EXEC sp_ShowWorst 'Physical', 10;
EXEC sp_ShowWorst 'Count' , 10;
-----------------------------------------------------------------------------------------------------------------------
Notes:
1. Remember that values returned are as of the creation date and time (creation_time column) of the execution plan
whether the RECENT option is used or not. It IS possible for a plan to never be flushed from cache and the
cumulative usage may skew the results. This frequently occurs over weekends.
2. Note to self... (TODO) Find a way to overcome the problem stated in the note above.
3. Note to self... (TODO) Find out why SQL Server sometimes doesn't return an execution plan. It's understood that
"Optimize for Ad Hoc Queries" can cause this for singleton queries, but not for queries
that are executed multiple times with the same plan handle.
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 29 Feb 2013 - Jeff Moden
- Formalize a script for regular usage.
Rev 01 - 25 Feb 2015 - Jeff Moden
- Add object name and ID information and make data collection for different types a lot easier by adding
"Sample*" information columns.
Rev 02 - 04 Jul 2016 - Jeff Moden
- 2.1 Added code and columns to determine the currently active database when ad hoc queries are executed.
- 2.2 Added the "Exec" prefix to columns that identify the object that was actual executed. Note that these
columns are normmally NULL for ad hoc queries via ORMs, applications, and SSMS.
Rev 03 - 06 Feb 2020 - Jeff Moden
- Add code to replace CHAR(31) with an empty string.
**********************************************************************************************************************/
--===== Define the I/O for this proc
@pSortType VARCHAR(30) = 'RECENT Reads',
@pRows INT = '10',
@pDebug BIT = 0
AS
--===== Environmental Presets
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--===== Declare some obviously named variable(s)
DECLARE @SQL VARCHAR(MAX)
;
--===== Create the SQL to get the data in the order requested
SELECT @SQL = REPLACE(REPLACE(REPLACE(REPLACE('
SELECT TOP (<<@pRows>>) --sp_ShowWorst
SampleDT = GETDATE() --Rev 01
,SampleType = ''<<@pSortType>>'' --Rev 01
,SampleValue = [stats].<<SortColumn>> --Rev 01
,CurrentDBName = db.DBName --Rev 02.1
,CurrentDBID = DB_ID(db.DBName) --Rev 02.1
,ExecDBID = [txt].DBID --Rev 01 - Rev02.2
,ExecObjectID = [txt].ObjectID --Rev 01 - Rev02.2
,ExecDBName = DB_NAME([txt].DBID) --Rev 01 - Rev02.2
,ExecSchemaName = OBJECT_SCHEMA_NAME([txt].ObjectID, [txt].DBID) --Rev 01 - Rev02.2
,ExecObjectName = OBJECT_NAME([txt].ObjectID, [txt].DBID) --Rev 01 - Rev02.2
,StatementText = (SELECT REPLACE(REPLACE(CAST(''--'' + CHAR(10)
+ SUBSTRING(txt.Text
,[stats].Statement_Start_Offset/2+1
,(CASE [stats].Statement_End_Offset
WHEN -1
THEN DATALENGTH(txt.Text)
ELSE [stats].Statement_End_Offset
END)/2 - [stats].Statement_Start_Offset/2+1
) AS VARCHAR(MAX)),CHAR(0),''''),CHAR(31),'''')
AS [processing-instruction(StatementTextSQL)] FOR XML PATH(''''), TYPE)
,FullText = (SELECT REPLACE(REPLACE(CAST(''--''+CHAR(10)+[txt].Text AS VARCHAR(MAX)),CHAR(0),''''),CHAR(31),'''')
AS [processing-instruction(FullTextSQL)] FOR XML PATH(''''), TYPE)
,[plan].query_plan
,[stats].*
FROM sys.dm_exec_query_stats [stats]
OUTER APPLY sys.dm_exec_sql_text ([stats].sql_handle) [txt]
OUTER APPLY sys.dm_exec_query_plan([stats].plan_handle) [plan]
OUTER APPLY (SELECT DBName = DB_NAME(CONVERT(INT,value)) -- Rev 02.1
FROM sys.dm_exec_plan_attributes([stats].plan_handle)
WHERE attribute = ''dbid'') [db]
WHERE [Stats].Last_Execution_Time >= ''<<LookBackDate>>''
ORDER BY [stats].<<SortColumn>> DESC
;'
------- This is the other end of the REPLACE's
,'<<@pSortType>>',@pSortType)
,'<<@pRows>>',CAST(@pRows AS VARCHAR(10)))
,'<<LookBackDate>>', CAST(CASE WHEN @pSortType LIKE '%Recent%' THEN DATEADD(hh,-1,GETDATE()) ELSE '1753' END AS VARCHAR(20)))
,'<<SortColumn>>', CASE
WHEN @pSortType LIKE '%Count%' THEN 'Execution_Count'
WHEN @pSortType LIKE '%CPU%' THEN 'Total_Worker_Time'
WHEN @pSortType LIKE '%Duration%' THEN 'Total_Elapsed_Time'
WHEN @pSortType LIKE '%Reads%' THEN 'Total_Logical_Reads'
WHEN @pSortType LIKE '%Physical%' THEN 'Total_Physical_Reads'
WHEN @pSortType LIKE '%Writes%' THEN 'Total_Logical_Writes'
END)
;
--===== Produce the output according to what the @pDebug variable is set to
IF @pDebug = 0 EXEC (@SQL);
ELSE PRINT @SQL;
--Jeff Moden
September 3, 2020 at 4:27 pm
Yes this helps and it's a very useful query. thanks Jeff!
September 3, 2020 at 9:06 pm
You bet. Thank you for the feedback.
I use the code to find the worst performing queries even each execution is measured in milliseconds. Repetition means a lot. In other words, your worst queries are usually NOT your longest running queries. It also helps to identify which part of a stored procedure is the worst part. It does things a bit like Adam Machanic's sp_WhoIsActive but I wrote mine because I didn't know his existed at the time.
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply