Sage Pastel and Pervasive - Exceptionally slow - 2 minutes to open a company

We have moved our accounting software to a new VM.

Physical  Server - Dell R730, dual proc, 128Gb Ram - Windows Server 2012 Datacentre
Virtual Server - 4 vProc, 8GB RAM - Windows Server 2012 Standard

The Sage application uses Pervasive Database 10.

When opening companies, it can take 5 minutes before the user can do anything. It takes 2 minutes to commit a transaction.

We have reinstalled Pervasive. Disabled IPv6. Added a host entry to resolve and potential DNS/NETBIOS issues.

When running the application locally via Terminal Services, It is very quick. And both the physical and virtual are idling. This points to a networking issue. Or possibly an ODBC/driver issue.

I don't believe its network related as the network is configured on Stacked Dell 5548 switches with 10 Gig uplinks. I also ran Process Explorer from SysInternals. The process only transfers 250KB of data while opening a company.

Any ideas on how I troublshoot this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill BachPresident and Btrieve GuruCommented:
The easiest way to troubleshoot this is to load up Wireshark  ( on the client workstation and capture all of the network traffic while the process starts up. Then, work through the trace file looking for large time delays, packet retransmissions, and the like. Where these delays are seen, look at what makes it start working again. This should give you a good idea what to look more closely at. Make sure that all other applications are shut down on the client to simplify the trace, too. If you need help analyzing the file, you can post it here,too.
xpanditAuthor Commented:
Please see results attached.
Bill BachPresident and Btrieve GuruCommented:
Sorry, but I do not see any attachment.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

xpanditAuthor Commented:
Apologies. File attached. Please rename file with extension .pcapng is the server the client machine connects to.
Bill BachPresident and Btrieve GuruCommented:
That helps a LOT.  Everything I see in the trace leads me to believe that the server is having a major disk subsystem issue.  I would start by analyzing the server HDD and checking to see if it is operating properly.  If it is a RAID array, look for a failed drive.  Try running any diagnostics you can find on it.  Try timing simple disk to disk copies on the server itself or a decent-sized file (say 100MB) -- you will likely see it running very slowly.  Try SQLIO from Microsoft, if you want, too.  I have seen this recently with some RAID controllers and bad drivers, so try updating the RAID firmware and OS drivers as a possible fix.  Also, you may have an AV solution on the server that is impacting disk performance, too, so check with Process Monitor and Performance Monitor to see if the disks are just abnormally busy.

This is why I think your disk is an issue:
1) Packets 4831-4839 show a VERY slow database-level FileOpen, that is taking around 200ms to open a file.  The database engine usually needs about 10-15 disk I/O operations to complete a brand new FileOpen request - and this takes forever.
2) Packets 5119-5126 show a VERY slow OS-level File Read, as well, indicating that the problem is NOT specific to the PSQL database engine.  In this read, the server takes 40ms to come back with just 1118 bytes.
3) Packets 6146-6153 should another VERY slow disk read, with some 200ms to read a 64K blcok of data and start streaming the data over.
4) Packets 6293-6300 show another very slow FileOpen, again around 200ms to complete.  
These long delays continue throughout the trace.  Jumping down about 1/3 of the way:
5) Packets 15605-15609 show a 190ms delay opening a file.
6) At the 1/2-way point, Packets 20896-20898 show a database-level FileOpen taking 190ms.
7) Pkts 21134-21142 show another 172ms FileOpen at the database level.
and so on.

When the database engine has data in memory, the response times are very fast.  About 3/4 of the way through the trace, packet 37676 shows a read that gets a reply in 1.08ms, followed by more at 1.26ms, 1.28ms, 1.03ms, and 1.00ms.  However, the very next read is a data page that was probably not in RAM yet, and it takes over 15ms to come back with that record.  Based on this, your expected network round trip time is right around 1ms (or slightly above).  With disk problems this bad, I am not surprised that you are having speed issues.
xpanditAuthor Commented:
Thank you. I appreciate the feedback. The server disk is running a VHD file on a what I'd consider a good RAID setup. But I will look into it further. When running the application locally on the server it is lightning quick. No problems. Do you think this could maybe be SMB issue / Windows 2012 network share issue?

There are various 2012 issues. This being one:

Should I maybe disable SMB v2 and v3?
Bill BachPresident and Btrieve GuruCommented:
Hmm.  That confuses things a bit.  Can you run Wireshark on the database server instead, then?  This will help isolate the disk versus network response time. I suppose it is POSSIBLE that the packets are being delayed in the network or coming through the hypervisor, but this REALLY looks like a disk subsystem issue.  For the best trace results, you need to get everyone out of the system first.  Then, start Wireshark capture and load up the first user session.  This will ensure that we get ONLY traffic from that one workstation coming in.  

I've had issues with SMB2, but most of them are related to the file caching environment, and are more functionality issues than performance issues.  SMB3 is supposed to fix most of these problems.  You could kill SMB2 is you wanted, but I'm not expecting that to help.  

For the record, I have almost NEVER seen performance problems on VMware -- all of the major issues that people face seem to be on Hyper-V.  This is not intended as a slam on Hyper-V, but just an observation from my experience with people having problems.  You may wish to consider putting in a test ESX box to see if the problems go away, if you happen to have spare hardware or run out of other ideas.  In the meantime, a Wireshark trace from the server would be my next recommendation.
xpanditAuthor Commented:
attached is the server wireshark. we also decided to change the location of the VHD file to another drive with a slightly different RAID setup.
xpanditAuthor Commented:
The issues has been resolved. It was a combination of three things relating to Windows 2012 and Hyper V.

On the physical server I disabled Receive Side Scaling under advanced properties of the NIC. I also disabled Virtual Machine Queue. This fix is apparently only applicable to Broadcom Network cards.

This domain security policy also improved things somewhat but I'd consider this to be secondary.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xpanditAuthor Commented:
Further troubleshooting and Googling found this solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.