SQL Server edition upgrade and cached execution plans

Question

I've just upgraded SQL Server 2008 R2 Express Edition to the SQL Server 2008 R2 Standard edition. The setup tool has performed the upgrade without any problems.

However, the promised performance boost is not here. For example, the SQL Server uses the single CPU core.

I assume that the SQL Server still uses the "old" execution plans for SPs. If so, is there a way to rebuild/reset execution plans?

Or should I consider something else when upgrading an Express to Standard edition?

Please also let me know if this should be moved to ServerFault.

Answer 1

Execution plans are in memory only. Upgrading the product implies a replacement of the binaries, that implies a new process. Therefore you cannot possibly have a 'old' plans laying around, every single plan cached or in execution now is a new R2 plan.

As to why you do not see SQL Server using multiple cores, there could be a multitude of reasons:

• you only send requests from one session/connection, thus there is no concurrency at all
• your load does not have queries that can benefit from parallel scans (which is actually a good think for most loads, it means they already scan only small amounts of data)
• your server is hard coded to one CPU affinity mask (this could had been inherited during upgrade)
• your observation methodology could be wrong and SQL Server does actually use all cores