SQL server 2014: different performances (huge “num

问题:

I have 2 identical VM (16 vCPU,RAM:64GB) with the same database, same tables and views and same number of lines. View1 has 4.7M lines.

On VM1 (UAT) a SELECT TOP 1000 .. FROM View1 answers in less than 1 s.

On VM2 (PROD) the same query answers in 4 minutes.

I've checked properties of database, tables, view1 and didn't find any difference between VM1 and VM2. I've checked statistics but it shows that nothing has to be refeshed.

The plan of execution on VM2 shows:

• estimated number of executions : 1000
• number of executions : 4.2M

On VM1:

• estimated number of executions : 1000
• number of executions : 900

What could be the problem with VM2 (PROD) that is supposed to be exactly the same than VM1 (UAT)?

Here are the execution plans of "SELECT TOP 1000 .. FROM View1" on both VM :

Execution plans on both VM: UAT and PROD

They seem to be very similar.

Here are UAT vs PROD statistics :

What can explain the huge difference of numbers of logical reads (and read-ahead reads) between the 2 environments ???

By advance, thanks to help me to understand this "mystery".

回答1:

Eventhough it is two different VM's with same configuration it has lots of differences:

1. Did you checked the number of IOPS in both the server?

10 MB/s during select top 1000 ... from View1

2. Eventhough both the RAM are same how much it is allocated to SQL Server?

60GB/64GB allocated for "SQL Server 2014" on both VM

3. Data load patterns : How data load happens and how Indexes are rebuilt/re-organized? What is the schedule for that?

No DBA "on hand", I hope he'll be back next week

4. Statistics updates : Eventhough you have indexes rebuilt, does the statistics upto date in both the server?

Please, how to force statistics update ? On both VM, last update was 1.5 months ago ! But it says statistics are up to date...

5. How network test works? Is there any connectivity issues if you are querying from remote machine?

The tests are done on VM1 and VM2 via "Terminal server" with "SQL Server Management Studio". Network activity is very low during the test on VM2

6. Any extra triggers, constraints on the same table without your knowledge?

Theoretically not but ... perhaps. I'll see with the DBA when he'll be back.

Points 3 and 4 are vital for estimated and actual number of executions and their differences.

Additional info about View1: it uses 2 tables - view0 : 4.7M rows, no problem of performance (select top 1000 < 1 s.) - table2 : 3 rows

During the test of View1 on VM2, 100% of 1 CPU is used.

Is there a tool to compare quiclky parameters of 2 databases and its components (tables, views, indexes...) ?

Thanks for your help ! :)

For point 3: You can execute below script:

SELECT a.index_id, name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDatabase'), OBJECT_ID(N'YourTable'), NULL, NULL, NULL) AS a  
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;  

Results on UAT & PROD

Results UAT & PROD

Do Index rebuild or Reorganize as per below percentages:

avg_fragmentation_in_percent value Corrective statement

5% and < = 30% ALTER INDEX REORGANIZE 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

https://msdn.microsoft.com/en-us/library/ms189858.aspx

For point 4:

You can run below script:

update statistics yourtablename

Which will update your statistics. After that you can check the statistics histogram by using

dbcc show_statistics(yourtablename, yourindexname)

It has even distribution in both servers which make Query optimizer to select optimal plan.

Considering that both the execution plans are similar. If you post both the execution plans we can find the exact difference.