database comparison

[collierd2]

Hello

We have 2 identical database on 2 seperate servers (SQL Server 2000)
One live server, one test (live is a higher spec)

Running the exact same script on the live server takes a number of times (20+) longer

I've backed up and copied the database from live to test and given it a different name

Executing select scripts still takes a number of times longer

This proves the issue lies with the structure of the database and not the server

How do I compare databases (tables, indexes etc) as they are clearly not the same
Is there a simple command that returns differences?

Thanks

Damian.

 

[ptheriault]

Have you run a showplan for the query? Is the execution plan the same on both servers? Does everything run slower on Live?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[collierd2]

The execution plan differs on both servers AND on the same server when both databases reside there
Not sure if everything runs slower but there are around 250 tables
As I don't trust them being identical I don't really want to go through all possibilities

 

[ptheriault]

Well,
There is a very good tool by Idera named SQL Change Manager. It can compare two databases and synchronize them for you. You may want to check it out. There is a 14 day trial version.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLSister]

SQl Compare from redgate will do this as well.

How busy is your live server? Could the proc be slower because it is running into locks that it doesn't run into when run on dev?


"NOTHING is more important in a database than integrity." ESquared

 

[SQLDenis]

SQL Compare from RedGate is excellent


However here are some questions I have

Are your statistics up to date
Are you tables fragmented
Do you see a lot of blocking



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[collierd2]

Hi

stats up to date and tables not fragmented
it's a management info server that's only just gone in
1 months data (10000 order lines), 30 users (used intermitantly)
No blocking looks to be occuring

I'll look at one of these products and see what happens

Thanks everybody

 

[SQLDenis]

I had this happens once where the comment column was 5000 chars instead of varchars on the live server
So each page only held 1 row which was very bad in terms of IO, the average comment was only 30 characters

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions