Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I run a simple query like select * from table and i take the following results:

primary database : 90 seconds
replication database : 2 seconds

The tables are identical and have the same indexes in both servers.

My primary server has about 50 databases and 10 of these are replicated with one-way transactional replication to the second server.

What can i do to solve this performance issue?

I have also noticed than when i restore a database in the primary server the performance is dramatically slows down.

share|improve this question

migrated from stackoverflow.com Jan 2 at 12:20

This question came from our site for professional and enthusiast programmers.

    
1.Do you have checked fragmentation of indexes at both server? 2. Check main memory usage, CPU usage & hardDisk usage for both servers, 3.DO any other application(Web, window service or batches running on primary)? 4. DO hardware configurations of both server are same? –  Pranav Singh Jan 2 at 11:08
    
Sounds like a storage issue on the primary server, check perfmon to see if it's being maxed out, also have a look at the wait stats to see if you can see what is causing the slowdown. –  steoleary Jan 2 at 11:22
    
thanks for your answers, –  panos Jan 2 at 11:42
    
All applications run on the primary server, the replication is used for disaster recovery. The fragmentation of indexes is in the same level both sides. The CPU usage in primary server has a pick of 30% when restoring and then is about 10%. The database I/O has a pick of 20MB/sec. From the wait stats i see that in the SQLCLR i have the maximum values with wait time 2968ms/sec –  panos Jan 2 at 11:49
    
Are both publisher and subscriber server on same version ? Is distribution database on the publisher server ? Any specific query running slow or all queries ? All stats are updated ? Give us more info to help you better. –  Kin Jan 2 at 13:01
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.