Need Practical Advice on SQL2000 DB Optimization PLEASE!
I need a practical advice from anybody who had an experience with DB optimizations.
Here is the scenario: The Company I work has DB that grew in last year. We are relatively small company and we do not have DB Admin, so I am as developer will take on this job. I have dealt with DB only on development level and never had to deal with optimizations and administrator procedures. I have done some reading on topic of optimization that gave me bunch of different options. I would like to hear from professionals in a field who have hands-on experience and have done this things and can give me practical advice or point me in a right directions.
From My reading I narrowed down 2 things that I must DO:
1. is to run DBCC DBREINDEX on all tables
2. is to use Index Turning Wizard to give me ideas of better indexing schema
Please make any comments on my plan and give me any advice on other things might be useful for me.
Here are 2 reasons why I need it:
1. DB grew 5 times in size, and nothing was done to it, so I feel there is a need as maintains
2. We have many subscription remote clients performing merge replication and lately it started to time out a lot.
Thanks LOT for any advice.
[1250 byte] By [
stardv] at [2007-11-19 20:47:16]
# 1 Re: Need Practical Advice on SQL2000 DB Optimization PLEASE!
well this is one very open ended question, much like "how long is a string", but I'll try and put in a few words here :)
Although you dont have a dba (or rather, you actually fill the role), I will still assume you have a working backup strategy in place. first question here will then actually be if you are using Maintenance Plans?
If yes, then have a look at some of the other options in your plan, they should sort out a lot of the "basic" stuff.
There are other questions here though, such as Filegroups, and their placements? this is more important if your db is "large". Hardware raid? which options do you have here?
For the server, what type of memory allocation have you set up, dynamic, or fixed?
if the hardware is multi cpu, how have yo uset the affinity?
does the host only serve as a sql server, or does it host other services as well?
try running perfmon, and use counters for buffer cache hit ratio, you should see something around 70 (this is not an exact science...).
how are the queries executed, dynamic sql, stored procedures ? in my experience, 90% of all performance issues relates to poorly written sql.
run a trace capturing sql stmt, start and stop, and identify your longest running queries. review them. look at indexing, joins, lock hints?
space allocation and monitoring is crucial, for log and data files. I guess you have autogrowth on, so again, use perfmon and use counters for file growths (dont remember exact name of counter, writing this out of memory only). You want to have few times where the server has to extend its files, so if you see excessiv filegrowths, try and set the growth options more sensible.
depending on what type of system you are running, growth might be fairly predictable, linear (day by day), periodic (small growth until end of month, then grows a lot), or completly impossible to predict. Either way, it is advisable to monitor file sizes, used space within the files, and actual space free on the harddrives themselves.
this is what I could think of on top of my head. not much to answer your question perhaps, but hope it might give you some ideas on where to look.
