Why does SQL Server table partitioning stop working? 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)
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:


data type mismatches and auto-parameterization (see Why Doesn’t Partition Elimination Work?)
not being specific enough with predicates when joining to other tables (see Implied Predicate and Partition Elimination)
limitations related to join types (see why no 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 =)