question

Wilfred van Dijk avatar image
Wilfred van Dijk asked

What determines the execution plan?

I have a SQL 2014 database with SP1 running in 2K8 compatibility mode. On a server, with Windows server 2012 and 128GB of memory, MSSQL 2014 standard the execution plan for a specific query is different compared to my local development PC, Windows 8.1 also with SQL 2014 std and 16GB. No traceflags active... The database on my PC is a restore from the server. I want to know why these plans are different. Is it memory? OS? What about server updates? I'm stuggling for several days with this issue. Any help would be appreciated
execution-plan
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
It could be number of CPUs (or rather cores) in the different Machines, it could be server settings (threshold for parallellism, maxdop-settings etc). It could also be that on the server, you have an old plan which SQL Server finds is OK enough to not create a new plan. If it's a production server, you probably don't want to do DBCC FREEPROCCACHE for all plans, but you might want to try removing the specific plan with DBCC FREEPROCCACHE and then run the Query again, to see if SQL Server generates a new plan. If statistics on the underlying tables are old, updating the statistics on the tables might also make SQL Server consider creating a new plan on next Query execution. Try UPDATE STATISTICS (possibly using the option WITH FULLSCAN, unless the table has billions of rows - then you're in for waiting for completion of UPDATE STATISTICS). Updating statistics is by default done automatically, but with a very, very small sample size and done on Query execution. I Think it's a good idea to create jobs which updates statistics nightly or weekly, to make sure you have updated statistics on your tables. Ola Hallengren has created scripts which are used in organisations all over the World. Look at ola.hallengren.com
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Most likely it's the hardware. Take a look at the first operator. You'll see information there about some of the compile-time factors, optimization level, parameters used, stuff like that. It'll give you your best guess. If you can, post the plans. If you need to, put 'em through SQL Sentry Plan Explorer and use the anonymize function.
1 Like 1 ·
Thanks for your reply. I am already using Ola's scripts for daily reindex/stats. FREEPROCCACHE is not the case. MaxDOP and CostTreshold are also equal, but CPUs are different. I've read some documentation about the optimizer (for example the ebook from Grant Fritchey), but I can't tell if hardware factors are influencing the execution plan.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.