Trace SSIS variables value

Insert Script Task Editor in Control Flow and use Visual Basic language

Insert the below lines between Public Sub Main() and End Sub

TypeVariableName is variable Name


Public Sub Main()
Dim variableList As Variables

Dts.VariableDispenser.LockOneForRead("TypeVariableName", variableList)

MsgBox(variableList("TypeVariableName").Value.ToString())
variableList.Unlock()

Dts.TaskResult = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

End Sub

Missing Indexes and Building Queries to Create Indexes

SELECT


[Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),

[Table] = [statement],

[CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_'

+ sys.objects.name COLLATE DATABASE_DEFAULT

+ '_'

+ REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')+ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_')

+ ' ON '

+ [statement]

+ ' ( ' + IsNull(mid.equality_columns, '')

+ CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE

CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END

+ mid.inequality_columns END + ' ) '

+ CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END

+ ';',

mid.equality_columns,

mid.inequality_columns,

mid.included_columns

FROM sys.dm_db_missing_index_group_stats AS migs

INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle

INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID

WHERE (migs.group_handle IN

(SELECT TOP (500) group_handle

FROM sys.dm_db_missing_index_group_stats WITH (nolock)

ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))

AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1

ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

GO   USER Seeks:   SELECT D.index_handle, [statement] AS full_object_name, unique_compiles,

avg_user_impact, user_scans, user_seeks, column_name, column_usage

FROM sys.dm_db_missing_index_groups G

JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle

JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle

CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC

ORDER BY D.index_handle, [statement];

SQL Server Blocking DBCC Inputbuffer Script

SELECT spid,dbid,DB_NAME(dbid),status,hostname,program_name,*


FROM MASTER.SYS.sysprocesses

WHERE blocked <>0

GO

SELECT 'DBCC INPUTBUFFER(' + CAST(spid AS VARCHAR) + ')' ,dbid,DB_NAME(dbid),status,hostname,program_name

FROM MASTER.SYS.sysprocesses

WHERE blocked <>0

GO

SELECT 'KILL ' + CAST(spid AS VARCHAR) + ';GO' ,dbid,DB_NAME(dbid),status,hostname,program_name

FROM MASTER.SYS.sysprocesses

WHERE blocked <>0

DBCC INPUTBUFFER(70)

Turn On Deadlock Trace

Reference:  http://sqlserverplanet.com/troubleshooting/turn-on-deadlock-trace-flag

Reference: http://www.sqlservercentral.com/articles/deadlock/65658/

Reference: http://www.sqlserver-training.com/how-to-trace-deadlock-in-sql-server/-

SQL Server Blocking Report using DMV

Blocking Information with details:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
GETDATE() as DateTime
,Waits.wait_duration_ms / 1000 AS WaitInSeconds
, Blocking.session_id as BlockingSessionId
, Sess.login_name AS BlockingUser
, Sess.host_name AS BlockingLocation
, BlockingSQL.text AS BlockingSQL
, Blocked.session_id AS BlockedSessionId
, BlockedSess.login_name AS BlockedUser
, BlockedSess.host_name AS BlockedLocation
, BlockedSQL.text AS BlockedSQL
, DB_NAME(Blocked.database_id) AS DatabaseName
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked
ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_exec_sessions Sess
ON Blocking.session_id = sess.session_id
INNER JOIN sys.dm_tran_session_transactions st
ON Blocking.session_id = st.session_id
LEFT OUTER JOIN sys.dm_exec_requests er
ON st.session_id = er.session_id
AND er.session_id IS NULL
INNER JOIN sys.dm_os_waiting_tasks AS Waits
ON Blocked.session_id = Waits.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)
AS BlockingSQL
INNER JOIN sys.dm_exec_requests AS BlockedReq
ON Waits.session_id = BlockedReq.session_id
INNER JOIN sys.dm_exec_sessions AS BlockedSess
ON Waits.session_id = BlockedSess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
WHERE Waits.wait_duration_ms > 3000 -- This is 3 seconds.
--You can set it up based on your preferable seconds
ORDER BY WaitInSeconds

Lead Blocker Script for SQL Server

Find LeadBlocker from sysprocesses.

SELECT

spid
,sp.STATUS
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(CHAR(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0

BCP and Bulk Insert

The below script will avoid if the columns in the table does have comma or tab space. If it is the case, the below script will take care since the terminator is ** along with tab **



BCP "SELECT * FROM DBName.dbo.TableName (NOLOCK)" QUERYOUT H:\EXPORT\TableName.txt -t "**\t**" -S ServerName -c -T

--Truncate table DBNAME.dbo.TableName

SET identity_insert dbo.TableName on -- This will apply only if you have identity or auto increment
bulk insert dbo.TableName from 'C:\EXPORT\TableName.txt'
with (batchsize=10000,tablock,FIELDTERMINATOR='**\t**',Firstrow=1)
SET identity_insert dbo.TableName off -- This will apply only if you have identity or auto increment

Trim and Join Two Columns in Excel With Comma

=SUBSTITUTE(TRIM(C2& " " & D2), " ", ", ")

SQL Server Management Studio Keyboard Shortcuts

SQL Server Management Studio Short Cuts through Keyboard is in the below link is very useful

http://msdn.microsoft.com/en-us/library/ms174205(v=sql.90).aspx

Performance Counters

The following list of performance counters have been modified to include the most pertinent in identifying the bottleneck of your system. This list is for reference purposes. If needing to troubleshoot slowness read troubleshooting sql server slowness.


Memory

Object: – Memory

Counter: – Available Mbytes

Preferred Value: – > 2000MB

Description: With 64 bit OS’s the available bytes recommendation has increased.



Object: – Memory

Counter: – Pages/Sec

Preferred Value: – < 50

Description: - Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. Indicative of possible page file usage, and definitely a memory bottleneck somewhere on the system.



Object: - Paging File

Counter: - %Usage

Preferred Value: - < 5%

Description: - Current amount of Page file being used. This is a key number when weighing the amount of memory allocated to the OS. If this number is high, then the OS is choked for RAM. Either increase the RAM on the box or deallocate from SQL. If this is between 10% and 25% you should reboot.



Object: - Paging File

Counter: - %Usage Peak

Preferred Value: - < 15%

Description: - Show the peak the page file got to since the last reboot. Usually this is not too far off from the page file usage %. Unfortunately when page file issues happen, they do not correct themselves without a reboot.



Object: - SQL Server:Buffer Manager

Counter: - Page Life Expectancy

Preferred Value: - > 300

Description: – The single most important factor in determining whether your SQL Server has enough RAM allocated to it. The greater the number the better off you are. Historically it is said that over 300 is good. If this number is low and you do have a lot of RAM allocated, then the issue is probably lack of indexes. Index scans read in a lot of rows into the buffer pool whereas a seek only reads in a small subset. Investigate indexes.



Object: – SQL Server:Buffer Manager

Counter: – Buffer Cache hit ratio

Preferred Value: – > 90%

Description: – Percentage of pages that were found in the buffer pool without having to incur a read from disk. If this number is low, then you are probably reading from the paging file. Reboot.

Reference: –



Disk Bottleneck Analysis

Object: – PhysicalDisk

Counter: – Avg. Disk Sec/Read

Preferred Value: – < 8ms

Description: - Measure of disk latency. Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk. While it is better to have fast disk read times, this can easily be compensated for by allocating enough RAM to the server and to SQL.

More Info:

Reads or non cached Writes

Excellent < 08 Msec ( .008 seconds )

Good < 12 Msec ( .012 seconds )

Fair < 20 Msec ( .020 seconds )

Poor > 30 Msec ( .030 seconds )



Object: – PhysicalDisk

Counter: – Avg. Disk sec/Write

Preferred Value: – < 8ms (non cached) < 1ms (cached)

Description: - Measure of disk latency. Avg. Disk sec/Write is the average time, in seconds, of a write of data to the disk.

Reference: -



Object: - PhysicalDisk

Counter: - Avg. Disk Read Queue Length

Preferred Value: - < 2 * spindles

Description: - Avg. Disk Read Queue Length is the average number of read requests that were queued for the selected disk during the sample interval. If this is high, you are probably missing indexes; doing deletes where you could be doing truncates; or could simply be selecting too much data.

More Info:

< (2+ no of spindles) Excellent

< (2*no of spindles) Good

< (3* no of spindles) Fair



Note: If the disk has say 20 disk and it is RAID 10 then no. of spindles = 20/2 = 10. If it is RAID 5 then the no. of spindles = no of disks = 20.

Reference: -



Object: - PhysicalDisk

Counter: - Avg. Disk Write Queue Length

Preferred Value: - < 2 * spindles

Description: - Avg. Disk Write Queue Length is the average number of write requests that were queued for the selected disk during the sample interval.

Reference: -



Object: - SQL Server:Buffer Manager

Counter: - Page reads/sec

Preferred Value: - < 90

Description: - Number of physical database page reads issued. 80 – 90 per second is normal, anything that is above indicates indexing or memory constraint.

Reference: -



Object: - SQL Server:Buffer Manager

Counter: - Page writes/sec

Preferred Value: - < 90

Description: - Number of physical database page writes issued. 80 – 90 per second is normal, anything more we need to check the lazy writer/sec and checkpoint counters, if these counters are also relatively high then, it's memory constraint.

Reference: -



Processor

Object: – Processor

Counter: – %Processor Time

Preferred Value: – < 60%

Description: - % Processor Time is the percentage of elapsed time that the processor spends to execute a non-Idle thread. The consensus is generally that over 80% you officially have a processor bottleneck. What needs to be taken into consideration here is the number of cores and if hyper-threading is turned on. Aside from the fact that you should not turn on hyper-threading on a SQL Server box, if you do, this number should probably be around 50%. Hyper-threading is not a true processor, it is unused cycles of another processor. Also, if you have over 4 cores, I would recommend this number be no more than 55%. While high processor utilization may seem like a major issue, it is actually one of the best bottlenecks to have because it is the easiest to fix. It is usually lack of indexing.

Reference: -



Object: - SQLServer:Access Methods

Counter: - Full Scans / sec

Preferred Value: - < 1

Description: - If we see high CPU then we need to invistigate this counter, otherwise if the full scan are on small tables we can ignore this counter. Values greater than 1 or 2 indicates that we are having table / Index page scans. We need to analyze how this can be avoided.

Reference: -



Object: - SQLServer:Access Methods

Counter: - Worktables Created/Sec

Preferred Value: - < 20

Description: - Number of worktables created in tempdb per second. Worktables are used for queries that use various spools (table spool, index spool, etc). Even if you see this as high there is likely very little you can do about it. It requires rewriting your procedures.

Reference: -



Object: - SQLServer:Access Methods

Counter: - Workfiles Created/Sec

Preferred Value: - < 20

Description: - Number of work files created per second. Tempdb workfiles are used in processing hash operations when the amount of data being processed is too big to fit into the available memory. They may be able to reduce this number by making the queries more efficient by adding/changing indexes, adding additional memory, etc.

Reference: -



Object: - SQLServer:Access Methods

Counter: - Page Splits/sec

Preferred Value: - < 20

Description: - Interesting counter that can lead us to our table / index design. This value needs to be low as possible. If you find out that the number of page splits is high, consider increasing the fillfactor of your indexes. An increased fillfactor helps to reduce page splits because there is more room in data pages before it fills up and a page split has to occur.

Reference: -



Overall SQL Server Bottleneck Analysis

Object: – SQLServer:General Statistics

Counter: – User Connections

Preferred Value: –

Description: – The number of users currently connected to the SQL Server.



Object: – SQLServer:General Statistics

Counter: – Logins/sec

Preferred Value: – < 2

Description: - > 2 per second indicates that the application is not correctly using connection pooling.



Object: – SQLServer:General Statistics

Counter: – Logouts/sec

Preferred Value: – < 2

Description: - > 2 per second indicates that the application is not correctly using connection pooling.



Object: – SQLServer:SQL Statistics

Counter: – Batch Requests/Sec

Preferred Value: – > 300

Description: – The higher this number, the better. What it generally means is that your SQL Server can scale when needed. If you see peaks hit over 2000, then you are on your way to an optimized box. I have worked with servers that never got over 150. Then after changing a critical process like removing a scalar UDF from a computed column, or rewriting a critical process to not use looping, see this number hit over 500 to 2000.



Object: – SQLServer:SQL Statistics

Counter: – SQL Compilations/sec

Preferred Value: – < 10% of the number of Batch Requests / sec

Description: - Not a major indicator. I would not worry too much about it, however be aware of it.



Object: - SQLServer:SQL Statistics

Counter: - SQL Re-Compilations/sec

Preferred Value: - < 10% of the number of SQL Compilations/sec

Description: - No you don't want recompilations, and the higher the number here, probably the more temp tables you use. Not much you can do about this except change code.



Transaction Management

Object: – SQL Server:Locks

Counter: – Number of Deadlocks/sec

Preferred Value: – < 1

Description: – The number of lock requests that resulted in a deadlock.



Object: – SQL Server:Locks

Counter: – Lock Requests/sec

Preferred Value: – < 1000

Description: – Not a critical number but watch it. The higher it gets generally means you are missing indexes.



Object: – SQL Server:Locks

Counter: – Average Wait Time (ms)

Preferred Value: – < 500

Description: – This is the average wait time in milliseconds to acquire a lock. Lower the value the better it is. If the value goes higher then 500, there may be blocking going on; we need to run blocker script to identify blocking.

Reference: http://sqlserverplanet.com/optimization/performance-counters

Oracle Client Libraries Problem While Using SSIS in MS SQL Server

Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed
This will happen  when IIS is running in 32bit mode in Win2k3 64bit OS system, so change IIS to 64bit mode it will stare working.

1. Type the following command to enable the 64-bit mode:

   cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 0

2. Type the following command to install the version of ASP.NET 1.1 and to install the script maps at the IIS root and under:

   %SYSTEMROOT%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i

Restoration or Backup - Percentage Progress

SELECT
r.Session_ID,
r.Command,
CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
CONVERT(VARCHAR(20),
DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r
WHERE command
IN ('RESTORE DATABASE','BACKUP DATABASE')

Decimal to Integer

CREATE FUNCTION [dbo].[Decimal_To_Integer_F] (
@InputValue nvarchar(max)
)
RETURNS BIGINT
AS
BEGIN

DECLARE @DecimalStartPosition int

SET @InputValue = LTRIM(RTRIM(REPLACE(@InputValue,',','')))
SET @DecimalStartPosition = CHARINDEX('.',@InputValue)
SET @InputValue = SUBSTRING(@InputValue,1,@decimalstartposition-1)
SET @InputValue = CAST(@InputValue as BIGINT)

RETURN @InputValue

END

Findings Words in a File from Any Directory

Go to the particular directory and search using the below syntax:
FINDSTR /S /I write_name_you_are_searching *.* >C:\TMP\Results.txt
FINDSTR /S /I "deleted"  *.* >C:\TMP\Results.txt

Reference: http://www.computerhope.com/findstr.htm

DOS Command to OutPut File Names Only

To display only the procedures starting with dbo.Admin. The below syntax won't provide other informations like date, file size etc C:\backup>dir dbo.Admin* /B > C:\TMP\Procedureslist.txt

SSRS Uninstallation Error

Reporting Services: Can’t Uninstall – The setup failed to read IIsMimeMap table. The error code is -2147024893 Solution: stop IIS and World wide web Publishing service through service.msc and then re-run the Uninstall.

ASP.Net 32 Bit Enable in 64 Bit System

cscript %SystemDrive%\inetpub\AdminScripts\adsutil.vbs set w3svc/AppPools/Enable32bitAppOnWin64 1