JonMny
asked on
Two copies of the same database
I think I know the answer to this question but want to get some outside input. We have a single server (64bit, 64gb ram , best raid etc.) We have a db with about 30 million records that is very slow to run queries. To solve the problem a second copy of the db was created the idea I am assuming was to offload some of the more intensive quieries but it's replicated to the same sql instance,server and hard drive .So I cant see how this could do anything but make things worse, Right?
ASKER
not sure what your saying
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that makes some sense to me, I know we can't add an instance but we might be able to move it to another drive.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>So I cant see how this could do anything but make things worse, Right?<<
It really depends. Unfortunately, without knowing what is the bottleneck (CPU, I/O, memory, network, etc.) all we can do is make educated guesses. So my suggestion, is that you first find out what is the problem and then we can give you a better idea. Incidentally, saying something is "very slow to run queries" is not a problem, it is merely the symptoms to a problem.
It really depends. Unfortunately, without knowing what is the bottleneck (CPU, I/O, memory, network, etc.) all we can do is make educated guesses. So my suggestion, is that you first find out what is the problem and then we can give you a better idea. Incidentally, saying something is "very slow to run queries" is not a problem, it is merely the symptoms to a problem.
Hi antony..;)
<<So my suggestion, is that you first find out what is the problem and then we can give you a better idea.>>
A wise decision indeed. The OP would need to confirm drive contention vs blocking first.
<<So my suggestion, is that you first find out what is the problem and then we can give you a better idea.>>
A wise decision indeed. The OP would need to confirm drive contention vs blocking first.
>>The OP would need to confirm drive contention vs blocking first.<<
Absolutely, and locking was the one I missed and one case where a separate instance for reporting could alleviate a poorly designed database.
Racimo,
As always, good to see you around. Your comments always lend balance to the threads here.
Absolutely, and locking was the one I missed and one case where a separate instance for reporting could alleviate a poorly designed database.
Racimo,
As always, good to see you around. Your comments always lend balance to the threads here.
ASKER
I did some more digging and only 1% of all the queries run are hitting the second db. Replicating to it is 7% of all activity on the server. I would be surprised if anybody noticed if we removed it. Looking at the db design I noticed that it doesn't have a single clustered index. (47 tables) the primary key is sequential int. This seems like an obvious problem to me unless this would be done because of replication?
>>Looking at the db design I noticed that it doesn't have a single clustered index. <<
It could be a problem.
>>(47 tables) the primary key is sequential int. <<
That should in of itself present a problem. Are you saying these primary key are not clustered? Not that they necessarily should be, I was just curious.
>>This seems like an obvious problem to me unless this would be done because of replication?<<
In order to replicate a table there has to be a primary key.
It could be a problem.
>>(47 tables) the primary key is sequential int. <<
That should in of itself present a problem. Are you saying these primary key are not clustered? Not that they necessarily should be, I was just curious.
>>This seems like an obvious problem to me unless this would be done because of replication?<<
In order to replicate a table there has to be a primary key.
ASKER
yeah pk is not clustered. Based on the design of the DB it makes sense to me that the pk for most tables should be clustered, lots of tables have multiple covering indexes bases on the pk column.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the input gives me a few more ideas on how to look at this.
HTH