SQL Server Performance

Slow update on SS2000, fast update on SS2005

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by goodideadave, Jun 4, 2008.

  1. goodideadave New Member

    This is kind of a long story. Since this involves questions on SQL Server 2000 and SS2005, I wasn't sure if this was the right forum.
    I have an update that runs as part of our data warehouse load each Tuesday morning. When it runs on our production server (SQL Server 2000 SP3 Std, Win Server 2003, 4, 2.7gHz processors, 4M memory, PAE) , the update takes about 9 hrs. 40 min. It starts running at 2 a.m., so it has the server to itself. We borrowed some space on another, underutilized server (SQL Server 2005 RTM Std, 4, 3.6 gHz processors, 4MB memory) and it runs in about THREE MINUTES. I did not expect such a difference. Here is some DDL:
    UPDATE BAR SET ENCOUNTER = CASE
    WHEN Orig_Inv_From_Split IS NULL AND A.TOT_CHG > 0 THEN 1
    WHEN Orig_Inv_From_Split IS NOT NULL AND A.TOT_CHG > 0 THEN .5
    WHEN Orig_Inv_From_Split IS NULL AND A.TOT_CHG < 0 THEN -1
    WHEN Orig_Inv_From_Split IS NOT NULL AND A.TOT_CHG < 0 THEN -.5
    WHEN Orig_Inv_From_Split IS NULL AND A.TOT_CHG = 0 THEN SIGN(UNITS)
    WHEN Orig_Inv_From_Split IS NOT NULL AND A.TOT_CHG = 0 THEN SIGN(UNITS) * .5
    END
    FROM BAR INNER JOIN TRANSAC_INV_COUNTER A
    ON A.INV_NUM = BAR.INV_NUM AND A.COUNTER = BAR.COUNTER
    WHERE BAR.Bar_Provider_Key <> 840
    CREATE TABLE dbo.BAR(
    GROUP_ZZ_Key int NOT NULL,
    PATIENT_Key numeric(12, 0) NOT NULL,
    INVOICE_Key numeric(12, 0) NOT NULL,
    TRANSACTION_Key numeric(18, 0) NULL,
    ADM_DT datetime NULL,
    BAR_Billing_area_Key int NULL,
    BAR_Specialty_Key int NULL,
    BAR_Diagnosis_Code_1_Key int NULL,
    BAR_Diagnosis_Code_3_Key int NULL,
    BAR_Diagnosis_Code_2_Key int NULL,
    BAR_Current_FSC_Key int NULL,
    INV_CRE_DT datetime NULL,
    INV_CRE_PD int NULL,
    INV_NUM int NULL,
    BATCH_NUM int NULL,
    SEC_USER_NAME varchar(50) NULL,
    SEC_USER_DEPT varchar(50) NULL,
    Orig_Inv_From_Split int NULL,
    BAR_Location_Key int NULL,
    BAR_Provider_Key int NULL,
    BAR_Secondary_Provider_Key int NULL,
    BAR_Medical_Group_Key int NULL,
    BAR_Place_of_Service_Key int NULL,
    BAR_Program_Key int NULL,
    Visit_Number int NULL,
    BAR_Original_FSC_Key int NULL,
    BAR_HMO_Profile_Key int NULL,
    PAY_AMT money NULL,
    BAR_PAYMENT_CODE_Key int NULL,
    PAY_CODE_NUM int NULL,
    MOD varchar(20) NULL,
    POST_DT datetime NULL,
    POST_PD int NULL,
    BAR_Procedure_Code_Key int NULL,
    BAR_VENDOR_Key int NULL,
    SER_DT datetime NULL,
    UNITS numeric(10, 3) NULL,
    CHARGE_AMOUNT money NULL,
    ADJ_AMT money NULL,
    CREDIT_AMT money NULL,
    DEBIT_AMT money NULL,
    MEMBER_NUM varchar(20) NULL,
    BAR_PCP_Key int NULL,
    BAR_PLAN_Key varchar(10) NULL,
    BAR_EMPLOYER_Key int NULL,
    CIN numeric(12, 0) NULL,
    MC_FFS char(3) NULL,
    MAX_SER_DT datetime NULL,
    MIN_SER_DT datetime NULL,
    Work_RVU money NULL,
    Total_RVU money NULL,
    SMG_ANC money NULL,
    SWMG_ANC money NULL,
    MGMA_WRVU money NULL,
    MGMA_TRVU money NULL,
    MGMA_WTYPE varchar(25) NULL,
    MGMA_TTYPE varchar(25) NULL,
    PRYR_WRVU money NULL,
    PRYR_TRVU money NULL,
    PRYR_WTYPE varchar(25) NULL,
    PRYR_TTYPE varchar(25) NULL,
    COUNTER int IDENTITY(1,1) NOT NULL,
    ENCOUNTER numeric(12, 9) NULL,
    MEMBER_FSC_KEY int NULL,
    DX_NUM varchar(30) NULL,
    ADJ_FSC_KEY int NULL,
    CORR_INV_NUM int NULL,
    MCA_APPROVED_AMT money NULL,
    LINE_PAY_AMT money NULL,
    ENCOUNTER_TEST numeric(12, 9) NULL,
    ANC_TYPE varchar(4) NULL,
    INJ_CREDIT money NULL,
    CONTRACTUAL_ADJUSTMENT float NULL,
    BAD_DEBT_ADJUST float NULL,
    MC_ALLOWABLE float NULL,
    REPORTING_CATEGORY varchar(30) NULL,
    NEW_PATIENTS int NULL,
    BAR_Referring_Physician_Key int NULL,
    BAR_Hospital_Key int NULL,
    SNSMG_ANC money NULL
    ) ON PRIMARY
    GO
    CREATE TABLE dbo.TRANSAC_INV_COUNTER(
    INV_NUM int NULL,
    COUNTER int NULL,
    TOT_CHG money NULL
    ) ON PRIMARY
    CREATE CLUSTERED INDEX Ind_Key ON dbo.TRANSAC_INV_COUNTER
    (
    INV_NUM ASC,
    COUNTER ASC
    )WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
    The table BAR has over 17 million rows, table transac_inv_counter has 3.6 million rows. There is a clustered index on both tables.
    Lots of FK constraints between BAR and other tables that I did not port over to the borrowed server for this test. No triggers on BAR.
    I ran profiler on the borrowed server, but since it's just the one update statement it really just told me "you are here. I can't run profiler on the production server until mid-month because of processing loads. The execution plan does not look unusual.
    So what accounts for this difference? Some theories:
    1. Yes, SQL Server 2005 is that much faster than SQL Server 2000.
    2. A 3.6gHz, 4 way server is that much faster than a 2.7 gHz, 4-way server.
    3. Our production server has not had disks defragged in about two years. D drive with .mdf is 410GB, E drive with logs is 135GB, and F with tempdb is 558GB. But we have no idea how long a defrag will take, and I hesitate to tell our user community that it could run overnight or it could take three days.
    4. There is something wrong with our production server. We are departmental developers and the server lives in a data center at the mother ship. If it burst into flames, the server monkeys would likely notice it, but they can't run any diagnostics other than "the power light is green".
    I like theory 1, since in the next few months we are due to get a new server and will go to SS2005. We know we need to defrag the production server, but is there a good way to determine how long that will take? So what am I missing? Any ideas? Thanks.
  2. moh_hassan20 New Member

    Can you generate execution plan on both servers , and tell us.
    What SQL engine do you use: standard addition or Enterprise edition. ?
    did you run statistics on both servers ?
  3. goodideadave New Member

    From the borrowed server where it ran in three minutes (we can't run profiler on the production server until mid-month due to processing loads):
    Execution Tree
    --------------
    Clustered Index Update(OBJECT:([BARTest].[dbo].[BAR].[IND_POST_DT]), SET:([BARTest].[dbo].[BAR].[ENCOUNTER] = [Expr1007]) WITH ORDERED PREFETCH)
    |--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(numeric(12,9),CASE WHEN [BARTest].[dbo].[BAR].[Orig_Inv_From_Split] IS NULL AND [BARTest].[dbo].[TRANSAC_INV_COUNTER].[TOT_CHG] as [A].[TOT_CHG]>($0.0000) THEN (1.0000) ELSE CASE WHEN [BARTest].[dbo].[BAR].[Orig_Inv_From_Split] IS NOT NULL AND [BARTest].[dbo].[TRANSAC_INV_COUNTER].[TOT_CHG] as [A].[TOT_CHG]>($0.0000) THEN (0.5000) ELSE CASE WHEN [BARTest].[dbo].[BAR].[Orig_Inv_From_Split] IS NULL AND [BARTest].[dbo].[TRANSAC_INV_COUNTER].[TOT_CHG] as [A].[TOT_CHG]<($0.0000) THEN (-1.0000) ELSE CASE WHEN [BARTest].[dbo].[BAR].[Orig_Inv_From_Split] IS NOT NULL AND [BARTest].[dbo].[TRANSAC_INV_COUNTER].[TOT_CHG] as [A].[TOT_CHG]<($0.0000) THEN (-0.5000) ELSE CASE WHEN [BARTest].[dbo].[BAR].[Orig_Inv_From_Split] IS NULL AND [BARTest].[dbo].[TRANSAC_INV_COUNTER].[TOT_CHG] as [A].[TOT_CHG]=($0.0000) THEN CONVERT_IMPLICIT(numeric(12,4),sign([BARTest].[dbo].[BAR].[UNITS]),0) ELSE CASE WHEN [BARTest].[dbo].[BAR].[Orig_Inv_From_Split] IS NOT NULL AND [BARTest].[dbo].[TRANSAC_INV_COUNTER].[TOT_CHG] as [A].[TOT_CHG]=($0.0000) THEN sign([BARTest].[dbo].[BAR].[UNITS])*(0.5) ELSE NULL END END END END END END,0)))
    |--Top(ROWCOUNT est 0)
    |--Parallelism(Gather Streams, ORDER BY:([BARTest].[dbo].[BAR].[POST_DT] ASC, [Uniq1002] ASC))
    |--Table Spool
    |--Sort(DISTINCT ORDER BY:([BARTest].[dbo].[BAR].[POST_DT] ASC, [Uniq1002] ASC))
    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([BARTest].[dbo].[BAR].[POST_DT], [Uniq1002]))
    |--Hash Match(Inner Join, HASH:([A].[INV_NUM], [A].[COUNTER])=([BARTest].[dbo].[BAR].[INV_NUM], [BARTest].[dbo].[BAR].[COUNTER]), RESIDUAL:([BARTest].[dbo].[TRANSAC_INV_COUNTER].[INV_NUM] as [A].[INV_NUM]=[BARTest].[dbo].[BAR].[INV_NUM] AND [BARTest].[dbo].[TRANSAC_INV_COUNTER].[COUNTER] as [A].[COUNTER]=[BARTest].[dbo].[BAR].[COUNTER]))
    |--Bitmap(HASH:([A].[INV_NUM], [A].[COUNTER]), DEFINE:([Bitmap1019]))
    | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([A].[INV_NUM], [A].[COUNTER]))
    | |--Clustered Index Scan(OBJECT:([BARTest].[dbo].[TRANSAC_INV_COUNTER].[Ind_Key] AS [A]))
    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([BARTest].[dbo].[BAR].[INV_NUM], [BARTest].[dbo].[BAR].[COUNTER]), WHERE:(PROBE([Bitmap1019])=TRUE))
    |--Clustered Index Scan(OBJECT:([BARTest].[dbo].[BAR].[IND_POST_DT]), WHERE:([BARTest].[dbo].[BAR].[BAR_Provider_Key]<>(840)) ORDERED)
  4. moh_hassan20 New Member

    i expected your execution plan , and I found your sql is very optimized. [:D]
    i agree with you with option 1. SQL Server 2005 is that much faster than SQL Server 2000.

  5. MartinSmithh New Member

    Is ENCOUNTER part of the clustered index on the table? If so are there any maintenance plans on the live server that shrink the database?


  6. goodideadave New Member

    No, the clustered index is on BAR.POST_DT. We run a shrink on the database every Tuesday night.
  7. MartinSmithh New Member

    There is such a huge order of magnitude difference between the 2 results that it seems unlikely to me that you would achieve this simply by moving to SQL2005 without rewriting any SQL. I would suspect option 4 or that something else is happening on the live server to interfere with things.
    When I've migrated to SQL2005 I have found some things to be a little bit slower, other things to be a little bit faster and most of the speed improvements I have got have been by taking advantage of new language features rather than happening automatically.

  8. goodideadave New Member

    Here is the execution plan from the live server. I don't understand why it says "Clustered Index Scan" when it shouldn't have to look at that particular index (Post_Dt).
    Execution Tree
    --------------
    Clustered Index Update(OBJECT:([BARTest].[dbo].[BAR].[IND_POST_DT]), SET:([BAR].[ENCOUNTER]=[Expr1005]))
    |--Compute Scalar(DEFINE:([Expr1005]=Convert(If ([BAR].[Orig_Inv_From_Split]=NULL AND [A].[TOT_CHG]>0.00) then 1.0000 else If ([BAR].[Orig_Inv_From_Split]<>NULL AND [A].[TOT_CHG]>0.00) then 0.5000 else If ([BAR].[Orig_Inv_From_Split]=NULL AND [A].[TOT_CHG]<0.00) then -1.0000 else If ([BAR].[Orig_Inv_From_Split]<>NULL AND [A].[TOT_CHG]<0.00) then -0.5000 else If ([BAR].[Orig_Inv_From_Split]=NULL AND [A].[TOT_CHG]=0.00) then Convert(sign([BAR].[UNITS])) else If ([BAR].[Orig_Inv_From_Split]<>NULL AND [A].[TOT_CHG]=0.00) then (sign([BAR].[UNITS])*0.5) else NULL)))
    |--Top(ROWCOUNT est 0)
    |--Parallelism(Gather Streams)
    |--Table Spool
    |--Hash Match(Aggregate, HASH:([Bmk1000]), RESIDUAL:([Bmk1000]=[Bmk1000]) DEFINE:([BAR].[UNITS]=ANY([BAR].[UNITS]), [BAR].[Orig_Inv_From_Split]=ANY([BAR].[Orig_Inv_From_Split]), [A].[TOT_CHG]=ANY([A].[TOT_CHG])))
    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([Bmk1000]))
    |--Hash Match(Inner Join, HASH:([A].[INV_NUM], [A].[COUNTER])=([BAR].[INV_NUM], [BAR].[COUNTER]), RESIDUAL:([BAR].[INV_NUM]=[A].[INV_NUM] AND [BAR].[COUNTER]=[A].[COUNTER]))
    |--Bitmap(HASH:([A].[INV_NUM], [A].[COUNTER]), DEFINE:([Bitmap1012]))
    | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([A].[INV_NUM], [A].[COUNTER]))
    | |--Clustered Index Scan(OBJECT:([BARTest].[dbo].[TRANSAC_INV_COUNTER].[Ind_Key] AS [A]))
    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([BAR].[INV_NUM], [BAR].[COUNTER]), WHERE:(PROBE([Bitmap1012])=TRUE))
    |--Clustered Index Scan(OBJECT:([BARTest].[dbo].[BAR].[IND_POST_DT]), WHERE:([BAR].[BAR_Provider_Key]<>840) ORDERED)
  9. goodideadave New Member

    I'm still having this problem. Anyone have an idea why it says "Clustered Index Scan" when it shouldn't have to look at that particular index (Post_Dt).

Share This Page