SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reduction Of Performance after migration SQL 2000 to SQL 2008


Reduction Of Performance after migration SQL 2000 to SQL 2008

Author
Message
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97097 Visits: 38988
StephenNL (6/14/2014)
SadSorry, 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.

Cool
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)
StephenNL
StephenNL
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 83
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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97097 Visits: 38988
It may have nothing to do with SQL but with how the VM is configured and/or built.

Cool
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)
StephenNL
StephenNL
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 83
Yes, I think so, too...

I guess, this is another topic, isn't it.
andrew gothard
andrew gothard
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2816 Visits: 5971
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.
StephenNL
StephenNL
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 83
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. Wink

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

Any idea ?
andrew gothard
andrew gothard
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2816 Visits: 5971
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. Wink

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.
Marcio Augusto
Marcio Augusto
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 106
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.
StephenNL
StephenNL
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 83
We do not use VMWare, but Hyper V.
StephenNL
StephenNL
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 83
Dear Augusto,

once again, the reason for the database being that slow is caused by a wrong Hyper-V configuration.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search