|
There is a great how-to on SQL Server performance
monitoring on www.sql-server-performance.com:
Follow these for doing performance analysis, and it has
some great solutions and thresholds. So you will know exactly where and how you
can tune and increase SQL performance.
-Brandon From: Axapta-Knowledge-Village@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Steeve Gilbert Sent: Thursday, October 20, 2005 1:40 PM To: Axapta-Knowledge-Village@yahoogroups.com Subject: RE : [Axapta-Knowledge-Village] Performance analysis I'll transfert that to my sysadmin and my manager to let them know what to do if they want a kick ass server. J
To tell you the truth, I didn't know what AWE was until this week. So I've looked for information: http://www.sql-server-performance.com/awe_memory.asp Since we have Windows 2000 Server and SQL Server 2000 Standard Edition, If I enable AWE, can I expect my SQL Server to go up to 2G? How do I know If SQL Server really needs more RAM? Steeve...
-----Message
d'origine-----
I have to say Steve, we are on an HP San, Fiber Channels, and all, and it kicks major butt! I recommend it to anyone, MSA 1000 (Now the MSA 1500).
We have the SQL Server clustered for Fail over, and we have some issues when we first started. Of course using SQL Profiler showed us that we had Non Axapta Queries taking a while. We analyzed them, improved them, then we also upped out RAM to 3 Gig. You must also realize that SQL server will not go beyond the 1.7 you are at unless AWE is enabled, and the 3 Gig. Switch is used (you can search MSDN for how to do this). This will allow SQL server more room to use in RAM.
As for knowing what Query that is... I wish I knew of a way because then you could really do some fine tunning within Axapta. If you find that out please let me know!
-Brandon
From:
Axapta-Knowledge-Village@yahoogroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of Steeve Gilbert Hi Harry and Brandon,
Here's our setup for a 60 users environment:
1 AOS Dual 1.3 Ghz 1 gig RAM
1 DB Server Dual 1.3 Ghz 3 gig RAM DB : 4 SCSI disks Raid 10 Log : 2 SCSI disks Mirror OS : 2 SCSI disks Mirror
Brandon : We're already did a review from the ground up and added some RAM to our DB server and now the sqlserver uses 1.7 gig and there's always 1gig available. The real bottleneck we've found is the Read on our DB disks. When looking at the windows performance tool we can see "Avg. Disk Read Queue Length" hitting real high for long period of time. I'm guessing some querys uses Table Scan. That's why I wanted to reduce read by reviewing bad queries in the code. Our DB disk setup with pretty much the best we can do before going on a SAN. Our AOS seems at ease with the work load. That's why I'm now arrived at step 4, looking at the code.
Harry : No specific place in Axapta. What I want to do is reduced Table Scan globally and minimize blocking. For blocking I will start to look at custom process that does a lot of inventory transfer in 1 long transaction and try to split it up. Blocking here is a major pain. It can happen 3 times a day sometimes. They usually resolve themselves out but the more it takes time, the more people get blocked.
Finally, I'm using the SQL Profiler to check query with long duration. Sometimes, when I see lines like : exec sp_cursorexecute 241, @P1 output, @P2 output, @P3 output, @P4 output, 'boa', 0, 0, 'boa', '596965' How can I know the query that is executed?
Thanks for reading thru, Regards, Steeve...
-----Message
d'origine-----
Steve,
The improvement of performance, for any system, starts always at the bottom level. So I would do the following:
(1) Can you improve your system through hardware? (ex: increase RAM, CPU, Speed of Hard Drives, etc.) (2) Can you increase performance at the Database level? (ex: RAM of SQL server, SQL server AWE configuration, Multiple Processors, un-needed performance drags by Non-Axapta Queries., clustering.) (3) Can you increase performance at the Business level? (ex: have two AOS server for load balance, etc.) (4) Can you increase performance through code? (ex: making sure Axapta-SQL statements make use of Indices, review large chunks of code and break them down into more efficient functional blocks of code, etc.)
This is how I would approach it... from the ground up, not the other way around.
-Brandon
From:
Axapta-Knowledge-Village@yahoogroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of Steeve Gilbert Hi everyone,
I'm currently looking at ways to improve performance of our installation. When looking thru menus I stumble upon "Performance analysis" in Administration -> Performance test wizard. I've run it in our Dev environment to see what it does and the result was just an info box with the name of some forms. What does it do and where are the result of that analysis?
Btw, if you have any hints at where to look to improve performance/minimize blocking (software way) besides maximizing the use of index, let me know.
Have a nice day, Steeve...
Sharing the knowledge on Axapta.
SPONSORED LINKS
YAHOO! GROUPS LINKS
|
