Learn how to a build a cloud-first strategyRegister Now


Improve DataBase Performance

Posted on 2004-11-19
Medium Priority
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
  • 2
  • 2
  • 2
  • +3
LVL 50

Expert Comment

ID: 12628991
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

ID: 12629052
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

ID: 12629064
=o|... poor guy... we got tons of questions... but all those are good questions to get an idea what's going on
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 12629106
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

ID: 12629158
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

ID: 12629311
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

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

Expert Comment

ID: 12651881
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?


Accepted Solution

pedros7 earned 2000 total points
ID: 12653234
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

ID: 12653697
"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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

810 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