why select query has writes, how to avoid?

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

Feng 

Answer 1

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

Answer 2

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

Answer 3

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.

Josh

Answer 4

thank you, you all mention the cause. How should I make the statistics be same in those 2 databases (resides in different server)?

Answer 5

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.

Answer 6

Thank you Josh. they are in two servers that have same software and hardware. what SQL server confguration/parameters should I check, compare?

Answer 7

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.

Answer 8

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.

Answer 9

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?

Answer 10

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.

Regards,
Lin