Issues

Error updating temporal tables

Scenario

At this point, you have probably heard about the feature called System Versioned Temporal Tables released on SQL Server 2016. So, let’s imagine that your team has decided to  implement that on a table called “Orders”.

The application is using different access patterns to modify the data in this table. There are direct statements coming from the application or the application is running long batches in explicit transactions where it makes several changes to multiple tables. The update on the “Orders” table is not the first statement in those long batches! So, suddenly you start seeing the following error message:

Data modification failed on system-versioned table “Orders” because transaction time was earlier than period start time for affected records.

You can simulate the error message running the following script:

CREATE TABLE dbo.Orders
(
[OrderId] INT NOT NULL PRIMARY KEY CLUSTERED
, [OrderValue] DECIMAL(19,4)
, [ValidFrom] DATETIME2 (2) GENERATED ALWAYS AS ROW START
, [ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));
GO
INSERT dbo.Orders ([OrderId], [OrderValue])
VALUES (1, 9.99), (2, 9.99);
GO
SELECT * FROM dbo.Orders;
GO

--Run first query
BEGIN TRAN
WAITFOR DELAY '00:00:15';
UPDATE dbo.Orders
SET [OrderValue] = [OrderValue] + 1;
COMMIT TRAN

--Run Query 2 in another session
BEGIN TRAN
UPDATE dbo.Orders
SET [OrderValue] = [OrderValue] + 1;
COMMIT TRAN

 

But, what’s the problem here?

The system generated start and end times are keyed to the server’s system time at the moment the BEGIN TRANSACTION is executed.

The times recorded in the system datetime2 columns are based on the begin time of the transaction itself. For example, all rows inserted within a single transaction will have the same UTC time recorded in the column corresponding to the start of the SYSTEM_TIME period.

Is that a bug?

No, this is a standard behavior of System Versioned Temporal Tables.
More info: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017#how-does-temporal-work

Workarounds

  • Build in exception handling that can retry the transaction.
  • Use triggers instead of Temporal Tables
  • Wait for Microsoft to build/release the Application-time feature (that is part of the Standard). More info: https://en.wikipedia.org/wiki/SQL:2011

Why ado.net queries run so slow and my SSMS queries are so fast?

Many times I face questions from development teams “Why ado.net queries run so slow and my SSMS queries are so fast?”.

The most common reason of this behavior is about the option ARITHABORT.
Ado.net defaults are different than SSMS settings:

Your application connects with ARITHABORT OFF, but when you run the query in SSMS, ARITHABORT is ON and thus you will not reuse the cache entry that the application uses, but SQL Server will compile the procedure anew, sniffing your current parameter values, and you may get a different plan than from the application. So there you have a likely answer to the initial question of this article. There are a few more possibilities that we will look into in the next chapter, but the by far most common reason for slow in the application, fast in SSMS in SQL 2005 and later is parameter sniffing and the different defaults for ARITHABORT.

arithabort

This and many more issues can be fixed if you read “Slow in the Application, Fast in SSMS? Understanding Performance Mysteries” by Erland Sommarskog.

You can check the ARITHABORT option, running the query below:

DECLARE @ARITHABORT VARCHAR(3) = 'OFF';
IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON';
SELECT @ARITHABORT AS ARITHABORT;

Good reading!

Alwayson Availability Group Replica is Not Synchronizing (Error: 976, Severity: 14, State: 1.)

tech-coffee-sql-server2
You might lose communication between the primary and secondary replicas.

As known, there is a policy on SQL Server to check the data synchronization state of the database replica. The policy is in an unhealthy state when the data synchronization state is NOT SYNCHRONIZING or the state is not SYNCHRONIZED for the synchronous-commit database replica.

After investigating the sql server errorlog, I got the following msg:

The target database, ‘YourDatabase’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. (Microsoft SQL Server, Error: 976)

According to Technet, this issue can be caused by the following:

  • The availability replica might be disconnected.
  • The data movement might be suspended.
  • The database might not be accessible.
  • There might be a temporary delay issue due to network latency or the load on the primary or secondary replica.

Further information: https://technet.microsoft.com/en-us/library/hh245199.aspx

Resolution:
You can fix it, using the following T-SQL command to force the resume synchronization:

ALTER DATABASE YourDatabase SET HADR RESUME;

 

You can also follow the link: How to resume an AG database.

Check out these related tips:

How to monitor Timeout and Changes Roles from Availability Groups with Alerts
How to Monitor AlwaysON – Primary / Secondary Replicas

NodeRunner process in SharePoint 2013

The node runner application used by SharePoint 2013 preview for search related process slows down the system due to memory leak issues.

To fix this issue follow the below 2 steps

1. Open SharePoint PowerShell window and execute the below command

Set-SPEnterpriseSearchService -PerformanceLevel Reduced

2. Open the config file at C:\Program Files\Microsoft Office Servers\15.0\Search\Runtime\1.0\noderunner.exe.config and change the value of memoryLimitMegabytes in node to some other value other than 0, say 100 or 200 which sets the cap limit for memory to be used.

3. Restart the machine

Error while installing the Pre-requisite for SharePoint Server 2013

When I was trying to install the SharePoint 2013 pre requisite on Windows Server 2008 R2 Std SP1, I got an error message:

2015-02-02 11:14:27 – “C:\Windows\system32\cscript.exe” “C:\Windows\system32\iisext.vbs” /enext “ASP.NET v4.0.30319”
2015-02-02 11:14:29 – Install process returned (-2146646015)
2015-02-02 11:14:29 – [In HRESULT format] (-2146646015)
2015-02-02 11:14:29 – Error when enabling ASP.NET v4.0.30319
2015-02-02 11:14:29 – Last return code (-2146646015)

Solution:
Start the CMD.exe as Administrator

Execute this command: “C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe” -i -enable

Problems installing Sharepoint 2013 – bad module “ManagedPipelineHandler” in its module list

Days ago I was installing the Sharepoint 2013 on a new BI environment of a costumer, but during the configuration I had some issues that I will share with you.

Scenario

1 – For safety, we have separated the database service (SQL Server 2012) from the analysis/report/sharepoint services.
2 – We have changed the port number of database and analysis services.

Issues

1 – During the installation of Sharepoint, we have received an error on the step of creation databases.
I don’t know the reason, but when we Install the Sharepoint with another port number, the installation loses itself.
So, we created an alias on SQLServer and tried again setting the alias name. (Success)

2 – After the Sharepoint installation, I tried to connect on the site and we receveid an error 404.
Then, we created a log on IIS and we got the message bellow.

Handler “PageHandlerFactory-Integrated” has a bad module “ManagedPipelineHandler” in its module list

Where I got the solution:
http://stackoverflow.com/questions/20912617/500-21-bad-module-managedpipelinehandler-in-its-module-list

There is a lot of advice out there indicating that asp.net is not installed correctly. The usual fix is to run:

%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i
or
%windir%\Microsoft.NET\Framework\v4.0.21006\aspnet_regiis.exe -i