Microsoft – SQL Server – Datatype – Table Value Parameter (TVP)

Prelude

As I review a body of work it seems that I could speed it up a bit by isolating an expensive lookup table processing outside of a frequently used Stored Procedure.

 

Global Tables

Traditionally, one could use Local Temporary Tables and Global Temporary Tables, #tab-name and ##table-name.   But, I never quite liked using them extensively.

Script

Let us review our code-line for provisioning and using user defined types.

 

Initial Code

dbo.tvpDatabaseFileSize.sql

 


use [master]
go

/*
	DROP TYPE tvpDatabaseFileSize
*/

/*
	Create type
*/
if not exists
	(
		select 1
		from   sys.table_types
		where  name = 'tvpDatabaseFileSize'

	)
begin

	CREATE TYPE dbo.tvpDatabaseFileSize AS TABLE
	(
		  [databaseID]		int
		, [databaseName]	sysname
		, [fileType]		sysname
		, [fileSizeInKB]	bigint

		PRIMARY KEY
			(
				  [databaseName]
				, [fileType]
			)
	)

end

GO

 

dbo.sp_DatabaseObjectSizeContextual

 

use master
go

/*
	drop proc dbo.sp_DatabaseObjectSizeContextual
*/

if OBJECT_ID('dbo.sp_DatabaseObjectSizeContextual') is null
begin
	exec('create procedure dbo.sp_DatabaseObjectSizeContextual as select 1/0 as [shell] ')
end
go

alter procedure dbo.sp_DatabaseObjectSizeContextual
(
	@databaseFileSize dbo.tvpDatabaseFileSize READONLY
)
as
begin

	set nocount on;

	select *
	from   @databaseFileSize tblDFS
	where  tblDFS.databaseID = DB_ID()

	select 

			db_name(DB_ID())
				as [databaseName]

		  , SCHEMA_NAME(tblO.schema_id)
				as [schemaName]

		  , tblO.name
				as [objectName]

		  , sum( tblAU.total_pages * 8)
				as [totalPagesInKB]	

		  , sum( tblAU.total_pages) * 8 * 100.00
				 / tblDFS.[fileSizeInKB]
				as [%]	

		  , sum( tblAU.used_pages * 8)
				as [usedPagesInKB]						

	from   sys.objects tblO

			inner join sys.indexes tblI
				on tblO.object_id = tblI.object_id

			inner join sys.partitions tblP
				on  tblI.object_id = tblP.object_id
				and tblI.index_id = tblP.index_id	

			inner join sys.allocation_units tblAU
				on  tblP.partition_id = tblAU.container_id

			outer apply
					(
						select *
						from @databaseFileSize tblDFS
						where tblDFS.databaseID = DB_ID()
						and   tblDFS.fileType = 'ROWS'
					) tblDFS		

	where tblO.is_ms_shipped = 0

	group by
				SCHEMA_NAME(tblO.schema_id)
			  , tblO.name
			  , tblDFS.[fileSizeInKB]

end
go

exec sys.sp_MS_marksystemObject 'dbo.sp_DatabaseObjectSizeContextual'
go

 

 

 

dbo.sp_DatabaseObjectSizes

 


use master
go

--drop proc dbo.sp_DatabaseObjectSizes
if OBJECT_ID('dbo.sp_DatabaseObjectSizes') is null
begin

	exec('create procedure dbo.sp_DatabaseObjectSizes as select 1/0 as [shell] ')

end
go

alter procedure dbo.sp_DatabaseObjectSizes
as
begin

	set nocount on;

	declare @strSQLStringTemplate nvarchar(4000)
	declare @strSQLString nvarchar(4000)
	declare @strParmDefinition nvarchar(4000)
	declare @strDatabaseName sysname

	declare @databaseFileSizeLocal tvpDatabaseFileSize

	/*
		sp_executeSQL - Template
	*/
	set @strSQLStringTemplate =
				   ' exec [%s].dbo.sp_DatabaseObjectSizeContextual '
			    +  ' @databaseFileSize = @databaseFileSize ' 

	/*
		Parameter Definition
	*/
	set @strParmDefinition = N'@databaseFileSize tvpDatabaseFileSize READONLY '	

	/*
		Database Name
	*/
	set @strDatabaseName = 'master'
	--set @strDatabaseName = 'AdventureWorksDW2008R2'	

	/*
		Get database sizes
	*/
	insert into @databaseFileSizeLocal
	(
		  [databaseID]
		, [databaseName]
		, [fileType]
		, [fileSizeInKB]
	)
	select
			  tblSD.database_id as [databaseID]
			, tblSD.name as [databaseName]
			, tblMF.type_desc as [fileType]
			, sum(tblMF.size * 128) as [fileSizeInKB]

	from   master.sys.databases tblSD

				inner join master.sys.master_files tblMF

					on tblSD.database_id = tblMF.database_id
	group by
			  tblSD.database_id
			, tblSD.name
			, tblMF.type_desc

	exec master.dbo.xp_sprintf
			  @strSQLString output
			, @strSQLStringTemplate
			, @strDatabaseName

	exec sp_executesql
			  @strSQLString
			, @strParmDefinition
			, @databaseFileSize = @databaseFileSizeLocal

end
go

 

Invoke – Database – Master

When we invoke dbo.sp_DatabaseObjectSizes, we get a nice output that reads:

 

DatabaseObjectSize

 

 

Invoke – Database – AdventureWorksDW2008R2

We changed the dbo.sp_DatabaseObjectSizes Stored Procedure code a bit by un-commenting the line that reads AdventureWorksDW2008R2

/*
Database Name
*/ 
set @strDatabaseName = 'master' 
set @strDatabaseName = 'AdventureWorksDW2008R2' 

 

And, our lamentations surfaced:

Output – Textual:

Msg 2715, Level 16, State 4, Procedure sp_DatabaseObjectSizeContextual, Line 74
Column, parameter, or variable #1: Cannot find data type dbo.tvpDatabaseFileSize.
Parameter or variable '@databaseFileSize' has an invalid data type.


Output – Image:

CannotFindDatabase

 

 

 

Comment

Not so sure what the problem is, but it appears that we need to create our user table type in each database where it will be used.

In our case, we switched context to AdventureWorksDW2008R2 and ran our user table type creation script. i.e.

 

Create User table type in targeted database

 


--use [db-name]
go

/*
	Create type
*/
if not exists
	(
		select 1
		from   sys.table_types
		where  name = 'tvpDatabaseFileSize'

	)
begin

	CREATE TYPE dbo.tvpDatabaseFileSize AS TABLE
	(
			  [databaseID]		int
			, [databaseName]	sysname
			, [fileType]		sysname
			, [fileSizeInKB]		bigint

		PRIMARY KEY
			(
				  [databaseName]
				, [fileType]
			)
	)

end

GO

 

 

But, still same error.

Msg 2715, Level 16, State 4, Procedure sp_DatabaseObjectSizeContextual, Line 74
Column, parameter, or variable #1: Cannot find data type dbo.tvpDatabaseFileSize.
Parameter or variable ‘@databaseFileSize’ has an invalid data type.

Other Errors

Tried numerous other things and got other errors such as:

Msg 206, Level 16, State 2, Procedure sp_DatabaseObjectSizeContextual, Line 0
Operand type clash: tvpDatabaseFileSize is incompatible with tvpDatabaseFileSize

I wish I know which code version experienced the incompatibility error.  I mean that is why we write, dedicate time and efforts into grounded and esoteric subject areas and come back and affirm ours and others perspectives; knowing that we are unlikely to be so easily enchanted.

 

Final Code

I called Microsoft’s all kind of names, but like an old girl friend once said to me, “go wash your mouth and take it all back“.

I found much needed help via:

Which basically says, to do the following:

  • Declare a variable in which ever database the user table type is registered
  • Populate it
  • Output its contents to tempdb
  • In each successive Stored Procedure, reference the persisted object in tempdb

Here we go:

dbo.sp_DatabaseObjectSizeContextualUseTempDB

 


use master
go

/*
	drop proc dbo.sp_DatabaseObjectSizeContextualTempdb
*/
if OBJECT_ID('dbo.sp_DatabaseObjectSizeContextualUsetempdb') is null
begin
	exec('create procedure dbo.sp_DatabaseObjectSizeContextualUseTempdb as select 1/0 as [shell] ')
end
go

alter procedure dbo.sp_DatabaseObjectSizeContextualUseTempdb
as
begin

	set nocount on;

	declare @totalPagesInKB decimal(20, 2)
	declare @usedPagesInKB  decimal(20, 2)

	; with cteAllObject
	(
		  [totalPagesInKB]
		, [usedPagesInKB]
	)
	as
	(
		select
			   sum( tblAU.total_pages * 8)
				as [totalPagesInKB]	

			  , sum( tblAU.used_pages * 8)
				as [usedPagesInKB]						

		from   sys.objects tblO

			 inner join sys.indexes tblI
				on tblO.object_id = tblI.object_id

			inner join sys.partitions tblP
				on  tblI.object_id = tblP.object_id
				and tblI.index_id = tblP.index_id	

			inner join sys.allocation_units tblAU
				on  tblP.partition_id = tblAU.container_id

		where tblO.is_ms_shipped = 0

	)		

	select
		  @totalPagesInKB = tblAO.totalPagesInKB
		, @usedPagesInKB  = tblAO.usedPagesInKB
	from    cteAllObject tblAO

         /* Reference object into #tempDB */
	select
			  tblDFS.*
			, @totalPagesInKB as totalPagesInKB
			, @usedPagesInKB as usedPagesInKB
	from   #databaseFileSize tblDFS
	where  tblDFS.databaseID = DB_ID()
	and    tblDFS.[fileType] = 'ROWS'

	select
		   db_name(DB_ID())
				as [databaseName]

		  , SCHEMA_NAME(tblO.schema_id)
				as [schemaName]

		  , tblO.name
				as [objectName]

		  , sum( tblAU.total_pages * 8)
				as [totalPagesInKB]	

		  ,  sum( tblAU.used_pages * 8)
				as [usedPagesInKB]						

		  , cast(
				sum( tblAU.total_pages * 8) * 100.00
					/ @totalPagesInKB
				as decimal(10,2)
			)
			  as [%InDatabase]						

	from   sys.objects tblO

			inner join sys.indexes tblI
				on tblO.object_id = tblI.object_id

			inner join sys.partitions tblP
				on  tblI.object_id = tblP.object_id
				and tblI.index_id = tblP.index_id	

			inner join sys.allocation_units tblAU
				on  tblP.partition_id = tblAU.container_id

			cross apply
				(
					select *
					from    #databaseFileSize tblDFS
					where   tblDFS.databaseID = DB_ID()
					and     tblDFS.fileType = 'ROWS'
				) tblDFS							

	where tblO.is_ms_shipped = 0

	group by
			    SCHEMA_NAME(tblO.schema_id)
			  , tblO.name
			  , tblDFS.filesizeInKB

	order by
			  sum( tblAU.total_pages * 8) desc

end
go

exec sys.sp_MS_marksystemObject 'dbo.sp_DatabaseObjectSizeContextualUseTempdb'
go

 


dbo.sp_DatabaseObjectSizesUseTempDB


use master
go

/*
	drop proc dbo.sp_DatabaseObjectSizesUseTempDB
*/	
if OBJECT_ID('dbo.sp_DatabaseObjectSizesUseTempDB') is null
begin

	exec('create procedure dbo.sp_DatabaseObjectSizesUseTempDB as select 1/0 as [shell] ')
	
end
go

alter procedure dbo.sp_DatabaseObjectSizesUseTempDB
as
begin

	set nocount on;
	
	declare @strSQLStringTemplate nvarchar(4000)
	declare @strSQLString nvarchar(4000)	
	declare @strParmDefinition nvarchar(4000)	

	declare @databaseFileSizeLocal tvpDatabaseFileSize

	/*
		Prepare SQL String Template
	*/			
	set @strSQLStringTemplate = ' exec [%s].dbo.sp_DatabaseObjectSizeContextualUseTempdb '				
	

	/*
		Populate Table Value Parameter
	*/			
	insert into @databaseFileSizeLocal
	(
		  [databaseID]
		, [databaseName]
		, [fileType]
		, [fileSizeInKB]
	)
	select 
			  tblSD.database_id as [databaseID]
			, tblSD.name as [databaseName]
			, tblMF.type_desc as [fileType]
			, sum(tblMF.size * 128) as [fileSizeInKB]
	from   master.sys.databases tblSD
		  inner join master.sys.master_files tblMF
			   on tblSD.database_id = tblMF.database_id

	group by				
		  tblSD.database_id
		, tblSD.name
		, tblMF.type_desc

	/*
		If Temp Table already exists, please drop it
	*/			
	if OBJECT_ID('tempdb.dbo.#databaseFileSize') is not null
	begin
		drop table dbo.#databaseFileSize
	end

	/*
		To use same schema in other databases, keep safe in TempDB
	*/			
	select *
	into   #databaseFileSize
	from   @databaseFileSizeLocal

	/*
		replace database place holder %s with actual DB Name
	*/			
	exec master.dbo.xp_sprintf
			  @strSQLString output
			, @strSQLStringTemplate
			, 'AdventureWorksDW2008R2'
			    
	/*
		Execute sql
	*/			
	exec sp_executesql @strSQLString 

	/*
		Clean up temp table
	*/
	if OBJECT_ID('tempdb.dbo.#databaseFileSize') is not null
	begin
		drop table dbo.#databaseFileSize
	end

	
end
go

Conclusion

There are good arguments as to if the gain is worth the effort & resource uptake that will result from creating a secondary copy in Tempdb.

In our case, I hope it is, as I do want to repeat the gathering of data every time my child SP is invoked.

In the future, I might find simpler pathways for demonstrating the use of this important tool, user defined types, but for now my actual use case is tightly locked up in someone else’s IP Portfolio, as it should be; the company paid good money to have people work on their code-base.

 

 

References

 

Using User defined types across databases

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s