A view runs on our development server in 30 seconds, but it takes over 10 minutes on the production server. Why?

Question

We have two SQL Server servers, both with virtually the same hardware. One is a development server, where we create and test Transact-SQL code, and the other is our production SQL Server. Here's my problem. When a particular view runs on the development server, it takes about 30 seconds. But when I run the identical view on the production server, the view takes over 10 minutes to run. The databases on both servers are almost identical. We periodically copy the production database to the development server so that we are working with the same data and the same indexes. What could be causing the difference in time for this particular view to run?

Answer

Assuming that the servers and the databases are virtually the same, and the statistics have been updated on both of them, here's what I suggest you check. First, do an Execution Plan of the view that is causing the problem on the development server. Find out if this particular view is using parallelism as part of the execution plan. I am guessing that this is the case.

Now, do an Execution Plan of the view on the production server. Find out if this particular view is using parallelism as part of the execution plan. My guess is that it is not.

If I am write so far, then check the SQL Server Parallelism setting for each server. I bet that it is set to "Use All Available CPUs" on the development server, and set to use only one CPU on the production server. If I am right, then set the production server to "Use All Available CPUs," and the performance difference of the same view on both servers should be resolved, and performance should be virtually identical on both servers.

By default, SQL Server  is set to use "Use All Available CPUs" on a server. If this is not your current setting, then this has been changed by someone.

Essentially, this setting tells SQL Server whether or not to try and attempt to execute queries in parallel using multiple processors. If the default "Use All Available CPUs" option is selected, then SQL Server will attempt to run queries in parallel, using multiple CPUs. This can sometimes result in dramatic performance differences in a query's performance. But if this option is turned off, then SQL Server will only use one CPU to execute a query, which can be much slower.

Keep in mind that if SQL Server is set to use parallelism, that it may not always use it. If the server is very busy, it may not be used, but when the server is less busy, it may be used. This means that sometimes the view will be fast, and other times, slow (depending if parallelism is being used or not).

On the other hand, sometimes parallelism can cause queries to take longer to execute than if parallelism is not used. In these cases, you can use a Query Hint to turn off parallelism for the odd-acting query.

A Response

"You were right. The development server was set to use only one CPU, not all of the available CPUs. Once we changed the setting, the view ran in the same amount of time on both servers."