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

  • 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


    • Edited by BodoHH Wednesday, March 9, 2016 8:18 AM
    Wednesday, March 9, 2016 8:16 AM

Answers

  • 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

    Monday, March 28, 2016 3:53 PM

All replies

  • 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.


    Thursday, March 10, 2016 7:44 AM
  • 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

    Friday, March 11, 2016 6:40 PM
  • 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

    Friday, March 11, 2016 7:13 PM
  • 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.

    Sunday, March 13, 2016 6:52 PM
  • 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.



    Monday, March 14, 2016 6:45 AM
  • 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

    Monday, March 28, 2016 3:53 PM
  • 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.


    Tuesday, March 29, 2016 1:05 AM