Issues

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!

Advertisements

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

tech-coffee-sql-server2
For some reason you can 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