Improve DataBase Performance

Posted on 2004-11-19
Last Modified: 2008-02-22
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?
Question by:amedexitt
    LVL 50

    Expert Comment

    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...

    LVL 13

    Expert Comment

    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
    LVL 13

    Expert Comment

    =o|... poor guy... we got tons of questions... but all those are good questions to get an idea what's going on
    LVL 5

    Expert Comment

    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.

    Author Comment

    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

    Author Comment

    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.
    LVL 18

    Expert Comment

    SQL queries will be inherently slow with the absence on any indexes.   You must appropriately index your tables.
    LVL 50

    Expert Comment

    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?

    LVL 6

    Accepted Solution

    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)



    LVL 18

    Expert Comment

    "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 ?

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now