Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Reduction Of Performance after migration SQL 2000 to SQL 2008 Expand / Collapse
Author
Message
Posted Saturday, June 14, 2014 2:27 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 23,089, Visits: 31,634
StephenNL (6/14/2014)
Sorry, again... I have been looking forward too early. I am now up to a certain point...
Did the following:

Copying the original database for test purposes, upgraded it to 90% comp....
Basically I am now able to simulate productive mode.

I did what GilaMonster said:
After updating statistics and defragmentating indexes, I ran the same script again, and again whithout no significant success.

How is it possible that the same script has better perfomances running under SQL 2000 ????

The goal is to achieve a query time less than 30 seconds.
Within the productive database, the query time is about at 3-5 mins.

BTW:
Refreshing views does not make much sense, because the certain script does not refer to one.

Any other idea ?


Two things. First, drop the %. Compatibility mode 90 is SQL Server 2005 compatibility.

Second, to really help you we need to see the query, the actual execution plan, and the DDL for the tables and indexes involved.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1580871
Posted Saturday, June 28, 2014 10:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 AM
Points: 33, Visits: 78
Hello,

I made some test series with the combination of the db showing apparently slow query execution times, sql server 2008 r2, windows server 2008 r2 running a vm under hyper v as well as under the host itself.

The database size is approx. 6 GB.
The test-server (host) has 32 GB Ram and a 8 core cpu.

Interestingly the lack of perfomance is appearing only under the virtual machine.
This shows quite impressively that the reason for the bad performance is not the db it self.
Post #1587205
Posted Saturday, June 28, 2014 10:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 23,089, Visits: 31,634
It may have nothing to do with SQL but with how the VM is configured and/or built.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1587210
Posted Saturday, June 28, 2014 5:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 AM
Points: 33, Visits: 78
Yes, I think so, too...

I guess, this is another topic, isn't it.
Post #1587286
Posted Tuesday, July 8, 2014 2:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 330, Visits: 3,219
How many cores does your VM have - what's their use %? If it's low (you're over provisioned) you need to drop some cores, overprovisioning VCPUs on a SQL Server VM can be a significant perfrmance hit - ideally you want your VCores to be as few as possible to cope wit the workload so they're busy. Also, is your RAM 'yours'? Are you getting ballooned?

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Post #1590224
Posted Tuesday, July 8, 2014 5:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 AM
Points: 33, Visits: 78
Here some data of our vm:

NUMBER OF VM: 1

RAM: 16 GB (static)
CORES: 4
HD: IDE, 150 GB.

Filesize (mdf): 5.92 GB
Filesize (log): 3.93 GB

The capacity of the cpu (VM) during the query is runnig is approx. 2 %.
I guess, the reason is not the CPU for being that slow. ;)

Maybe, we should choose another network configuration, because part of the work is done by the client.

Any idea ?
Post #1590311
Posted Tuesday, July 8, 2014 8:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 330, Visits: 3,219
StephenNL (7/8/2014)
Here some data of our vm:

NUMBER OF VM: 1

RAM: 16 GB (static)
CORES: 4
HD: IDE, 150 GB.

Filesize (mdf): 5.92 GB
Filesize (log): 3.93 GB

The capacity of the cpu (VM) during the query is runnig is approx. 2 %.
I guess, the reason is not the CPU for being that slow. ;)

Maybe, we should choose another network configuration, because part of the work is done by the client.

Any idea ?

firstly - assuming you have no other workloads on that machine kicking CPU significantly over 25%, I would cut to 1 CPU. If possible, I'd also consider whether this could be consilidated with other servers. Too many cores on a VM can actually degrade performance.
I'd also get your VMWare admin to swear blind you're not being ballooned. If you're new to SQL Server on VMs I'd have a look at these;
http://www.brentozar.com/sql/virtualization-best-practices/
also Jonathan Keyannes on this issue on PluralSight (not free this one $29 pcm) and also Brent's paid for VM and SAN training video ($400 ish IIRC) if you're feeling flush or can get the company to swing for it. You are going to find stuff in there that's going to surprise your VM/SAN admin(s). As mine have found out.
It's NOT like running a File Server, SQL Server "Doesn't Play Nice With Others" on these systems


I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Post #1590380
Posted Wednesday, July 9, 2014 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:28 AM
Points: 2, Visits: 102
1 - check the size of your tempdb.
This is usual vilain.

2 - drop and recreate the view and maybe the envolved indexes.
No logic, ok, but works.
Post #1590783
Posted Wednesday, July 9, 2014 10:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 AM
Points: 33, Visits: 78
We do not use VMWare, but Hyper V.
Post #1590996
Posted Wednesday, July 9, 2014 10:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 AM
Points: 33, Visits: 78
Dear Augusto,

once again, the reason for the database being that slow is caused by a wrong Hyper-V configuration.
Post #1590998
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse