How to improve SQL server performance? (1 Viewer)

[KitaYama]

We have a database with SQL Server backend. More than 50 tables, most of them filled with several thousands records, some of them over 2 or 3 million.
FEs have DSNless linked tables, views, and queries. Everything is blazing fast. Inputting and searching data and printing reports all is done without any lag in performance. Exporting extremely large set of data to Excel for monthly and yearly graphs is done in a matter of seconds.
SQL server is installed on the domain controller and clients use LAN connection to access the data.

To add some new features and testing, and for not working on live data, I installed sql server on my PC, copied the latest backup of main database and imported it into sql sever on my machine, relinked the tables in FE to point to the new server. Now I have an exact copy of BE and FE on my PC.
When I open the FE it's very slow. Opening a form takes more than a minute, in some cases more than 5 minutes.

I expect a database with a localhost BE to be faster than one with a BE on a remote machine. How can I trouble shoot the problem?

Some points you may need to know:

• The forms' recordsource is SELECT * FROM mytable Where False, but on open or loading events they read the privileges of the user to enable/disable the controls or set some default values.
• The connection string is : "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
• I've also used SQL SERVER Client 11.0 driver. The same result.
• Some tables are opened quickly by being double clicked in navigation pane, but others show a lag time between double clicking and opening.
• I waited for about 12 hours for indexes of the tables to be refreshed, still no change in performance.
• Using a T-SQL from this site I rebuilt all the indexes. Still the performance is too poor.
• My PC is extremely powerful. It's been made for 3D CAD. 32GB memory on an intel Xeon CPU and P2000 Nvidia GPU. So I don't think the resource may be a concern.

Is there anything I can do to have the same performance as our main database?
Is there any reason the same BE and the same FE show this difference when the BE is on different servers?

Any kind of advice is much appreciate.

 

[theDBguy]

Hi. I don't know the answer to your question. I have a different setup than your situation. I took the backup from the production database and then restored it in another instance of SQL Server on the same server machine. I then pointed the development FE to that test BE instance. The performance is the same between the live and the test applications. Maybe you could try that setup as well just to see if you get a different result than when you used your own machine to create the test environment. Just a thought...

 

[KitaYama]

Hi. I don't know the answer to your question. I have a different setup than your situation. I took the backup from the production database and then restored it in another instance of SQL Server on the same server machine. I then pointed the development FE to that test BE instance. The performance is the same between the live and the test applications. Maybe you could try that setup as well just to see if you get a different result than when you used your own machine to create the test environment. Just a thought...

Thanks for the suggestion, but unfortunately I don't have the permission to touch the main server, let alone installing a new instance.

 

[Minty]

Make sure your local SQL Server is in a trusted location as far as Access is concerned.

How much memory do you have on your local machine?
SQL Server is a massive memory hog, so it you only have 8gb of memory you might well find SQL is struggling.

 

[KitaYama]

Make sure your local SQL Server is in a trusted location as far as Access is concerned.

I've never heard about sql Server being in a trusted location. I didn't even know there's a trusted location for a server. The only trusted location I've heard of is Excel or Access.
By default SQL Server is installed on C:\Program Files and I have a feeling it's not a trusted location.
I'll search google to see what can I find. I need a little bit reading on this.

How much memory do you have on your local machine?
SQL Server is a massive memory hog, so it you only have 8gb of memory you might well find SQL is struggling.

I have 32GB. Twice the main server. Our DC has only 16GB.
If the main database is that fast on 16GB, I assume it should be faster on localhost with twice memory.

Thanks.

 

[KitaYama]

@Minty When installing a new virtual machine, there's a setting that how much of the over all memory can be used by virtual machine.
Is there any setting like this for SQL server too? Though the overall RAM is 32GB, maybe the server is not allowed to use all of it.

And during my tests, I had several hug apps opened.
I'll try to close all running application and test again. But it won't be until Monday morning. (36 hours from now)

Thanks again.

 

[Minty]

You have it on a local Virtual machine, that might make a difference.
How have you mapped the location as a URL.
You can set the amount of memory available to the Virtual machine in the VM settings, make sure it's not 3 or 4 Gb.

Personally, I would use at least SQL Server Driver 17 - the older ones are deprecated for later versions of SQL server.

 

[KitaYama]

You have it on a local Virtual machine, that might make a difference.
How have you mapped the location as a URL.
You can set the amount of memory available to the Virtual machine in the VM settings, make sure it's not 3 or 4 Gb.

Personally, I would use at least SQL Server Driver 17 - the older ones are deprecated for later versions of SQL server.

Sorry for the confusion.
No I don't have any virtual machine. I just was saying we can set how much memory a virtual machine can use.
Is there any setting like that for sql server too?
I googled it and it seems there's not a setting like that.

 

[sonic8]

We have a database with SQL Server backend. More than 50 tables, most of them filled with several thousands records, some of them over 2 or 3 million.

This is a tiny amount of data for SQL Server.
If you are experience serious performance problems on a powerful computer, there is something fundamentally wrong there.
The only thing I can think of in that regard is a real-time virus scanner scanning the SQL Server database file on each access. Solution: Exclude the database files from any kind of real-time scan.

 

[KitaYama]

Personally, I would use at least SQL Server Driver 17

I'll download the driver and will give it a try.

 

[KitaYama]

The only thing I can think of in that regard is a real-time virus scanner scanning the SQL Server database file on each access. Solution: Exclude the database files from any kind of real-time scan.

No virus scanner app. I hate them and have never used them.
The only protector I have is windows defender.

 

[CJ_London]

other thought - your computer has high level of resources - is your local instance of sql server set up to make best use of them? or throttled back in some way?

 

[KitaYama]

other thought - your computer has high level of resources - is your local instance of sql server set up to make best use of them? or throttled back in some way?

How can I check that?

Thank you.

 

[CJ_London]

Not sure, just know from previous experience at clients that resources can be managed to prioritise one process over another.

One client migrated to sql server believing performance would be better and then migrated back when it wasn’t - because the server prioritised other applications and IT weren’t prepared to either redistribute resource sharing or invest in additional resources.

at another, one particular routine swallowed nearly all the resources between 3 and 4pm every day. Every other app was forced to drop out.

so think you would need to talk to your IT. Have you installed a full blown version of sql server ? Or the express version? The express version only uses one core of memory (same as access) whilst the full blown typically uses four or more

 

[KitaYama]

Not sure, just know from previous experience at clients that resources can be managed to prioritise one process over another.

One client migrated to sql server believing performance would be better and then migrated back when it wasn’t - because the server prioritised other applications and IT weren’t prepared to either redistribute resource sharing or invest in additional resources.

at another, one particular routine swallowed nearly all the resources between 3 and 4pm every day. Every other app was forced to drop out.

so think you would need to talk to your IT. Have you installed a full blown version of sql server ? Or the express version? The express version only uses one core of memory (same as access) whilst the full blown typically uses four or more

Thanks for the info.
I've installed the express version. I'll talk to IT to see if they give me a license.

 

[CJ_London]

a couple of links so you can go prepared

What is the difference between SQL Server and SQL Server Express?

MS SQL Server is a platform for solving critical tasks on an enterprise-wide scale, with high availability, increased productivity and security. The solution is

www.sqlsplus.com

Note the maximum RAM usage

Resource Governor - SQL Server

Learn about the SQL Server Resource Governor feature that limits the amount of CPU, physical I/O, and memory that incoming application requests can use.

docs.microsoft.com

looks like may not be available for express

 

[Pat Hartman]

Are you absolutely certain that your FE is linked to YOUR BE server rather than the production server? This sounds like the responsiveness you would get over the internet rather than on a local BE loaded on your PC.

 

[KitaYama]

Are you absolutely certain that your FE is linked to YOUR BE server rather than the production server? This sounds like the responsiveness you would get over the internet rather than on a local BE loaded on your PC.

Yes, I've checked tables properties several times. They're linked to the server on my PC. I've also deleted all linked tables several time and added them again.

Thank you.

 

[Pat Hartman]

Are you working through a VPN?
Does your local PC have a printer connected to it? If you don't have a physical printer, make the .pdf driver the default to ensure that there is always a printer. Strangely for certain processes, Access needs information from the default printer for formatting. So, this is always one thing to check when you are experiencing strange slowness.

 

[KitaYama]

Are you working through a VPN?
Does your local PC have a printer connected to it? If you don't have a physical printer, make the .pdf driver the default to ensure that there is always a printer. Strangely for certain processes, Access needs information from the default printer for formatting. So, this is always one thing to check when you are experiencing strange slowness.

Yes, my PC is connected to 3 printers on LAN and one printer on USB cable.
Thanks for additional info.