SQL SERVER – Basics of Delayed Durability in SQL Server 2014

SQL Server 2014 has introduced many new features and one of performance is Delayed Transaction Durability. This concept is indeed very interesting. To explain this feature in detail, we will require to understand what is Full Transaction Durability. The current default of SQL Server is Full Transaction Durability. A common question I often received is why would we go away from to delayed durability. The answer is – Performance prioritation over Durability.

I try to stay as brief as possible to explain various concepts over here. I will try to convert this blog post in questions, and answer format so it is easy to understand.

What is Full Transaction Durability?
In Full Transaction Durability transactions write the transaction log to disk before returning control to the client / user. This means your data is safe on disk before it is committed. SQL Server uses Write Ahead Log logic for this default behavior of Full Transaction Durability.

What is Delayed Transaction Durability?
In Delayed Transaction Durability transactions are written asynchronously and in batches to the disk before returning control to the client / user. This means your transaction log records are kept buffer and written to disk either when it is filled up or buffer flush happens.

When to use Full Transaction Durability?
You should full transaction durability when you can’t afford any data loss.

When to use Delayed Transaction Durability?
You should use Delayed Transaction Durability when performance is priority over data loss.

What particular sign I should observe in my SQL Server, which suggests that if I implement Delayed Durability it will improve my performance?
Well, if your performance issue is due to latency in transaction log writes, or in another word, if you notice the bottleneck on transaction log writes, you can implement delayed transaction durability.

When are transaction written to the disk in case of delayed transaction durability?
There are three different scenarios, when in-memory transaction logs are flushed to the disk (as per MSDN).

  • A fully durable transaction in the same database makes a change in the database and successfully commits.
  • The user executes the system stored procedure sp_flush_log successfully.
  • The in-memory transaction log buffer fills up and automatically flushes to disk. (Update: One of my SQL Expert friend suggest this statement requires amendment and it is on the way, I will update once I have more details).

Well, that’s it for today. In upcoming blog posts we will see a working demo for Delayed Transaction Durability.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – What is the Difference Between Latches and Locks

A very common question I often get in the email is

“What is the difference between latches and locks?”

The answer to this question is not an easy one. Here is a quick answer from the MS white paper.

A page in SQL Server is 8KB and can store multiple rows. To increase concurrency and performance, buffer latches are held only for the duration of the physical operation on the page, unlike locks which are held for the duration of the logical transaction. Latches are internal to the SQL engine and are used to provide memory consistency, whereas locks are used by SQL Server to provide logical transactional consistency.

Above answer is taken from the old, but extremely relevant white paper written in the year 2011. It was written for SQL Server 2008 R2, however, almost all the concepts from this Whitepaper are still very accurate.

If you want to learn more about Latches and how to diagnose and resolve problems around latch contention, I strongly suggest following whitepaper.

Diagnosing and Resolving Latch Contention on SQL Server

Let me know your thoughts about this subject.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

Here is one of the very common interview questions I often ask people -

Q: What is the default Port SQL Server running on?

A: 1433 for TCP/IP and 1434 for USD connection.

I always get the correct answer for this question. However, when I ask a follow up question, so far I have yet not got a single correct answer.

Q: Great, now assumes that there is more than one instance of SQL Server running on the same server. What will be the port of the second SQL Server running on the server as the default port is already used by default instant?

A: (I have yet to get the correct answer for this one in interview).

Because of the same reason, I have decided to blog about this.

Here are different ways one can figure out on which port the second instance of SQL Server is running.

Method 1: using xp_readerrorlog

Execute following stored procedure on the instance where you want to find out port on which SQL Server is running.

xp_readerrorlog 0, 1, N'Server is listening on'

The above query will return results something very similar to the following:

Method 2: Configuration Properties

Go to Windows >> Start >> SQL Server Program Folder >> SQL Server Configuration Manager

Now go to SQL Server Network Configurations >> Protocols for Your Server and go to TCP/IP and right click over it.

Now over here when you scroll down you will notice Port Details.

It is that easy to find the port of the second instance of SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Validate Syntax and Not Execute Statement – An Unexplored Debugging Tip

In one of the recent interview I had asked questions of interviewees – If you are a SQL Server Developer there must be times when you want to validate syntax, but do not want to execute a query.

What is the surest way to check that your syntax is valid and will work with SQL Server?

I often get answers that intelligence in SQL Server will automatically let us know or we should try this out on a development server or we should attempt to create stored procedure etc. Honestly, none of the answer is accurate. They are all workaround which works sometime and which does not work sometime.

Here is how you can do it.

You can set the context of your execute to On or Off with the help of NOEXE setting. Let me explain you with the help of AdventureWorks Database and setting NOEXEC.

First look at the following query:

USE AdventureWorks2012
-- Change Setting of NoEXEC to ON
-- INSERT Statement
INSERT INTO HumanResources.Shift
(Name,StartTime, EndTime,ModifiedDate)
-- Change Setting of NoEXEC to OFF
-- Check Table Data
FROM HumanResources.Shift;

Here is the result of the query when we executed.

Even though we have an INSERT statement right before SELECT statement, there is no impact of the INSERT statement because we have executed SET NOEXEC ON before the INSERT. When Setting NOEXEC is set to ON, it will validate the syntax of the statement but will not execute it. If there is an error it will display the error on the screen. Now try to change the name of the table or anything in the above statement and it will throw an error.

Please do not forget to set the value of NOEXEC statement to OFF right after your test or otherwise all of your statements will not execute on SQL Server.

Now when you are debugging and see any syntax which is part of large query and you want to validate it, you can just do this with about Syntax. If you know similar cool tip, which you think I should share on the blog, please leave a comment and I will post on the blog with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL – A Career in Database Forensics

A growing field in the information security domain – Database Forensics offers a comprehensive and highly sophisticated skill set that allows professionals to uncover and trace data security breaches of the highest order and complexity. Many enterprises are looking to hire such professionals nowadays. Critical data needs to be guarded and organizations are leaving no stone unturned to ensure that their confidential records are safely stored and accessed. But how do you become a forensics expert without getting lost in the maze of security programs and products. Koenig India shows you the way:

Why are databases critical assets?

  • They hold crucial information
  • Database servers contain sensitive information which can be used as well as misused
  • Data security regulations mandate all security breaches to be reported

Therefore, Using analysis and  investigative tools and techniques, professionals gather evidence from the database which is suitable for presenting legally in a court of law.

Some Benefits

  • Identify pre and post transaction data
  • Retrace user DDL & DML operations
  • Recover deleted data rows
  • Prove/dismiss a data security breach
  • Determine the scope and extent of database intrusion

Apart from these technical skills, individuals must possess analytical abilities and a problem solving attitude. Uncovering hidden patterns, unexpected correlations, and sorting through data to extract meaning out of it – are some of the essential skills required of a forensic expert.

Another crucial skill is the knowledge of cyber laws. An understanding of the legal procedures and law enforcement agencies’ requirements is of great importance if you are to conduct digital investigations.

SQL Server Forensics Analysis course offers the foundation required to become a forensic expert in the online world. Also learn other associative skills such as cryptography, e-discovery and incident response in this latest training program.  A relatively new field of study, the course offers a great opportunity for those who wish to gain credibility in this stream.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Delay Command in SQL Server – SQL in Sixty Seconds #055

Have you ever needed WAIT or DELAY function in SQL Server?  Well, I personally have never needed it but I see lots of people asking for the same. It seems the need of the function is when developers are working with asynchronous applications or programs. When they are working with an application where user have to wait for a while for another application to complete the processing.

If you are programming language developer, it is very easy for you to make the application wait for command however, in SQL I personally have rarely used this feature.  However, I have seen lots of developers asking for this feature in SQL Server, hence I have decided to build this quick video on the same subject.

We can use WAITFOR DELAY ‘timepart‘ to create a SQL Statement to wait.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Maximize Database Performance with DB Optimizer – SQL in Sixty Seconds #054

Performance tuning is an interesting concept and everybody evaluates it differently. Every developer and DBA have different opinion about how one can do performance tuning. I personally believe performance tuning is a three step process

  1. Understanding the Query
  2. Identifying the Bottleneck
  3. Implementing the Fix

While, we are working with large database application and it suddenly starts to slow down. We are all under stress about how we can get back the database back to normal speed. Most of the time we do not have enough time to do deep analysis of what is going wrong as well what will fix the problem. Our primary goal at that time is to just fix the database problem as fast as we can. However, here is one very important thing which we need to keep in our mind is that when we do quick fix, it should not create any further issue with other parts of the system.

When time is essence and we want to do deep analysis of our system to give us the best solution we often tend to make mistakes. Sometimes we make mistakes as we do not have proper time to analysis the entire system. Here is what I do when I face such a situation – I take the help of DB Optimizer. It is a fantastic tool and does superlative performance tuning of the system.

Everytime when I talk about performance tuning tool, the initial reaction of the people is that they do not want to try this as they believe it requires lots of the learning of the tool before they use it. It is absolutely not true with the case of the DB optimizer. It is a very easy to use and self intuitive tool. Once can get going with the product, in no time. Here is a quick video I have build where I demonstrate how we can identify what index is missing for query and how we can quickly create the index.

Entire three steps of the query tuning are completed in less than 60 seconds. If you are into performance tuning and query optimization you should download DB Optimizer and give it a go.

Let us see the same concept in following SQL in Sixty Seconds Video:

You can Download DB Optimizer and reproduce the same Sixty Seconds experience.

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)