2

I have taken a backup of a database from a development environment in order to tune a query. When running the original query on the dev environment people started complaining that the database response time for the application becomes very bad.

Due to this I've taken a backup of the database from the dev environment and restored it on my local machine.

Now, when running those problematic queries, on the dev machine it took 02:22 min/sec to run, while on my machine it takes 00:20 min/sec.

I had a look at the CPU utilization on the dev machine and it does spike up one thread to almost 100% (4 logical threads available, and by the way the DEV machine is a VM).

Now, when running the same queries on my machine, one thread spikes up to about 70% and that's it. No paralelism seems to happen on occur on either machines (check the execution plans at the end of the question).

I've ran the queries in both environments with SET STATISTICS TIME ON and SET STATISTICS IO ON and the number of pages used/read by the query is the same in both scenarios, except the CPU timings are very different.

DEV:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(5599 row(s) affected)
Table '#dashboard'. Scan count 1, logical reads 884, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 52407 ms,  elapsed time = 84516 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(5599 row(s) affected)
Table '#3E73D64A'. Scan count 5599, logical reads 5794, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#dashboard'. Scan count 1, logical reads 884, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 55250 ms,  elapsed time = 57998 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Local Machine:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 357 ms.

(5599 row(s) affected)
Table '#dashboard'. Scan count 1, logical reads 884, physical reads 0, read-ahead reads 9, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 5469 ms,  elapsed time = 15237 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 30 ms.

(5599 row(s) affected)
Table '#A8E344A2'. Scan count 5599, logical reads 5599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#dashboard'. Scan count 1, logical reads 884, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 4890 ms,  elapsed time = 4976 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

My conclusion so far is that because the dev machine is a VM, the CPU could be slower than what shows up in Computer->Properties (Xeon E5630 @ 2.53Ghz), compared to my local machine (i7-4600M @ 2.90Ghz) and because of this the compute time takes much more.

I'm trying to understand what is the real difference that made my queries run so much faster on my machine, seeing that nothing has changed in the database structure and the number of rows returned in both environments is the same as well as the amount of data it needs to go over in order to run the queries is the same.

The execution plans are the same in both environments:

enter image description here

Is it possibly something else that I am missing?

  • You are using #temp table, so take a look at the tempDB configuration , see if there is the same configuration. Also look at cost threshold for parallelism - to have the same environment. sys.dm_os_waiting_tasks - see what is running in the same time – Sabin Bio Oct 19 '16 at 16:05
  • Additionally, in both environments make sure you're running identical versions of SQL Server via select @@version, run DBCC TRACESTATUS() to check for all trace flags running in both environments (one trace flag CAN make a difference), fully re-index both environments to start a level playing field (restore ruins indexes), and make sure the VM is running the latest processor optimized software stack for the O/S. – Sting Oct 19 '16 at 16:15
  • Might find some differences based on the checklists here and here. Also, what happens in each environment when you use option (recompile)? Can you convert the multi-statement TVF to an inline TVF? – Aaron Bertrand Oct 19 '16 at 16:31
  • what is MAXDOP setting on the server? how many tempdb files on the server vs local machines? Do you have SSD on your machine? What is storage on dev? There are many things that could lead to drastically different results including current load, system configuration and sql server options. – Vladimir Oselsky Oct 19 '16 at 17:45
  • 1
    if you diff the XML of the actual execution plans are there any differences? – Martin Smith Oct 19 '16 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.