Upsizing - SQL Server has slow response time (1 Viewer)

[Rx_]

Any ideas would be appreciated on why a simple query takes 7 seconds to respond?

Upsized the Access 2010 back-end to SQL Server 2008. Running one copy of Access 2010 Front end to a SQL Server DB and one Copy of the same Access 2010 Front-end to the Access 2010 DB (back end).

A form uses a query to open the first main screen that allows the user to select a Well.
On Access to Access it opens really fast.
On Access to SQL Server - it takes a grueling 7 seconds.
Created a View in SQL Server with the exact same query - It is under a second, but still not as fast.

We are not talking complex queries or large tables (see attachment).
Went to SQL Server and removed all indexes for these tables. Nothing changed. Changed SQL to Simple Recovery.
The ODBC driver is SQL Server for the SQL version.

SQL Server Version is RTM 10.50.1600.1 - no SP 1, 2, or 3.

The SQL Manager appears to run the view very fast for what that is worth.

Just a final note: The solution was posted below. However, this query (see red arrows) while improved is still unacceptable. This somewhat surprised me. The Access 2010 engine was expected to communicate with SQL Server 2008 more efficiently. So, as noted below - this will be converted to a View in SQL Server. The Linked Table to this View returns records much, much faster.

 

[bparkinson]

Is the slow query an Access query that SELECTS from a linked SQL Server view?

 

[SQL_Hell]

The grueling 7 seconds is because you are executing an access query against SQL server and basically the entire contents of each table is being returned to access and then the are being joined at access level.

The view is quicker because the joins are processed at the SQL server therefore you are only being sent the data you need.

Use your view and do not use anymore access queries, from now on you should be using only views and stored procedures.

If your 1 second view is still considered too slow create non clustered indexes on every field you are joining on, that should speed it up.

If this doesn't speed it up.. do you have a slow network?

 

[pbaldy]

Are all the tables on SQL Server? Or are those that slow it down local? Access (JET/ACE) is actually pretty good at passing the SQL back to SQL Server to let it do the processing when it can. It wouldn't be able to do that if one of the tables was local.

 

[Rx_]

Thanks Paul, that has also been my experience. Yes all the tables are all on SQL Server.

Attached is a Performance Monitor showing the same query run as a SQL Server View - run as a single user from SQL Server Management Studio.
The Memory Page Faults/sec and % Processor Time seem to appear a little high to me. The graph on the first 70% shows running the View several times in a row. The maximum is much higher than the chart shows.

This is a Virtual Server running SQL Server that was set up by IT for me to move into development. This View takes about 1.5 seconds to run.

Do these page and CPU appear to be normal for a single user?

MSDN has this to say about Vritual Machine running SQL - but I don't see a solution:
A high rate for the Pages/sec counter could indicate excessive paging. Monitor the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging.
A low rate of paging (and hence page faults) is typical, even if the computer has plenty of available memory. The Microsoft Windows Virtual Memory Manager (VMM) takes pages from SQL Server and other processes as it trims the working-set sizes of those processes. This VMM activity tends to cause page faults. To determine whether SQL Server or another process is the cause of excessive paging, monitor the Process: Page Faults/sec counter for the SQL Server process instance.

 

[Rx_]

http://technet.microsoft.com/en-us/library/ms176018(v=sql.100).aspx
Finally got to peek at the Virtual Machine. I has 2GB of Virtual memory. Showing the entire VMM SQL Server is running on 2 GB of RAM. Just guessing that that might be a little lightweight.
Any suggestions?

For what it is worth, posted a "is 2GB physical Memory enough on a VM" over at SQL Server Central dot com.
The answer in a nutshell is "No". One suggested that a stable environment might require 8 GB. Also, to set the min RAM high so VM doesn't have the overhead of allocating and de-allocating.
The IT department is going to raise the memory from 2 GB to 4 GB.

Thank you everone for the suggestions. Will update any results soon.

 

[Rx_]

Just wanted to report what fixed the primary problem just in case it helps others. This seemed to solve the bigger problem. However, the MS Access Query was much slower on Access 2010 than I had expected.

This was a Virtual Server with SQL Server 32-bit RTM installed to Upsize the back-end of MS Access.
The VM Server was set up with 2 GB of Physical Ram and 1 u-Processor. This is pretty much exactly what Microsoft recommends on MSDN.

By using Performance Monitor (Perf Mon) the Memory page fault, the SQL Server was starved for memory. The paging created its own problems with overhead and swapping. Running a View in SQL Enterprise took seconds. The memory was upgraded from 2 GB to 4 GB and this resulted in a improvement. But, running the query (as a view) in SQL Server Manager was still between 1 to 3 seconds. On a Access front-end to Access back-end it is basically instantatious.

With the Memory Page Faults now out of the way, the CPU time was peaked at 100% (low of 98%) just in SQL Server idle mode. A 2nd processor was allocated to the VM SQL Server. Now the SQL Server idle mode is about 3%. So, 2 uProcessors is probably the minimal required.

Now, the same view run from SQL Server Management Studio can be executed 5 to 7 times in one second. CPU time is less than 50%.

From MS Access calling the speed test query - this query is still very slow.
From MS Access calling the same query as a view in SQL Server (via linked talbe) is maybe a 1/4 of a second. Very acceptable considering 6,000 records are returned across the network to a desktop PC.

This same speed test query called from an MS Access front-end to MS-Access back-end is very fast. This surprised me. My expectation was that MS Access 2010 had more compatibility to task SQL Server. Well, in this case it didn't.

Upsizing to SQL server will generally include moving queries over to SQL Server and converting them to Views, Stored Procedures, or Pass-Through Queries. The Upsizing Wizard is just a first step.

The second step was to insure the SQL Server was provided with enough resources to reap improvements over a MS Acces back-end database.

A huge THANK YOU to everyone. Often the solution is found after all the possible suggestions are tried and eliminated.