Fransiscus Setiawan

Technical articles on AI agents, Azure, .NET, architecture, and EV charging systems from Sydney.

Category: SQL Server & Data Page 2 of 5

SQL Server Category

Simulate Deadlock in SQL Server

By

On August 16, 2012

In SQL Server & Data

How to simulate Deadlock in SQL Server, pretty simple

1. Create a table

CREATE TABLE MyDeadlockTable
(
REF INT PRIMARY KEY NOT NULL,
NAME VARCHAR(255)
)
GO

2. Populate a table with the record
INSERT INTO MyDeadlockTable(REF, NAME) VALUES(1, ‘Record 1’)
GO
INSERT INTO MyDeadlockTable(REF, NAME) VALUES(2, ‘Record 2’)
GO

3. On the first window let’s run this
BEGIN TRAN
UPDATE MyDeadlockTable set NAME=NAME WHERE REF = 1;
WAITFOR DELAY ’00:00:15′
UPDATE MyDeadlockTable set NAME=NAME WHERE REF = 2;
COMMIT

4. Open a 2nd window let’s run this
BEGIN TRAN
UPDATE MyDeadlockTable SET NAME=NAME WHERE REF = 2;
WAITFOR DELAY ’00:00:15′
UPDATE MyDeadlockTable SET NAME=NAME WHERE REF = 1;
COMMIT

Table Spool (Lazy Spool) in SQL Server 2005

By

On May 22, 2012

In SQL Server & Data

I have a web app that recently timing out and the timing out exception is actually coming from the SQL Server. When I run the execution plan I found that there is one item called Table Spool (Lazy Spool) which is costing about 20%. I thought it was caused by my full text search but when I drilled down further more is because of DISTINCT keyword. So I decided to change to use GROUP BY instead. In my case it is only a single column so It won’t make any difference at all. Once I’ve changed that my web application running fast and no more timeout

I got this explanation from this website

Explain Distinct:
3) We do an all-AMPs RETRIEVE step from … by way of an
all-rows scan with no residual conditions into Spool x
(group_amps), which is redistributed by hash code to all AMPs.
Then we do a SORT to order Spool 1 by the sort key in spool field1
eliminating duplicate rows.

First there’s a redistribution, then duplicate rows are removed:
Efficient, if there are just a few rows per value [per AMP].
Spool size is always about the same, but may be extremely skewed → 2646: No more Spool Space

Explain Group By:
3) We do an all-AMPs SUM step to aggregate from … by way
of an all-rows scan with no residual conditions, and the grouping
identifier in field 1025. Aggregate Intermediate Results are
computed globally, then placed in Spool x.

First each AMP removes duplicate rows locally (first aggregate) and hashes/redistributes the resulting
rows, then there’s a second aggregation to remove duplicate rows:
Efficient, if there are lots of rows per value [per AMP].
Large number of rows per value Spool → small spool size
Small number of rows per value Spool → large spool size
Spool is never skewed.

Other interesting fact quoted from this article/discussion

http://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week—lazy-spool/

http://www.sql-server-performance.com/forum/threads/table-spool-lazy-spool.15647/

INDEXING: Take a look at your indices to make sure that they’re all covering the columns that you’re selecting out of the tables. You’ll want to aim to get all the columns included in JOINs and WHERE clauses within the indices. All other columns that are in the SELECT statements should be INCLUDEd, or covered, by the index.

OPERATORS: See if you can get rid of the not equals (“<>”) operators, in favor of a single greater than or less than operator. Can this statement and T.CurrentHorizon <> 0 be changed to this and T.CurrentHorizon > 0?

JOINS: Get rid of the subqueries that are JOINing to tables outside of themselves. For instance, this line and FV2.elementId = FV.elementID might be causing some problems. There’s no reason you can’t move that out of a subquery and into a JOIN to dbo.aowCollectedFact FV, given that you’re GROUPing (DISTINCT) in the main query already.

DISTINCT: Change it to a GROUP BY. I’ve got no reason other than, because it’s good practice and takes two minutes.

LAST NOTE: The exception to all the above might be to leave the final subquery, the IF NOT EXISTS, as a subquery. If you change it to a JOIN, it’ll have to be a LEFT JOIN...WHERE NULL statement, which can actually cause spooling operations. No great way to get around that one.

SQL Statistical information – Index usage, etc and also tips on how to find the most costly SQL Server queries using DMV’s

By

On November 7, 2011

In SQL Server & Data

This is just an information for the users that might not know this feature in SQL Server Management Studio, eventhough I’m sure most of people should have known this feature

Basically SQL Server stores all the database usage information including index usage, disk usage, top transaction etc. *This information is not available from the backup file

1. Right click your database, Go to Reports, Go to Standard Reports and you can find all the out of the box reports

Clicking one of the report will give you detailed information – in this sample, I’m using Index Usage Statistics

How to find the top 20 most expensive queries by the total execution time, run this query against the database

SELECT TOP 20
 qs.sql_handle,
 qs.execution_count,
 qs.total_worker_time AS Total_CPU,
 total_CPU_inSeconds = --Converted from microseconds
 qs.total_worker_time/1000000,
 average_CPU_inSeconds = --Converted from microseconds
 (qs.total_worker_time/1000000) / qs.execution_count,
 qs.total_elapsed_time,
 total_elapsed_time_inSeconds = --Converted from microseconds
 qs.total_elapsed_time/1000000,
 st.text,
 qp.query_plan
FROM
 sys.dm_exec_query_stats AS qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
 CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC

-It will give you this result set

– Clicking the query_plan will give you detailed query plan

Usage of Pivot in SQL Server

By

On November 7, 2011

In SQL Server & Data

The usage of PIVOT keyword

In SQL Server we can transform a horizontal rows to be vertically represented by using Pivot Keyword

Run this query to have sample data

CREATE TABLE OrderItems(Order_No INT, ProductName VARCHAR(255), TotalQty INT)

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (112, ‘Sprite’, 5)

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (112, ‘Coke’, 7)

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (112, ‘Lemonade’, 2)

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (113, ‘Apple Juice’, 8 )

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (113, ‘Diet Coke’, 2)

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (114, ‘Coke’, 15)

GO

INSERT INTO OrderItems(Order_No, ProductName, TotalQty)

VALUES (114, ‘Lemonade’, 13)

GO

Running simple select from OrderItems will give you this table

Now we want to look it statistically – maybe for reporting purpose. Run the query below

SELECT Order_No, Sprite, Coke, Lemonade, [Apple Juice], [Diet Coke]

FROM (

SELECT Order_No, ProductName, TotalQty

FROM OrderItems) ItemList

PIVOT (SUM(TotalQty) FOR ProductName IN (Sprite, Coke, Lemonade, [Apple Juice], [Diet Coke])) AS PivotResult

ORDER BY Order_No

GO

It will give you this result set

using BCP to transfer a large amount of data in SQL Server

By

On January 11, 2011

In SQL Server & Data

I would like to transfer hundred millions of records between table in different database server. I tried to use DTS export/import and it’s not fast enough for my needs but i forget that there is a BCP command in SQL which is SQL Server format file.

The speed in my local machine is (22795.13 rows per sec)

Sample of the command: (You need to run it in command prompt), you can find bcp.exe in folder (C:\program files\Microsoft SQL Server\100\Tools\Binn) – The 100 is for SQL Server 2008, for SQL server 2005 it’s 90

Sample

1. You need to generate the BCP File from the source table

bcp MyDatabase.dbo.MyTable out C:\BCP_MyTable -n -S localhost -T -e[BCP_MyTable_ERROR]

*Using -T is for trusted connection

2. You need to import the BCP file to the destination table

bcp MyDatabase.dbo.MyTable in C:\BCP_MyTable -n -S localhost -T -e[BCP_MyTable_ERROR]

for further command line reference click here

*UPDATE:

you can configure your server to to reduce the amount of transaction log during bulk copy/insert transaction by executing

EXEC SP_DBOPTION MyDatabase, ‘SELECT INTO/BULKCOPY’, TRUE

bcp MyDatabase.dbo.MyTable in C:\BCP_MyTable /b 20000 -n -S localhost -T -e[BCP_MyTable_ERROR]

What I did was to add extra parameter of /b and the number 20000 (you should play around with the number to see the best one fit your situation) after it means the number of the records per transaction. Please be careful if you don’t put /b parameter, sql server by default will commit all the records at once. but if you put /b parameter then it will commit the transaction per x amount of records specified. If you transfer large data, you shouldn’t commit all at once because it will take sometime. In my case I transferred 18 millions of records

I’d recommend you to save the BCP file and copy the file over if it’s in different server because you need to be careful with the bandwidth of the network and the risk if the network connection is interrupted

Restore SQL Server database through network path

By

On November 10, 2010

In SQL Server & Data

I had a problem where I need to restore database using shared folder on the network. I tried to copy it locally to my local harddrive but then the connection is not stable enough. Hence I decided to create a script

Single Media

USE MASTER
DECLARE @fromdatabase varchar(50)

DECLARE @todatabase varchar(50)
DECLARE @sql as nvarchar(4000)
DECLARE @BackupPath as nvarchar(250)



/***************************************/
/* Change this string to match the database
	name you want to create a backup of  */
Set @fromdatabase = 'DailySupport-1'
Set @todatabase = 'MyDatabaseName'
/***************************************/



/***************************************/
/*  Which db server are you using?     */
/***************************************/

Set @BackupPath='\\sharednetwork\SQL\DatabasesBackup\'

/***************************************/
/***************************************/


/** Kill off any existing connections **/
SET @sql = ' use master'
SET @sql = @sql + ' EXEC sp_KILLSPIDS ' + @todatabase + ';'
Print @sql
EXEC sp_executesql @sql


/** Perform the restore **/
SET @sql = 'RESTORE DATABASE ' + @todatabase
SET @sql = @sql + ' FROM DISK = N''' + @BackupPath + '' + @fromdatabase + '.bak'''
SET @sql = @sql + ' WITH FILE = 1,NOUNLOAD, REPLACE, STATS=10 ;'
Print @sql
EXEC sp_executesql @sql

Multiple Media(Sometimes the backup source is stripped multiple files hence if you try to run it with above script you will get this error “The media set has 2 media families but only 1 are provided. All members must be provided.”)

USE MASTER
DECLARE @fromdatabase varchar(50)
DECLARE @fromdatabase2 varchar(50)

DECLARE @todatabase varchar(50)
DECLARE @sql as nvarchar(4000)
DECLARE @BackupPath as nvarchar(250)



/***************************************/
/* Change this string to match the database
	name you want to create a backup of  */
Set @fromdatabase = 'DailySupport-1'
SET @fromdatabase2 = 'DailySupport-2'
Set @todatabase = 'MyDatabaseName'
/***************************************/



/***************************************/
/*  Which db server are you using?     */
/***************************************/

Set @BackupPath='\\sharednetwork\SQL\DatabasesBackup\'

/***************************************/
/***************************************/


/** Kill off any existing connections **/
SET @sql = ' use master'
SET @sql = @sql + ' EXEC sp_KILLSPIDS ' + @todatabase + ';'
Print @sql
EXEC sp_executesql @sql


/** Perform the restore **/
SET @sql = 'RESTORE DATABASE ' + @todatabase
SET @sql = @sql + ' FROM DISK = N''' + @BackupPath + '' + @fromdatabase + '.bak'''
SET @sql = @sql + ', DISK = N''' + @BackupPath + '' + @fromdatabase2 + '.bak'''
SET @sql = @sql + ' WITH FILE = 1,NOUNLOAD, REPLACE, STATS=10 ;'
Print @sql
EXEC sp_executesql @sql

Read and Update XML Node of XML datatype in SQL Server

By

On July 22, 2010

In SQL Server & Data


Select Node value

This is a simple explanation on how to select a particular node from xml data type of a record in SQL server.

Sample XML


  00003479
  Brodie's HomeSton
  NULL
  
  
  AU941170
  2010-07-22T09:42:20
  vcAuditorDesc,chUpdateStaffCode,dtUpdateDateTime,dtUpdateDateTime

So what I want to do is basically to get the value of ChangedColumns node

select  xmlDetail.value('/iReference[1]/ChangedColumns[1]/.','VARCHAR(255)')
from tblReferenceHistory
WHERE intReferenceHistoryID = 125


Update Existing Node value

Sample XML


  00003479
  Brodie's HomeSton
  NULL
  
  
  AU941170
  2010-07-22T09:42:20

Changed column has no value in it and you want to update it. Basically you can’t edit the existing node value so what you need to do is to readd it and remove the first instance of that node

DECLARE @ChangedColumns VARCHAR(255)

SELECT @ChangedColumns = xmlDetail.value('/iReference[1]/ChangedColumns[1]/.','VARCHAR(255)')
FROM tblReferenceHistory
WHERE intReferenceHistoryID = 125

-- first update - add a new ... node
UPDATE tblReferenceHistory
SET xmlDetail.modify('insert {sql:variable("@ChangedColumns")} as last into (/iReference)[1]')
WHERE intReferenceHistoryID = 124

-- second update - remove the empty  node
UPDATE dbo.tblReferenceHistory
SET xmlDetail.modify('delete (/iReference[1]/ChangedColumns[1])[1]')
WHERE intReferenceHistoryID = 124

SELECT xmlDetail.value('/iReference[1]/ChangedColumns[1]/.','VARCHAR(255)')
FROM tblReferenceHistory
WHERE intReferenceHistoryID = 124

How to set variable from Dynamic SQL

By

On July 9, 2010

In SQL Server & Data

Here is the scenario, You have a stored procedure that builds dynamic insert SQL which means the “INSERT” statement is being composed on the fly based on the table name but at the same time you need to get SCOPE_IDENTITY or the last identity number inserted for further processing (e.g History table)

	DECLARE @nvcSQL			NVARCHAR(MAX)
	DECLARE @pvcTableName	VARCHAR(255)
	DECLARE @pvcColumn		VARCHAR(255)
	DECLARE @pvcValue		NVARCHAR(MAX)

	--This is used to actually get the primary key for identity record
	DECLARE @dvcPrimaryKey VARCHAR(255)
	SET @dvcPrimaryKey = ''

	DECLARE @dintPrimaryKey INT
	SELECT @dvcPrimaryKey = ISNULL(NAME,'') FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = @pvcTableName AND Is_Identity = 1


	-- Only execute when there is a @pvcValue.
	IF @pvcValue  ''
	BEGIN
		SELECT @nvcSQL = 'INSERT INTO ' + @pvcTableName + '(' + @pvcColumn+ ') VALUES (' + @pvcValue + ')'
		SELECT @nvcSQL = @nvcSQL + ';' + 'SELECT @dintPrimaryKey = SCOPE_IDENTITY()'
		--PRINT @nvcSQL

		EXEC sp_executesql @query = @nvcSQL, @params = N'@dintPrimaryKey INT OUTPUT', @dintPrimaryKey = @dintPrimaryKey OUTPUT
		--EXEC (@nvcSQL)
	END

	IF @dvcPrimaryKey  ''
	BEGIN
		SELECT (@dvcPrimaryKey + ' = ' + CAST(@dintPrimaryKey AS VARCHAR(10)))  AS PrimaryKey
	END
	ELSE
	BEGIN
		SELECT '' AS PrimaryKey
	END

Question: why do we need to use sp_executesql instead of EXEC?because EXEC just execute a SQL command without returning/expecting any result from it

Shrink all databases and Set it to Simple in SQL Server

By

On January 6, 2010

In SQL Server & Data

Normally, I need to run this script against dev sql server in order for me to free up some space

create table #temp_dbs_table
(
[db_name] sysname not null primary key,
[mod] tinyint not null default 1
)

insert into #temp_dbs_table ([db_name])
select
name
from
master..sysdatabases
where
dbid > 4 --- skip master, tempdb, model and msdb databases

declare @db_name sysname

set @db_name = ''

while @db_name is not null
begin
set @db_name = NULL

select top 1 @db_name = [db_name] from #temp_dbs_table where [mod] = 1

if @db_name is NULL
break

print '--------------------------------------------------'

print '> Database: ' + @db_name

print '> Changing recovery mode to simple'

declare @n_cmd nvarchar(4000)

set @n_cmd = 'alter database [' + @db_name + '] set recovery simple'

exec sp_executesql @n_cmd

print '> Shrinking database'

set @n_cmd = 'dbcc shrinkdatabase([' + @db_name + '])'

exec sp_executesql @n_cmd

update #temp_dbs_table set [mod] = 0 where [db_name] = @db_name
end

drop table #temp_dbs_table

I got this script from here

TRY….CATCH Rollback Transaction In SQL Server 2005

By

On May 23, 2009

In SQL Server & Data

This feature has been sometime in SQL Server 2005 in SQL Server 2000 you normally use @@TRANCOUNT to detect any exception but now in SQL Server 2005 you can use try catch block.

In this snippet, there are 2 INSERT statement and 1 UPDATE statement. I’m expecting when there is any failure (e.g the UPDATE statement fails) then all the preceding INSERT/UPDATE/DELETE within the TRANSACTION block will be canceled

e.g

BEGIN TRY
BEGIN TRANSACTION transABC

INSERT INTO TABLEA (col1,col2,col3) VALUES ('a','b','c')
INSERT INTO TABLEB (col1,col2,col3) VALUES ('a','b','c')

UPDATE TABLEA SET col2='abcde' WHERE col1 = 'a'

COMMIT TRANSACTION transABC
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION transABC --RollBack in case of Error

-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

Page 2 of 5

Previous

Next

Powered by WordPress & Theme by Anders Norén