Detach and Attach in SQL Server

Hello there, everyone! I hope everything is going well for you. Today, I'll show you about Detach and Attach

Detach SQL Server Database-

A SQL Server database is made up of at least two files: a data file (mdf) and a log file (ldf), though there may be additional data files in some circumstances. Since these files are attached to a SQL Server instance and the file system identifies them as open, moving them to another SQL Server instance requires more than a simple copy. Therefore, you can disconnect the pertinent database (files) from the SQL Server instance before relocating these files.

Detach a SQL Server Database Using SSMS-

Step 1- To detach a database, first right-click on it in SSMS and choose Tasks > Detach, as illustrated in the image below.

Fig.1

Step 2- Then, you will be taken to the following screen and click on Detach option and check the Drop connection box, select "OK".

Fig.2

In the above screen, there are two check boxes that you can use when detaching a database

Drop Connections -

Before the database may detach, there might be any active connections to the database that need to be closed. The disconnect will not work if there are any open connections to the database.

Update Statistics –

Before removing the database, this will update any stale statistics. Statistics will continue to be as they were at the time you separated the database if this option is left unchecked.

Step 3- In this case we will select only one as drop connection options as shown below and select OK.  After the detach occurs, the Status changes to Success.

Fig.3

Step 4- The database's data and log files will stay in the same file system location even after the database has been disconnected and is no longer visible in SSMS's Databases list.

SQL Server Attach Database with Log File-

After detaching the database using either of the methods above, the mdf, ldf and ndf (if exists, these are additional data files) need to be copied to the new location where you want to attach the database.

Attach a SQL Server Database Using SSMS-

Step 5- To attach the database, right click on Databases and select Attach... on the SQL Server instance where you want to attach the database.

Fig.4

The following screen opens.

Fig.5

Step 6- To connect a mdf file, click the Add button, look for it, choose it, and then click OK.

Fig.6

Step 7- Once you select "OK," you can successfully attach a mdf and ldf file.

Memory Grants Pending

Hello there, everyone! I hope everything is going well for you. Today In this blog I am going to explain you about Memory Grants Pending and How to monitor it.

Memory Grants Pending-

The number of processes that are pending a workspace memory grant is known as Pending. Because they are unable to acquire adequate RAM while awaiting a grant, queries cannot begin. A persistent value of more than 2 can be a sign of memory pressure.

Here is a quick script which you run to identify value for your Memory Grants Pending.

Fig.1

With the help of this query you can easily find out the Memory Grants Pending

Fig.2

If this parameter is frequently higher than 0, your server may be under memory pressure and could benefit from more memory. Once more, this counter shows that your server may benefit from extra memory.

Page Life Expectancy (PLE) in SQL Server

Hey guys, In this blog I am going to explain you about Page Life Expectancy and How to monitor it.

Page Life Expectancy (PLE)-

Page Life Expectancy (PLE) is an age of a data page in seconds in the buffer cache or buffer memory after querying the tables with the loading data page into the buffer memory. Page Life Expectancy value indicates the memory pressure in allocated memory to the SQL Server instance.

To measure the Page Life Expectancy, there are two methods

Method-1: Using SQL Server Query,

With the help of a T-SQL command, obtain PLE for your SQL Server instance-

select * from sys.dm_os_performance_counters where counter_name like '%page life%'

Fig.1

In this case, cntr value stands in for the PLE, which in the results of the previous query is 29570. We always presume that the buffer memory lake is affecting query speed and that SQL Server does not have enough buffer pool space to store the data.

Method-2: Using Performance monitor,

Monitoring Page Life Expectancy-

An important way to gauge the pressure on the buffer memory in terms of seconds is to use the PLE counter value. Users can record it using SSMS, any other external tools, or Microsoft Performance Monitor; it always produces an average result. The T-SQL code above allows users to find the current value. Still, investigation is needed in a certain time frame to determine whether fluctuations occur at a particular point in time. By adding the PLE counter, users can utilize the Windows Performance Monitor to track the same.

Step 1- Go to the search option in Jump Station and log in to the server.

Step 2- Go to the SQL Server Buffer Manager in the performance monitoring tool on the server.

Step 3- In SQL Server Buffer Manager Search Page Life expectancy and this PLE option to the add counter.

Fig.2

Fig.3

Fig.4

Fig.5

The PLE graph is displayed here with the help of Performance Monitoring Tools.

How to Determine the Life Expectancy of a Page-

We agree to a 300-second rule set by Microsoft. Memory pressure is particularly severe if PLE lasts shorter than 300 seconds (5 minutes), and we must take care of the performance side. However, that computation only included 4GB of memory for the SQL Server. 

Conclusion-

There are numerous more buffer manager parameters in addition to PLE (Page Life Expectancy) that might impact SQL Server speed. If detected, we can assume that the primary problem is buffer memory pressure. Nowadays, larger memory boxes are prevalent, and PLE in the thousands is also relatively frequent.

Buffer Cache Hit Ratio

Hey guys, In this blog I am going to explain you about Buffer Cache Hit Ratio

Buffer Cache Hit Ratio- It is a pool of memory pages into which data pages are read. In other words, the data pages which are read from Disk are stored in Memory (Cache) to provide data faster. When a Data Read request is submitted, what SQL Engine does is it will go to disk retrieve the data from disk and place it in Memory (Cache) and return back the requested data. How this helps? When the request to same data pages is sent next time, there is no need to go to Disk again, it just reads from Cache and returns the data making data retrieval faster and there by less IO Operations. So, what are the impact when my buffer cache hit ratio is low, how does it impact performance.

Let’s us take an example. Say that you see that the Buffer Cache hit ratio (query is below to find the buffer cache hit ratio) is around 95% when you request data. This mean 95 times out of 100 requests; SQL was able to provide data from Memory(cache) and only 5 times it had to go to Disk.

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio

FROM sys.dm_os_performance_counters  a

JOIN  (SELECT cntr_value, OBJECT_NAME

    FROM sys.dm_os_performance_counters 

    WHERE counter_name = 'Buffer cache hit ratio base'

        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME

WHERE a.counter_name = 'Buffer cache hit ratio'

AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

--BufferCacheHitRatio is between 90% to 100% then all is good.

--BufferCacheHitRatio is less than 90% then we need to think other counters.

select * from sys.dm_os_performance_counters where

object_name like '%SQLServer:Buffer Manager%'and counter_name like '%buffer ca%'

Low buffer cache results in High IO operations and thus leading to low performance.

Ø To measure the Buffer Cache Hit Ratio trend, there are two methods

Method-1: Using Performance monitor,

                   Go to Search ->Type Performance monitor -> Monitoring tools -> 

                   Performance monitor -> Add counter -> SQL Server Buffer Manager -> 

                   Buffer cache hit ratio

Fig-1

Fig-2

Method-2: Using SQL Server Query,

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio

FROM sys.dm_os_performance_counters  a

JOIN  (SELECT cntr_value, OBJECT_NAME

    FROM sys.dm_os_performance_counters 

    WHERE counter_name = 'Buffer cache hit ratio base'

        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME

WHERE a.counter_name = 'Buffer cache hit ratio'

AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

Deterministic and Nondeterministic Functions

Deterministic functions:

Deterministic functions always result in the same output every time they are called with a fixed set of input values and given the same condition of the database. 

For example, AVG() function always results the same result given the qualifications stated above.

Nondeterministic functions:

Nondeterministic functions result in different output each time they are called with a fixed set of input values even if the database state that they access remains the same. 

For example, GETDATE() function, results the current date and time value, always a different value.

Consider a statement that deletes one hundred rows of a one-thousand-row table with no ORDER BY clause. If the rows are ordered differently between source and replica, you may delete a different one hundred rows on each, leading to inconsistencies.

Enabled DAC to connect remotely

Hello there, everyone! I hope everything is going well for you. Today, I'll show you how to use the DAC and how to enable it for remote connections.

DAC-

The dedicated admin connection (DAC) can assist you in getting out of a bind. This was created to assist you in connecting to SQL Server and running simple queries in the event of significant performance issues. This tells SQL Server to set aside a thread for processing your queries in the event of an emergency.

How to enable DAC for remote connections-

Step 1- To begin, open SSMS and connect to the server.

Fig. 1

Step 2- To verify whether the DAC is enabled or not, open a new query window and enter the syntax sp configure.

Fig.2

Step 3- You must first write the syntax sp configure 'remote admin connections',1; reconfigure with override to activate the DAC.

Fig.3

Step 4- You must check that the DAC connection is enabled once more. sp configure is the syntax to use.

Fig.4

Fig.5

SQL Server Checkpoint and Lazy Writer

Good day, everyone! I hope you're doing well, and Today I'll show you how to use SQL Server's Checkpoint and Lazy Writer functions, as well as the many types of checkpoints available.

Checkpoint-

• Checkpoint is an internal operation that writes all dirty (modified) pages from the Buffer Cache to the physical disc, as well as log entries from the log buffer to the physical file. Hardening of dirty pages is the process of writing dirty pages from the buffer cache to the data file.
• It's a specialized process that SQL Server runs at predetermined times. SQL Server performs checkpoints for each database separately.
• Checkpoint speeds up SQL Server recovery time in the case of an unexpected shutdown or system failure.

Fig.1

Advantages of Checkpoint-

• It speeds up data recovery process.
• Most of the DBMS products automatically checkpoints themselves.
• Checkpoint records in log file is used to prevent unnecessary redo operations.
• Since dirty pages are flushed out continuously in the background, it has very low overhead and can be done frequently.

Types of Checkpoints-

There are four different kinds of checkpoints in SQL Server.

• Automatic checkpoint
• Indirect checkpoint
• Manual checkpoint
• Internal checkpoint

Automatic checkpoint-

When SQL Server satisfies certain requirements linked to the recovery interval set in the server configurations, this is automatically executed in the background. For SQL Server, you can set the recovery interval at the instance level. If the database engine detects a write latency of more than 50 milliseconds, automatic checkpoints are throttled.

Here is the script to set the CHECKPOINT value to 30 seconds automatically.

EXEC sp_configure'recovery interval','30'

Indirect Checkpoint-

When any SQL Server database satisfies certain requirements relating to the recovery time interval provided in the database configuration, this is run in the background. For SQL Server, you can set the recovery interval at the database level. This is enabled by default for your database in SQL Server 2016, and the value is 1 minute.

Here's how to set the Indirect CHECKPOINT value to 60 seconds with a script.

 ALTER DATABASE CURRENT

SET TARGET_RECOVERY_TIME = 60 SECONDS

Manual Checkpoint-

When the user runs the CHECKPOINT command on any database, this is run. With the aid of additional parameter checkpoint duration, the user can enhance or decrease the performance of this procedure as desired.

The script to run manual CHECKPOINT can be found here.

 CHECKPOINT

Internal Checkpoint-

When SQL Server completes specific tasks such as backups, shutdown, or maintaining isolations, it automatically runs this command to ensure that the disc contains the same database as the log.

This intrinsic operation is not done by a script.

Lazy writer-

The lazy writer is a system procedure that removes infrequently used pages from the buffer cache to keep free buffers available. Dirty pages are written to disc first. The eager write process publishes filthy data pages associated with actions that are only weakly logged, such as bulk insert and select into.