0

For a number of reasons, but mostly the sheer volume of data, we have 5 MS-SQL (2017) sharded servers to break up the overall load on any individual server. Secondly we have a real-time table that is partitioned by asset ID in order to store the real-time data. Data packets arrive approximately every 2 seconds for each asset. Each data packet can have from 300 to 12000 data points.

The table partitioning and sharded servers work very well. But every once in a while it seems that one of the servers seems to stop using the partitioning which results in deadlocks and timeouts as new data arrives when deleting old data and bulk loading the new data.

My question is why is this occurring, and is there a way to check to see when or why this is happening. I know that this is the issue since I can move the data to a staging table, drop the original table, recreate the original table with the same partition function and scheme and finally copy data back from the staging table to the new "original" table and the issue is resolved.

As requested the partition scheme. The asset ID is what the function uses as stated above

CREATE PARTITION SCHEME [RMAPartitionScheme] AS PARTITION [RMAPartitionFunction] 
TO ([HULL269_484_LymanMartin], [HULL275_541_ClarenceTriche], 
    [HULL277_546_RussellAdams], [HULL278_550_CharlieComeaux], 
    [HULL281_561_CInstaller], [HULL284_587_GrandIsle], 
    [HULL290_621_ShipIsland], [HULL291_638_HornIsland], 
    [HULL296_653_StimStarIV], [HULL293_654_CatIsland], 
    [HULL292_655_SanibelIsland], [HULL297_678_DauphinIsland], 
    [HULL137_679_StimStarBrasil], [HULL809_680_Robin], 
    [HULL1304_690_FastTrack], [HULL174_691_CRuler], 
    [HULL1328_692_FastTiger], [HULL240_695_CFighter], 
    [HULL778_730_Kudu], [HULL064_738_FastCheetah], 
    [HULL063_739_FastServer], [HULLT100_746_ITS100BSM], 
    [HULLBT10_749_ITSBuckeye], [HULL315_779_Elrington], 
    [HULL302_780_MarshIsland], [HULL321_781_Courageous], 
    [HULL316_782_LaTouche], [HULL317_784_BainBridge], 
    [HULL324_787_Contender], [HULL325_788_Champion], 
    [HULL318_790_Ingot], [HULL326_792_Challenger], 
    [HULLN01_797_SeawayMoxie], [HULLAS1_799_IslandCommander], 
    [HULL301_800_DeerIsland], [HULL810_804_Lucy], 
    [DEFAULT_FILE_GROUP])

And the function

CREATE PARTITION FUNCTION [RMAPartitionFunction](int) 
AS RANGE LEFT 
FOR VALUES 
     (484, 541, 546, 550, 561, 587, 621, 638, 653, 654, 655, 678, 
      679, 680, 690, 691, 692, 695, 730, 738, 739, 746, 749, 779, 
      780, 781, 782, 784, 787, 788, 790, 792, 797, 799, 800, 804)
7
  • 5
    also, what do you mean by "stop using the partioning"?
    – mustaccio
    Apr 29, 2019 at 16:46
  • @mustaccio. Prior to partioning the tables as data arrives there are many deadlocks and timeouts on the table because it is being used by another transaction. When it "stops", the symptoms are again deadlocks and timeouts. Yet the tables are partioned. Hence the need to drop, recreate, to solve. Apr 29, 2019 at 17:09
  • Partitioning doesn’t solve performance. It is a scalability feature. Consider doing more with index tuning. Apr 29, 2019 at 17:24
  • 3
    Wouldn't the real issue be your deadlocks? Since we don't know the deadlocks, queries, etc., it's entirely possible that the queries aren't using the partitioning key as one of their predicates... so I wouldn't say partitioning isn't working I'd say look at what the underlying issue is showing. Apr 29, 2019 at 18:21
  • 3
    No, the question is why are you getting deadlocks - which are holding locks - which is most likely (since we have no hard data) the culprit. Partitioning doesn't just "stop" working, but locks, contention, and blocking DOES happen and that's what you should be focused on given the deadlocks already mentioned. Apr 29, 2019 at 22:13

1 Answer 1

5

I imagine the slowness (and thus deadlocks, etc), and the perception that partitioning "stop working," are because your queries suddenly stop getting partition elimination.

There are lots of reasons that you can miss out on partition elimination:

My question is why is this occurring, and is there a way to check to see when or why this is happening.

You should get actual execution plans for the specific slow queries, or maybe just one of them. Get one when it's working great, and one when the problem is happening. You should be able to see the difference (likely a difference in the number of partitions being read). Feel free to post a question about it on the site.

Maybe stats get out of date and you get a hash join with the partitioned table on the probe side. Or you suddenly get a lower cost for the queries and they're being trivial-plan'd / auto-param'd.

The whole workflow of "dropping and recreating everything makes it work" makes me very suspicious that statistics are involved in the root cause of the issue.

The specific answer to your "why" question will likely be in the execution plans. Until then, these are just educated guesses, but hopefully they point you in the right direction =)

0

Not the answer you're looking for? Browse other questions tagged or ask your own question.