Access 2013 ODBC SQL Server 2012: Performance issue connecting to a named instance

Question

Hi,

on my Windows7 machine I have a SQL Server 2012 Developer edition installation runing
two instances, the main and a named instance.

I have duplicated my application database to both instances.

I experiencing performance issue when I connect my MS Access 2013 app to the database on the named instance.

There is always a delay of ~8sec when opening a form with 3 subforms and underlying tables with about 500 rows.

Navigating to a different record on the same form takes about 3 sec.

I don't have these issues when I connect to the database on the main instance of the Server.

All forms are opening instantly and theres no delay in navigating between records.

SQL Server profiler tells me a duration of 3ms for the query executed by the Access app.

Heres the connection string that I use to connect to the Server:

ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=SRV\SRV_DEV;Trusted_Connection=yes;APP=My Application;DATABASE=DBName;

The SQL Server network protocols for the main instance are enabled of TCP/IP and named pipes, Shared memory is disabled and for the named instance it's TCP/IP enabled other are disabled.

The named instance server properties are the same as for the main instance.

Does anyony experiences the same issue and found a solution?

Any help is greatly appreciated.

Thanks
Bodo

Answer 1

Hi Bodo,

If you run this query in named instance, how long will it spend?

I suggest you create a linked table in Access database from SQL named instance, and run query from Access database based on linked table in Access database.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Hi Edward,

it takes about 2 sec until Access shows the data of a linked table with 144 rows.

I also experiencing a delay when I use SSMS to connect to the named instance, esp. when

SSMS has establish the connection and expands the named instance server node on the Object Explorer.

This did not happen, when I connect to the main instance (MSSQLSERVER)

I assume it relates to the network protocol.

Thanks

Bodo

Answer 3

What about indexing? Have you re-indexed the named instance databases? That might help.

SET ARITHABORT ON 
SET QUOTED_IDENTIFIER ON 
 
USE MyDatabase  -- CHANGE THE DATABASE NAME
go 
DECLARE @tabname sysname 
DECLARE @dbstring varchar(300) 
DECLARE @exec_string varchar(300) 
 
DECLARE tabDBCC CURSOR FOR SELECT TABLE_NAME 
                           FROM INFORMATION_SCHEMA.TABLES 
                           WHERE TABLE_TYPE = 'BASE TABLE' 
 
OPEN tabDBCC 
FETCH NEXT FROM tabDBCC INTO @tabname 
 
SELECT @dbstring = DB_NAME() 
PRINT 'Starting DBCC DBREINDEX for database ' + upper(@dbstring) 
 
WHILE (@@fetch_status = 0) 
  BEGIN 
    PRINT 'Reindexing table ' + upper(@tabname) 
    SELECT @exec_string = 'dbcc dbreindex ([' + @tabname + '])' 
    EXEC(@exec_string) 
    FETCH NEXT FROM tabDBCC INTO @tabname 
  END 
CLOSE tabDBCC 
DEALLOCATE tabDBCC 
 
PRINT 'Finished DBCC DBREINDEX for database ' + upper(@dbstring) 

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 4

Thanks Bill,

I've tried your suggestions but unfortunately it didn't solve the issue.

So I checked the Network configuration with SQL Server Configuration manager and for the named instance I enabled the same protocols, which is now TCP/IP and Named  Pipes.

This works fine now when I use SSMS to connect to the named instance.

However, theres is still a delay accessing the database on the named instance by MS Access.

Answer 5

Hi Bodo,

>> theres is still a delay accessing the database on the named instance by MS Access.

Do you test with odbc or linked table? Does the table in default instance have the same design with named instance? To check whether it is related with Access, I suggest you create a very simple winform application and connect to SQL Server with ODBC to test again.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 6

Hi Edward,

I was able to solve this issue by the following steps:

1. Run SQL Server Configuration Manager

2. Locate the protocol for the named instance under the SQL Server Network Configuration node

3. On the right pane doubleclick the TCP/IP entry

4. Click the IP Adresses tab

5. For each IP Adress set Active = False and Enabled = False

    except for the IP-adress of my local computer and IP adress127.0.0.1

These steps works for me, because there is no delay accessing the databases on the named instance.

Best Regards,

Bodo

Answer 7

Hi BodoHH,

Thanks for sharing, and I suggest you mark your reply as answer to close this thread, then others who run into the same issue would find the solution easily.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.