TempDB Database Checks for optimal performance

•          Is Tempdb files big enough?
•          Is Instant file initialization on?
•          Configured multiple tempDB Files?
•          Configured multiple data files?
•          Who is using tempdb space?

o   sys.dm_db_session_space_usage

o   sys.dm_db_file_space_usage

o   sys.dm_db_task_space_usage

o   sp_WhoIsActive

• Is Tempdb experiencing PageLatch issues?
• Are their contention in SGAM Pages?
• Is trace flag 1118 set?
• Are their contentions in DDL Operations?
• Check if Named constraints are created.
• Check if DDL statements that affect the table are not run after the temp table creation.
• Check if Temp object is not created by using dynamic SQL.
• Check if Temp object is created inside another object, such as a SP procedure.

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