Transact SQL – Warning – “Type conversion in expression may affect “CardinalityEstimate” in query plan choice”

 

 

Background

Reviewing code and ran into this warning…

Code


use [tempdb]
go

declare @tblRange TABLE
(
      [id] int not null identity(1,1)

    , [min]  numeric(5,2) null
    , [max]  numeric(5,2) null

    , [range]  AS 
            (
                cast
					(
						case 
				            when [min]=[max] then CONVERT([varchar],CONVERT([float],[min])) 
					        else (
                            CONVERT([varchar],CONVERT([float],[min]))
                                +' - '
                                + CONVERT([varchar],CONVERT([float],[max]))
                        )			 
						end
					as varchar(30)
				)
            ) PERSISTED

)

insert into @tblRange
(
    [min], [max] 
)
select 3,3
union
select 0.5,10

select *
from   @tblRange

Query Plan

 

Warning

Image

Textual

Type conversion in expression (CONVERT(varchar(30),CONVERT(float(53),[min],0),0)) may affect “CardinalityEstimate” in query plan choice, Type conversion in expression (CONVERT(varchar(30),CONVERT(float(53),[max],0),0)) may affect “CardinalityEstimate” in query plan choice

Source Code Control

GitHub

DanielAdeniji/SQLServerTypeConversionMayAffectCardinalityEstimate
Link

Summary

This is a tough error as it appears there is no redemptive path.

 

Transact SQL – Which objects are referenced?

 

Background

In our last post we discussed removing all other objects other than a few designated tables that a problematic queries are using.

 

Identify objects in use

 

Outline

In this post we will discuss a couple of options available for identifying the tables a programmable object uses.

 

Options

sp_depends

sp_depends is the classic Stored Procedure for doing so.

Syntax


exec sp_depends @objname = @object

 

Sample


declare @object sysname

set @object = '[dbo].[usp_Agent_Retrieve]'

exec sp_depends @objname = @object

 

Output

sp_depends_20171212_0547PM

 

sys.dm_sql_referenced_entities

sys.dm_sql_referenced_entities is a later iteration of sp_depends.

Syntax


select *

FROM sys.dm_sql_referenced_entities (@object, 'OBJECT') tblRef

 

Sample


declare @object sysname

set @object = '[dbo].[usp_Agent_Retrieve]'

SELECT 
          [referenced_schema_name]
        , [referenced_entity_name]
        , [columnCount] = count(*)
        , [isSelected]
            = max(cast(tblRef.[is_selected] as tinyint))
        , [isUpdated]
            = max(cast(tblRef.[is_updated] as tinyint))
/*
        , [isInsert]
            = max(cast(tblRef.[is_insert_all] as tinyint))
*/

FROM sys.dm_sql_referenced_entities (@object, 'OBJECT') tblRef

group by

          tblRef.[referenced_schema_name]
        , tblRef.[referenced_entity_name]

order by
          tblRef.[referenced_schema_name]
        , tblRef.[referenced_entity_name]

 

 

Output

sysDOTdm_sql_referenced_entities__20171212__0558PM

 

 

Summary

There are some distinct advantages to choosing the more modern path, sys.dm_sql_referenced_entities.

Inclusive are:

  1. Because it is table value function compared to Stored Procedure ( SP ), it is a bit easier to consume
    • We can issue distinct and group by and just get the object names and not have to take entities and columns
  2. It provides detailed information regarding use-cases
    • It tells us which columns are involved in updates, selects, and so forth

 

References

  1. Microsoft
    • Docs / SQL / Relational databases / System dynamic management views
      • sys.dm_sql_referenced_entities
        • sys.dm_sql_referenced_entities (Transact-SQL)
          Link

SQL Server – keeping a few while discarding most Objects

Background

Time to look for help again.

The specific database has too many objects, but I only need to keep about twenty tables or so.

And, remove the other ones.

Script

And, so I wrote a little script.

Outline

There are two parts:

  1. dbo.udtt_Object ( Table Type )
  2. dbo.sp_ScriptDropUnTaggedObjects ( Stored Procedure )

Objects

Table Type

dbo.udtt_Object


if not exists
        (
            select *
            from  sys.types tblSST
            where tblSST.[name] = 'udtt_Object'
        ) 
begin

    CREATE TYPE [dbo].[udtt_Object] AS TABLE
    (

          [id]		bigint	not null identity(1,1) 
        , [schema]  sysname not null 
        , [object]  sysname not null

        , primary key
            (
                  [schema]
                , [object]
            )

    )

end

 

Stored procedure

dbo.sp_ScriptDropUnTaggedObjects


if object_id('dbo.sp_ScriptDropUnTaggedObjects') is null
begin

    exec('create procedure [dbo].[sp_ScriptDropUnTaggedObjects] as ')

end
go

alter procedure [dbo].[sp_ScriptDropUnTaggedObjects]
(
      @tblTable [dbo].[udtt_Object] READONLY
    , @errorIfObjectsToKeeepListIsEmpty bit = 1
)
as
begin

    set nocount on

    
    declare @tblTableCited TABLE
    (
          [id]	   int not null identity(1,1)
        , [schema] sysname not null
        , [object]  sysname not null
        , [type]    char(2) not null
        , [dropClause] 
                as case
                    when [type] = 'U' then ' Table '
                  end
    )

    declare @id smallint
    declare @table sysname 
    declare @iCount int
    declare @idMax smallint

    declare @idCited bigint
    declare @idCitedMax bigint

    declare @citedSchema	  sysname
    declare @citedObject	  sysname
    declare @citedObjectType  char(2)

    declare @citedObjectDropClause	varchar(30)

    declare @strLog nvarchar(600)
    declare @sql    nvarchar(255)

    declare @iNumberofTablesCited int


    declare @iNumberofAttempts int
    declare @iNumberofAttemptsMax int

    declare @CHAR_TAB char(1)

    set @CHAR_TAB = char(9);

    set @id = 1

    set @iCount = ( select count(*) from  @tblTable)
    set @idMax = ( select max( [id]) from  @tblTable)

    --print '@idMax ' + cast(@idMax as varchar(10))

    set @iNumberofTablesCited = -1
    set @idCited = 1

    set @iNumberofAttempts = 1
    set @iNUmberofAttemptsMax = 50


    set @strLog = 'Number of tables tagged ' 
                    + cast(isNull(@idMax, -1) as varchar(10))

    print @strLog


    if (
            (
                   ( @iCount = 0)
                or ( @idMax is null)
            )
            and 
            (
                (@errorIfObjectsToKeeepListIsEmpty = 1)
            )
       )
       begin

        set @strLog = 'Object to keep list is empty'

        raiserror(@strLog, 16,1)

        return -1

       end

    while (
                    ( @iNumberofTablesCited != 0)
                and ( @iNumberofAttempts <= @iNumberofAttemptsMax) ) begin set @strLog = @CHAR_TAB + 'Attempt ' + cast(@iNumberofAttempts as varchar(3)) print @strLog delete from @tblTableCited insert into @tblTableCited ( [schema] , [object] , [type] ) select tblSS.[name] , tblSO.[name] , tblSO.[type] from sys.objects tblSO inner join sys.schemas tblSS on tblSO.[schema_id] = tblSS.[schema_id] where tblSO.[type] = 'U' and not exists ( select tblT.[object] from @tblTable tblT where tblT.[object] = tblSO.[name] ) and not exists ( select * from sys.foreign_keys tblSFK where tblSO.[object_id] = tblSFK.[referenced_object_id] ) set @idCitedMax = ( select max( [id]) from @tblTableCited) set @iNumberofTablesCited = (select count(*) from @tblTableCited ) set @strLog = @CHAR_TAB + cast(@iNumberofTablesCited as varchar(10)) + ' objects cited' print @strLog if (@iNumberofTablesCited > 0)
        begin

            while (@idCited <= @idCitedMax)

            begin

                select
                          @citedSchema = tblTC.[schema]
                        , @citedObject = tblTC.[object]
                        , @citedObjectType = tblTC.[type]
                        , @citedObjectDropClause = tblTC.[dropClause]

                from    @tblTableCited tblTC
        
                where  tblTC.[id] = @idCited

                if (
                            ( @citedSchema is not null )
                        and ( @citedObject is not null )
                        and (@citedObjectDropClause is not null)
                    )
                begin
                
                    set @strLog =
                                    @CHAR_TAB + @CHAR_TAB + 
                                    + cast(@idCited as varchar(10))
                                    + @citedSchema
                                    + '.'
                                    + @citedObject


                    set @sql = 'DROP '
                                    + @citedObjectDropClause
                                    + quotename(@citedSchema)
                                    + '.'
                                    + QUOTENAME(@citedObject)

                    --print @sql

                    set @strLog = @CHAR_TAB + @CHAR_TAB + @sql
                
                    print @strLog

                    exec(@sql)

                end

                set @idCited = @idCited + 1

            end

        end -- @iNumberofTablesCited

        
        set @iNumberofAttempts = @iNumberofAttempts + 1

    end -- Number of retries

end
go


exec sp_MS_marksystemobject '[dbo].[sp_ScriptDropUnTaggedObjects]'
go

 

Invoke


set XACT_ABORT on
go

set nocount on
go

declare @tblTable as [dbo].[udtt_Object]

declare @commit bit

set @commit = 0

insert into @tblTable
select 'dbo', 'Users'
union
select 'dbo', 'Roles'

begin tran


    exec [dbo].[sp_ScriptDropUnTaggedObjects]
          @tblTable = @tblTable
        , @errorIfObjectsToKeeepListIsEmpty = 1

while (@@trancount > 0)
begin

    if (@commit=1)
    begin
    
        print 'commit tran';
        commit tran;
    
    end
    else
    begin
    
        print 'rollback tran';
        rollback tran;
    
    end
end

GitHub

DanielAdeniji/SQLServerScriptDropUntaggedObjects
Link

 

AWS-RDS :- Backing up SQL Database

Background

Backing up and restoring databases is one of the common tasks performed by DBAs.

Because of the regularity of the tasks, the DBA often schedules them to periodically occur and walks away.

Outside of occasionally alerts due to storage issues, life is good and fancy free.

 

AWS/RDS

Snapshots

In the Amazon RDS World, snapshots can be configured to auto-occur and life remains drama free.

 

S3 Storage

To facilitate husbandry of databases in-house and general safe-keeping, AWS now supports actual database backups.

Backup

Outline

The steps are:

  1. S3
    • Provision S3
  2. AWS Identity and Access Management IAM Role
    • Setup IAM Role
      • Setup Trust Policy for Native Backup and Restore
      • Setup Permissions Policy for Native Backup and Restore
  3. RDS Instance
    • RDS Instance Option Group
      • Option Groups
        • SQLSERVER_BACKUP_RESTORE

 

Steps

Step – S3

For the sake of brevity we will not touch upon provisioning a new S3.

We will instead focus on switching roles to a predefined role that has access to the S3 bucket.

Need to know
  1. S3 bucket
    • It is customal to have various S3 buckets in an organization
    • Determine the one pre-assigned to the group
    • Understand the folder structure
Permissions

In our original setup, my personal account did not have permission to the S3 bucket.

Permissions was granted to specific roles, and not to individual user’s personal accounts.

And, so if at first you fail to see S3 folders or the ones that you are looking for, please do not be too dissuaded,  as permissions might have been granted to specific roles instead.

 

Step – Identity & Access Management ( IAM )

Just as for S3, Identity and Access Management ( IAM ), is another big area and so we will skip the details here.

There are two areas that are important and have to be satisfied to allow RDS to access S3 buckets.  Those two areas are:

  1. Trust Policy
  2. Permission Policy
Trust Policy
Syntax

{
    "Version": "2012-10-17",
    "Statement":
    [{
        "Effect": "Allow",
        "Principal": {"Service":  "rds.amazonaws.com"},
        "Action": "sts:AssumeRole"
    }]
}

Explanation
  1. Designate that principal rds.amazonaws.com is allowed access to the S3 Service

 

Permission Policy
Syntax
{
    "Version": "2012-10-17",
    "Statement":
    [
        {
        "Effect": "Allow",
        "Action":
            [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
        "Resource": "arn:aws:s3:::bucket_name"
        },
        {
        "Effect": "Allow",
        "Action":
            [
                "s3:GetObjectMetaData",
                "s3:GetObject",
                "s3:PutObject",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload"
            ],
        "Resource": "arn:aws:s3:::bucket_name/*"
        }
    ]
}


Explanation
  1. Bucket
    • s3:ListBucket
      • List Bucket
    • s3:GetBucketLocation
      • Get S3 Bucket Location
  2. Bucket and sub-folders
    • s3:GetObjectMetaData
      • Get Object Metadata
    • s3:GetObject
      • Get Object
    • s3:PutObject
      • Put Object
    • s3:ListMultipartUploadParts
      • List Multipart upload parts
    • s3:AbortMultipartUpload
      • Abort Multipart Upload

Step – RDS Instance Option Group – Option Group – SQLSERVER_BACKUP_RESTORE

As said earlier, the option to perform database backup and restore is new to RDS.

The specific option’s name is SQLSERVER_BACKUP_RESTORE.

Need to know
  1. Review the SQL Server Instance
    • Review the Option group
      • Console
        • Sample
          • Region :- US-East-2
            • https://us-east-2.console.aws.amazon.com/rds/home?region=us-east-2#option-groups:

 

Step – Backup

After all that, we are finally at the point where we can backup the database

Metadata

The Stored Procedure that we will using is dbo.rds_backup_database.

Let us review how it should be invoked.

To do so we will use the sp_help command.

Syntax

exec msdb.dbo.sp_help 'dbo.rds_backup_database'

Output

Actual
Syntax

exec msdb.dbo.rds_backup_database 
          @source_db_name= @dbName
        , @s3_arn_to_backup_to=@s3arn
        , @overwrite_S3_backup_file=@overwrite

Sample

declare @dbName sysname
declare @s3arn  sysname
declare @overwriteS3BackupFile int

set @dbName = 'datahub';
set @s3arn = 'arn:aws:s3:::sqlServer/backup/datahub/datahub_20171207_0448PM.bak'
set @overwriteS3BackupFile = 1

-- exec  msdb.dbo.sp_help 'rds_backup_database'
exec msdb.dbo.rds_backup_database 
          @source_db_name= @dbName
        , @s3_arn_to_backup_to=@s3arn
	, @overwrite_S3_backup_file = @overwriteS3BackupFile



Output

Review
Syntax

declare @dbName sysname

exec msdb.[dbo].[rds_task_status]
	@db_name = @dbName

Sample

declare @dbName sysname

set @dbName = 'datahub';

exec msdb.[dbo].[rds_task_status]
	@db_name = @dbName


 

Output

 

References

  1. Amazon / AWS /RDS
    • AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Microsoft SQL Server on Amazon RDS » Importing and Exporting SQL Server Databases
      • Importing and Exporting SQL Server Databases
        Link
    • AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Microsoft SQL Server on Amazon RDS » Options for the Microsoft SQL Server Database Engine
      • Options for the Microsoft SQL Server Database Engine
        Link

Amazon AWS/SQL Server – Task Status

Background

Back in 2012, Amazon started offering support for MS SQL Server.

Recently they added support for whole database backup and restore.

 

Review

To review the status of current operation and past ones, one can issue a query against the msdb.dbo.rds_task_status Stored Procedure.

 

Metadata

msdb.dbo.rds_task_status

Syntax


declare @dbname  sysname
declare @taskID  int

exec msdb.dbo.rds_task_status 
		  @db_name = @dbname
		, @task_id = @taskID

Sample

Sample  – Review all tasks

SQL


declare @dbname  sysname
declare @taskID  int

exec msdb.dbo.rds_task_status 

Output

Grid

Sample  – Review tasks targeting specific database

Sample  – Review tasks targeting specific database ( datahub )


declare @dbname  sysname
declare @taskID  int

set @dbname = 'datahub'

exec msdb.dbo.rds_task_status 
		  @db_name = @dbname
		--, @task_id = @taskID

Output

Sample  – Review tasks targeting specific task

Sample  – Review tasks targeting specific database ( task )

If you initiated a task and recorded the task Number, you can go back and inquire about that task, as well.

Sample  – Review tasks targeting specific database ( task – Not Indicated)

If you indicate that you will be passing a task ID, but pass in a null one, you will get an error.


declare @dbname  sysname
declare @taskID  int

--set @taskID = 

exec msdb.dbo.rds_task_status 
     @task_id = @taskID

Output

Grid

Textual

Msg 50000, Level 16, State 1, Procedure rds_task_status, Line 36 [Batch Start Line 2]
Could not find the specified task. Execute without any parameters to show all tasks.

SQL Server – SSIS – Copying Packages Stored in the msdb database

Background

As part of a project that we are working on, we need to move SSIS packages between environments.

Storage Options

SSIS packages can be stored in few places:

  1. File System
  2. MSDB
  3. Integration Services Catalog

 

Storage – MSDB

Here is how to move them if they are stored in the MSDB Database.

 

SQL Server Management Studio

Because of DCOM, SSIS Management is very dependant on the SSIS Engine version.

And, so save yourself the headache and make sure that you are using Management Studio ( SSMS ) that matches your version.

 

Outline

  1. Launch SSMS
  2. Source
    • Connect To Source and export SSIS Package
    • Exported package should be save into File System
  3. Destination
    • Connect to Destination and import SSIS package
    • Import file saved earlier
  4. Validation

Steps

Launch SSMS

Once again launch SSMS that matches the version  of your  Database Engine

 

Connect To Source & Export Package

Steps

  1. Connect to “Integration Services” on your Source Server
  2. In the Explorer Pane on the left side of the window, navigate the Stored Packages node
    • Review the list of packages
    • Select the package
    • Right click on your selection and access the drop down menu
    • From the options availed through down menu, choose to “Export Package
    • In the “Export Package” window
      • Package Location
        • Accept the default choice of “File System”
      • Package Path
        • Please click on the button (  ) to the right of package path
        • A File Dialog appears
        • Navigate the dialog and settle on a target folder
        • Click the Save button to confirm the targeted folder
      • Review your choices
      • Press the OK button to export the package

 

Screen Shot

Connect to Server

Stored Packages

Export Package

 

Save package to path

 

Export Package

 

 

Import package to Destination

Steps

  1. Connect to “Integration Services” on your Destination Server
  2. In the Explorer Pane on the left side of the window, navigate the Stored Packages node
    • Access the msdb node
    • Review the list of packages
    • Delete or rename existing packages that share the name of the package that you will be importing
    • Right click the MSDB Node or the parent folder if you have chosen to use folders
    • From the drop-down menu, choose the “Import Package” menu item
    • In the “Import Package” window
      • Package Location
        • Accept the default choice of “File System”
      • Package Path
        • Please click on the button (  ) to the right of package path
        • A File Dialog appears
        • Navigate the dialog and access the target folder chosen earlier
        • Click the Save button to confirm the targeted folder
      • Review your choices
      • Press the OK button to import the package

 

Screen Shot

Connect to Server

Import Package – Initial

Load package

 

Import Package – Configured

Validation

Olap Helper

Olap Helper has many small SQL Scripts out there.

Olap’s blogs here.

We are going to use one of them to list our SSIS Packages

Coverage

  • Script Center > Repository > Databases > List all SSIS packages stored in msdb database
    List all SSIS packages stored in msdb database
    Link

Code


/*

	List all SSIS packages stored in msdb database. 
	sysssispackages (Transact-SQL)
	https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/sysssispackages-transact-sql
	
	List all SSIS packages stored in msdb database
	Script Center > Repository > Databases > List all SSIS packages stored in msdb database
	https://gallery.technet.microsoft.com/scriptcenter/List-all-SSIS-packages-3b247394
	

*/
SELECT 

		[package]
			= PCK.[name]

	  , [description]
			= PCK.[description]
 
	  , [folderName]
		= FLD.foldername 

	  , [packageType]
		= CASE PCK.packagetype 
			WHEN 0 THEN 'Default client' 
			WHEN 1 THEN 'SQL Server Import and Export Wizard' 
			WHEN 2 THEN 'DTS Designer' 
			WHEN 3 THEN 'SQL Server Replication' 
			WHEN 5 THEN 'SSIS Designer' 
			WHEN 6 THEN 'Maintenance Plan Designer or Wizard' 
			ELSE 'Unknown' 
		END
		 
	  , PCK.ownersid

	  ,[owner]
		= tblSSP.[name]

	  , [isEncrypted]
		= PCK.isencrypted 

	  , [createDate]
		= PCK.createdate 

	  , [version]
			= CONVERT(varchar(10), vermajor) 
				+ '.' + CONVERT(varchar(10), verminor) 
				+ '.' + CONVERT(varchar(10), verbuild) --AS version 

	  , versionComment 
		= PCK.vercomments

	  , [packageSize]
		= DATALENGTH
			(
				PCK.packagedata
			) 

FROM msdb.dbo.sysssispackages AS PCK 

INNER JOIN msdb.dbo.sysssispackagefolders AS FLD 
	ON PCK.folderid = FLD.folderid 


LEFT OUTER JOIN sys.server_principals tblSSP

	ON PCK.ownersid = [tblSSP].[sid]

ORDER BY 
		PCK.[name]


Ouput