Stored procedure giving different output for static database


  • Hi ,

    I have a stored procedure which gives different number of record every time I run it . But when I run the same query in SSMS it gives correct and same number of records.

    sorry I cannot paste the query here , but the process is similar to below

    1. Create #temp1 table from static table

    2. create #temp2 from #temp1 and static table

    3. create #temp3 from #temp1 and #temp2 

    4. create 3 CTE from #temp3 and static tables

    5. select statement from #temp3 , static tables and CTE with where clause W1 union all select statement from #temp3 , static tables and CTE with where clause W2

    Please let me know what could be the reason for this behavior .




    Thursday, February 8, 2018 12:45 AM


All replies

  • I have changed the max DOP to 1 and then it is showing correct data every time.

    Now I don't see any benefit of parallelism . 

    Thursday, February 8, 2018 1:48 AM
  • if you post sample data that would be easy to reproduce the problem. 

    What version you are using ?

    FIX: Incorrect results are returned when you run a query that uses parallelism in the query execution plan in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012

    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 8, 2018 6:28 AM
  • Hi Uri ,

    below code was returning different data every time , though data is static .


    con_con_id ,

    isnull(dateadd(day, -1, lead(cast(convert(date, rev_date, 103) as date)) over (partition by con_con_id order by cast(convert(date, rev_date, 103) as date))), '2099-01-01') as rev_end_date

    from contract

    here rev_date is date datatype .

    For each con_con_id there are different revision number (rev_no) and each revision has rev_date which can be same as its previous revision.

    Now I have used hint maxdop 1 to run this query in single thread instead of removing parallelism on database .

    Server version : SQL serer 2016 13.0.1742.0

    Thursday, February 8, 2018 7:39 AM
  • If you want us to help, we need a repro. That includes CREATE TABLE, INSER and SELECT. As simplified as possible. I know it might not be easy, but without we we have nothing to go on. 

    It might be some incorrect assumption by you (like the old classic that you can expect some ordering without ORDER BY) or it might be a bug in SQL Server. 

    Without a repro, you could either open an incident with MS and see if they can work privately with you to sort this out, or just go with MAXDOP 1.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, February 8, 2018 10:14 AM