Monthly Archives: April 2015 - Page 2

SSISDB Catalog logging levels in SQL Server 2012

As we are aware that multiple SSIS packages can be deployed with project deployment model in SSIS 2012.
Also previously the packages were deployed to MSDB or filesystem. These options are present in 2012 too except that it has additional option to
deploy to integration services catalog database to have better visibility on logging.

After the creating of SSISDB catalog from SSMS, logging options needs be tweaked to avoid unnecessary load on the resources. Here are the options available and choose one based on the requirements.

    Verbose : logs all the steps in the packages and it will be too noisy pretty soon.
    Basic : logs everything except onprogress and oncustom events
    Performance : logs only onerror
    None

World Economy – Treemap

OLEDB vs ADO.NET Connection Managers in SSIS

Both of these connection managers are NOT mutually exclusive but improper usage can cause data type mismatches with the precision lost.

OLEDB

    it supports a wide variety of databases like DB2, Mysql etc.,
    It is faster with fast load option set
    Connects using OLEDB provider and finds popular usage
    OLEDB providers like JET or ACE providers are 32bit only

ADO.NET

    Used mostly in for each loop container for looping over a recordset
    Lookup components are not supported by this connection
    Connects using .net provider
    Works best for parameter mapping in the execute SQL task
    ADO.NET quite flexible when moving across x86 or x64 environments
    No task like OLEDB source component
    MS has stronger inclination to support these providers historically

SSIS Connection Managers – Application Name property usage

When a SQL environment has an SSIS package with multiple connection managers connecting to multiple databases, it becomes tough to pin point a connection manager in SQL profiler.

So it is advisable to set the property of Application Name within the connection manager properties as below:

Application Name - Connection Manager

Resolving Memory Issues in SQL Server

get more RAM
run sql server on a dedicated machine with nothing else running on it
dont leave RDP sessions running constantly on SQL Server
DONT start SSMS on the server locally
try to use 64bit version
lock pages in memory access should be there for the service account
Monitor SQL Server:Buffer Manager – Page Life Expectancy (higher is better)
Monitor SQL Server:Buffer Manager – Buffer Cache Hit Ratio(higher is better)