SQL Server Performance

Performance Degradation On a Long Running Process

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by BenBetter, Jan 27, 2009.

  1. BenBetter New Member

    I have a long running process that is mostly executing combinatorial operations through T-SQL stored procs. the longer it runs, the more ram it uses, the lower the % processor statistic falls, and the fewer operations per minute the process performs. The first hour is solid, but within 24 hours the process slows to 50%. Is there anything specific to T-sql that causes performance degradation over time on a long running process? This is a dedicated server with 30+ gb of ram and quality processors.
  2. ndinakar Member

    What are you doing that is taking > 25 hrs?
  3. BenBetter New Member

    We're generating independent demand forecasts for a pretty big set of items that we sell.
  4. Kewin New Member

    Degrading performance over time isn't antything inherent to T-SQL in particular, but rather a general symptom of resource starvation.
    We always have bottlenecks somewhere, and when we hit one of those or more, performance of whatever we're doing starts to decline.
    For 'long running' things, I believe it's more likely you'll hit some hard limits along the way, and probably that is what you're seeing.
    The usual solution in such scenarios is to 'chunk up' the workload in pieces, small enough to avoid hitting 'the brick wall'.
  5. moh_hassan20 New Member

    T-sql is not preferred for math calculation
    Try to use the new CLR stored procedures for doing your calculations.

Share This Page