Improve DataBase Performance

We have a Production Database calls "A" , we do replicate from A database to "B" database for reporting purpose. We do not replicate the foreign keys and the Primary Keys so the only thing that gets replicated is the tables and View without their relationship. We have notice that that servers is getting slower every day. I tried to run the Index Tuning Wizard and it does not allow me to do it. What do you recommend to us in order to improve the servers speed?
Who is Participating?
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.

whats getting slower the server or the reporting queries your running on the copy data?

have you got any indexes on the report data?

have you done any Tuning on the report database?

how much data are we talking about?

what else is on the report server?

what do you use to report?

what the spec of the server...

do the tables in "A" keep growing?

how big are the tables (number of records)

does the data in all the tables in "A" change everyday?

do you replicate all the records regardless of if the record was changed or not? or do you only replicate the ones that are changed?

do you defragment your indexes regularly?

do you keep the important indexes in a different (faster) drive?

it depends a lot on the needs of your system, is all the data in "A" necessary every day?

have you considered or are you using the method of replicating say from
a.TableFromA  to b.TableFromA20041119 (archiving the tables to a new table in "B" for every day)

I have a database that is quite large, and our way of archiving it is using that method, we have each day in a separate table in "B" and we delete "A" every day, for the lookups we lookup all the different tables in "B"

if you give some more detail about your database and your needs maybe we can help more
=o|... poor guy... we got tons of questions... but all those are good questions to get an idea what's going on
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Just a thought. I would also check for disk fragmentation. Sometimes, if two daily processes are happening, fragmentation leap-frogs each process and can really cause serious problems.
amedexittAuthor Commented:
1- The Reporting queries
2- No have not any indexes
3- I tried but it said "no index recommendations for the workload and the chosen parameters"
4- Reporting database size 10112 mb
5- I have another Application
6-Crystal Report
7-SQL servers 2000 dual 1.2
amedexittAuthor Commented:
Hi BlackTiger
I have to replicate every single record change in A to B
The data in A is changing every single minute from 7 am to 10 pm
Some tables could have more than 50k records
The only records that get replicated are those that changes
I don't create Indexes on database B, remember that I don't Replicate Relationships From A to B, only the data. I mean if you change the application connection from A to B the Application will not work.
SQL queries will be inherently slow with the absence on any indexes.   You must appropriately index your tables.
agree with shogun,
i suggest that you make sure every table has at least a primary key index
and add indexes for your major foreign key relationships... you don't have to make them "unique"
there just there to speed things up...

try a reorganisation/optimisation. and updatestats

is the 10GB data or MDF + LDF size?

Given that you're already looking at indexes, I'd look at the following possibilities.

Here's a great performance tuning resource:

look into OS Parallelism
(Parallelism, Fill Factor)
hardware and OS:
(RAID, Location of OS Swap File, Location of the tempdb Database, Location of Log Files)




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
"3- I tried but it said "no index recommendations for the workload and the chosen parameters""

Out of curiosity are your stored procedures that you used on the tuning wizard making heavy use of temp tables and dynamic sql ?
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.