SQl SERVER 2005 databse server goes slow down after some time

raju_muchunde
raju_muchunde used Ask the Experts™
on
We are using the Following Specifications for the ERP Application.  
1.      ASP 3.0 as Front End
2.      Microsoft 2005 SQL server with sp3 as Back End
3.      IIS 6.0 as Web Browser
4.      Operating System 2003
5.      Crystal Report 9.2

Initially we had used windows 2000 OS and MS-2000 SQL Server and the application was going well for past 1.5 year. Total No of Users where consistent users where 150  and we faced some time delay problem in the core2deo system. Later on after purchasing the IBM server we switched over to 2003 OS and MS-2005 SQL server.  Right now the speed in initial state is good like within 1 sec the saving and retrieval is done. But after some time the performance goes down it is taking to 10 sec.

Right now the hardware setup is One database server (AMD server) one application server (IBM) and one domain server which is connecting this two servers. We had removed the antivirus in the network. And in the database tempdb database increase has been restricted. In all the server we had installed antivirus.



But after some time the performance goes down
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
The slow down may be caused by excesssive memory fragmentation. This is caused by programs which dynamically allocate memory, free some, allocate again, aso. You should try to allow for enough memory from start of each program. AFAIK, of the programs you listed, only MSSQL can be setup to fixed allocation, or to start with a high amount.

For a first go, you can observe the server with perfmon, looking on page faults per specific process, and memory allocation parameters (virtual and real memory amount).

Author

Commented:
we are using 8g memory for databse server and page file is set is attachment
plse help what is how to fix the mssql fixed allcation
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
64 bit Windows 2003? Which edition (Standard, Enterprise)? Which edition of MSSQL 2005?

32 bit editions are aware of a max of 4 GB (OS) resp. 3 GB (application, with PAE / AWE / Large Adress Awareness like MSSQL).
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
we are using
windows server 2003 enterprise editon with sp2.(32 bit)
MS SQL server 2005 standard edit with sp3.(32 bit)
For Database server we are using 8  Gb Memory.
and Application Server we are using 4 Gb memory.
now in device manger it showing 8 Gb memory.now what are setting are required for the server .
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
As you have two servers involved, first step is to see which one is the bottleneck. I suppose all applications (Crystal, IIS, ERP) run on the 4GB application server, and the database server is hosting MSSQL only. Further I guess MSSQL is set up to allocate memory dynamically.

With 32bit applications on 32bit OS, only 3GB can be addressed directly. All other memory has to be paged via address windows of 64 MB each. The management for this windowing is slowing done memory access, of course, and gets costly with memory fragmentation.
Best is to keep MSSQL SE on its 3GB limit. It will use the memory better.

You can change the memory settings in the properties of MSSQL Server in Management Studio, without need for restarting anything. The effect should be there immediately, which helps in testing.

Author

Commented:
we want to tune the database
which is the best tool or plse provide the databse tuneing step by step guide.
we are using sql server 2005,sql server 2008
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Can you exclude the memory fragmentation issue yet?

The first step for optimizing is to start MSSQL Profiler with a template containing "Tuning" while the database is getting slower. After some reasonable time of recording, stop it, run the Index Optimization Wizard (it's available in the Profiler Menu), and apply it to the recordings. Some indexes are suggested to create, and usually it's a good idea to do so.

After having applied some or all of the suggestions, you will have to observe the database again with Profiler, maybe immediately, maybe some days later, till you come to an acceptable performance.

But there is no application (AFAIK) which could tell you that it is better to NOT create 20 indexes if the base table is changing all the time. YOU have to decide whether you'll take the overhead trade-off between maintaining many indexes on write against high read performance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial