Very slow select query when database moved from 2005 to 2012

When we moved a database from SQL Server 2005 running on Windows Server 2003 R2 x86 with 4GB RAM and 1vCPU (vmware) to SQL Server 2012 Windows Server 2012 R2 SP2 (11.0.5623) with 8GB RAM and 2 vCPU (vmware and same host and storage) we noticed that Select querys where very slow. A simple "select * from tablename" took 60 sec in new server and 10 sec in old server.

We moved the database like:
1. backup and restore
2. Upgrade database compatibility level
3. dbcc updateusage (dbname)
4. Rebuild index and update stats (full scan).

Drop and recreate index and stats, still very slow, 60 sec.

After Days of troubleshooting we tried:
1. Create new empty database in SQL Server 2012.
2. Import data from old server to new server.
3. select * from tablename, result 8 sec, 2 sec faster than old server!!

Is this a known issue?
I can't find anything like this when I'm googling.

 

August 26th, 2015 10:37am

I take it that these tables are fairly big in size?

May I also guess that the contain LOB columns?

As they are "SELECT *" queries without filter, there is not much that can happen with query plans. And since you rebuilt all indexes, fragmentation is not an issue - save for LOB columns.

The other possibility is serious fragmentation in the file system of the database file. Or for that matter of the file that holds the VM disk.

Free Windows Admin Tool Kit Click here and download it now
August 26th, 2015 10:52am

Hi!

The table has 86000 rows, data size is 1354MB and index size 6,5MB. No LOB columns. We have nvarchar, bit, int, binary.
One clustered index on pk.

When we run the Query on the "2005 db" it takes 5-15% cpu.
When we run the Query on the "2012 db" it takes 60% cpu

All new databases created on server runs fine.

We moved a SQL Server 2008 R2 database with backup/restore on the same server. No problem with that one, select * from tablename with millions of rows, same exec time as original server.

August 26th, 2015 11:05am

Can u show us the actual query with execution plan and Client statistics. We would need to understand the execution plan it is using which would basically determine the cost of query.

Also I want you to run the same query with NO LOCK option to see if its based on some lock issue

Free Windows Admin Tool Kit Click here and download it now
August 26th, 2015 11:10am

One thing I found out quite recently is that with 2012 and 2014 SQL Servers after altering a table (e.g. adding or deleting a column) you need to issue the Command ALTER TABLE dbo.MyTable REBUILD.

It may be worthwhile trying this if you have dropped and recreated the indexes.

August 26th, 2015 11:12am

A "SELECT * FROM TABLE" query with no joins or filters that takes 60% of the CPU?  If all the data is stored, I would expect to see hardly and CPU usage as it should just be pulling data from disk or memory.  If the CPU is crunching numbers, there has to be some reason for it.  Unpersisted calculated columns?  Maybe the table is actually a view?  I would inspect the table definition first.
Free Windows Admin Tool Kit Click here and download it now
August 26th, 2015 11:14am

Also to add to my earlier suggestion..

Is the Maximum Degree of Parellelism set to 0?

Have you used PerfMon to check on disk queues etc?

Is AV software scanning your data files?

Did you run the query through the estimated execution plan?

Was there any other processes running at the time that could have been slowing your query down?

August 26th, 2015 11:29am

Its a table. The Query is:

SET STATISTICS IO  ON
SET STATISTICS TIME  ON
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
GO
Select * from handelse
Stats:

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.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:

   CPU time = 16 ms,  elapsed time = 50 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

   CPU time = 78 ms,  elapsed time = 93 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

(86675 row(s) affected)

Table 'handelse'. Scan count 1, logical reads 87507, physical reads 519, read-ahead reads 87502, lob logical reads 214002, lob physical reads 86675, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

   CPU time = 3438 ms,  elapsed time = 42124 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.




Free Windows Admin Tool Kit Click here and download it now
August 26th, 2015 11:43am

First thing is Select * is not recommended as it makes your query analyzer to make an entire table to scan using a Index scan. Try using where and required columns to allow a Index seek.

Also try the same query using

select * from handelse with NO lock

August 26th, 2015 1:12pm

Table 'handelse'. Scan count 1, logical reads 87507, physical reads 519, read-ahead reads 87502, lob logical reads 214002, lob physical reads 86675, lob read-ahead reads 0.

So you have at least one LOB column in this table. This explains why performance was improved after a table reload.

I would expect that the difference between the two machines are due to the machines as such, rather than the SQL Server version, although you relation is contradictive. In your first post you referred to two different machines with different operating systems. Next you said "the same server". But maybe that referred to the same physical host server for the VMs?

Free Windows Admin Tool Kit Click here and download it now
August 26th, 2015 2:11pm

So you have at least one LOB column in this table. This explains why performance was improved after a table reload.

I would expect that the difference between the two machines are due to the machines as such, rather than the SQL Server version, although you relation is contradictive.

Erland,

If the two VM's are on the same host, why would the performance differ?  I would think that the storage would behave identically on both VM's., would it not?

August 26th, 2015 5:44pm

Hi Erland, I missed that, yes one is a LOB column. Is it a known issue (performance loss) with LOB columns and backup/restore move?

The first post is right. Two different virtual guests running on same (esx) host.

Jinu, "select * from" is just a performance Query test, not used in app.

Free Windows Admin Tool Kit Click here and download it now
August 27th, 2015 2:36am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics