Arid regions are ecologically fragile and occupy a substantial portion of the global terrestrial surface. In these regions, ecosystem services (ESs) are strongly constrained by water availability and, more importantly, by the balance between water supply and demand. However, the nonlinear responses and threshold mechanisms linking water supply–demand balance to ES dynamics remain unclear. Taking the Kubuqi Desert in the “Great Bend” of the Yellow River as the study area, this study quantified the Comprehensive Ecosystem Service Index (CESI) and the Water Supply–Demand Ratio (WSDR) by integrating the InVEST model, RWEQ model, the RUSLE model, Water Balance Method, and so on. The dual-constraint line method and elasticity coefficient approach were integrated to identify the constraint effects and critical thresholds of WSDR on CESI. Ecological management zones were further delineated by integrating the inflection-point intervals of the dual-constraint lines with the threshold intervals identified by elasticity coefficients. The results showed that CESI remained relatively low, with a maximum value of approximately 0.5, suggesting that the overall ES level was still limited, but exhibited a continuous increasing trend. The regional water supply–demand pattern gradually shifted from deficit toward relative balance, although agricultural water use still accounted for about three-quarters of total consumption. CESI showed a nonlinear threshold response to WSDR: mild water deficit suppressed CESI growth, whereas moderate water surplus promoted CESI recovery by alleviating water constraints and improving ecosystem functioning. Thresholds identified by elasticity coefficients mainly occurred near critical transitions between water deficit and surplus. Based on ES supply and threshold sensitivity, nine ecological management zones were identified, with priority enhancement areas accounting for approximately 75%. These findings provide a threshold-based basis for ecological zoning and differentiated restoration in arid regions.