Here are some important perfomon counters that can be captured to track the performance of SQL Server.

Average Disk sec/read – measures how much time it takes to for an IO request to be sent from the application to the storage system

Average Disk sec/read – measures how much time it takes to for an IO request to be sent from the storage system to the application

Disk read/sec and disk write/sec – track the physical data being transferred

Disk Queue length let’s us know how busy the system

% Processor time – shows the processor load on the server.

System: Processor Queue Length – determines how many tasks are waiting on the CPU.

Memory : Available Mbytes – tells the memory currently in use

How does SQL Server read and write ?

• normally Reads in 64K data extents
• normally Writes in 8K data pages

Simple answer is No.

in fact when you try to issue a DML statement it complains:

Invalid use side-effecting operator Insert within a function

This is one of the limitation of SQL server function as defined in books online.
Functions are used to fetch data and stored procedures are ideally suited for all DML operations.

The error is normally generated when migrating the applications or code segments that used to run prior to SQL Server 2012 to later versions of SQL Server 2012 (Because it was deprecated in SQL Server 2012).

• When can you get it ?
  When the SQL script was created in earlier versions before SQL Server 2012 ()i.e compatibilty mode < 110). So installation of older databases like pubs, northwind fail.

• When trying to create a sharepoint 2003 site (WSS 2.0) collection as it relies on this system stored procedure using SQL Server 2012

• when configuring reporting services for sharepoint 2010 (non SP1) using SQL Server 2012 (SQL 2012 only works with SP 2010 SP1 or higher) – so slipstream SP1 for Sharepoint 2010 installation

• When configuring ACM (audit and control management) server for Sharepoint

• When creating WebMarshal database

Solution:
replace the occurrence of sp_dboption with ALTER DATABASE statement manually.

Please see attached sp_dboption SQL file when needed.

version 1

version 2

SQL Server 2012 introduced a new function called as concat().

Previous versions of SQL Server, the null values in any of the concatenated fields was handled by either using ISNULL or COALESCE functions or by CASE END statements.

Concat handles NULLs in any of the participating fields and suppresses them without additional code handling.

So use CONCAT() wherever applicable.

[cc lang=”SQL”]

— sample case when middle name is null
select * from [dbo].[employee]
where emp_id = ‘PMA42628M’

— simple concatenation fails
select fname + minit + lname as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’

— handling NULLs before SQL Server 2012
——————————————————————————-
select fname + isnull(minit, ‘ ‘) + lname as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’

select fname + coalesce(minit, ‘ ‘) + lname as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’

select fname + case when minit is null then ‘ ‘ else minit end + lname as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’
——————————————————————————-

— handling NULLs after SQL Server 2012
——————————————————————————-
select concat(fname, ‘ ‘,minit, ‘ ‘, lname) as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’
——————————————————————————-

[/cc]

There could be scenarios where we would like to remove all page breaks for a report and just would like to see all records in one single page.

Here are the steps :

• Check all individual tablixes have pagebreak to be disabled

• On the report properties set interactivesize for height  = 0 (an interactive height of 0 means the report has an infinite length)

• You will get a warning that it might be non-performant and be aware of consequences if the dataset is returning massive rows.

Other alternative option is to open the report solution XML i.e RDL file in BIDS and look for <Page></Page>  sections in entire RDL file and then insert below code

<InteractiveHeight>0in</InteractiveHeight>
<InteractiveWidth>0in</InteractiveWidth>

Disclaimer: Non HTML rendering is NOT supported