why select query has writes, how to avoid? RRS feed

  • Question

  • Hi all,

    we have 2 database instances have same DB structure.

    same select query in one of db instance has 5 times query cost than in another one.  Through SQL server profiler trace.  I see the query in first db instance has writes, in second db instance has no writes only reads. 

    I'd like to know what could cause the query in first db instance had writes, how to aviod it?

    thank you in advance


    • Edited by alanzhouoad Tuesday, August 16, 2016 10:21 PM typo
    Tuesday, August 16, 2016 10:20 PM


All replies

  • Your best course of action is to keep both databases in top shape performance wise. The database engine generates an execution plan based on the current state of the database. One database may have up-to-date indexes/statistics, the other may have stale.

    Reference articles:

    Top 10 SQL Server Performance Tuning Tips

    How to Tune Microsoft SQL Server for Performance

    Indexing Dos and Don’ts  Increase performance, not overhead

    Can you post the query in question and the related index information?

    Kalman Toth Database & OLAP/AI Architect SQL Server 2016 Database Design
    New Book : Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

    • Edited by Kalman Toth Tuesday, August 16, 2016 11:29 PM
    Tuesday, August 16, 2016 11:24 PM
  • Gather the query plans from the two instances and compare these to identify the differences - this should give you a better idea about where to look.

    It is likely to be either index or statistics differences and you are probably seeing spills to tempdb.

    Martin Cairney SQL Server MVP

    Tuesday, August 16, 2016 11:46 PM
  • These are two database in the same SQL Server instance, or two instances on the same server, or two different servers?

    Memory available, cores available, and dynamic load conditions can affect how SQL generates and executes a plan.  Sort and hash spills might explain things, you can turn those on in profiler as well.


    Wednesday, August 17, 2016 12:34 AM
  • thank you, you all mention the cause. How should I make the statistics be same in those 2 databases (resides in different server)?
    Wednesday, August 17, 2016 3:43 AM
  • thank you Kalman.  Because one database works well, so no need to do performance tunning.

    I'd like to know how to make the indexes/statistics to update-to-date.

    Wednesday, August 17, 2016 3:48 AM
  • Thank you Josh. they are in two servers that have same software and hardware. what SQL server confguration/parameters should I check, compare?
    Wednesday, August 17, 2016 3:51 AM
  • Is the amount of data the same in the two databases?

    The writes could come from worktables, or hash/sort spills.

    You need to check the query plans in the two databases and work from there.

    Wednesday, August 17, 2016 8:19 AM
  • Hi Erland,

    the amount of data is not same, the difference is 9000, (11%).

    I used DBCC SHOW_STATISTICS, found every index's  statistics is updated this morning.  the rows number is same the rows sampled. I think which means the statistics is up-to-date.

    Wednesday, August 17, 2016 3:18 PM
  • the amount of data is not same, the difference is 9000, (11%).

    That may be enough to produce different query plans. Did you look at them?

    Wednesday, August 17, 2016 9:24 PM
  • Hi alanzhouoad ,

    I’m writing to follow up with you on this post. Was the issue resolved? If you issue has resolved, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.

    If you have any other questions, please let me know.

    Friday, August 26, 2016 2:00 AM