How to Perform Database Hack-Attach

This blog demonstrates attaching a database on the SQL Server Instance which already has the same name database up and running. Scenario: Suppose, you got a hardware migration activity. The migration activity involves moving the databases from old hardware to new hardware. Let’s say the server got only one big database to be migrated. During […]

Read more
Finding Untrusted Foreign Keys and Constraints

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 constraint in order to improve performance. […]

Read more
Find Missing Indexes For All SQL Server Databases

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 on… In this blog, I […]

Read more
SQL Server Logical Query Processing

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 show that there was no […]

Read more
How to Check SQL Server View Utilization

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 the indexes utilization including View indexes […]

Read more
How SQL Compilation Can Prove the Server CPU

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. Disk latency Bad indexes Poorly […]

Read more
SQL Performance – Using Query Hint OPTION (FAST N)

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 the drawback of the query hint. So, he […]

Read more
New Built-in Function CONCAT_WS() – SQL Server 2017

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

Demonstration Let’s create a simple table structure: […]

Read more
Set New Errorlog File location – SQL Server on Linux

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 the name of master database files […]

Read more