SQL SERVER – SET STATISTICS IO – DROPCLEANBUFFERS- SET Statement

SET STATISTICS IO : This set statement gives information about the disk IO activities of the T-SQL statements executed in the current session.

It displays disk information when it is set to ON and it hides the disk information when it is set to OFF.

Usage

SET STATISTICS IO ON

or

SET STATISTICS IO OFF

Disk activity information

Sample output when STATISTICS IO is ON

DBCC DROPCLEANBUFFERS

It cleans all buffers from the buffer pool and it helps user to see the actual query performance without using cache data. It should be the first step before we start query performance tuning.

Note: We should not execute this DBCC command in the production server.Because, it will remove all the cached data from the buffer and make the query execution slower for the all new queries.

Example

When STATISTICS IO is ON and running a SQL query in the query windows

DBCC DROPCLEANBUFFERS
GO

set statistics io on
go

select * from [Person].[PersonPhone]

set statistics io off
go

Statistics io data is very important while doing query performance tuning. I hope you have learned about STATISTICS IO set statement from this blog post. Please let me know if you have any questions.

SQL SERVER – SET SHOWPLAN_XML – SET Statement

SET SHOWPLAN_XML : This set statement is used to get the detailed information about the transact SQL statement’s execution (execution plan) without executing the statements. If we set this set statement to ON then the SQL Server returns only the details of the T-SQL statements query plan for the current session until it is turn OFF.

SQL Server returns XML execution plan output of type nvarchar(max). The XML schema definition can be download from this link.

Note: SHOWPLAN_XML statement cannot be used inside the stored procedure.

Usage

SET SHOWPLAN_XML ON

or

SET SHOWPLAN_XML OFF

Example

when ShowPlan_XML is ON,

If we click the XML link, graphical query execution plan will be shown in a separate window.

Dynamic Management View

Query plans can be obtained using the DMV sys.dm_exec_query_plan

Example

select * from sys.dm_exec_query_plan
((select plan_handle from sys.dm_exec_requests where session_id = {User Session ID}))

SQL SERVER – SET SHOWPLAN_TEXT – SET Statement

SET SHOWPLAN_TEXT : This set statement is used to get the detailed information about the transact SQL statement’s execution (execution plan) without executing it. If we set this statement to ON then the SQL Server returns only the statement execution plan of the T-SQL statements in the current session until it is turn OFF. But, when you need more execution plan details such as resource required, estimated memory, estimated rows and etc, we have to use SET SHOWPLAN_ALL.

Note: SHOWPLAN_TEXT statement cannot be used inside the stored procedure.

Usage

SET SHOWPLAN_TEXT ON

or

SET SHOWPLAN_TEXT OFF

Output information

Example

When SHOWPLAN_TEXT ON, the current session shows only the execution plan details for the T-SQL statements.

SQL SERVER – SET SHOWPLAN_ALL – SET Statement

SET SHOWPLAN_ALL : This set statement is used to get the detailed information about the transact SQL statement’s execution (execution plan) without executing it. If we set this statement to ON then the SQL Server returns only the details of the T-SQL statements in the current session until it is turn OFF.

Note: SHOWPLAN_ALL statement cannot be used inside the stored procedure.

Usage

SET SHOWPLAN_ALL ON

or

SET SHOWPLAN_ALL OFF

Output information

Example

When SET SHOWPLAN_ALL is ON

SQL SERVER – SET FORCEPLAN – SET Statement – JOIN HINTS

SET FORCEPLAN ON – This set statement tell the SQL Query Optimizer to process the join tables as given in the user specified order and use Nested Loop Join. Unless user specified an explicit  query or join hint and the query optimizer does not require to use other join to construct the actual plan for execution.

SET FORCEPLAN OFF – This tell the SQL Query Optimizer to construct the optimized plan on it own.

Note: SET FORCEPLAN and Hits are used to override the logic used by the query optimizer. So, avoid using hints or Forceplan unless it is necessary. Because SQL Server query optimizer typically construct and select the best execution plan for a query.

Usage

SET FORCEPLAN ON

or

SET FORCEPLAN OFF

Example

I have 3 tables (tblEmployee, tblJobTitle and tblDepartment) in my database. I am going to generate actual query execution plan using the SQL Query given below with FORCEPLAN ON or OFF.

When SET FORCEPLAN OFF,  SQL Server query optimizer uses its own plan and we can clearly see in the execution plan that the order of the join table is different and it uses hash match (inner join) for table join.

When SET FORCEPLAN is ON, SQL Server query optimizer uses the join table in the order that use specified and it uses Nested Loops (inner join) to join tables.

I hope you enjoy reading this post. Please write you comment and question in the comment section. Happy coding.

SQL SERVER – SET ANSI_PADDING – SET Statement

ANSI_PADDING is used to control the tailing white spaces and zeros stored in the char, varchar, binary and varbinary data typed column. This set statement is used to trim or add white spaces or zeros at the end of the character and binary data stored in a column.

Note: In the future version of SQL Server (SQL SERVER 2014) ANSI_PADDING will be ON and we cannot turn it OFF.

Usage

SET ANSI_PADDING ON

When ANSI_PADDING is ON and

If we use the char data type, it adds white space at the end of the string if the length of the value is less than the column size.

If we use binary data type, it adds zero at the end of the binary value if the length of the value is less than the column size.

Example for ANSI_PADDING ON

1. Handling char and binary columns in a table

SQL Query

SET ANSI_PADDING ON

Drop table tblpadd
GO

create table tblPadd
(
CHAR_name1 char(5),
BINARY_name1 binary(5)
)

insert into tblpadd (CHAR_name1, BINARY_name1) values(‘AB’, 0×2)
GO

Select CHAR_name1, BINARY_name1 from tblpadd
GO

Output

If the data types are varchar and varbinary and ANSI_PADDING is ON than it does not remove tailing blank spaces in the varchar column and tailing zeros in varbinary column.

SET ANSI_PADDING OFF

When ANSI_PADDING is OFF and the data types are char and binary than it adds tailing space to the character column and tailing zeros to the binary column.

But, when ANSI_PADDING is OFF and the data types are varchar and varbinary than it removes the tailing white space from the varchar column and removes tailing zeros from the varbinary column.

Example for ANSI_PADDING OFF

1. Using varchar and varbinary column to store values with tailing space and tailing zeros.

we can see it the output that SQL Server removed tailing space from the varchar column and tailing zeros from the varbinary column.

I hope you have understood the concept and the use of ANSI_PADDING set statement. Happy coding!

 

 

Changing Files and Folders Attribute using SSIS – Integration Services – SQL SERVER

We know there are two types of attributes available for file and folder in windows system. Those are Read Only and Hidden. Read Only attribute setting assign read only access to a file or folder and we cannot delete it. In the same way Hidden attribute settings hides a file or folder from the users eyes.

Recently, I was asked to design a package to read and update all files in a folder and sub-folders in a directory. But, files and folders in that directory has read-only access. So, I could update those files. I know, we can use script task and write a piece of code using file system object to change the file attribute in this situation. But, I am lazy person and want to do everything in simple method. When I say the word lazy,  Bill Gates quote about lazy person comes from my memory. let talk about my solution.

I remember a command prompt command name Attrib. This command is used to manages files and folders attribute property. So, I just used this command in Execute Process Task to remove read only attribute property from all files in that directory and complete my file update operation and then I set Read Only attribute to all files again.

Attrib command and it’s argument definition.

Implementation

I am using my test folder named MyFiles. In this directory I have 4 text files, 2 of them are in root directory and other 2 are in a sub-folder. Below given image shows my folder structure.

Steps to use Attrib command in Execute Process Task.

1: Create a empty package and add Execute Process Task

Step 2: Edit this task and fill the values to the necessary property.

Properties used in this task

Executable : C:\Windows\System32\Attrib.exe

Arguments : -r /s

WorkingDirectory : [Root folder]

Step 3: Execute the package and see the attributes of the files.

I think you do like this method. If you want to apply Read Only property again to all files in  the directory than use the Execute Process Task and use the following values for the necessary property.

Executable : C:\Windows\System32\Attrib.exe

Arguments :  +r /s

WorkingDirectory : [Root folder]

I believe, this blog post help you in your project and saves your time. Happy coding!