Database locks

Question

Hi All,                                                                     


I would like to share with you a problem I'm facing with SQL Server 2008
I have a very large table with partition on a Date column
Every hour I'm getting new information from text files, around half a million lines that I want to insert into my historical table,
In order to get best performance, I extract the specific date partition (based on the dates in my files) to a side table,
Then I'm inserting to that table the new data from my files and using "SWITCH PARTITION" I return the data into the original table.
This way the insert is very quicker.
Altough the table is smaller it's still takes several minutes to insert to the side table.


This process is backup with Transaction command and untill I do Commit my large table is locked on any paratition, 
even ones I'm not working on.


Maybe my solution is not the correct one and there is an alternative way to do it, I will be very happy to learn and understand where I'm worng.


Below is a script to build the table and the partition and a simulation of the problem,
I've marked the commit command, so after you finish to run it,
Please open a new window and run the following command:


SELECT * FROM AggTable WITH(NOLOCK) WHERE DateOnly = '2011-01-02' 


This is the simulation:








/****** Object:  Table [dbo].[AggTable]    Script Date: 09/05/2011 17:20:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AggTable]') AND type in (N'U'))
DROP TABLE [dbo].[AggTable]
GO
/****** Object:  Table [dbo].[AggTable]    Script Date: 09/05/2011 17:20:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AggTable_NEW]') AND type in (N'U'))
DROP TABLE [dbo].[AggTable_NEW]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AggTable_Log]') AND type in (N'U'))
DROP TABLE [dbo].[AggTable_Log]
/****** Object:  PartitionScheme [PS_Daily]    Script Date: 09/05/2011 17:19:05 ******/
IF  EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'PS_Daily')
DROP PARTITION SCHEME [PS_Daily]
GO
/****** Object:  PartitionFunction [PF_Daily]    Script Date: 09/05/2011 17:19:17 ******/
IF  EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'PF_Daily')
DROP PARTITION FUNCTION [PF_Daily]
GO
/****** Object:  PartitionFunction [PF_Daily]    Script Date: 09/05/2011 17:13:03 ******/
CREATE PARTITION FUNCTION [PF_Daily](DATE) AS RANGE LEFT FOR VALUES (
N'2011-01-01', N'2011-01-02', N'2011-01-03', N'2011-01-04', N'2011-01-05')
GO
 
/****** Object:  PartitionScheme [PS_Daily]    Script Date: 09/05/2011 17:13:36 ******/
CREATE PARTITION SCHEME [PS_Daily] AS PARTITION [PF_Daily] ALL TO ([PRIMARY])
GO
CREATE TABLE AggTable(
DateOnly  Date,
AdvertiserId
int,
ActioinId
int,
Total  Int) ON [PS_Daily](DateOnly)
CREATE TABLE AggTable_NEW(
DateOnly  Date,
AdvertiserId
int,
ActioinId
int,
Total  Int) ON [PS_Daily](DateOnly) 
CREATE TABLE AggTable_Log(
DateOnly  Date,
AdvertiserId
int,
ActioinId
int,
Total  Int)  
GO
INSERT INTO AggTable
SELECT '2011-01-01',150,100,50 
GO 20 
GO
INSERT INTO AggTable
SELECT '2011-01-02',650,100,64 
GO 43  
GO
INSERT INTO AggTable
SELECT '2011-01-03',23,245,99 
GO 66
GO
INSERT INTO AggTable
SELECT '2011-01-04',243,34,12 
GO 11  
GO
INSERT INTO AggTable
SELECT '2011-01-05',132,34,132 
GO 1999  
 GO
INSERT INTO AggTable_Log
SELECT '2011-01-05',32,34,1342 
GO 3455  
 
SELECT CONVERT(DATE,prv.value) AS [Date] ,  CAST(p.rows AS float) AS [RowCount]    FROM sys.tables AS tbl    with(nolock)    
INNER JOIN sys.indexes AS idx with(nolock) ON idx.object_id = tbl.object_id and idx.index_id < 2         
INNER JOIN sys.partitions AS p with(nolock) ON p.object_id=CAST(tbl.object_id AS int)     
INNER JOIN sys.partition_range_values AS prv with(nolock) ON prv.boundary_id = p.partition_number    
WHERE tbl.name  = 'AggTable' AND CAST(p.rows AS float) > 0 
 
SELECT COUNT(*) FROM AggTable_Log
 
BEGIN TRANSACTION 
ALTER TABLE AggTable    
SWITCH PARTITION $PARTITION.PF_daily('2011-01-05')       
TO  AggTable_NEW PARTITION $PARTITION.PF_daily('2011-01-05') 
SELECT tbl.name, CONVERT(DATE,prv.value) AS [Date] ,  CAST(p.rows AS float) AS [RowCount]    FROM sys.tables AS tbl    with(nolock)    
INNER JOIN sys.indexes AS idx with(nolock) ON idx.object_id = tbl.object_id and idx.index_id < 2         
INNER JOIN sys.partitions AS p with(nolock) ON p.object_id=CAST(tbl.object_id AS int)     
INNER JOIN sys.partition_range_values AS prv with(nolock) ON prv.boundary_id = p.partition_number    
WHERE tbl.name  IN( 'AggTable' , 'AggTable_NEW' )
AND CAST(p.rows AS float) > 0 
INSERT INTO AggTable_NEW
(DateOnly,AdvertiserId,ActioinId,Total)
SELECT * FROM AggTable_Log
ALTER TABLE AggTable_NEW  
SWITCH PARTITION $PARTITION.PF_daily('2011-01-05')       
TO  AggTable   PARTITION $PARTITION.PF_daily('2011-01-05')
SELECT tbl.name, CONVERT(DATE,prv.value) AS [Date] ,  CAST(p.rows AS float) AS [RowCount]    FROM sys.tables AS tbl    with(nolock)    
INNER JOIN sys.indexes AS idx with(nolock) ON idx.object_id = tbl.object_id and idx.index_id < 2         
INNER JOIN sys.partitions AS p with(nolock) ON p.object_id=CAST(tbl.object_id AS int)     
INNER JOIN sys.partition_range_values AS prv with(nolock) ON prv.boundary_id = p.partition_number    
WHERE tbl.name  IN( 'AggTable' , 'AggTable_NEW' )
AND CAST(p.rows AS float) > 0  
--COMMIT

Answer 1

Hi Ofer,

If there is SQL Server Integration Services tool available in your company, your solution is really not much good. In your case, you need to first extract data into Staging table from the source files and then insert into historical table by using "SWITCH PARTITION". I would like to create a package where the data of files will directly extracted into buffer and then fast load into the historical table. The fast load option configured in destination should be equal to "SWITH PARTION" in performance. So, the execution time should be less than yours.

thanks,
Jerry

Answer 2

For such circumstances  of archiving huge  data entity , we have 3 probable tiers :

• Switch out data using
  schema partitioning functionalities  .
• ETL processes based
  on either DTS (Data transformation Service) of SSIS (SQL Server Integration
  Service) or through direct Import& Export functionality of DB Service.
• Bulk Merge commands of
  2008 which you could establish more faster & scalable  data warehousing solution for archiving
  purposes ..Please have a look at my article for this regard:

http://www.sqlserverpath.org/blog/2012/02/15/data-warehousing-workshop-34/

Moreover , please bear in mind that all such tiers represent  some kind of OLTP transactions where they
need for the smallest index sizes to reduce their IO cost while any of these OLTP
transactions and thereby we could boost significantly their performance

Have a look on indexing regard at my article below to
help you better for boosting performance of your archiving solutions :

http://www.sqlserverpath.org/blog/2012/01/09/towards-t-sql-queries-of-0-sec-sixth-part/

---

Think more deeply of performance terms

Answer 3

Read the below link ,

http://sqlskills.com/blogs/paul/post/sql-server-2008-partition-level-lock-escalation-details-and-examples.aspx