• Possible causes:

    1) Blocking. Use sp_whoisactive to check for this.

    2) Review Estimated and Actual rows from the production run. Something is likely causing very low estimates and you are getting index seek/nested loop join plan which is spinning a bajillion logical IOs in prod.

    3) Parameter sniffing issue. Crack open the actual execution plan xml and check the parametercompilevalue and see how that compares to the actual execution value for parameter inputs.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service