SQL Server Performance

Help needed to Process Optimization

Discussion in 'ALL SQL SERVER QUESTIONS' started by G Sivaramakrishnan, Jan 21, 2014.

  1. G Sivaramakrishnan New Member

    Dear All,
    I am working in one Employee Salary Calculation Process Project.The following is my table design and Index Design.In my process Employees are grouped in to Logical Group called Paygroup.In a PayGroup Employees will be attached.If I execute the Payroll process Sequentially its working smoothly.If I Go for Parallel execution so may wait and block type analysed through Activity monitor.Some time Deadlock also noticed.I requset all your help how to over come this.Kindly help me out.If any other information let me know.Every thing will be done through Stored proedure.Insert/update will happen concurrntly in the following table.

    CREATE TABLE dbo.PROCESSED_DATA(
    EMPLOYEE_ID numeric(18, 0) NOT NULL,
    PAY_DATE datetime NOT NULL,
    PAY_GROUP_ID numeric(10,0) NOT NULL,
    MONTH numeric(2, 0) ,
    YEAR numeric(4, 0) ,
    COMPONENT_ID numeric(10, 0) NOT NULL,
    COMPONENT_CODE varchar(10) ,
    COMPONENT_TYPE varchar(1) ,
    CALCULATIOTYPE varchar(1) ,
    ACTUAL_AMOUNT numeric(23,3) ,
    EARNED_AMOUNT numeric(23,3) ,
    LOP_AMOUNT numeric(23,3) ,
    ARREAR_AMOUNT numeric(23,3) ,
    ADJUSTED_AMOUNT numeric(23,3) ,
    LAST_MONTH_ROUNDOFF numeric(23,3) ,
    CURRENT_MONTH_ROUNDOFF numeric(23,3) ,
    CURRENCY_CODE varchar(3) ,
    CONSTRAINT PK_PROCESSEDDATA PRIMARY KEY CLUSTERED
    (
    PAY_GROUP_ID,
    EMPLOYEE_ID ASC,
    PAY_DATE ASC,
    COMPONENT_ID ASC
    )
    )


    CREATE NONCLUSTERED INDEX IX1_MCOMPCODE ON dbo.PA_PROCESSED_DATA
    (
    COMPONENT_CODE ASC,
    COMPONENT_TYPE ASC,
    CALCULATIOTYPE ASC
    ) INCLUDE(PAY_GROUP_ID)
    GO
    CREATE NONCLUSTERED INDEX IX2_MPRODATA_MONTHYEAR ON dbo.PA_PROCESSED_DATA
    (
    PAY_DATE ASC,
    MONTH ASC,
    YEAR ASC
    ) INCLUDE(PAY_GROUP_ID)


    Wait Types :

    MERGE Statement Execution:
    IOCOMPLETION
    LOGBUFFER

    Other Query :
    PAGEIO_LATCH
    SLEEP_TASK
    CXPACKET
    LCK_M_U
    LATCH_EX
    PAGEIOLATCH_SH
    PAGEIOLATCH_UP
    PREEMPTIVE_O

    Regards
    Siva

Share This Page