[David Morinello]

I would like to get some measurements to show (justify to) management where we are in the scale of the general GP system requirements. I suspect we are well beyond the 4000 transactions per day mark, and know we import a substantial amount of data sales data.

But how to I give them good numbers? Peak for the year? Daily Averages?
Does anyone have some SQL to measure numbers like these for GP?

 

System Requirements for Microsoft Dynamics GP 2018

Server Recommendations: Customer Profile 1

·         Process fewer than 250 transactions per day

·         No web applications in use

Server Recommendations: Customer Profile 2

·         Process fewer than 1000 transactions per day

·         Import very little data

Server Recommendations: Customer Profile 3

·         Using an import routine/eConnect/Integration Manager

·         Process between 1000 and 4000 transactions per day-Originating in Sales (SOP and/or RM), Payables or General Ledger

Server Recommendations: Customer Profile 4

·         Use an import routine extensively – eConnect

·         Process more than 4000 transactions per day-Originating in Sales (SOP and/or RM), Payables or General Ledger

——————————
David Morinello
Senior Dynamics GP Systems Architect
Ascend Learning, LLC
Leawood KS
——————————

[Mark LeRette]

An over-simplified method, if you just wanted a count of your Sales Transactions for a date range, would be to go into Smartlist, click on Sales and the Sales Transactions smartlist, then click Search and search for where Document Date is between the dates desired.

If you want a SQL script that looks at transactions across the modules, you could try something like the attached file.

——————————
Mark LeRette
Application System Analyst II
Muscatine Power and Water
Muscatine IA
——————————
——————————————-

[Derek Albaugh]

Along with the information above, I wanted to add that the System Requirements we state for different Server Recommendations for the different Dynamics GP versions are ‘minimum’ recommendations as well, to be used more as a starting point when trying to setup your customer’s environment and then go from there.

Thanks.

——————————
Derek Albaugh
Sr. Support Engineer
Microsoft
Moorhead MN
——————————
——————————————-

[Jo deRuiter]

I want to second the response from !

One of the biggest mistakes I see made by companies is trying to squeak through on the “minimums”??

——————————
Kindest Regards,
Jo deRuiter , MCP, DCP
“That GP Red Head”
AISLING DYNAMICS CONSULTING, LLC
WEBSITE: https://aislingdynamics.com/
BLOG: https://community.dynamics.com/gp/b/gplife
GPUG Academy Instructor
Dynamics GP Credentialing Council-Vice Chair
770-906-4504 (Cell)

——————————
——————————————-

[David Morinello]

Believe me, I understand completely! We had a go at upgrading about two years ago when my system numbers, backed by Microsoft recommendations from Amplify, were reviewed by our internal system people and cut by more than 75%. SQL Server at 4 GB? For 38 companies, 87 concurrent users, and 10k plus imports daily? Needless to say, the upgrade didn’t happen then.

I have got to make the upgrade from 2013R2 to 2018R2 happen this time, so I am doing every due diligence to back up my system recommendations. I am trying to avoid that by both showing that the numbers are Microsoft minimum recommendations and that our throughput is considerably higher than even the Profile #4 minimums. 

I see the SmartList Trick  to get a single module count for one day, and I combined several of ‘s SQL queries into one to get header and line counts for GP, Sales, Inventory, and Fixed Assets.
– But how do I get one “Transactions per day” number out of 6190 GL Transactions and 15035 GL Transaction Lines? is it 6190 or 15035?
– 7880 Sales Orders and 12912 Lines?
– Etc..

And that is the count for one day for one of 38 companies. The 38 GP companies have different peak days. I need to understand first how to get one number from a header/line combination. Then understand if I combine GP, SOP, Inventory, and FA into one transaction number count, or some ratio, so I am not counting transactions two or more times. and finally do the same for each company.

I suspect that what I am looking to do will need me to extract numbers to a data warehouse and slice and dice them from there. It’s a project, for certain.

I appreciate any insight you all can give me, so I can present accurate numbers to management and get a bullet-proof buy-in.
??

——————————
David Morinello
Senior Dynamics GP Systems Architect
Ascend Learning, LLC
Leawood KS
——————————
——————————————-

[David Morinello]

I also modified someone else’s GL Journal Entry SQL query (I would give credit of if I knew from whom), to pull the top 5 for the open year (GL20000). This helps me see what the busiest days might be.

——————————
David Morinello
Senior Dynamics GP Systems Architect
Ascend Learning, LLC
Leawood KS
——————————
——————————————-

[Kim Peterson]

these are some things that Beat Bucher knows – he has helped the community on set up and requirements.  Might be worth checking with him.

——————————
Kim Peterson
218-862-5002
Kim@DynamicsConnections.com
#KimPetersonGP
——————————
——————————————-

[Rob Klaproth]

John,

I’ve worked with clients of all sizes, and performed over 100 upgrades from many different versions of GP.

I can tell you, without a doubt in my mind, that the minimum recommendations from Microsoft are just that, and if you go with them you’re going to get “minimum” level performance.

I never recommend less than 32 gigs of RAM for the SQL Server, and my clients have never complained about slow performance after upgrading the hardware.  RAM is dirt cheap now, as are servers, and even solid state drives.

Secondary to the RAM, Disk IO is the #1 bottleneck today on SQL because if it has all the RAM in the world, it still needs to read and write transactions to disk.   Solid State drives are the way to go.  Whether you’re in a SAN with a small array of SSD disks, or a dedicated server, SSD drives are going to give you the biggest bang for your buck.

Also, don’t put everything on one server!  You want one server for SQL and one for Management Reporter and all your apps, i.e. if you use ReqLogic, eRequester, and other 3rd party apps that run on IIS or other platforms outside of GP (or they run as a service), definitely place those on their own app server.

Lastly, terminal server, again another dedicated server for your users, should be beefy and scaled up to how many users you have using it.  Thick clients are just a waste of time now days, and not only that but they don’t support wi-fi and VPN so if your users will use GP over wifi, or a VPN you must also invest in a terminal server.

So, in general here’s some sizing based on the numbers you gave in your post:

1 – SQL Server – 4 Cores, 32GB RAM, SSD Storage volume for Data and Log files for SQL
   *** NOTE ON SQL – if you have any GP company databases larger than 32GB you may want to consider increasing the RAM, i.e. if your largest database is 100GB you may want to go with 64GB of RAM rather than 32GB.
2 – App Server – 2 Cores, 16GB RAM – Storage not as important, but generally it’s all coming from the same place anyway so use SSD
   *** NOTE ON APP SERVER – I would recommend installing eConnect and Web Services here,  putting your integration on the app server and running from there.   You’ll need to monitor the RAM usage of the integration, if it needs more RAM then bump it up.  
3 – Terminal Server – 4 Cores, 128GBRAM – SSD or SAS 15K storage
   *** NOTE ON TERMINAL SERVER – 1-2GB RAM per user recommended, with 72 users 128GB is a good baseline.  You may be better off having 2 terminal servers w/ 64GB each and load balancing them across your users

??

——————————
Rob Klaproth
Dynamics Certified Professional
(GP Install & Configure)
Sr. GP Consultant
Armanino, LLP
San Diego, CA
——————————
——————————————-

[Blair Christensen]

?As a DBA, I’ll echo everything Rob says here.  The notion that some finance types have to scrimp on the hardware supporting mission-critical systems like your accounting platform is a mentality I like to call “penny-wise and pound stupid” – especially when good hardware is so cheap these days.  I used to work for a company which had an annual maintenance budget on their vehicles in the millions of dollars, but would intensely scrutinize a new server running cheaper than a replacement transmission (and the server would last longer).

For the reticent Finance-type who needs to be persuaded by numbers, however, simply calculate the number of hours people aren’t productive because of cheap hardware and multiply times $35 (the loaded cost per hour of the typical non-managerial employee) times the number of employees affected.  It’s really easy for that number to get really big, really fast.  Case in point: how many minutes every day does your typical user spend waiting for a search screen to open up?  Take that and multiply times the number of times they use a lookup times the number of users…  You get the picture.

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-

[Rob Klaproth]

Very well said Blair! I was at a client site today and ran the inventory historical status report and it took 2 minutes to run on their old server.   5 seconds to run on the new one. :).  Same with the payables and GL trial balance reports.  

Get Outlook for iOS

 

——Original Message——

?As a DBA, I’ll echo everything Rob says here.  The notion that some finance types have to scrimp on the hardware supporting mission-critical systems like your accounting platform is a mentality I like to call “penny-wise and pound stupid” – especially when good hardware is so cheap these days.  I used to work for a company which had an annual maintenance budget on their vehicles in the millions of dollars, but would intensely scrutinize a new server running cheaper than a replacement transmission (and the server would last longer).

For the reticent Finance-type who needs to be persuaded by numbers, however, simply calculate the number of hours people aren’t productive because of cheap hardware and multiply times $35 (the loaded cost per hour of the typical non-managerial employee) times the number of employees affected.  It’s really easy for that number to get really big, really fast.  Case in point: how many minutes every day does your typical user spend waiting for a search screen to open up?  Take that and multiply times the number of times they use a lookup times the number of users…  You get the picture.

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————

[Beat Bucher]

Fully Agree on what @Rob Klaproth & @Blair Christensen said,
Aside of that, not two customer sites are identical and what Microsoft’s recommendation are, well they are just recommendation 🙂
Every little aspects of the customer’s premise is important, but in general the consensus is to say they a Terminal server system reasonably sized should be able to accommodate 25 simultaneous users without too much performance hit, and using the GP Web client allows you to double that count, as the resources on the Web client are smaller impact than the full client. ???
For the SQL server, well again it depends on the number of Databases you have and their size, but 4GB is for sure not going to make it .. not at least in production. I’ve run TEST & Demo SQL servers with as little as 4GB, but then again, you don’t really expect big performances and usually there will be just 1 or 2 users.
I’ve seen very small businesses getting away with a half-dozen GP users, a couple of companies and run everything off a single server (yes, even the TS was on that system) with 16GB and they were perfectly fine. I’ve run our corporate GP server during several years with 8GB (back in v 2010), then 10 and finally 12GB on 2013R2, with an average load of 12 users and 8-10 companies.. never really had performance issues.
Every case is different, but cutting short here can be a costly mistake on the long… and the easiest way is to show the $$$ when employees are unproductive due to a slow responsive server.
Just recently a customer complained that users couldn’t work in GP because the server had become so slow that they had to reboot it on a regular basis, to reset the TempDB that was outgrowing their disk space.. Not that the server was undersized, but some of the important setup steps hadn’t been taken care of and some regular clean-up tasks wouldn’t be executed, which was on the long run making the server slower and slower.

——————————
Beat Bucher
Business Analyst, Dynamics GP SME
Montreal QC/Canada
@GP_Beat http://www.gp-geek.com
Montreal QC GPUG Chapter Leader
MBS MVP (2015-2018)

——————————
——————————————-

[Blair Christensen]

?So I quickly put together the following script which will dump out all of the posted invoices from all your company database into a single result set (from which you could construct a pivot table if desired).  It would be fairly simple to modify this to look at any of a number of other tables as well.

IF OBJECT_ID('tempdb..#InvoiceCount') IS NOT NULL

DROP TABLE #InvoiceCount

;
CREATE TABLE #InvoiceCount (DB VARCHAR(20), DocMonth INTEGER, DocYear INTEGER, EntryCount INTEGER);
DECLARE @DB_Name VARCHAR(20), @Command NVARCHAR(2000);
DECLARE database_cursor CURSOR

FOR select RTRIM(INTERID) from DYNAMICS.dbo.SY01500;

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @Command = 'INSERT INTO #InvoiceCount (DB, DocMonth, DocYear, EntryCount)

SELECT ' + '''' + @DB_Name + '''' + ' as DB, MONTH(DOCDATE) as DocMonth, YEAR(DOCDATE) as DocYear, COUNT(*) as EntryCount

FROM ' + @DB_Name + '.dbo.SOP30200 group by MONTH(DOCDATE), YEAR(DOCDATE) ORDER BY YEAR(DOCDATE), MONTH(DOCDATE)'

--PRINT @Command

EXEC sp_executesql @Command

FETCH NEXT FROM database_cursor INTO @DB_Name

END

CLOSE database_cursor;

DEALLOCATE database_cursor;

select * from #InvoiceCount;

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-

[Jo deRuiter]

This script is the BOMB  Thanks for sharing!!?

——————————
Kindest Regards,
Jo deRuiter , MCP, DCP
“That GP Red Head”
AISLING DYNAMICS CONSULTING, LLC
WEBSITE: https://aislingdynamics.com/
BLOG: https://community.dynamics.com/gp/b/gplife
GPUG Academy Instructor
Dynamics GP Credentialing Council-Vice Chair
770-906-4504 (Cell)

——————————
——————————————-

[Blair Christensen]

?Feel free to like if useful!  🙂

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-

[David Morinello]

I am checking it out now. Looks awesome!

——————————
David Morinello
Senior Dynamics GP Systems Architect
Ascend Learning, LLC
Leawood KS
——————————
——————————————-

[David Morinello]

,
Just what I needed!. I added a Day count to your script and pulled the results to Excel and a Pivot.

Voila! Daily Transaction totals for all companies for each day of the year. And numbers that averaged around 20,000 transactions daily.

I especially liked your numbers for Terminal Server. Our 4 Citrix servers are nowhere near large enough to support our GP user count.??? I am shooting for a better GP user experience with concrete measurements for logon times and commonly run reports, and your specs will help me sell it!

Thank you all for a wonderful response!

——————————
David Morinello
Senior Dynamics GP Systems Architect
Ascend Learning, LLC
Leawood KS
——————————
——————————————-

[Beat Bucher]

For the statistical counts of your GP user licenses usage, you could use my Excel workbook & scripts to collect some data and draw some conclusions. This might also help to show your board how you’re currently set with that.
We recently redid a webinar on GPUG from our Summit session that Steve & I had presented and it seemed to be very popular.
We’re working on improving the whole Dashboard, but in the mean time, have a look at this thread 🙂?

——————————
Beat Bucher
Business Analyst, Dynamics GP SME
Montreal QC/Canada
@GP_Beat http://www.gp-geek.com
Montreal QC GPUG Chapter Leader
MBS MVP (2015-2018)

——————————
——————————————-

[Beat Bucher]

Thanks for sharing the script.
it’s certainly going to prove handy in the future when having to jump in for performance assessment and try to justify for some investments 🙂 ?

——————————
Beat Bucher
Business Analyst, Dynamics GP SME
Montreal QC/Canada
@GP_Beat http://www.gp-geek.com
Montreal QC GPUG Chapter Leader
MBS MVP (2015-2018)

——————————
——————————————-

[Bruce Strom]

This is an old post, but a good post.
Dr Google and/or http://www.stackoverflow.com know about some SQL scripts that returns the row count for all tables in a database, I believe.

——————————
Bruce Strom
Programmer Analyst
Associated Grocers of Florida / Supervalu
Sunrise FL
——————————
——————————————-