Q: Performance when adding additional partitions I have a requirement to add an additional 4.8k partition ranges to a large partitioned table and I can see that doing this has a small impact on query performance.
I added the additional partitions in 1k increments at first and could see that the there was a slight increase in duration on each occasion. In total the average has gone from ~10ms to ~20ms so the overall performance is still acceptable, but I just wondered if the additional partitions SQL Server would have to eliminate when fetching the data would explain this?
Query we're using is:
SELECT this_.INST_NO AS INST1_12_0_,
       this_.ACCT_NO AS ACCT2_12_0_,
       this_.REC_NO AS REC3_12_0_,
       this_.TRAN_DATE AS TRAN4_12_0_,
       this_.JRNL_NO AS JRNL5_12_0_,
       this_.TRAN_TYPE AS TRAN6_12_0_,
       this_.VAR_AREA AS VAR7_12_0_
FROM dbo.tbl1 this_
WHERE (
          this_.INST_NO = CONVERT(BINARY(3),@p0)
          AND
          (
              (
                  this_.ACCT_NO = CONVERT(BINARY(16),@p1)
                  AND this_.REC_NO IN ( @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15,
                                        @p16, @p17, @p18, @p19
                                      )
              )
              OR
              (
                  this_.ACCT_NO = CONVERT(BINARY(16),@p20)
                  AND this_.REC_NO IN ( @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33,
                                        @p34, @p35, @p36, @p37, @p38, @p39
                                      )
              )
          )
      );

