Monitioring Performance in SSIS using Performance counters.

SSIS provides a set of Performance counters.

few of them are

• Buffers in USE
• Flat buffers in Use
• Private buffers in use.
• Buffers spooled.
• Rows Read
• Rows written

Out of the above mentioned Counters, “Buffers in use”,”Flat buffers in use”  and “Private buffers in use” are useful to discover Leaks. During Package execution time, we 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 initial value of 0. when it goes above 0 , it indicates that the engine has started memory swapping.

“Rows Read”,”Rows Written”  show how many rows the entire data Flow has processed. They give you an overall idea about the execution Progress.

 

Differences between 2005 to 2008/2008(R2)

SQLServer 2005 to 2008

• Transparent Data Encryption.
• Back up encryption.
• Data Compression
• Data Compression.fact table size reduction and improve performance.
• installation Improvements.
• Dates and Times.New data types:Date,time,Date Time offset.
• Spatial Data.Datatype for storing longitudinal and Latitutude and GPS entries.
• SQL Server integration services. improved multiprocessor support and faster lookups.

SQLServer 2008 to SQLServer R2

•  Backup Compression is avialable in R2 standard
• SQLServer  2008 R2 Express edition database size limit increased to 10 GB( from 4 GB).
• Enhanced security to prevent an authentication relay attack.
• Master Data Services introduced to Manage Master data .

Note : This is partial list of differences, for complete list please check Microsoft online Documentation.

How to identify the SQLServer Details that has been installed on your machine.

We often sometimes need to identify the SQLServer version that has been installed on our Machine. this query can provide us all such details.

select SERVERPROPERTY(‘Productverision’),SERVERPROPERTY(‘Productlevel’),SERVERPROPERTY(‘edition’)

New Features in SQLServer 2012

These are some of the new features in SQLServer 2012.

1 .Window function.

Window function is a function that applied against a set of rows. It allows you to do grouping and also allows you to see individual rows in one query. This is the replacement to cursors in TSQL, This function will improve the performance of the Queries.

2.OffsetFunctions :

– LAG/LEAD

– FIRST_VALUE/LAST_VALUE

3.   Additional Analytical Functions.

Distribution functions

Percent_Rank, CUME_DIST

Inverse distribution functions

Percentile_Cont,Percentile_Disc

Query to change the database state to Multi user/single user.

Here is the Query to Change the database state.

ALTER DATABASE TestDatabase
SET MULTI_USER;