Asp.net Connection String Performance Optimization:
Introduction:
Most
ADO.NET data providers use connection pooling to improve the performance of
applications built around Microsoft's disconnected .NET architecture. An
application opens a connection (or gets a connection handle from the pool),
runs one or more queries, processes the row set, and releases the connection
back to the pool. Without connection pooling, these applications would spend a
lot of additional time opening and closing connections.When you use ADO.NET connection pooling to manage connections for your Web-based applications and client/server Web service applications, your customers will usually get faster connections and better overall performance. But what happens when your application or Web site is suddenly flooded with customers who all want to connect at the same time? Will your applications sink or swim? Like a lifeguard, you need to monitor your connection pools carefully to maintain good performance and to prevent your pools from overflowing. Let's explore the reasons a connection pool might overflow, and then see how you can write code or use Windows Performance Monitor to monitor your pools.
These are ways to following the connection string in your application some of avoid the discomfort and increase performance better in your application.
Your check status in your mssql server Process .we were open the connection data provider .net framework using sqlserver means in display program name “.Net SqlClient Data Provider “ and also display the waiting time .
SELECT SPID,STATUS,waittime,PROGRAM_NAME,LOGINAME=RTRIM(LOGINAME),HOSTNAME,CMD FROM MASTER.DBO.SYSPROCESSES
once the connectio open to exiting the process status by sleeping.
SELECT SPID,STATUS,waittime,PROGRAM_NAME,LOGINAME=RTRIM(LOGINAME),HOSTNAME,CMD FROM MASTER.DBO.SYSPROCESSES where Status='runnable'
If you have to execute query using Sql query Editor to display status by runnable.
Otherwise to execute System Store procedure:
EXEC SP_WHO
Pooling:
Pooling Architecture:
Connection string pooling to Controlling properties:
1. Pooling
2. Packet Size
3. Min Pool Size
4. Max Pool Size
5. Connection Life Time
6. Connection Timeout
7. Enlist
8. Increase the Pool Size
9. Decrease the Pool size
Sample Connection String Controlling pooling:
<add name="LocalSqlServer" connectionString="Data Source=************;Persist Security Info=True;User Id=****;Password=*******;Initial CataLog=*******;Trusted_Connection=False;Pooling=True;Max Pool size=100;Min pool size=0;Packet Size=512;Connection Timeout=0" />
1. Pooling:
Introduction:
.net framework pages Connection through the database improving the performance to use and Polling Concept. A connection container of open the pool reusable connection. When the connection to open to use a pool memory .when last connection is closed the database other pool can used. The main disadvantage is that one or more database connections, even if they are currently not being used, are kept open. The Data Providers in ADO.NET have Connection Pooling turned on by default; if you need to turn it off, specify Pooling = false in the connection string being used. Connection Pooling gives you an idle, open, reusable connection instead of opening a new one every time a connection request to the database is made. When the connection is closed or disposed, it is returned to the pool and remains idle until a request for a new connection comes in. If we use Connection Pooling efficiently, opening and closing of connections to the database becomes less resource expensive. This article discusses what Connection Pooling is all about and how Connection Pooling can be used efficiently to boost the performance and scalability of applications.
The Default Pool is: Pooling=True;
When pooling to true. The SQLConnection object is drawn from the appropriate pool, or if it is required, is created and added to the appropriate pool to false.
2. Packet Size:
The Default Packet Size is: Packet Size =512;Packet Size may be a value in the range of 512 and 32767 bytes. An exception is generated if the value is outside this range.
Setting the default value to a number greater than 8000 will cause the packets to use the MultiPage allocator on the instance of SQL Server instead of the much more efficient SinglePage allocator, reducing the overall scalability of the SQL Server.
3. Minimum Pool Size:
The Default Minimum Pool Size is: Min Pool Size =0;
The minimum number of connections allowed in the pool.
4. Maximum Pool Size:
The Default Maximum Pool Size is: Max Pool Size=100;
The Maximum Pool Size is depends upon the Server Configuration. The Maximum number of Connection allowed in the pool configuration.
5. Connection Life Time:
The Default Connection Life time is: Connection LifeTime =0;
When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time seconds. The value specified by Connection Lifetime. A value of zero causes pooled connections to have the maximum connection timeout.
6. Connection Timeout:
The Default Connection TimeOut is: Connection TimeOut =15;
Maximum Time (in secs) to wait for a free connection from the pool
7. Enlist:
The Default Enlist: Enlist=True;
When Enlist true means Pooler automatically enlists the connection in the creation thread's current transaction context. Recognized values to use Set Enlist = "false" to ensure that connection is not context specific.
8. Increase the Pool Size:
The Default Increase pool size: Incr Pool Size=5;
Controls the number of connections that are established when all the connections are used.
9. Decrease the Pool size:
The Default Decrease pool size: Decr Pool Size=1;
Controls the number of connections that are closed when an excessive amount of established connections are unused.