Wednesday, June 5, 2013

Interview Questions in SQL Server Database


What are DMVs?

Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:

SELECT * FROM sys.dm_os_wait_stats;

Another example is examining current sessions, much like the sp_who2 command:

SELECT * FROM sys.dm_exec_sessions;

What are temp tables? What is the difference between global and local temp tables?

Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character is used to declare a temporary table as it is prepended to the table name. A single hash (#) specifies a local temporary table.

CREATE TABLE #tempLocal ( nameid int, fname varchar(50), lname varchar(50) )

Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects.

Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed.

CREATE TABLE ##tempGlobal ( nameid int, fname varchar(50), lname varchar(50) )

Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.

How are transactions used?

Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.

What is the difference between a clustered and a nonclustered index?

A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.

A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.

You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book’s index.

What are DBCC commands?

Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases — maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples:

DBCC CHECKALLOC — Check disk allocation consistency.

DBCC OPENTRAN — Display information about recent transactions.

DBCC HELP — Display Help for DBCC commands.

What is the difference between truncate and delete?

Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.

What does the NOLOCK query hint do?

Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data — you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.

SELECT * FROM table_name (NOLOCK)

Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint

What is a CTE?

A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:

WITH ExampleCTE (id, fname, lname)

AS

(

SELECT id, firstname, lastname FROM table

)

SELECT * FROM ExampleCTE

A CTE can be used in place of a view in some instances.

What is a view? What is the WITH CHECK OPTION clause for a view?

A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables.

The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.

What is a query execution plan?

SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

What does the SQL Server Agent Windows service do?

SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned — often it is as simple as the SQL Server Agent service not running.

What is the default port number for SQL Server?

If enabled, the default instance of Microsoft SQL Server listens on TCP port 1433. Named instances are configured for dynamic ports, so an available port is chosen when SQL Server starts. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

The list of possible questions is endless. I am sure these questions will spawn debate and discussion.

 

Tuesday, February 7, 2012

Job step wise duration counts

select step_id [Step No],step_name [Step Name],case when LastRunDuration is null then NULL else STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(LastRunDuration), '19000101'), 8), 1, 2, CAST(LastRunDuration / 3600 AS VARCHAR(12))) end LastRunDuration
   FROM (select step_id,step_name, CASE
          WHEN run_duration > 0
          THEN
               (run_duration / 1000000) * (3600 * 24)
             + (run_duration / 10000 % 100) * 3600
             + (run_duration / 100 % 100) * 60
             + (run_duration % 100)
          ELSE
             NULL
       END AS LastRunDuration from msdb.dbo.sysjobhistory where job_id='A09DCA56-E235-4A87-A3D7-C3BB9D823F20' and run_status=1 and step_id <> 0
   and run_date >= (select MAX(run_date) from msdb.dbo.sysjobhistory where step_name='CHECK_job_to_complete')
   ) T
  

Job Schedule and Execution Details

IF EXISTS (SELECT *
           FROM    tempdb.dbo.sysobjects
           WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[JobHistoryInformation]')
                )
        DROP TABLE [tempdb].[dbo].[JobHistoryInformation]
GO
CREATE TABLE [tempdb].[dbo].[JobHistoryInformation]
(
        job_id uniqueidentifier NOT NULL,
         last_run_date nvarchar (20) NOT NULL,
         last_run_time nvarchar (20) NOT NULL,
         next_run_date nvarchar (20) NOT NULL,
         next_run_time nvarchar (20) NOT NULL,
         next_run_schedule_id INT NOT NULL,
         requested_to_run INT NOT NULL,
         request_source INT NOT NULL,
         request_source_id sysname
                   COLLATE database_default NULL,
         running INT NOT NULL,
         current_step INT NOT NULL,
         current_retry_attempt INT NOT NULL,
         job_state INT NOT NULL
 )
    
DECLARE @job_owner   sysname
DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()
        
INSERT INTO [tempdb].[dbo].[JobHistoryInformation]
   EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

SELECT job_name,StartTime ScheduledStartTime,EndTime ScheduledEndTime,next_scheduled_run_date,
LastRunOutcome,case when LastRunDuration is null then NULL else STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(LastRunDuration), '19000101'), 8), 1, 2, CAST(LastRunDuration / 3600 AS VARCHAR(12))) end LastRunDuration,
schedule_name,[Description],Freq_Type,Freq_Interval
FROM
(
SELECT SJ.name as job_name
, SJ.enabled as is_job_enabled
, SS.enabled as is_schedule_enabled
/*, ISNULL(SJ.description, '') as job_desc*/
, SS.name as schedule_name
, CASE freq_type
WHEN 1 THEN 'Occurs on ' + STUFF(RIGHT(active_start_date, 4), 3,0, '/') + '/' + LEFT(active_start_date, 4) + ' at '
+ REPLACE( RIGHT(CONVERT(varchar(30),
CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime) /* hh:mm:ss 24H */
, 9), 14), ':000', ' ') /* HH:mm:ss:000AM/PM then replace the :000 with space.*/
WHEN 4 THEN 'Occurs every ' + CAST(freq_interval as varchar(10))
+ ' day(s) '
+ CASE freq_subday_type
WHEN 1 THEN 'at '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' hour(s)'
ELSE '' END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ' between '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
+ ' and '
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 8 THEN
'Occurs every ' + CAST(freq_recurrence_factor as varchar(10))
+ ' week(s) on '
+
REPLACE( CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
+ '|', ', |', ' ') /* get rid of trailing comma */
+ CASE freq_subday_type
WHEN 1 THEN 'at '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' hour(s)'
ELSE '' END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ' between '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
+ ' and '
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 16 THEN
'Occurs every ' + CAST(freq_recurrence_factor as varchar(10))
+ ' month(s) on '
+ 'day ' + CAST(freq_interval as varchar(10)) + ' of that month '
+ CASE freq_subday_type
WHEN 1 THEN 'at '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' hour(s)'
ELSE '' END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ' between '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
+ ' and '
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 32 THEN
'Occurs ' +
CASE freq_relative_interval
WHEN 1 THEN 'every first '
WHEN 2 THEN 'every second '
WHEN 4 THEN 'every third '
WHEN 8 THEN 'every fourth '
WHEN 16 THEN 'on the last '
END
+ CASE freq_interval WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'day'
WHEN 9 THEN 'weekday'
WHEN 10 THEN 'weekend'
END
+ ' of every ' + CAST(freq_recurrence_factor as varchar(10)) + ' month(s) '
+ CASE freq_subday_type
WHEN 1 THEN 'at '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
WHEN 2 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' second(s)'
WHEN 4 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' minute(s)'
WHEN 8 THEN 'every ' + CAST(freq_subday_interval as varchar(10)) + ' hour(s)'
ELSE '' END
+ CASE WHEN freq_subday_type in (2,4,8) /* repeat seconds/mins/hours */
THEN ' between '+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
+ ' and '
+ LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' '))
ELSE ''
END
WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
WHEN 128 THEN 'Runs when the computer is idle'
END as [Description]
, CASE freq_type
WHEN 1 THEN 'One Time'
WHEN 4 THEN CASE freq_subday_type WHEN 1 THEN 'Daily'
WHEN 2 THEN 'Second-ly'
WHEN 4 THEN 'Minutely'
WHEN 8 THEN 'Hourly'
ELSE ''
END
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly, relative to freq_interval'
WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
WHEN 128 THEN 'Runs when the computer is idle'
END As [Freq_Type]
, CASE freq_subday_type
WHEN 1 THEN 'At the specified time'
WHEN 2 THEN 'Every ' + CAST(freq_subday_interval as varchar(10)) + ' Second(s)'
WHEN 4 THEN 'Every ' + CAST(freq_subday_interval as varchar(10)) + ' Minute(s)'
WHEN 8 THEN 'Every ' + CAST(freq_subday_interval as varchar(10)) + ' Hour(s)'
ELSE ''
END AS freq_subday_type
, CASE
WHEN freq_type = 4 /* (daily) */ THEN 'Every ' + CAST(freq_interval as varchar(10)) + ' Day(s)'
WHEN freq_type = 8 /* (weekly) */ THEN
REPLACE( CASE WHEN freq_interval&1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
+ '|', ', |', ' ') /* get rid of trailing comma */
WHEN freq_type = 16 THEN 'On Day ' + CAST(freq_interval as varchar(10)) + ' of Every Month'
WHEN freq_type = 32 /* (monthly) */ THEN 'Every ' +
CASE freq_interval WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'Day'
WHEN 9 THEN 'Weekday'
WHEN 10 THEN 'Weekend day'
END
ELSE ''
END as [freq_interval]
, CASE WHEN freq_type = 8 /* (weekly) */ THEN 'Every ' + CAST(freq_recurrence_factor as varchar(10)) + ' Week(s)'
WHEN freq_type = 16 /* (monthly) */ THEN 'Every ' + CAST(freq_recurrence_factor as varchar(10)) + ' Month(s)'
WHEN freq_type = 32 /* (monthly relative) */ THEN 'Every ' + CAST(freq_recurrence_factor as varchar(10)) + ' Week(s)'
ELSE ''
END as freq_recurrence_factor
, LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' ')) as starttime
, LTRIM(REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time as varchar(10)), 6), 3, 0, ':' ), 6, 0, ':' ), 8) as datetime), 9), 14), ':000', ' ')) as endtime
, STUFF(STUFF(active_start_date, 5, 0, '/'), 8, 0, '/') as schedule_start_date
, STUFF(STUFF(active_end_date, 5, 0, '/'), 8, 0, '/') as schedule_end_date
 ,ja.next_scheduled_run_date
 ,CASE h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Completed' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'Running' END AS LastRunOutcome
       ,CASE
          WHEN h.run_duration > 0
          THEN
               (h.run_duration / 1000000) * (3600 * 24)
             + (h.run_duration / 10000 % 100) * 3600
             + (h.run_duration / 100 % 100) * 60
             + (h.run_duration % 100)
          ELSE
             NULL
       END AS LastRunDuration
FROM msdb.dbo.sysjobs SJ
LEFT Join [tempdb].[dbo].[JobHistoryInformation] x
ON x.job_id = SJ.job_id
LEFT JOIN msdb.dbo.sysjobschedules SJS
ON SJ.job_id = SJS.job_id
LEFT JOIN msdb.dbo.sysschedules SS
ON SJS.schedule_id = SS.schedule_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date
          AND x.last_run_time = h.run_time AND h.step_id = 0
LEFT OUTER JOIN msdb.dbo.sysjobactivity ja on ja.job_history_id=h.instance_id and ja.job_id=SJ.job_id
) TEMPTA

Thursday, February 2, 2012

Logging details from sysssislog

select Package,
source,
StepId GroupId,
STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(TimeInMinutes), '19000101'), 8), 1, 2, CAST(TimeInMinutes / 3600 AS VARCHAR(12))) Duration,
TimeInMinutes TimeInSeconds,
[Status]
from (
select
A.starttime,
A.id,
c.source Package,
A.[source],
DENSE_RANK() OVER (order by A.executionID ) StepID,
CONVERT(VARCHAR(100),DATEDIFF(SECOND,A.starttime,ISNULL(B.endtime,GETDATE()))) TimeInMinutes,
CASE WHEN B.endtime IS NULL THEN ' (running)' ELSE '' END AS [Status]
from
(select * from sysssislog where event = 'OnPreExecute') As A LEFT JOIN
(select * from sysssislog where event = 'OnPostExecute') As B ON A.sourceid = B.sourceid and A.executionid = B.executionid
LEFT JOIN (select * from
(select source,executionid,Row_Number() over (PARTITION by executionid order by executionid) RowId from sysssislog where event = 'OnPreExecute' )
t where RowId=1) C on C.source=A.source and C.executionid=a.executionid
) t
order by StepID,id

Tuesday, January 10, 2012

Parallel execution in SSIS


Parallel execution in SSIS improves performance on computers that have multiple physical or logical processors. To support parallel execution of different tasks in a package, SSIS uses two properties: MaxConcurrentExecutables and EngineThreads.

If SSIS runs on a dedicated server and you have a lot of operations that run in parallel, you will likely want to increase this setting if some of the operations do a lot of waiting for external systems to reply. On the other hand, if you do not have a dedicated SSIS machine and your data integration application runs alongside several other applications, you may need to reduce this setting to avoid resource conflicts.

The MaxConcurrentExecutables property is a property of the package. This property defines how many tasks can run simultaneously by specifying the maximum number of executables that can execute in parallel per package. The default value is -1, which equates to the number of physical or logical processors plus 2.

Please note that if your box has hyper threading turned on, it is the logical processor rather than the physically present processor that is counted.

The EngineThreads property is a property of each Data Flow task. This property defines how many threads the data flow engine can create and run in parallel. The EngineThreads property applies equally to both the source threads that the data flow engine creates for sources and the worker threads that the engine creates for transformations and destinations. Therefore, setting EngineThreads to 10 means that the engine can create up to ten source threads and up to ten worker threads. The default is 5 in SQL Server 2005 and 10 in SQL Server 2008, with a minimum value of 2.

One thing we want to be clear about EngineThreads is that it governs both source threads (for source components) and work threads (for transformation and destination components). Source threads and work threads are both engine threads created by the Data Flow’s scheduler.

One other thing to consider: If you are using the Execute Package Task, the child package to be executed can be run in-process or out-of-process by use of the ExecuteOutOfProcess property. If a child package is executed out-of-process, you will see another dtshost.exe process start. These processes will remain “live”, using up resources, for quite a while after execution is complete.

If executing in-process, a bug in a task of the child package will cause the master package to fail. Not so if executing out-of-process. On 32-bit systems a process is able to consume up to 2GB of virtual memory. Executing out-of-process means each process can claim its own 2GB portion of virtual memory. Therefore if you are simply using many packages to structure your solution in a more modular fashion, executing in-process is probably the way to go because you don’t have the overhead of launching more processes.

A thread will process one buffer at a time, executing it against all transforms in the execution tree before working on the next buffer in the flow, at which point it would pass the current buffer to another thread executing another execution tree and it would pull a new data buffer from its buffer list which was queued from an upstream component (either a data source or the last asynchronous transform before this execution tree started).

However, the general rule is to not run more threads in parallel than the number of available processors. Running more threads than the number of available processors can hinder performance because of the frequent context-switching between threads.

MaxConcurrent

·         This is a property on the ForEachLoop which says how many instances of the loop contents can be run in parallel.

Example:

Suppose we have a package with 3 Data Flow Tasks. Each task has 10 flows in the form of “OLE DB Source -> SQL Server Destination”.

Set MaxConcurrentExecutables to 3, then all 3 Data Flow Tasks will run simultaneously.



Now whether all 10 flows in each individual Data Flow Task get started concurrently is a different story. This is controlled by the second property: EngineThreads.



The EngineThreads is a property of the Data Flow Task that defines how many work threads the scheduler will create and run in parallel. Its default value is 5.  

If we set EngineThreads to 10 on all 3 Data Flow Tasks, then all the 30 flows will start off at once.


Thursday, December 29, 2011

SSIS Performance Counters


SQL Server Integration Services provide a set of performance counters. Among them the following few are helpful when you tune or debug your package:

·    Buffers in use

·    Flat buffers in use

·    Private buffers in use

·    Buffers spooled

·    Rows read

·    Rows written



“Buffers in use”, “Flat buffers in use” and “Private buffers in use” are useful to discover leaks. During package execution time, you will see these counters fluctuating. But once the package finishes execution, their values should return to the same value as what they were before the execution. Otherwise, buffers are leaked.



“Buffers spooled” has an initial value of 0. When it goes above 0, it indicates that the engine has started memory swapping. (“Set BLOBTempStoragePath and BufferTempStoragePath to Fast Drives”) to set Data Flow Task properties BLOBTempStoragePath and BufferTempStoragePath appropriately for maximal I/O bandwidth.

BLOBTempStoragePath and BufferTempStoragePath are two properties on Data Flow Task. They define where on the hard disk(s) to page buffer data to. BLOBs (text, next, image) are written to BLOBTempStoragePath. All other buffer data will be swapped to BufferTempStoragePath. See the figure below for where these two properties are exposed in designer.



Now what could cause a buffer to swap? There are two possible causes. The first one is when a memory allocation fails. The second one is when Windows signals the low memory resource notification event. Both will trigger SSIS to reduce its working set. SSIS does so by moving buffer data to disk.

When BLOBTempStoragePath is not set, the paths as defined by the system variables TEMP and TMP will be used. The same rule applies to BufferTempStoragePath.

For better performance, we recommend both BLOBTempStoragePath and BufferTempStoragePath point to fast drives. We also suggest that the drives for BufferTempStoragePath and BlobTempStoragePath be on separate spindles in order to maximize I/O throughput.

“Rows read” and “Rows written” show how many rows the entire Data Flow has processed. They give you an overall idea about the execution progress.

Developing a Custom Data Flow Component in SSIS


Introduction

SSIS is a great ETL tool and has been there now for a long time. But not always we get all the desired component that we need for transformation. For that we go ahead with Script component. But the disadvantage being that it is application specific. If we need to do the same kind of operation in other SSIS packages, then script component won't help. Instead we can go ahead with our own custom transformation component and even that can be reusable. This article will focus on building, deploying and using a custom script transformation component in a step by step manner

SSIS transformation components helps us to transform the source data to some desirable format. Though SSIS provides many transformation components, but, sometime we may need to perform certain actions for which there is no components available. In such cases custom components comes very handy. In this article we will look into how we can make a custom SSIS transformation component. The custom component is a simple email validator that will read the emails from some source and will let us know if it is a valid email or not. We will use C# as the language of choice.

Steps for Implementation 

Step 1:

To begin with, let us create a Class Library project (say DFCEmailValidator).

Step 2:

Once the project is created, let us add the following dll references in our project.

Microsoft.SqlServer.Dts.Design

Microsoft.SqlServer.DTSPipelineWrap

Microsoft.SqlServer.DTSRuntimeWrap

Microsoft.SqlServer.ManagedDTS

Microsoft.SqlServer.PipelineHost

Step 3:

Let us create a class by the name CustomEmailValidator.cs .Inherit the class from the PipelineComponent class and decorate it with DtsPipelineComponent attribute



Step 4:

Build and deploy the component into the Gac

Now, we should build the class library, generate a Strong name and then deploy it to the gac by using the command

C:\Program Files\Microsoft Visual Studio 9.0\VC>gacutil  -i "D:\DFCEmailValidator\bin\Debug\DFCEmailValidator.dll"



N.B.~ The dll path will vary... So change it accordingly

If successful we will get the message

Assembly successfully added to the cache


We can even verify this browsing to C:\WINDOWS\assembly where we will find the assembly by the name DFCEmailValidator


Step 5: Copy the dll and put into the PipeLineComponents folder

Once the previous step is over, next we can copy the dll to the PipelineComponents folder which will be in C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents.


Step 6:

Open BIDS. Go to the DataFlow editor’s toolbox. Right Click -> Choose Items.Visit the SSIS DataFlow Items tab and locate the CustomEmailValidator. Tick the checkbox option and click the OK button.


We can find that the custom transformation has been added to the toolbox


Step 7:

Drag and drop a Flat File Source and configure it. Let’s say we have a text file (Source.txt) whose content is as under

testmailwrong

testmail@test.com

invalid@invalid

valid@gmail.com

valid_123@yahoo.co.in

123@123@123@1123#456~xud.com

Next drag and drop our CustomEmailValidator and add the data flow path from Flat File Source to it. Double click on the CustomEmailValidator for opening the Editor.


The screen shows that it has three tabs viz Custom Properties, Input columns and Input Output Properties.

The first tab shows the Custom Properties that we set.

Visiting the Input Columns tab we find the available input columns and that needs to be selected. In this case it is Column0.


And in the InputOutput Properties, if we expand the Output columns , we can find that our Output column has been created automatically whose name is IsValidEmail_Column0.


Click Ok.Next drag and drop a Conditional Split and add the data flow path from the CustomEmailValidator to it. And Configure the Conditional Split component as under


Output Name
Condition
Success
[IsValidEmail_Column0] == TRUE
Failure
[IsValidEmail_Column0] == FALSE

Lastly add two flat file destination component one of whose source will be the success path of the Conditional path and the other being the Failure. The destination files are named respectively as ValidEmail.txt and InvalidEmail.txt.

The entire package is as under


Step 8:

Let us run the application and we will see the valid emails in ValidEmail.txt

testmail@test.com

valid@gmail.com

valid_123@yahoo.co.in

while the InvalidEmail.txt contains the content as

testmailwrong

invalid@invalid

123@123@123@1123#456~xud.com