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

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!

Memory, Memory and Memory

It’s not been long when I was troubleshooting sql server memory related performance issue and it came back again for another environment

I thought why not write it down something about memory, which can help you and me remember clearly about memory related troubleshooting :) :)

 To easily understand this, I am trying to put notes in bullet points.

First two important questions you should ask yourself are:

·        Is this external memory pressure? In simple terms is it Physical RAM A.KA. Windows OS memory pressure?

·        Is this internal memory pressure a.k.a pressure on VAS allocated to SQL Server by windows OS?

Two answer this, it is important to determine where the pressure is coming from?

Next question comes:

·        How quickly I can determine that? and using what tool?

 Answer is using sys.dm_os_ring_buffers:)

 ·        What exactly this DMO is?

This DMO tells us about if any alerts are fired or recorded within the system

If you select result set from this DMO, you will get lot of ring buffers. The one and only which can point to memory pressure is called "RING_BUFFER_RESOURCE_MONITOR"

If you look at the xml record for RING_BUFFER_RESOURCE_MONITOR, keep an eye on node called "<Notification>"

This node will give you insight on the reason for low memory pressure.

 So before we start looking at performance counters like buffer cache hit ratio, page life expectancy, target server memory etc etc, why not query sys.dm_os_ring_buffers and see what it has to tell about any recording of memory!

My SSIS performance is slow! What can I do apart from sneaking into TSQL behind each package!

Hi Guys, it’s been while I posted something interesting but lately I experienced the scenario dealing with SSIS performance tuning and I thought it’s a good chance to share some knowledge with you!

I am sure, as a DBA's we come across scenarios where we get complaints like SSIS ETL data loading exceeds allowable time threshold etc etc

I am also sure, we try to leverage things like sneaking into queries running behind each step or turn on performance counters on source and destination sql server

But there is something else, we can look into and that is SSIS buffer usage!!

I would like to split this article into two major parts

1. Transformation

I would prefer not to just say to my client that SSIS design is not efficient. Rather I would say how efficient transformation behaviour for each tree in the SSIS package is :)!

Let’s take one step further, where we would like to collect how SSIS plays with its buffer when executing the threads in each tree. I am highlighting each sentence as a bullet point for better understanding!

·        We all are familiar with Buffer, but here I am focusing more on SSIS buffer instead of SQL Server DB engine buffer.

·        SSIS takes advantage of their in- memory structure (Buffer) in a same way as SQL engine does. Basically to avoid loading data all the time from the source, It controls the data using buffer as part of the transformation from source and destination

·        Behind SSIS, we have logic of execution trees and each tree has its own in memory structure a.k.a buffer to play with.

·        There can be multiple threads running for each tree.

·        Goal of design should be to avoid overhead of data into new buffer every time!

2. Buffer Sizing

To utilise buffer to its maximum potential, SSIS makes certain decisions, which are based on 4 input parameters:

Estimated Row Size: SSIS computes this value based on metadata

DefaultMaxBufferRows: setting which can be change (10,000 records)

DefaultMaxBufferSize: setting which can be change (10 MB)

MinBufferSize: setting which can be change (65536 bytes)

If Estimated Row Size * DefaultMaxBufferRows > MaxBufferSize

{

     SSIS reduces total number of rows which can be store in buffer

}

Else

{

SSIS increases the total number of rows which can be store in buffer

}

So next time, If you turn on package logging with the BufferSizeTuning property enabled, you can get better picture of where and how buffer size changes and you can adjust the values of DefaultMaxBufferRows and DefaultMaxBufferSize to optimize the number of rows in buffer

Make DBA life easier with powershell - Get File size and percentage of space used

As a SQL dba, we all manage disk spaces. Whether it is associated with data drive, log drive or even TempDB drive.

In one particular scenario, it can be bit tricky or rather I would say bit time consuming to get the correct insight on what is taking most spaces in your volume.

This scenario is where you are hosting several databases and their files on the same drive or mount points and each database files are managed in different file group and folders.

One way to know is by manually logging on to the SSMS and actually sees the size of each database files and location.

But the way, I am going to suggest you here is to use to our great PowerShell script. All you have to do is simply remote desktop and open/execute PowerShell script.

For example: to get all the files under c:\temp location and their space usage (in %), you can execute below script:

$path = "c:\temp"
$totalfilesize = [int]((Get-ChildItem $path | Measure-Object -Sum Length).Sum / 1kb)
Get-ChildItem $path  | Select-Object Name, CreationTime,  @{Name="kb";Expression={$_.Length / 1KB}}, `
@{Name="Percent";Expression={(($_.Length / 1KB)/$totalfilesize )*100}}| sort Percent -descending;

This is very handy especially when you are managing lot of low disk space alerts.

SQL Server audit and Lock request time out period exceeded. (.Net SqlClient Data Provider) error

Have you ever noticed "PREEMPTIVE_XE_CALLBACKEXECUTE" wait type on one of your SPID which is not letting you perform any operation at SQL Server instance???

I have seen this wait type  when using SQL Server auditing and it seems that transaction is stuck and blocking meta deta. The exclusive lock was placed on sysclsobj in master database

If you try to see corresponding OS thread(KPID), you would notice that thread  actually belongs to sql server agent task.

Below are some additional findings :

 SPID for server audit is blocking all other instance level operation
 Stopping and restarting sql server agent job does nothing
 "PREEMPTIVE_XE_CALLBACKEXECUTE" reflects that sql thread is waiting on OS level resource
 Eventually there is no wait resource

This is a bug in SQL Server 2008 R2 SP1 and is fixed in CU 6 : ): )
Puneet