Slow performance - Starting point

Thanks alot, lot of learning here.

Please find attach the two screen shot. one is sorting by %access write, other is %access read, Looks like we need to do all database not specific tables.

For fill factor it is 0 for current fill factor for all tables.


Thanks
AD
 

Attachments

  • 180668-% Accesses are Writes.PNG
    180668-% Accesses are Writes.PNG
    97.1 KB · Views: 58
Really.........we're opening up the VMWARE debate again? Thought this ship had left port like 5 years ago.

I do pretty much all installs where we have under 200 concurrent clients on VMWARE if the platform is Windows or Linux.

I have about 20 clients running their complete JDE infrastructure on VMWare and 75% of these are using SQL Server with no issues.

Heck I even have Oracle RAC on VMWare and it runs fine.

SQL Server can easily run well on any virtualization layer.

Colin
 
Colin,

Many shops think they can just virtualize, without upgrading the hardware behind it (seen it, moved on after experiencing it)....

The question shouldn't be the use of VM, the question / issue should be the hardware behind the VMs....

Right?

(db)
 
[ QUOTE ]
Really.........we're opening up the VMWARE debate again? Thought this ship had left port like 5 years ago.

I do pretty much all installs where we have under 200 concurrent clients on VMWARE if the platform is Windows or Linux.

I have about 20 clients running their complete JDE infrastructure on VMWare and 75% of these are using SQL Server with no issues.

Heck I even have Oracle RAC on VMWare and it runs fine.

SQL Server can easily run well on any virtualization layer.

Colin

[/ QUOTE ]


Colin, I would say that there is always room for debate on most any topic. I happen to believe that in a typical environment, running the database virtualized can cause performance issues. I have seen all the VMware papers showing how a perfectly configured system can scale to a million IOPS but the real-world situation is that, without attention paid to a lot of settings and details, VMware adds overhead. Given that many SQL shops are understaffed and under budgeted (a large reason for the SQL choice to begin with) it is not unreasonable to say that too little attention is being paid to the SQL settings let alone the VMware settings and configuration that affects SQL Server.

Please note that the original poster is on 8.12. The latest this release could be obtained was sometime in 2008. Also note that the Vmware release that purported to fix acknowledged disk IO issues, 4.0, was not released until 2009, making it somewhat likely that the organization is on an older release of VMware, which could indeed cause performance issues with a database server.

I agree that one can run SQL Server on a properly configured VMware system that has been properly capacity planned. Whether that last part gets done 'properly' seems to be the issue.
 
[ QUOTE ]
Thanks alot, lot of learning here.

Please find attach the two screen shot. one is sorting by %access write, other is %access read, Looks like we need to do all database not specific tables.

For fill factor it is 0 for current fill factor for all tables.


Thanks
AD

[/ QUOTE ]

What you can do with the results is to sort them by Total Accesses and rebuild the indexes of your most used tables first. If there are very large tables that were not included in the most used, rebuild those indexes.
 
Thanks again
We are talking only the DTA and CTLs not the enterprise tables right?

Also here is the memory info your requested:

Memory reserved for VMware
8GB RAM and 2xvCPUs

SQL Server
name value value_in_use description
max 4096 4096 in MBS
min 512 512 MBS

Yes we have VMware and our enterprise and sql in the same box. (Just in case that could be a reason).

Thanks
AD
 
[ QUOTE ]
Thanks again
We are talking only the DTA and CTLs not the enterprise tables right?

Also here is the memory info your requested:

Memory reserved for VMware
8GB RAM and 2xvCPUs

SQL Server
name value value_in_use description
max 4096 4096 in MBS
min 512 512 MBS

Yes we have VMware and our enterprise and sql in the same box. (Just in case that could be a reason).

Thanks
AD

[/ QUOTE ]

Well, all tables should be defragged but right now you need to focus on business data tables.

That is not a lot of memory available to SQL Server at all. Is there any way you can increase amount of memory reserved and increase SQL max memory?
 
Okay.......wow. Think you found your issue.

My 2 vCPU SQL Server's are running with a minimum of 16 GB RAM allocated just to SQL Server (plus some RAM for the O/S).

As a rule of thumb I allocate 8 GB per core at a minimum on Windows.


Colin
 
[ QUOTE ]

For fill factor it is 0 for current fill factor for all tables.


Thanks
AD

[/ QUOTE ]

A Fill Factor of 0 is stating that every page created will be full. Without going into a long discourse on fill factors, page splits and index fragmentation suffice it to say that 0 (or 100 percent full) is not the optimum value and greatly contributes to the index fragmentation that you are seeing.

Changing the fill factor to 90 for all indexes would be a good idea but will take up more disk space, which you say it not available. You could alternatively use the numbers you gathered from the earlier analysis of most used tables and change the fill factor on tables that are written to often. There is a more sophisticated method of identifying page splits (and from there poorly chosen fill factor values) but it takes a bit of work.

Changing the fill factor for indexes to something other than 0 (100 percent) will decrease costly page splits and will allow you to maintain lower index fragmentation.
 
Thanks Everyone for the feedback, by looking the thread i see 800+ views that tells me performance is the huge interest among us.


Colin
I believe what you say, and thanks for the feedback. Does it means we dont need to worry about the reindexing?

brother_of_karamazov
The fill factor will be on each table or the database level? how to do that as i stated that it is 0 percent how to make 90% just for an example.

Thanks
AD
 
[ QUOTE ]

brother_of_karamazov
The fill factor will be on each table or the database level? how to do that as i stated that it is 0 percent how to make 90% just for an example.

Thanks
AD

[/ QUOTE ]

Now that you are going to either have to learn on your own or find a consultant.
 
Jeff is telling you how to tune your "engine" to get better performance and gas mileage.

I'm telling you that you need to put gas in your car if you hope for it to run.

We're both right but just looking at 2 sides of the same problem.

Following Jeff's suggestion you might want to engage some consulting help. Either that or spend a lot of time on Google and in the library.
 
Lol Good one. or buy a new car, may be electric.

Thanks alot for all your help.


AD
 
Hi,

Get debug.log of application and try to use Performance Workbench tool to get more details.
 
Hi ,

When the system goes slow . First you do some basic health checks on JDE side and on Database side. On DB side, By checking on CPU utilisations, user I/O, locks in database . and on E1 side you can have a quick view on the links login and check by submitting a test report, evrything goes well or not . Sometimes network too causes issues, so it cant be ignored . and on level 2 go for any further digging whatever your case might be. Try clearing zombies . A monthly restart of the servers and services also should be in your schedule activities .
 
Thats an awesome Meme, Jeff ! Glad to see YOU'RE still alive as well !!!!
 
Back
Top