none
Database locks

    Pergunta

  • 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
    segunda-feira, 27 de fevereiro de 2012 13:40

Respostas

  • 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

    sexta-feira, 2 de março de 2012 07:11
    Moderador
  • 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

    sexta-feira, 2 de março de 2012 14:30
  • Read the below link ,

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

    segunda-feira, 5 de março de 2012 07:54

Todas as Respostas

  • 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

    sexta-feira, 2 de março de 2012 07:11
    Moderador
  • 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

    sexta-feira, 2 de março de 2012 14:30
  • Read the below link ,

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

    segunda-feira, 5 de março de 2012 07:54