Question

I m working here in a small company and one of my jobs is the administration of the ERP system AXAVIA (www.axavia.com)

There are .NET Clients and a MSSQL Server 2005 Database with a size of about 10GB. The system works on a metadata model, this means they have very few tables (one for each datatype and some for the relations) and this data is computed with adhoc queries. Up to 2000 batches / sec...

I guess they don t really hava a database specialist, because the didn t know anything about index fragmentation and i allready deleted a lot of unused indexes - now the db is about 30% smaller...

What else can i do for more performance? - I rebuild now the indexes every night

I think, there are no missing indexes and also the primary keys are at least ok
The filesystem is a fast 10 raid - and with 6,6 GB Ram there is very little IO
The Server is a VM Ware with one virtual CPU - here i guess is the beste possibility: The huge ammount of small batches would benefit from a phyical cpu with 4 cores?!
I m also thinking about partitioned tables, but in the moment the database isn t big enough to benefit much from this.

So - any other ideas?

Answer 1

Add a CPU, at lesat for test. I Would say you likely run into a problem here. Generally - and I mean really in general - I never have one core VMS anymore. Even the smallest machine has 2 cores. Makes thigns a lot faster even on windows level (OS operations ahppen on the second core).

10gb is tiny today. Still there is no database crappy programming can not kill (and it is likely in your case that is a lot of crappy programming going on, from your explanations). Start a full analysis of why things are waiting. If they are just hitting he server with a lot of sequential SQL for any operation the only thing you can do is make sure (a) you have as little waits as possible and (b) you have as fast a CPU as possible. In a sdatabase like you describe it the problem is seriously in the program - and basically there is only so much you can tune down at the database level.

Answer 2

If not already, have your data and log files on seperate drives. You can also move your tempdb to it s own drive, and also split it into multiple files. Read Brent s piece on tempdb here: Brent Ozar

Answer 3

I suggest you to use Glenn Berry s script to determine troubles in your server:

https://dl.dropboxusercontent.com/u/13748067/SQL%20Server%202005%20Diagnostic%20Information%20Queries(September%202014).sql

There are many another potential problems, not only missing indexes.

I was used this script as knowledge database to create my own tool to check my ERP health. And I can tell you it works well.

友情链接