SQL Server 2014 Live Today - Customers Moaning (1 Viewer)

Just upsized from Access FE/BE to Access FE and SQL Server 2014 BE. Never done this before but all seemed to go smoothly after some weeks of planning. And help from this forum. Went live yesterday.

Today about 100 Terminal Services users logged on this morning and all are moaning about speed, which is significantly slower than Access only. We haven't made any changes to the FE.

One of the key features of the system is the display of a multi-tab form which reads data in from about 10 different tables, and it's this which is annoying them the most. It used to take 1 to 2 seconds to populate the entire form and display it - it's now between 8 and 10!!

This performance drop from Access to SQL Server is completely unexpected. As I write, the hardware guy is looking into that side of things as a matter of urgency. So we don't know whether this sudden drop in performance is software or hardware related.

From the software point of view, can anyone please offer any assistance in any changes I can make, and quickly. I know this is a big ask but I'm under a lot of pressure here.

Any help really appreciated - and I do mean that. And the simpler the better as we are totally new to SQL Server. More info available if needed.

Thanks
Dave

One of the key features of the system is the display of a multi-tab form which reads data in from about 10 different tables

Click to expand...

If by this you mean each tab has a separate subform then I would remove the recordsource from each of your subforms (except the default one on opening), then repopulate it when the appropriate tab is selected - typically the tab click event

Alternatively, if you don't want to modify the subform, remove the reference to it in the subform sourceobject so it is blank and reinstate similar to above

Both the above ideas are very valid. The solution might be a process of elimination.

And Now for Something Completely Different:

In my case, for development I load the client copy from my local hard drive and link to the same production SQL Server.
My old 2 1/2 core processor desktop was running the same Access front-end many times faster than the Citrix Access Application server version running on a server.
Is this your case??? If not please skip and GoTo XXX:

Where do I begin? LOL Why do we programmers have to explain things to system server experts? This takes the point of view that your enterprise is starving for Resources.

First: These virtual experts typically create a Virtual Machine and load both SQL Server and Citrix on a Virtual Machine. Just realize, the amount of RAM (and resources) they fantasize associated to either server is more vapor than a hippie's air-guitar when they are on mushrooms. It isn't real, it is "virtual".
Second: to eliminate each possibility, read the articles about Citrix running on Virtual Servers. Think about this, a Virtual Server running a Virtual Server.
Here is a 15 minute virtual tutorial about this to convey the situation:
http://video.adultswim.com/rick-and-morty/m-night-shaym-aliens.html
No, its not a bad link, just go with it and the reason for thinking about a virtual in a virtual will start to become more clear. And yes, my hair is gray as portrayed.

As Citrix says: (paraphrased) there are no tools, try these things until it works.
First: Get the staff to add more RAM to both SQL Server and more RAM to Citrix.
For some reason, the expert types allocating resources always go with the minimum.

Log into your Access Application server as Admin and start the resource monitor. Have users log into the application and watch carefully for the Disk Cache activity. If the OS is thrashing the Windows Virtual Machine to hard drive (that is virtual too), then the processor can be spending 80% of the time just managing virtual memory.
Oh, did I just say the OS Virtual Memory manager inside a virtual machine running your virtual Citrix application? Maybe now that episode above makes more sense.

Maybe your IT group won't give you admin rights to your Virtual Application Server where Access runs?
Try this: Post a non-compiled version of your front-end application on Citrix to start-up.
Launch it from Citrix. Go into the Citrix session's design mode, start up a module. Then run:
Shell "taskmgr.exe", vbNormalFocus
Now, look at the Virtual Memory swapping to hard drive and the total resources allocated.

When I started mine, 50 Access users had less than 3 GB to share on 2 processors for that virtual machine running the Access Application on the server.
After the screaming subsides, make sure there are at least 4 processors allocated with at least 16 GB of Ram. That the top level virtual server puts your Virtual Machine as the top priority.


xxx: ' Maybe its just SQL Sever under resourced
Now, do the same for SQL Server. Yes, my professional actually allocated 1GB of RAM to SQL Server because that is exactly what Microsoft recommended in its official setup.
With low RAM, the SQL Server disk in the server room was shaking more than an unbalanced washing machine with a bowling ball on spin cycle.
Make sure your SQL Server has at least 4 processors allocated with at least 16 GB of RAM. That is if it is a stand alone physical SQL Server.

Of course, there is more. These are things that you need to check out in order to eliminate (or fix) the possible cause.

PiedPiper70 said:

Just upsized from Access FE/BE to Access FE and SQL Server 2014 BE. Never done this before but all seemed to go smoothly after some weeks of planning. And help from this forum. Went live yesterday.

Today about 100 Terminal Services users logged on this morning and all are moaning about speed, which is significantly slower than Access only. We haven't made any changes to the FE.

Click to expand...

I'd assume for such a significant change you would do the upgrade well in advance of go-live, run a series of tests and have a period of parallel running to make sure all was going well. If you didn't do that then I'd seriously rethink what you are attempting here. Downgrade to how it was before (I'd have to assume you took a backup and have a fallback plan to undo the changes you made) and then begin again.

When you are in a position to diagnose the problem properly without impacting end users you can run a test suite and capture server trace (use SQL Profiler) to look at any long running queries. You may need to consider what indexes or even changes to queries might be necessary. Testing everything is essential. Just flicking a switch and expecting 100 users to work instantly is like playing Russian roulette and obviously does no-one any favours.

ButtonMoon has a valid point. Was this application running on desktops before the Citrix launch? I kind of read-in-between-the-lines that it was "moved over" to Citrix and SQL Server.
Although, we can also understand that the cliff has been jumped and perhaps you are building an airplane during the fall.

Does your client from your personal workstation linked to the production SQL Server run quickly while Citrix runs slow?
Also, how slow is it? Is it use able, or is "watch the graphics render" slow?

Let us know. As a group, we want to see you be successful.

Check that the appropriate indexes have been implemented on the server tables.

Does the selection of the data in the tabs rely on any joins to records that are not on the server? If you have local tables also involved the joins won't be efficient.

Are any Access only built in or user defined functions involved in the selection of records? If they are required the server must pass the whole table (or at least a subset based on what it can select itself) to Access.

Do the tabs directly display independent data or are they Master/Child linked subforms?

Update:-

We were obviously aware of the pitfalls of changing the entire client over one weekend. And we checked with the client but parallel working was not possible due to the nature of their work. But yes we did have a backup plan and yes we have reverted the client to exactly where they were and as I write all is well. So we now have a little breathing space to review what just happened.

Now for the hard bit, where to go from here. Firstly we're not sure if this is a hardware or software issue, or probably a bit of both. The reason for uncertainty is as follows - I developed the entire system here in my office on a new set up of SQL Server. In short everything went well. My business partner is resposible for the hardware and he set up a duplicate system in his office and at the client's site.

In short, on my setup the entire program runs fine. On his copy, but more importantly on the clients server, it's slow. As a comparison, the main multi-tab form that I mentioned before is about 1 to 2 seconds to open here, and was the same at the client when using Access BE - but it's now around 10 seconds at the client and at my mate's office. And that's even running it single user when all staff have gone home!

So our first job is to find out why mine is quick and their's is slow. In terms of hardware my server is the least capable and yet is easily the fastest. Just for info, the clients server is a twin 6-core Zeon machine with 128Gb ram. Mine is just a 4-core Zeon with only 32Gb ram.

So thanks to everyone who has commented so far. I'll be looking into the software issues once we understand the issues with the hardware.

Dave

Access Blaster - sorry if this wasn't clear, but this is, and has always been a split database. From day 1, 5 years ago, it was an Access Front End and an Access Back End for the data. It is the backend that has been "upsized" to SQL Server and the Access Front End was relinked to that instead of the Access Back End.

I have only a limited experience of Access on a network with an Access FE and an Access BE and no experience at all with SQL Server. So feel free to shoot down this suggestion in flames if you wish.
I seem to remember reading in a forum quite recently of some one suffering slow performance like this. I believe they reported much better performance once the BE file was moved closer to the root directory on the sever.
You guys are clearly more experienced in these matters than I and I have no wish to try and "teach my Grandmother how to suck eggs". I make the suggestion purely in an attempt to help.

Excellent, you are so lucky to have an actual SQL Server, not a virtual machine.
Look closely at the Performance Monitor.
Look at the disk caching.
Look at the physical RAM used.

There can be bottlenecks due to setup.

I personally know people who use the "upsized" successfully.
My personal preference is the SQL Server Migration Assistant for Access.
http://www.access-programmers.co.uk...oft+SQL+Server+Migration+Assistant+for+Access

My preference is to create Linked Tables with VBA code. With a single click, I can switch over from ProductionDB to the TestDB -
http://www.access-programmers.co.uk/forums/showthread.php?t=224121&highlight=Native

The ODBC drivers can also make a difference. Just a personal preference, I use the SQL Server Native Client 11.0 (Free download)
http://www.access-programmers.co.uk/forums/showthread.php?t=182076&highlight=Native

How big is the database on Access? Your SQL Server sounds like a great system. A mid-sized DB would be the 50 GB range. If it is only a couple of hundred MB, indexing may not make too much difference.

Your problem reminded me of a SQL Server years ago where the network Port on SQL server had a problem. Basically, each request had to be resolved. We hard coded the port to what should have been the default and added that to the connection string.

This is a stab in the dark, but must ask anyway.
When a client starts a session, my server makes a copy of the production front-end and puts it into a folder on the server named for the users network ID. Then, the Citrix session runs that individual front-end against SQL Server.
When I arrived at this site, the Citrix person had one copy (front-end) for all users to use. That was old dog slow. Just want to make sure that each user runs an individual copy of the front-end.

Loading:
For Citrix, there is an Application server that runs Access. Actually, there are two identical ones. Every other users is switched to the other server. It is a primitive version of load balancing. However, this doesn't explain your initial start up for a single user being slow.

Just another oh-by-the-way
I found that many processes took priority over my Access.exe on a server.
http://www.access-programmers.co.uk/forums/showthread.php?t=235076&highlight=priority
This code boost the Access priority on the Application Server.

Bob - thanks for your suggestion. I did try that but it didn't make any difference and I nearly did my back in moving the server closer. I said it was a big server.

Dave
ps just kidding!! But seiously I don't believe your suggestion is relevant to a default installation of SQL Server on it's own machine.

PiedPiper70 said:

Bob - thanks for your suggestion. I did try that but it didn't make any difference and I nearly did my back in moving the server closer. I said it was a big server.

Dave
ps just kidding!! But seiously I don't believe your suggestion is relevant to a default installation of SQL Server on it's own machine.

Click to expand...

And I'm sure you're correct. As I said, I have no experience with SQL Server. Just threw in in there in case it was relevant.
PS
Hope the back gets better soon

To Rx - thanks for all your feedback, it's much appreciated. Here are some of the answers:-

1. The SQL Server is in a virtual Server 2012
2. We did use the SSMA, I just used the word upsize as shorthand
3. I do all the linking in code, as you say very quick to switch
4. We are using the SQL Server Native Client 11.0
5. Database approx 1GB as an Access file
6. All Terminal Service users get a fresh front end when they log in

By the way when you refer to Citrix are you meaning that which we call Terminal Services?

I'll take a look at your suggestions with my mate who's more into the server side of things

Thanks again
Dave

Thanks, I just had to ask. Had a situation where a client was running everyone on one single copy via TS - and couldn't understand why it slowed down.
Microsoft has MS Office (except for Access) to comply with Citrix (TS). Go figure.

Citrix and Terminal Server are a joint technology. Citirx has more management interfaces for server/network types to make management easier. We are basically on the same page.
The SQL Server running on a Virtual Server can be very, very tricky.
It can report RAM, processor, other resources, and disk swap that isn't at all true-- its virtual.
It is a very common misunderstanding. There are many good articles about it. Up until a few years ago, nobody would ever consider SQL Server on a Virtual Machine. The documentation on it was very lacking as of last year.

Here is how I divided and conquered.
Get 5 workstations running the front end to directly connect to the SQL Server and run your most aggressive part of the application.
If they run fast, then it probably isn't the SQL Server, it is the TS.
If they run about the same as TS (slow) then look closer at the SQL Server side.

For SQL Server, once the disk caching was reduced, it regained speed. In very general terms Disk cache gets priority over everything on SQL Server.
Feel free to set up recursive complex joins against SQL to measure this.

I have read most of this thread and I think the problem is not hardware, 2 x 6 core processors and 128gb of ram is an awful lot of power for a 1 GB database.

But however what is SQL server configured to use out of the pool of hardware, just because it has 128GB of RAM doesn't mean SQL server is configured to use all of that RAM and the same for processors.

Do you have a sysadmin logon to this database server?

When the upsizing process happened, did all primary / foreign keys get created (and primary key indexes)? Can you check?

Did this database get re-indexed and statistics updated after the upsizing process?

What is the level of index fragmentation? If you don't know how to see this please say.

To SQL_Hell (great name by the way).

I agree that it seems unlikely to be hardware but we are still puzzled by the fact that the apparently least capable machine, mine, is 4x faster than the client's server. Just to be clear, there are many parts of the program but the key part is the display of a quite complex multitab form, and we are measuring how quickly this form is displayed as a measure of speed. On my setup here at home it's 2 seconds (considered acceptable) whereas at the client's offices it is 8 seconds. We do have full access by the way as we do both hardware and software.

The answer to your question about indexes is......not sure. We used the SSMA and sort of assumed that it handled indexes for us. Certainly all 105 tables have primary keys, but as for the other indexes I'm beginning to wonder.

As for the level of index fragmentation - please explain as it means nothing to me.

Thanks for your input
Dave

Haha! the name came as a result of migrating an access application to SQL server back in 2003, and learning SQL server was hell back then .

So...

Please connect to your new database server, open SQL server management studio,open new query and run the following:


Then run


and post the output in this thread.

Also could you please tell me the name of the database in question? I need it for the index fragmentation query

SQL_Hell

Here it is

allow updates,0,1,0,0
backup checksum default,0,1,0,0
backup compression default,0,1,0,0
clr enabled,0,1,0,0
contained database authentication,0,1,0,0
cross db ownership chaining,0,1,0,0
default language,0,9999,0,0
filestream access level,0,2,0,0
max text repl size (B),-1,2147483647,65536,65536
nested triggers,0,1,1,1
remote access,0,1,1,1
remote admin connections,0,1,0,0
remote login timeout (s),0,2147483647,10,10
remote proc trans,0,1,0,0
remote query timeout (s),0,2147483647,600,600
server trigger recursion,0,1,1,1
show advanced options,0,1,1,0
user options,0,32767,0,0

Sorry about the formatting, couldn't get it right

Database name is "LCSDat"

oops so sorry I missed the reconfigure command:

Please run:


then run


now run


There should be more results this time.

Index fragmentation occurs when new records are added to a table that has indexes (all primary keys have there own clustered index by default), these records are not added in the order of the index so overtime the indexes become less performant, basically you just need to rebuild the indexes once a week.

Please run this code to check index fragmentation:

2 files attached.

I haven't worked out how to attach nice looking files. Hope the attached txt files are readable

Dave