SQL Server: Best practice to Move database from one Location to another location.

Most of  SQL Server professional may know the how to move the database form One location to another location. And most of the professional use the "detach and attach" method but..

"Is it best practice?"   NO!!

WHY? – If your database file size is1TB or around 1TB it is not a best practice. So here is the question how to move the database if the database files size is large.

Why it not good practice because while attaching the database it will create the New database on new location where we are moving the database log files. So in this case the existing user will be removed from the database.

Actually it’s quite easy.

Please find the below steps-

USE master;

GO

-- Return the logical file name.

SELECT * FROM sys.master_files WHERE database_id = DB_ID('MY_test_DB')

    AND type_desc = 'LOG';

GO

-- Offline the database

ALTER DATABASE MY_test_DB SET OFFLINE;

GO

-- Physically move(Copy and Paste) the file to a new location.

-- Now we have to modify the path specified in FILENAME to the new location of the file on your server.

ALTER DATABASE MY_test_DB MODIFY FILE ( NAME = MY_test_DB_Log,

FILENAME = 'D:\NewLocation\MY_test_DB_Log.ldf');

GO

-- Online the Database

ALTER DATABASE MY_test_DB SET ONLINE;

GO

--Verify the new location file location.

SELECT * FROM sys.master_files WHERE database_id = DB_ID('MY_test_DB')

    AND type_desc = 'LOG';

SQL Server - Difference between Checkpoint and Lazy writer in SQL Server.

Please find the 18 differences between Checkpoint and Lazy Writer in SQL Server. 

SNo	CHECKPOINT	LAZY WRITER
1	Checkpoint is used by sql engine to keep database recovery time in check	Lazy writer is used by SQL engine only to make sure there is enough memory left in sql buffer pool to accommodate new pages
2	Check point always mark entry in T-log before it executes either sql engine or manually	Lazy writer doesn’t mark any entry in T-log
3	Checkpoint only check if page is dirty or not	Lazy writer clears any page from memory when it satisfies all of 3 conditions. 1. Memory is required by any object and available memory is full 2. Cost factor of page is zero 3. Page is not currently reference by any connection
4	Checkpoint is affected by two parameters 1. Checkpoint duration: is how long the checkpoint can run for. 2. Recovery interval: affects how often it runs.	Lazy writer is affected by 1. Memory pressure 2. Reference counter of page in memory
5	Flush dirty pages to Disk	Flush dirty pages to disk.
6	Flush only Data pages to disk	Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)
7	Default, Occurs approximately every 1 minute, run as per defined frequency	Occurs depending upon memory pressure and resource availability
8	Can be managed with sp_confige -recovery interval option	It is lazy; SQL Server manages by its own.
9	Does not check the memory pressure	Monitor the memory pressure and try maintaining the available free memory.
10	Crash recovery process will be fast to read log as data file is updated.	No role in recovery
11	Occurs for any DDL statement	Occurs per requirement
12	Occurs before Backup/Detach command	Occurs per requirement
13	Depends upon the configuration setting, we can control.	Works on Least recent used pages and removed unused plans first, no user control.
14	for simple recovery it flush the tlog file after 70% full.	No effect on recovery model.
15	can manually /Forcefully run command “Checkpoint”	No command for Lazy Writer
16	Very Less performance impact	No performance impact
17	Members of the SYSADMIN, DB_OWNER and DB_BACKUPOPERATOR can execute checkpoint manually	Not Applied
18	To get checkpoint entry in error log DBCC TRACEON(3502, -1)	Not Applied
19	Checkpoint is affected by Database recovery model	Lazy writer doesn’t get impacted with recovery model of database
20	Auto frequency can be controlled using recovery interval in sp_configure	Works only @ memory pressure , It uses clock algorithm for cleaning buffer cache
21	It keeps no. of dirty pages in memory to minimum	It helps to reduce paging

Max degree of parallelism (MAXDOP) in SQL server 2000/2008/2008R2/2012/2014/2014/2017.

As an SQL Server DBA, recently, I have given interview and interviewer asked below questions in Bangalore(India).

1. What is max degree of parallelism<MAXDOP> in SQL server 2000/2008/2008R2/2012/2014/2014/2017?
2. When SQL Server considers parallel execution plans for queries?
3. How to change max degree of parallelism in SQL server? 
4. Who can change the max degree of parallelism in SQL server? 
5. What permission required change max degree of parallelism in SQL server? 
6. Where we can set of max degree of parallelism in SQL server? 
7. What is the limitation of max degree of parallelism in SQL server?
8. In which scenario, we need to use max degree of parallelism option in SQL server?
9. Examples of max degree of parallelism?

What is max degree of parallelism<MAXDOP> in SQL server 2000/2008/2008R2/2012/2014/2014/2017?

A max degree of parallelism is a number of processor. which says, how many CPU or Microprocessor will use execution plan parallelly to execute the query.

When the SQL instance run the query in SSMS that use microprocessor or CPU to execute the query. At that time, CPU or microprocessor detect max degree of parallelism.

For example, if the max degree of parallelism value set as 4, and we are running any query, so the 4 CPU will parallel execute the execution plan of that query.

When SQL Server considers parallel execution plans for queries?

When run DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP, index data definition language (DDL) operations, parallel insert, online alter column, parallel stats collection, and static and keyset-driven cursor population.

How to change max degree of parallelism in SQL server?

Right Click on SQL Server Instance à Properties à Advance à max degree of parallelism à change the value  à Ok.

OR

USE AdventureWorks2012 

GO  

EXEC sp_configure 'show advanced options', 1; 

GO 

RECONFIGURE WITH OVERRIDE; 

GO 

EXEC sp_configure 'max degree of parallelism', 4; 

GO 

RECONFIGURE WITH OVERRIDE; 

GO

Who can change the max degree of parallelism SQL server? What permission required change max degree of parallelism SQL server?

User should have sysadmin and serveradmin fixed server roles.

What is the limitation of max degree of parallelism SQL server?

If the affinity mask set as default (option is checked), it will not restrict the number of processors available to SQL Server on symmetric multiprocessing (SMP) systems.

If affinity mask not set as default (option is Unchecked) it will restrict the number of processors available to SQL Server on symmetric multiprocessing (SMP) systems.

Where we can set of max degree of parallelism in SQL server?

We can set it on instance level and object level. In below example, we have set on object level.

In which scenario, we need to use max degree of parallelism option in SQL server?

As per the number of query- 

If you have very small number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a larger value.

For example, you can set the MAXDOP value to 16.  

If you a have very large number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a smaller value.

For example, you can set the MAXDOP value to 4. 

As per the Microsoft Stranded- 

MaxDop setting will vary depending upon the number of CPU cores:

CPU Cores (Total)	MaxDop
1	1
2-7	2
8-16	4
>16	8

* Assuming mixed SQL workloads, involving both OLTP and reporting.

Note Any value that you consider using should be thoroughly tested against the specific application activity or pattern of queries before you implement that value on a production server.

Examples:-

To go the table à expend à Indexes à select Index à right click on select index à Properties à Option à change the value (default value is 0).

Example - MAXDOP for Create Index: -

USE AdventureWorks2012; 

GO 

CREATE INDEX IX_Product_NewID ON Product (ID)  WITH (MAXDOP=4); 

GO 

Example - MAXDOP for Rebuild Index: -

USE AdventureWorks2012; 

GO 

Alter INDEX IX_Product_NewID ON Product  REBUILD WITH (MAXDOP=4); 

GO 

Example - MAXDOP for select command: -

USE AdventureWorks2012; 

GO 

Select * from Product (nolock)  ORDER BY ProductID

OPTION (MAXDOP 1)

Note: - If the MAXDOP is set for the query and query execution time is more then check the execution plan of that query, in the plan you can see the parallelism cost in %, and then increase or decrease MAXDOP value. 

Please add if you have some interesting fact on MAXDOP - Jainendra Verma

SQL Server : Some DBCC commands and other T-SQL Script for Performance Tuning in SQL Server

Few below important T-SQL Scripts and DBCC commands for Tuning in SQL Server. Using below queries you can tune the Database in SQL Server-

/*Returns size and fragmentation information for the data and indexes of the specified table or view*/

SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'Sparsed'), NULL, NULL , 'DETAILED');

/*This command is used to scan the current DB

and display the pages and extens and fragemention*/

DBCC SHOWCONTIG

/*This command is used to scan a particular table

and display pages,extens,avg size,row size,

and scanned pages of a table*/

DBCC SHOWCONTIG(tbl_Emp)

SELECT * FROM sys.dm_db_index_physical_stats('HRMS_DB','tbl_Emp' , NULL,NULL , 'LIMITED');

/* To display total pagesize,datapage size,used pages on a database*/

select * from sys.allocation_units

/*To display the rows spread accross the pages in a table*/

DBCC CHECKTABLE ('tbl_Emp')

/*To check the overall Database and tables with rows spread across the pages*/

DBCC CHECKDB (HRMS_DB)

/*DBCC CLEANTABLE reclaims space after a

variable-length column is dropped.

A variable-length column can be one of the following data types:

varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml.

The command does not reclaim space after a fixed-length column is dropped */

/*For the below example let's create a table

and insert some data and then drop the

column and then use the command and see the result*/

/*Step 1: Create table */

drop table dbo.CleanTableTest

CREATE TABLE dbo.CleanTableTest

    (FileName nvarchar(4000),

    DocumentSummary nvarchar(max),

    Document varbinary(max)

    );

/*Step 2: Insert the data into the table

using wizard or from the table which contains

large records */

/*Now Verify the current page counts

 and average space used */

 SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'dbo.CleanTableTest'), NULL, NULL ,'DETAILED');

/*Now find out from the output few columns like

avg_pagesize,record count,pagecount,max record byte size,

avg_record byte size*/

/*Step 3: drop the columns of the table*/

ALTER TABLE dbo.CleanTableTest

DROP COLUMN FileName, Document;

/*Now Check the size of the table*/

SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'dbo.CleanTableTest'), NULL, NULL ,'DETAILED');

/*Now Clean the table*/

/*Even though you drop the column the space

is still reserved so now take a screen shot

and test the min,max,avg,row sized */

/*(After DBCC CLEANTABLE now check the

space used the min,max,row sizes will be different*/

DBCC CLEANTABLE (HRMS_DB,'dbo.CleanTableTest');

/*Now check the space of the table*/

SELECT * FROM sys.dm_db_index_physical_stats

(DB_ID(N'HRMS_DB'), OBJECT_ID(N'dbo.CleanTableTest'), NULL, NULL ,'DETAILED');

DBCC CheckDB(HRMS_DB)

/*This Command is used Removes residual information left on database pages

because of data modification routines in SQL Server*/

/*Before this command check the DBSpace using

sp_helpdb HRMS_DB */

EXEC sp_clean_db_free_space

@dbname = N'HRMS_DB' ;

/*To get the complete infor of all the data type*/

EXEC sp_datatype_info

/*This Command display's all the objects that references the table tbl_Emp

like views,sp,indexes releated to this table*/

EXEC sp_depends @objname = N'tbl_Emp';

/*To Display the Memory Status*/

DBCC MemoryStatus

Note: Please do not run the below query on production server if it's not required. To run above queries we can tune the our database easily.