Monthly Archives: June 2017

SQL Server 2016 SSRS Best Practice

1.Enable Caching
enable Report Caching for Reports that are frequently accessed because every time the report runs it will not hit the report server to process and render the report.

2: Run Report Caching and Snapshots during off peak hours

3: Monitor Performance of Database Source system
the health of the database source system should be periodically monitored.
4: Monitor Performance of Report Server system
Report Server system should be periodically monitored for the health.

5: Use saved authentication when configuring report data sources.
Configuring a data source to use a SQL Authentication login.

6: Set Report Execution Timeout
Set the Report Execution Timeout so that your query does not eat up all your resources.

7: Monitoring Execution Log Data to Identify Slow Reports
Monitor the execution log to identify where the report server is spending most time.

For detailed information please read this tip SSRS Execution Log.
8: Back up the SSRS Report Server Databases
It is always recommended to take a backup of the reportserver and reportservertempdb database periodically.
9: Practice SSRS migration to another server

10: Keep all reports under source control

SQL Server 2016 Top 10 New Features

SQL Server 2016 New features


Query Store

One common problem many organizations face when upgrading versions of SQL Server is changes in the query optimizer (which happen from version to version) negatively impacting performance. The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have issues recently, allowing rolling back to previous working version.



Microsoft had introduced Polybase, a SQL Server connector to Hadoop (and Azure Blob Storage) to its data warehouse appliance Analytics Platform System in 2015. But now Microsoft has incorporated that functionality into the regular on-premises product. This feature will allow you integrate with external data source with a lot of large text files — they can be stored in cloud or Hadoop, and queried as if they were database tables.


Stretch Database (data tiers)

Base on the useage of the data, they will be stored in SAN, local disk and network/cloud, tier based data access allow you have the most frequent used data in memory/SAN, while less frequest used data stored on localdisk, and even less used data on network or Cloud.


JSON Support

In addition to supporting direct querying to Hadoop, SQL Server 2016 adds support for the lingua franca of Web applications: Java Script Object Notation (JSON). The way this is implemented in SQL 2016 is very similar to the way XML support is built in. Making data exchange easier with applicatoin such as web development easier.


Row Level Security


Always Encrypted, increase data security such as for PII data.


In-Memory Enhancements

SQL Server 2014 introduced the concept of in-memory tables. These were optimally designed for high-speed loading of data with no locking issues or high-volume session state issues



Checking BLock email


spid varchar(90),

p_status varchar(90),

p_login varchar(90),

Hostname varchar(90),

blk_by varchar(90),

DBName varchar(90),

sqlCmmd varchar(max),



LastBatch Varchar(30),

ProgramName varchar(255),

Spid2 varchar(90),

RequestId INT


INSERT INTO ##temp exec sp_who2

select blocking.Hostname blocking_host,

blocking.DBName blocking_db,

blocking.p_login blocking_user,

blocking.ProgramName blocking_program,

blocking.sqlCmmd blocking_sql,

blocked_by.Hostname host_blocked,

blocked_by.DBName db_blocked,

blocked_by.p_login user_blocked,

blocked_by.ProgramName program_blocked,

blocked_by.sqlCmmd sql_blocked

INTO ##Blockings

FROM ##temp blocking

LEFT JOIN ##temp blocked_by

ON blocking.spid = blocked_by.blk_by

Declare @count INT

SELECT @count = COUNT(*) from ##Blockings

IF @count >1



@TableHead VARCHAR(1000),

@TableTail VARCHAR(1000)

SET @TableTail = ‘</table></body></html>’ ;

SET @TableHead = ‘<html><head>’ + ‘<style>’

+ ‘td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} ‘

+ ‘</style>’ + ‘</head>’ + ‘<body>’ + ‘Report generated on : ‘


+ ‘ <br> <table cellpadding=0 cellspacing=0 border=0>’

+ ‘<tr> <td bgcolor=#E6E6FA><b>blocking_host</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>blocking_db</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>blocking_user</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>blocking_sql</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>blocking_program</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>host_blocked</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>db_blocked</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>user_blocked</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>program_blocked</b></td>’

+ ‘<td bgcolor=#E6E6FA><b>sql_blocked</b></td></tr>’ ;

SET @msgBody = ( SELECT td = blocking_host, ,

td = blocking_db, ,

td = blocking_user, ,

td = blocking_sql, ,

td = blocking_program,,

td = host_blocked, ,

td = db_blocked,,

td = user_blocked,,

td = program_blocked,,

td = sql_blocked,

FROM ##Blockings

FOR XML RAW(‘tr’),



SELECT @msgBody = @TableHead + ISNULL(@msgBody, ) + @TableTail

–PRINT @Body

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘profile_name’,

@recipients = ‘’,

@body = @msgBody,

@body_format = ‘HTML’,

@subject = ‘TEst mail from SQL’


Drop table ##temp

Drop table ##Blockings