In the previous blog, we discussed the Foreign Keys Constraints and how the CHECK Constraints are useful to verify the data that goes into your tables. But, sometimes when you need to load a lot of data quickly, SQL Server allows you to temporarily disable any CHECK or FOREIGN KEY… Read more

DMV (Database Management View) and DMO (Database Management Objects) were added in 2005. There have been huge improvements in each new edition of SQL Server. It provides us a lot of useful information about SQL Server like – expensive queries, wait type, missing index…., and the list goes on and… Read more

Recently, while reviewing one of the stored procedure performance, I suggested a couple of changes to a developer to improve the performance of the SP. One of the suggestions was to replace DISTINCT with GROUP BY.

A few days later, he came back to me with a performance matrix to… Read more

If you are working on SQL Server optimization delicately, you may get often queries that how to find if any index, table or view is being used in database or not.

In recent past, we went live for one of the critical application. The developers decided to check through all… Read more

Yesterday, I was attending a database design meeting. In the meeting, I was asked one of the questions – “How many foreign keys can you have on a single table?” Honestly, I had no clue about the limit. But, I must acknowledge that it was an excellent question… Read more

It is relatively easy to spot CPU-bound system. But, the source of CPU pressure is not always easy to pinpoint. The CPU can be driven by many factors on the server such as;

• Memory pressure which is causing SQL Server to continually move data in and out of the memory.

Read more

It is fun working with SQL/Application developer where you exchange a lot of technical thoughts to find the root of the issues. Recently, one of the developers optimized the OLTP system stored procedure by putting the query hint OPTION (FAST ‘N’). But, he was not sure enough about… Read more

In my previous post on new functions in SQL Server 2017, I discussed about the STRING_AGG function. Here, I am going to discuss about the function CONCAT_WS.

This function concatenates a variable number of arguments with a delimiter specified in the first argument.

Syntax for the function

CONCAT_WS ( separator,…

Read more

For SQL Server 2017 RTM, the fourth cumulative update was released on 20th February, and it is available for download at the Microsoft Downloads site. It brought a couple of significant improvements like;

• Set new errorlogfile location
• Change the location of master database file on existing SQL installation
• Change…

Read more

SQL Server 2017 introduces a set of useful functions like STRING_AGG(), STRING_SPLIT(), TRIM()… and many more. In this tip, I am going to discuss one of the built-in aggregate functions – “STRING_AGG()”.

The function concatenates values from rows as one value with a separator, and the main advantage is it… Read more

Recently, I was working with the team and got stuck up with SQL Server Installation information. We got an urgent requirement to patch the physical box from Windows team.

As the server was not in production, so the server entry was missing. Since Windows team wanted to take action on… Read more

In my recent blog, I discussed about how to move master database to another location. One of the readers requested to blog about renaming the master database files. So, we are going to explore step by step process of renaming the files in this blog.

Change the name of…

Read more

With the release of SQL Server 2017 CU4, you can use the mssql-conf utility to move the master database file to another location. By default, master database is located at the location /var/opt/mssql/data. Now, if you want to change the location of master database file to a different location,… Read more

In my introduction to Extended Events blog, I mentioned that the xEvent is my favorite toy for performance troubleshooting. This blog may make you to start playing with it. In this tip, I will be discussing how you can find what caused SQL Server to write error in the… Read more

I continue blogging on extended events. Today, I will discuss about the top level containers (Package) that gives access to one or more smaller items inside of extended Events

Extended Event Packages Definition

In the Extended Events Engine, Packages are registered inside the modules and contain the information about what… Read more

Since I started actively participating in SQL community, I found it is very common that people will approach to seek help on their SQL related issues. I do love to help people because it is always a win and win situation for both of us.

Recently, I had a phone… Read more

This article is about how to proceed when you forgot the password of your SQL Server “sa” account, Password was entered wrong many times then “sa” account got locked, or someone wants to reset the password without using SQL Server management studio (GUI).

The mssql-conf is a configuration tool that… Read more

“How much time SQL Server is going to take to complete the database Backup or Restore” – This is one of the very common questions DBAs face in day to day life when they are performing database refresh activity, database migration activity, or any adhoc activity where DBA may want… Read more

Since I started playing with Extended Events, it has been my favorite toy for performance troubleshooting. It was introduced in SQL Server 2008, and it provides a very rich method of collecting diagnostic data from SQL Server. You can get in-depth detailed information using extend event for troubleshooting which was… Read more

In my previous tips, I explained about install RHEL Server on the virtual machine, install SQL Server 2017 on Red Hat Enterprise Linux, and Install SQL Server Command-Line Tools on RHEL. In this tip, I will explain how to install SQL Server Agent on a Linux server,… Read more