Why SQL Server Always On Availability Group Listener is not working

There are things which sometime we only learn from experience and this is one of those things. Recently I was involved in the designing and implementation of  SQL Server 2014 AAG DR solution for one of the client and issue encountered when client was not able to connect to SQL Server via Listener name,

It was found that just setting multisubnetfailover paramater value "true" was not sufficient and there is something more to it. Basically legacy applications  sometime do not support multisubnetfailover and to make sure they work with AAG Listener, we need to look at few things

You need to use powershell to amend these settings

1. Make sure below parameters are set rightly for AlwaysOn listener
   Import-Module FailoverClusters
   Start-ClusterResource <LISTENER RESOURCE NAME>

   HostRecordTTL 300
   RegisterAllProvidersIP 0

   Import-Module FailoverClusters
   Get-ClusterResource <LISTENER RESOURCE NAME>| Get-ClusterParameter

2. When RegisterAllProvidersIP is set to 1 (default value), two subnet entries will be created in the DNS.Client need to use the MultiSubnetFailover = true property

Please note RegisterAllProvidersIP value need to be set to 0 for the legacy application which does not support multisubnetfailover property.For example, there are cases where excel 2010 is used and that is why this value need to be adjusted to 0 for this environment.

For latest application, we should leave the RegisterAllProvidersIP value to 1

RESOURCE SEMAPHORE, QUERY MEMORY and PERFORMANCE

Memory has always been my favorite topic when comes to troubleshooting performance in SQL Server :)

I have been contributing and posting few articles on Buffer and Data pages in cache and how it works and all that! But this time I would like to share what exactly query memory is
and how and where to look at, when query performance is suffering!

Let me try to explain in easy language, what happens when query enters the door of SQL Server RDBMS:

1. SQL Server receives query

2. SQL Server creates compiled plan before it creates execution plan

3. SQL server when create compiled plan evaluate the need of two memory parameters

·       Required memory( Min memory need to run sort and hash operations)

·       Additional memory(need to store rows in memory)

4. Server calculates how much memory query need. Formula is
(Required memory * degree of parallelism) + Additional memory

5. If above needed memory exceeds the query memory limit then server reduce the memory to fit the need

6. Now this memory which is able to fit a.k.a requested memory need is fulfilled by RESOURCE SEMAPHORE

7. If RESOURCE_SEMAPHORE can not satisfy this memory need then query is put under RESOURCE_SEMAPHORE wait type

8. And cycle goes on..

If you see high RESOURCE_SEMAPHORE wait types, it is an indicator that query need more requested memory.

This opens up the case to optimize the query!