3

Just encountered an odd behavior during our regression with a stored procedure which output thousands of records:

The stored procedure could output the results in different orders with same parameters if it's being called more than 20 times in a short while.

Anyone got any idea about this behavior?

For some reason, we are not able to specify the order explicitly in the stored procedure.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rex
  • 2,130
  • 11
  • 12
  • possible duplicate of [Why do results from a SQL query not come back in the order I expect?](http://stackoverflow.com/questions/10999913/why-do-results-from-a-sql-query-not-come-back-in-the-order-i-expect) – David Manheim Dec 24 '14 at 22:51

1 Answers1

1

A parallel query plan sounds a likely culprit ( or different query plans being used on different runs).

If you are not explicitly ordering with an ORDER BY, then no order is guaranteed.

Can you post the Stored Procedure and a query plan?

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • yes, it was due to parallel query plan. an interesting article - http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx the stored procedure is very complex and takes a few seconds to execute normally and that explainis the parallel palns used, as spotted with our testing – Rex Jul 25 '13 at 03:52