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?

LVL 9
JonMnyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cavp76Commented:
New database, new array (also for logs and tempdb); and I mean different physical drives

HTH
JonMnyAuthor Commented:
not sure what your saying
aleghartCommented:
You will see an improvement if you move it to a new instance and all activity takes place on a separate array...not a different logical volume on the same array.  64GB RAM is plenty to host multiple instances.  But the I/O on the disks will be a bottleneck.

If you can't get it all onto different disks, then it's not worth the extra work.

I have a query that hits many thousands of records and several joins across...it pegs the disks at around 120MB/sec.  But, there's still RAM and CPU left over.  The disks are the issue.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

JonMnyAuthor Commented:
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.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<So I cant see how this could do anything but make things worse, Right?>>
Exactly.  Creating a new duplicate database on the same *physical* drive will simply add the overhead of reading the data from the source database and writing the target database onto the drive that holds both databases.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>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.
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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.  
Anthony PerkinsCommented:
>>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.
JonMnyAuthor Commented:
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?
Anthony PerkinsCommented:
>>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.
JonMnyAuthor Commented:
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.
Anthony PerkinsCommented:
I just realized what I wrote is the opposite of what I meant.  This:
That should in of itself present a problem.

Should have read:
That should not be a problem.
JonMnyAuthor Commented:
Thanks for the input gives me a few more ideas on how to look at this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.