Connection pooling questions

Posted on 2006-05-02
Last Modified: 2008-02-01
If I have 2 web servers accessing a DB on a separate DB server, does the connection pool reside on the DB server or is there a separate pool on each web server? How do I configure the pool size? How do I determine the number of connections in use? Does the choice of SQLOLEDB vs  ODBC impact the answers?
Environment: ASP apps on IIS5 hitting SQL Server 2K SP3, all on Win2K SP3.

Question by:monosodiumg
    LVL 4

    Expert Comment

    If you enable configure the data provider to use connection pooling, you can define additional options that affect performance:

    Connection Lifetime: You can define how long to keep connections in the pool. The pool manager checks a connection's creation time when it is returned to the pool. The creation time is compared to the current time, and if the timespan exceeds the value of the Connection Lifetime option, the connection is destroyed. The Min Pool Size option can cause some connections to ignore this value.

    Connection Reset: Resetting a re-used connection to the initial configuration settings impacts performance negatively because the connection must issue additional commands to the server.

    Max Pool Size: Setting the maximum number of connections that the pool can contain too low might cause delays while waiting for a connection to become available. Setting the number too high wastes resources.

    Min Pool Size: A connection pool is created when the first connection with a unique connection string connects to the
    database. The pool is populated with connections up to the minimum pool size, if one has been specified. The connection pool retains this number of connections, even when some connections exceed their Connection Lifetime value.

    SQL Server allows you to control a few of its configurable options during startup with the use of startup parameters. These parameters are often useful for troubleshooting purposes -- usually in the cases of extreme emergencies; but they can also be useful for some long-term configuration changes as well.

    To change a parameter through the Enterprise Manager:

    Open Enterprise Manager and right click on a registered installation.

    Click Properties.

    Click the Startup Parameters button.

    Add your new parameters or remove an existing one. Be careful not to remove the default parameters that give the locations of the master database, the master database log, and the error logs unless you provide an alternate location.
    If you take a look at the startup parameters dialog box, will see there are a few default parameters that are part of every SQL Server installation:




    You can use these default parameters to change the locations of the master database file, the master database log file, or the SQL Server error logs. I would not do this unless you have a problem with the default location's drive or if the master database or error logs outgrow their current directories and you need to move them to another drive.

    If you are simply moving the master database data file or log file to another location, then you can change the parameter from Enterprise Manger before you stop the instance. If you are moving the file because of a problem, stop the instance and use the sqlservr.exe command to restart the instance pointing to the new data file or log file location.

    SQL Server also has a few optional startup parameters that you can set to help troubleshoot your installation or make some minor performance optimizations.

    -c Shortens SQL Server's startup time by starting the instance independent of the Service Control Manager. Starting SQL Server independent of the Service Control Manager will keep if from running as a Windows service. I have not had the need to use this parameter so I can only guess that it could be useful for troubleshooting startup issues.

    -f This parameter starts an instance of SQL Server with the minimal configurations and enables the sp_configure allow updates option. This parameter is very useful if a configuration option is set that disables SQL Server. You can use this parameter to start SQL Server and change the inappropriate configuration setting back to the original.

    -g This parameter will specify the amount of virtual address space that SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. What does that mean? This memory allocation outside of the SQL Server memory pool is used for loading times such as extended stored procedures, dll files, OLE DB providers referenced by queries, and automation objects reference in Transact-SQL statements. The default size of this memory is 128 MB, which is usually fine for most SQL Server installations. Keep in mind that SQL Server installations under 2 GB (SQL Server 2000 Standard Edition) or 3 GB (Enterprise Edition) will only use 128 MB no matter what size you set with the -g parameter. You can however, use this parameter to optimize the memory on installations above the 2 GB and 3 GB cutoffs. Microsoft recommends that you do not use this parameter unless you see the following error in your error log.

    WARNING: Clearing procedure cache to free contiguous memory

    However, I have used this and seen others use this parameter for errors such as:

    OLE DB provider 'SQLOLEDB' reported an error. The provider ran out of memory.

    -m This parameter may be one of the most used, especially if you are studying for the SQL Server tests. The -m parameter will start SQL Server in the single user mode and enable the sp_configure allow updates configuration option. Starting SQL Server in the single user mode does not issue a CHECKPOINT command. This parameter allows you work on your system databases if you encounter a problem with one of them. If you ever have to rebuild your master database you will be familiar with this parameter.

    -n This parameter turns off SQL Server logging its errors to the Windows application log. It is recommended that if you use this parameter you should also use the -e starup parameter, which you should be doing anyway. This is another parameter that I have not used and personally, I would not recommend turning off the Windows application logging.

    -s This parameter is used to start a named instance of SQL Server. Without the use of this parameter, SQL Server will try to start the default instance, which may or may not be present or the one you want to start.

    /Ttrace# Used to start a Trace Flag when you start SQL Server. This parameter is useful for general troubleshooting purposes.

    -x This parameter will disable the keeping of the CPU time and CACHE-HIT ratio statistics. While this parameter allows maximum performance, I do not use it as the benefits of the CACHE-HIT ratio outweigh the small performance gain this parameter buys you.
    Most of the time, you will not have to use these parameters but they are nice to know for that rare instance that you have system database problems or when you need to start a Trace Flag.

    Hope this will answer your query.

    Chetan Sachdeva
    LVL 12

    Author Comment

    I don't see anything in there that answers any of my questions.
    LVL 4

    Accepted Solution

    The connection pool always resides on your DB server.

    ADODB by default enforces connection pooling.However you can configure the pool size using the "min pool size"  and "max pool size" parameter in your connection string.

    The number of connections can be determined in 2 ways:
     * by looking at the process info available in SQL server Enterprsie manager (Enterprise manager->MicrosoftSQLServer->SQLServerGroup->YourDBServer->Management->currentActivity->ProcessInfo)
    * by using performance monitor:
    1) type perfmon in command prompt to open performance monitor window
    2) right click on the right pane and choose add counter
    3) in the add counters window choose SQL server general statistics under performance object
    4)select "select counters from list"
    5)choose "user connections" and click ok.

    The choice of SQLOLEDB vs  ODBC does not impact connection pooling


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction If you are not already aware of what you could use a table with sequential integer values for in SQL, you can read Delimited String Parsing in SQL Server 2005 or later ( by BrandonGalderisi (h…
    Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now