Author: Enrique

No comments

SQL SERVER – Understanding ACID

  By ,

Concurrent operation has big implications for data integrity. In order to guarantee integrity, a relational database system must comply with the ACID principle. This is normally implemented through transactions.

In ACID, A stands for Atomicity, and that means when data is modified by some transaction, either all of its data modifications are performed or none of them are performed.

The C in ACID stands for Consistency. When a data modification transaction completes, all data must be in a consistent state. All constraints must be satisfied, and all internal structures must be correct.

I is all about Isolation. Modifications made by one transaction must be isolated from those made by other concurrent transactions. This also implies that if you redo the same operations with the same starting data, the results will always be the same.

The D in ACID stands for Durability. When a transaction is complete the results are stored permanently in the system and persist even if a system failure occurs. Now in order to get you set up, so that you can follow along with all the demos, let me introduce you to the tools I’ll be using.

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
One Comment

SQL SERVER – Deadlock on ALTER DATABASE to MULTI_USER mode

  By ,

I was trying to alter the database to MULTI_USER mode, but faced this error.

Msg 1205, Level 13, State 68, Line 10 Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Msg 5069, Level 16, State 1, Line 10 ALTER DATABASE statement failed.

Instinctively, you would run sp_who2, and then try to kill the processes connected to that particular database. But what if the SPID is less than 50? You tried to kill it, but you get the the following error.

Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.

Basically, it says that you cannot kill a system process.

So, to resolve this problem, here is the secret Ninja move. Simply execute the command below.

SET DEADLOCK_PRIORITY HIGH 
ALTER DATABASE YourDBName SET MULTI_USER WITH ROLLBACK IMMEDIATE

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL SERVER – Merge Table Partitions

  By ,

Here are the scripts that I use to merge table partitions.

First, let’s check the partition information of a particular table (Indicate the table name in the

SELECT  pf.name AS pf_name ,
            ps.name AS partition_scheme_name ,
            p.partition_number ,
            ds.name AS partition_filegroup ,
            OBJECT_NAME(si.object_id) AS object_name ,
            rv.value AS range_value ,
            SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows
                     ELSE 0
                END) AS num_rows
    FROM    sys.destination_data_spaces AS dds
            JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
            JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
            JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
            LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
                                                          AND dds.destination_id = CASE pf.boundary_value_on_right
                                                                                     WHEN 0 THEN rv.boundary_id
                                                                                     ELSE rv.boundary_id + 1
                                                                                   END
            LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
            LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id
                                             AND si.index_id = p.index_id
                                             AND dds.destination_id = p.partition_number
            LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
                                                           AND p.partition_id = dbps.partition_id
	WHERE pf.name = 'TableName'
    GROUP BY ds.name ,
            p.partition_number ,
            pf.name ,
            pf.type_desc ,
            pf.fanout ,
            pf.boundary_value_on_right ,
            ps.name ,
            si.object_id ,
            rv.value
	ORDER BY 
		p.partition_number
GO

Below is a screenshot of a partitioned table information in one of our environments.

Next, let’s merge the empty partitions. I will merge the empty partitions on the left side only, because I will be needing the partitions on the right side for future growth of my database

Below is the script to merge empty partitions

ALTER PARTITION FUNCTION functionName()
MERGE RANGE(boundaryPoint)


Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL SERVER – Get Table Partition Information

  By ,

Here is a script that retrieves table partition information

SELECT
	pf.name as PartitionFunction,
	ds.name AS PartitionScheme, 
	p.partition_number AS PartitionNumber, 
	CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS PartitionFunctionRange, 
	CONVERT(BIGINT, prv_left.value) AS LowerBoundaryValue, 
	CONVERT(BIGINT, prv_right.value) AS UpperBoundaryValue, 
	fg.name AS FileGroupName,
	p.[row_count] as TotalRows,
	CONVERT(DECIMAL(12,2), p.reserved_page_count*8/1024.0) as ReservedSpaceMB,
	CONVERT(DECIMAL(12,2), p.used_page_count*8/1024.0) as UsedSpaceMB
FROM
	sys.dm_db_partition_stats AS p (NOLOCK)
	INNER JOIN sys.indexes AS i (NOLOCK) ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
	INNER JOIN sys.data_spaces AS ds (NOLOCK) ON ds.data_space_id = i.data_space_id
	INNER JOIN sys.partition_schemes AS ps (NOLOCK) ON ps.data_space_id = ds.data_space_id
	INNER JOIN sys.partition_functions AS pf (NOLOCK) ON pf.function_id = ps.function_id
	INNER JOIN sys.destination_data_spaces AS dds2 (NOLOCK) ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number
	INNER JOIN sys.filegroups AS fg (NOLOCK) ON fg.data_space_id = dds2.data_space_id
	LEFT OUTER JOIN sys.partition_range_values AS prv_left (NOLOCK) ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1
	LEFT OUTER JOIN sys.partition_range_values AS prv_right (NOLOCK) ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
WHERE
	OBJECTPROPERTY(p.[object_id], 'IsMSShipped') = 0 AND
	-- OBJECT_NAME(p.[object_id]) = 'partitioned_table' AND
	p.index_id=1
ORDER BY p.partition_number;

Cheers!

No comments

SQL SERVER – Move Data Files

  By , ,

Here is how you can move data files in SQL Server.

1. Retrieve DB LogicalName and current location.

SELECT name, physical_name, state_desc AS OnlineStatus
FROM sys.master_files  
WHERE database_id = DB_ID(N'AdventureWorks2014')  
GO

2. Set Database offline.

ALTER DATABASE AdventureWorks2014 SET OFFLINE;
GO

3. If your database it is taking too slow to become offline, you should kill all session ID’s that are connected to the database (including the sleeping connections). I have a script below to do just that. Just specify the database name for the @DBName variable.

DECLARE @DBName VARCHAR(50) = 'AdventureWorks2014'
DECLARE @sql NVARCHAR (500)
DECLARE @spid INT
DECLARE @numberOfRowsToKill INT
DECLARE @i INT = 0
DECLARE @sessionID INT

SET NOCOUNT ON

SET @sql = 'sp_who'

IF OBJECT_ID('tempdb..#spids') IS NOT NULL
	DROP TABLE #spids

CREATE TABLE #spids
(
	spid INT,
	ecid INT,
	status VARCHAR(30),
	loginname VARCHAR(30),
	HostName VARCHAR(50),
	Blk INT,
	DBName VARCHAR(30),
	Command VARCHAR(MAX),
	RequestID INT
)

INSERT INTO #spids
EXEC (@sql)


SELECT @numberOfRowsToKill = COUNT(*) FROM #spids WHERE DBName = @DBName

IF  @i < @numberOfRowsToKill
	WHILE @i < @numberOfRowsToKill
	BEGIN
		SELECT TOP 1 @sessionID = spid from #spids WHERE DBName = @DBName
		SET @sql =  'kill ' + CAST(@sessionID AS NVARCHAR(10))

		EXEC (@sql)
		PRINT 'SPID ' + CAST(@sessionID AS VARCHAR) + ' has been killed'

		DELETE FROM #spids WHERE spid = @sessionID
		SET @i = @i + 1
	END
ELSE
	PRINT 'There are no more sessions connected to ' + @DBName + ' database.'


Preview(opens in a new tab)

4. Move the physical files using Powershell. Run Powershell as Administrator and run the following command.

Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CreditData.mdf" -Destination "C:\SQL\DATA"

Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CreditLog.ldf" -Destination "C:\SQL\DATA"

5. Modify the file name. Specify the new file path.

ALTER DATABASE AdventureWorks2014 MODIFY FILE (NAME = 'LogicalName'	,FILENAME = 'C:\SQL\DATA\CreditData.mdf')
GO

ALTER DATABASE AdventureWorks2014 MODIFY FILE (NAME = 'LogicalName',FILENAME = 'C:\SQL\DATA\CreditLog.ldf')
GO

6. Set database online.

ALTER DATABASE AdventureWorks2014 SET ONLINE;
GO

7. Verify that the change is okay.

SELECT name, physical_name, state_desc AS OnlineStatus
FROM sys.master_files  
WHERE database_id = DB_ID(N'AdventureWorks2014')  
GO

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL SERVER – The logical file name is already in use

  By , ,

I just encountered the error below.

Msg 1828, Level 16, State 4, Line 3
The logical file name “OTDB_FG_WRTestResults_01” is already in use. Choose a different

I was able to solve it by executing the following scripts. Essentially, you just have to rename the logical file name to different one, then rename it back to your preferred name.

ALTER DATABASE [DBName] MODIFY FILE (NAME=N'OTDB_FG_WRTestResults_01', NEWNAME=N'OTDB_FG_WRTestResults_01_Temp')

The file name ‘OTDB_FG_WRTestResults_03_Temp’ has been set.

Then change it back to the original name

ALTER DATABASE [OptimalTestDB] MODIFY FILE (NAME=N'OTDB_FG_WRTestResults_01_Temp', NEWNAME=N'OTDB_FG_WRTestResults_01')

Done.

Cheers!

No comments

SQL SERVER – Backup Database to Multiple Files (Faster, and more manageable files)

  By ,

Benefits of writing database backups to multiple files:

  • Backup process can finish faster because it will run using multiple threads
  • Better I/O throughput if you have multiple disk arrays where you can write your  backup files.

Here are two methods of writing your backup to multiple files:

Using SQL Server Management Studio 

Using T-SQL

BACKUP DATABASE [AdventureWorks2014] TO  
DISK = N'C:\SQLBackup\AdventureWorks_backupfile1.bak',  
DISK = N'C:\SQLBackup\AdventureWorks_backupfile2.bak', 
DISK = N'C:\SQLBackup\AdventureWorks_backupfile3.bak',  
DISK = N'C:\SQLBackup\AdventureWorks_backupfile4.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2014-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

No comments

SQL SERVER – Trace Flag 1117 and 1118

  By ,

Trace flag 1117 will allow us to take care of tempdb contention. It permits us to grow our data files at the same time.

We also use 1118 to prevent tempdb contention. Trace flag 1118  deals with how we allocate extents whether it be mixed extents or uniform extents

These trace flags are enabled by default in SQL Server 2016. For prior versions, you have to manually turn-on these trace flags.

Below are the ways to enable Trace Flags:

To Enable Trace Flags at Startup

Go to Configuration Manager > Select your instance > Choose Properties > Go to Startup Parameters tab.

Enable Trace Flags with DBCC TRACEON/TRACEOFF

Add the -1 to enable it globally on the instance

DBCC TRACEON( 1117, -1)

DBCC TRACEON( 1118, -1)

To see which trace are enabled:

DBCC TRACESTATUS

 

 

No comments

SQL SERVER – Changing MAXDOP and Cost Threshold of Parallelism

  By

Firstly, you have to check out Recommendations and guidelines for the “max degree of parallelism” configuration option in SQL Server .

The KB basically says, if we have 8 or more logical processors in a NUMA node, generally we will get the best performance by setting maxdop to 8 or lower.

Secondly, use the script below to get to instantly get the suggested MAXDOP for your SQL instance.

DECLARE @CoreCount int;
DECLARE @NumaNodes int;

SET @CoreCount = (SELECT i.cpu_count from sys.dm_os_sys_info i);
SET @NumaNodes = (
    SELECT MAX(c.memory_node_id) + 1 
    FROM sys.dm_os_memory_clerks c 
    WHERE memory_node_id < 64
    );

IF @CoreCount > 4 /* If less than 5 cores, don't bother. */
BEGIN
    DECLARE @MaxDOP int;

    /* 3/4 of Total Cores in Machine */
    SET @MaxDOP = @CoreCount * 0.75; 

    /* if @MaxDOP is greater than the per NUMA node
       Core Count, set @MaxDOP = per NUMA node core count
    */
    IF @MaxDOP > (@CoreCount / @NumaNodes) 
        SET @MaxDOP = (@CoreCount / @NumaNodes) * 0.75;

    /*
        Reduce @MaxDOP to an even number 
    */
    SET @MaxDOP = @MaxDOP - (@MaxDOP % 2);

    /* Cap MAXDOP at 8, according to Microsoft */
    IF @MaxDOP > 8 SET @MaxDOP = 8;

    PRINT 'Suggested MAXDOP = ' + CAST(@MaxDOP as varchar(max));
END
ELSE
BEGIN
    PRINT 'Suggested MAXDOP = 0 since you have less than 4 cores total.';
    PRINT 'This is the default setting, you likely do not need to do';
    PRINT 'anything.';
END

 

Cost Threshold Parallelism

Cost Threshold of Parallelism is like a bar that we set to control which queries qualifies to go parallel and which queries are cheap, that it is going to get 1 core no matter what.

The default value is 5 which is very low . The starting value for Cost Threshold of Parallelism is 50.

To change the values of these 2 parameters, execute the scripts below.

-- Change MAXDOP value
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  
EXEC sp_configure 'max degree of parallelism', 8;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO    
 
-- Change Cost Threshold for Parallelism value
EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
GO
RECONFIGURE WITH OVERRIDE
GO

-- Verify the changes made
SELECT
	name
	,value
	,value_in_use
	,description
	,is_dynamic
	,is_advanced
FROM
	sys.configurations WITH (NOLOCK)
WHERE
	name IN ('max degree of parallelism','cost threshold for parallelism',)

 

Cheers!

 

No comments

POWERSHELL – Running scripts is disabled on this system (SOLVED)

  By ,

I encountered the following error after executing a script in Powershell.

The reason for this error is because the Execution Policy is set to Restricted by default. We have to change it to prevent this error by running the script below.

PS C:\> Set-ExecutionPolicy RemoteSigned

Verify the change.

PS C:\> get-executionpolicy

You can also run the script below to bypass the policy.

c:\> powershell -ExecutionPolicy ByPass -File script.ps1

Cheers!