Performance Issue

Djou2424

We have a new client that is now running Nav 2013, since the beginning of March.
Basic installation, only 4 users, almost no customization done to the database.

They have been complaining about performance issue for the past 2 weeks.
The system is slow in the morning, gets better in the afternoon and start to slow down again at the end of the day.

I'm far from a SQL/Server expert and I have no idea what to look at.
I have started the performance monitor on their server but I'm not sure what I should be monitoring.
What should I look at to try and find why the system is being slow and what needs to be done to improve performance?

Isitar

Hi
The cheapest & most efficient performance upgrade is always the hardware upgrade.

Djou2424

It's a brand new server and the only thing installed and running on that server is SQL and Navision

bbrown

Isitar wrote:

Hi
The cheapest & most efficient performance upgrade is always the hardware upgrade.

Hardly.

davmac1

What are the specs of your server?
RAM
Disks
Operating system version
SQL Server Version
Database size
number of databases

bbrown

While purchasing new hardware is hardly the cheapest solution (although it is sometimes part of the solution), purchasing the wrong hardware can be a very expensive mistake. Another mistake can be purchasing the correct hardware, but configuring it wrong.

But hardware is only 1 part of the performance puzzle.

Djou2424

davmac1 wrote:

What are the specs of your server?
RAM --> 8.00 GB
Disks --> what do you want to know about the disks?
Operating system version --> Windows Server 2008 R2 Enterprise
SQL Server Version --> Microsoft SQL Server 2008 R2
Database size --> 1813.69 MB
number of databases --> 2 (one for live and one for testing purposes)

bbrown

Djou2424 wrote:

davmac1 wrote:

What are the specs of your server?
RAM --> 8.00 GB
Disks --> what do you want to know about the disks?
Operating system version --> Windows Server 2008 R2 Enterprise
SQL Server Version --> Microsoft SQL Server 2008 R2
Database size --> 1813.69 MB
number of databases --> 2 (one for live and one for testing purposes)

info on disk (for each physically separate array:

Number, size, and type of disk
RAID level
WHat is array used for?

bbrown

Why the selection of "Windows Server 2008 R2 Enterprise"? Was there a feature there you required?

Djou2424

bbrown wrote:

Why the selection of "Windows Server 2008 R2 Enterprise"? Was there a feature there you required?

We gave the client the system requirements for Microsoft Dynamics Nav Server and it's one of the O/S listed on that list.
They already had a license for that O/S so they selected this one.

As for the disk, I do not have the information.
I will ask them and get back to you.

Anything else I should be looking at while I wait for their answer?

bbrown

Verify the min and max memory settings in SQL.

Also confirm that the login account for the SQL server engine has been granted the "Lock Pages in Memory" right.

With such a tiny database and few users, it's doubtful that hardware is a major issue. You could probably run this on a laptop. However, your description of preformance worsening and improving over the course of the day has me thinking about memory paging. That's why my questions above. You don't want memory paging on a SQL box. It just kills performance.

Djou2424

They have 3 disks, all VMware Virtual disk SCSI Disk Device
1 - 74.9 GB capacity, 44.48 GB available (O/S and a few programs installed)
2 - 100GB capacity, 96.91 GB available (Database)
3 - 100GB capacity, 99.25 GB available (Backups)


On SQL
Min memory is set at 0
Max memory is set at 2147483647

As for the "lock pages in memory", I'm not familiar with this.
I went to the local group policy editor and looked at the security settings for "Lock pages in memory" and no user has been assigned there.
Is this what you are talking about? If so, which users should be added? If not, where should I be looking?

Also, by looking at the help on SQL Server Management for "Lock Pages in Memory", it says that "Locking pages in memory is not required on 64-bit operating system" and they are on 64-bit O/S

bbrown

You never mentioned before that this was a virtual server. That can make a lot of difference. What we are lookign for is the physical disk configurations. Not the virtual disk. Those specs you provided before, are they for the virtual server or the host server? What else is running on the host?

The max memory should be set to something below the physical memory on the server. Also allow for the O/S requirements plus anything else on the server.

That "lock pages in memory" settign is what I am referrign to. The account that is runnign the SQL service should have it granted.

Are the virtual disk, thick or thin partitioned?

Djou2424

I have to admit that I'm not a technical/hardware/configuration person at all...
We normally just handle the programming/customization of Navision and the client is responsible of the hardware/security/configuration of their server.

So all this is like "Chinese" for me.
The specification I gave you is for the virtual machine. I do not have access to the host.
I was just trying to help the client to see if this is something we could find and fix easily before referring them to a hardware specialist.

davmac1

If this was a standalone system installed with no thought, it would probably run much better than this. (For one thing, you would not have a max memory setting of 2GB for SQL Server on a 8GB server.)
Since you are running under VMware, you should find a VMware specialist who understands SQL Server and transaction processing.

It is hard to mess up a 4 user system. Your VMware people have nevertheless accomplished the job.

bbrown

davmac1 wrote:

... (For one thing, you would not have a max memory setting of 2GB for SQL Server on a 8GB server.)...

That setting is actually 2TB. The default max memory setting.

davmac1

I stand corrected! :oops:
It does seem like a vmware setup issue.

bbrown

davmac1 wrote:

I stand corrected! :oops:
It does seem like a vmware setup issue.

No, actually you're right on target. SQL, and many other 64 bit applications, don't distinquish between physical and virtual memory (think paging file). That default setting tells SQL that it's allowed to use up to 2 TB of memory. The fact that the server only has 8 GB does not matter to SQL. It will just use virtual memory (paging file) to satify its needs. However, since that virtual memory it actually a disk file, in can be rather slow. The need for SQL to constantly page memory can have a performance impact.

Not saying this is the only issue. Or even the major one. Just it's soemthing that sticks out to me based on the info provided so far.

kapamarou

Djou2424 wrote:

They have 3 disks, all VMware Virtual disk SCSI Disk Device
1 - 74.9 GB capacity, 44.48 GB available (O/S and a few programs installed)
2 - 100GB capacity, 96.91 GB available (Database)
3 - 100GB capacity, 99.25 GB available (Backups)

Where are the log files? :-k

bbrown

kapamarou wrote:

Djou2424 wrote:

They have 3 disks, all VMware Virtual disk SCSI Disk Device
1 - 74.9 GB capacity, 44.48 GB available (O/S and a few programs installed)
2 - 100GB capacity, 96.91 GB available (Database)
3 - 100GB capacity, 99.25 GB available (Backups)

Where are the log files? :-k

I'm betting this whole machine is on a single physical array. So does the log location really matter?

Excepti0nal

For best performance you should have two RAID 10 arrays. One for the database file and the log file should go on the other. Virtualization of SQL is not the optimal setup but on some IT budgets of small companies like this one, it is the only option. SQL is a resource hog. I would check your disk I/O to see if that is killing your performance. You need to find out the disk array setup, the speed of the drives in the array, and where the SQL files reside on the arrays. Also, I would monitor your disk I/O and the CPU usage of the service tier. I performed an upgrade at the end of February on our system and the service tier CPU usage had an impact on our system. I had to add more virtual CPUs to the service tier to improve performance. However with a load of only 4 users, I would not think this would be the case. If you could speak with the person/company that setup the VM env up, they should be able to tell you the array configuration.

David_Singleton

Why do you think this is a hardware or even a SQL issue, the first thing to look at is Navision and determine what exactly is the performance bottle neck.