1. Avoid using “*” in SELECT queries
Always specify the required list of columns in the select list. This will ensure that only the columns required by the query are returned to avoid unnecessary I/O and processing.
2. Always use variables of the appropriate data type and size
Use appropiate data types to avoid implicit data type conversion being perfromed by SQL Server and appropiate sizes to avoid excess usage of memory.
For example, assigning an integer type to a varchar is allowed but SQL Server will implicitly convert the integer data type to varchar which causes additional overhead.
For a column/variable like Age a data type of type integer is required. For this int, smallint or tinyint types can be used. But using int or smallint would require 4 and 2 bytes respectively of memory which is very large to accommodate the Age variable. Hence using tinyint would be a better choice, which would occupy 1 byte and provide a range between 0 and 255.
3. Keep transactions as small as possible
Try and avoid using large transactions. Using large transactions across an entire batch can cause other processes to be locked/blocked and the resources involved in the transaction cannot use them until the transaction is completed (committed or rolled back) causing performance issues in the database or even causing deadlocks.
4. Avoid excessive usage of temp tables
Excessive usage of temp tables may cause tempdb contention which can degrade the overall performance of the system. You can use table variables instead of temp tables, as this can use the memory allocated to that process and avoid usage of tempdb. It is recommended to use temp tables when dealing with considerably large amounts of intermediate data.
When using temp tables avoid using the SELECT INTO clause to create and insert data into temp tables. This will lock the entire tempdb and will cause blocking issues with other processes trying to use the tempdb resources. Always use the CREATE TABLE statement to create the temp table and then use INSERT INTO statement to insert data into the temp table. This wwill avoid tempdb contention and allow other process to use the tempdb resources.
5. Environmental settings
Always use “SET NOCOUNT ON” in stored procedures to avoid unnecessary data traffic on the network.
6. Avoid usage of cursors
Cursors are CPU intensive and make round trips to the CPU for every execution, degrading the overall performance of the batch. Instead use while loops to loop through the data rows. In SQL Server 2005, a CTE can be used to loop through data rows.
7. Never use a function towards left side in the WHERE clause
Using a function towards the left side in the WHERE clause can prevent SQL Server using an index. If needed use the function call on the right side in the WHERE clauses which allows the index to be used for the query.
8. Avoid using the DISTINCT clause
If you need to return a distinct set of rows, use a GROUP BY clause instead of DISTINCT. As the GROUP BY clause is evaluated before the DISTINCT clause.
9. Always restrict the size of SP or UDF
SQL Server maintains a data/execution plan in cache of 300 sec. If a SP/UDF is executed its execution and context plan is cached by SQL Server for 300 sec, and if a call happens to the same SP/UDF within 300 sec the cached execution plan (context plan may be same or changed depending on the parameters) is used by SQL Server for execution else it will reload the entire SP/UDF and then execute it. If the size of the SP/UDF is large then the time take for loading it into memory is more and hence there is a performance hit. If the SP/UDF is smaller enough to load faster, it adds up to the performance gain. Generally restrict the size of the SP/UDF to be around 4-5KB.
10. High Read intensive queries
High read intensive queries i.e. queries which retrun large amount of data (rows) from a database will require more I/O then CPU. Such queries should be run on a single processor rather than spanning it across multiple processors. Either set the “Max Degree of Parallelism” to 1 using sp_configure (this affects entire database) or use the MAXDOP option to restrict the particular query to use only one processor.
11. Always use UDF instead of SP when it is supposed to return a scalar value
When deciding between an SP and a UDF to return a scalar value, it is recommended to use a UDF. Performance wise both an SP and UDF are equal. A UDF provides the facility to be used within a SELECT query and in a WHERE clause.
12. Use Locking hints / Isolation Levels as required
Use the locking hints especially NOLOCK with read only queries to avoid locking/blocking other processes using the same object(s). When a batch/SP needs to be run under non-blocking condition use the transaction isolation (read uncommitted) level. This also improves the performance of the queries as SQL Server does not have to cater the lock resources.
13. Avoid using OR in WHERE/JOIN clauses in a Query
Using the OR clause in a WHERE or JOIN would make SQL Server not use correct indexes. It is sometimes ok to use the OR in WHERE but it’s dangerous to use OR in a JOIN. If it’s a mandate (depends on the requirement) to use OR in the JOIN then divide the query in two parts and combine the data using UNION or UNION ALL. This will provide a drastic improvement in the performance.
14. Avoid using IN and NOT IN clauses
The usage of IN and NOT IN clauses in WHERE conditions will make SQL Server check for all the values within the IN clause, and this will degrade the performance if the number of values within the IN are too many. (IN clause query can be replaced using JOINs.) Instead of IN/NOT IN use EXITS/NOT EXITS which will check for the very first existence and continue with the query, improving the performance.
15. Always have a CLUSTERED INDEX defined on a table
When designing (Normalization) a table it is recommended to have CLUSTRED INDEX created on the table. Having a clustered index sorts the data w.r.t. the column data and any subsequent non-clustered index on that table will use this clustered index (sorted) to seek the data. Also it will help reduce fragmentation within the data/index pages. Consider having a clustered index on a primary key column(s) or define an identity column for the table and qualify that for the PK and clustered index.
When planning for a Index (clustered or non-clustered), and depending on the table functionality (high read or write intensive) specify a FILLFACTOR. If the table is more write intensive specify a fillfactor between 85-90% depending on the size of the row that would be inserted. For read only tables (OLAP) its not required to have a fill factor. Having a proper fill factor would minimize the page splits avoiding SQL Server time to service the page splits.
16. Turn off the AUTO SHRINK
For databases with more write operations, always ensure that the AUTO SHRINK property is turned OFF. Database shrink operation shrinks the database files and also causes fragmentation of indexes which will hinder the over all performance. Always run the database shrink operation during off peak hours immediately followed by a defragmentation of indexes.
17. Run UPDATE STATISTICS regularly
For databases with more write operations, the statistics will go out of date causing SQL Server to use old statistics causing performance problems. Hence always have a scheduled job (off peak hours) to update the statistics of all the tables (or transactional tables) regularly.
Saturday, July 16, 2011
Friday, July 1, 2011
Comma seperated values using SQL Server using Query...
How to get comma seperated string from table.
I have table called TableName and I have colum name "Age"
Using simple query I get
Age
===
12
23
45
56
67
78
78
but I want the output like this
12,23,45,56,67,78,78
Then use this query to get output in this format...
You can use this query but first comma will be display...
========================================
select ', ' + columname from -- create comma separated values
(
select ColumName from TableName--Your query here
) AS T FOR XML PATH('')
OR
If you want to remove first comma also then use this query
========================================
select STUFF(
(
select ', ' + ColumName from -- create comma separated values
(
select ColumName from TableName--Your query here
) AS T FOR XML PATH('')
)
,1,1,'') AS [Name]
I have table called TableName and I have colum name "Age"
Using simple query I get
Age
===
12
23
45
56
67
78
78
but I want the output like this
12,23,45,56,67,78,78
Then use this query to get output in this format...
You can use this query but first comma will be display...
========================================
select ', ' + columname from -- create comma separated values
(
select ColumName from TableName--Your query here
) AS T FOR XML PATH('')
OR
If you want to remove first comma also then use this query
========================================
select STUFF(
(
select ', ' + ColumName from -- create comma separated values
(
select ColumName from TableName--Your query here
) AS T FOR XML PATH('')
)
,1,1,'') AS [Name]
SQL Server Connection Pooling (ADO.NET)
Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.
In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.
Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.
Only connections with the same configuration can be pooled. ADO.NET keeps several pools at the same time, one for each configuration. Connections are separated into pools by connection string, and by Windows identity when integrated security is used. Connections are also pooled based on whether they are enlisted in a transaction.
Pooling connections can significantly enhance the performance and scalability of your application. By default, connection pooling is enabled in ADO.NET. Unless you explicitly disable it, the pooler optimizes the connections as they are opened and closed in your application. You can also supply several connection string modifiers to control connection pooling behavior. For more information, see "Controlling Connection Pooling with Connection String Keywords" later in this topic.
Note
When connection pooling is enabled, and if a timeout error or other login error occurs, an exception will be thrown and subsequent connection attempts will fail for the next five seconds, the "blocking period". If the application attempts to connect within the blocking period, the first exception will be thrown again. After the blocking period ends, another connection failure by the application will result in a blocking period that is twice as long as the previous blocking period. Subsequent failures after a blocking period ends will result in a new blocking periods that is twice as long as the previous blocking period, up to a maximum of five minutes.
Pool Creation and Assignment
When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created. Connections are pooled per process, per application domain, per connection string and when integrated security is used, per Windows identity. Connection strings must also be an exact match; keywords supplied in a different order for the same connection will be pooled separately.
In the following C# example, three new SqlConnection objects are created, but only two connection pools are required to manage them. Note that the first and second connection strings differ by the value assigned for Initial Catalog.
using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=Northwind"))
{
connection.Open();
// Pool A is created.
}
using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=pubs"))
{
connection.Open();
// Pool B is created because the connection strings differ.
}
using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=Northwind"))
{
connection.Open();
// The connection string matches pool A.
}
If MinPoolSize is either not specified in the connection string or is specified as zero, the connections in the pool will be closed after a period of inactivity. However, if the specified MinPoolSize is greater than zero, the connection pool is not destroyed until the AppDomain is unloaded and the process ends. Maintenance of inactive or empty pools involves minimal system overhead.
Removing Connections
The connection pooler removes a connection from the pool after it has been idle for a long time, or if the pooler detects that the connection with the server has been severed. Note that a severed connection can be detected only after attempting to communicate with the server. If a connection is found that is no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connection pool only when they are closed or reclaimed.
If a connection exists to a server that has disappeared, this connection can be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. This is the case because the overhead of checking that the connection is still valid would eliminate the benefits of having a pooler by causing another round trip to the server to occur. When this occurs, the first attempt to use the connection will detect that the connection has been severed, and an exception is thrown.
Clearing the Pool
ADO.NET 2.0 introduced two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections being used at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.
Transaction Support
Connections are drawn from the pool and assigned based on transaction context. Unless Enlist=false is specified in the connection string, the connection pool makes sure that the connection is enlisted in the Current context. When a connection is closed and returned to the pool with an enlisted System.Transactions transaction, it is set aside in such a way that the next request for that connection pool with the same System.Transactions transaction will return the same connection if it is available. If such a request is issued, and there are no pooled connections available, a connection is drawn from the non-transacted part of the pool and enlisted. If no connections are available in either area of the pool, a new connection is created and enlisted.
When a connection is closed, it is released back into the pool and into the appropriate subdivision based on its transaction context. Therefore, you can close the connection without generating an error, even though a distributed transaction is still pending. This allows you to commit or abort the distributed transaction later.
Controlling Connection Pooling with Connection String Keywords
The ConnectionString property of the SqlConnection object supports connection string key/value pairs that can be used to adjust the behavior of the connection pooling logic. For more information, see ConnectionString.
Pool Fragmentation
Pool fragmentation is a common problem in many Web applications where the application can create a large number of pools that are not freed until the process exits. This leaves a large number of connections open and consuming memory, which results in poor performance.
Pool Fragmentation Due to Integrated Security
Connections are pooled according to the connection string plus the user identity. Therefore, if you use Basic authentication or Windows Authentication on the Web site and an integrated security login, you get one pool per user. Although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. It also results in at least one connection per user to the database server. This is a side effect of a particular Web application architecture that developers must weigh against security and auditing requirements.
Pool Fragmentation Due to Many Databases
Many Internet service providers host several Web sites on a single server. They may use a single database to confirm a Forms authentication login and then open a connection to a specific database for that user or group of users. The connection to the authentication database is pooled and used by everyone. However, there is a separate pool of connections to each database, which increase the number of connections to the server.
This is also a side-effect of the application design. There is a relatively simple way to avoid this side effect without compromising security when you connect to SQL Server. Instead of connecting to a separate database for each user or group, connect to the same database on the server and then execute the Transact-SQL USE statement to change to the desired database. The following code fragment demonstrates creating an initial connection to the master database and then switching to the desired database specified in the databaseName string variable.
// Assumes that command is a SqlCommand object and that
// connectionString connects to master.
command.Text = "USE DatabaseName";
using (SqlConnection connection = new SqlConnection(
connectionString))
{
connection.Open();
command.ExecuteNonQuery();
}
Application Roles and Connection Pooling
After a SQL Server application role has been activated by calling the sp_setapprole system stored procedure, the security context of that connection cannot be reset. However, if pooling is enabled, the connection is returned to the pool, and an error occurs when the pooled connection is reused. For more information, see the Knowledge Base article, "SQL application role errors with OLE DB resource pooling."
In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.
Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.
Only connections with the same configuration can be pooled. ADO.NET keeps several pools at the same time, one for each configuration. Connections are separated into pools by connection string, and by Windows identity when integrated security is used. Connections are also pooled based on whether they are enlisted in a transaction.
Pooling connections can significantly enhance the performance and scalability of your application. By default, connection pooling is enabled in ADO.NET. Unless you explicitly disable it, the pooler optimizes the connections as they are opened and closed in your application. You can also supply several connection string modifiers to control connection pooling behavior. For more information, see "Controlling Connection Pooling with Connection String Keywords" later in this topic.
Note
When connection pooling is enabled, and if a timeout error or other login error occurs, an exception will be thrown and subsequent connection attempts will fail for the next five seconds, the "blocking period". If the application attempts to connect within the blocking period, the first exception will be thrown again. After the blocking period ends, another connection failure by the application will result in a blocking period that is twice as long as the previous blocking period. Subsequent failures after a blocking period ends will result in a new blocking periods that is twice as long as the previous blocking period, up to a maximum of five minutes.
Pool Creation and Assignment
When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created. Connections are pooled per process, per application domain, per connection string and when integrated security is used, per Windows identity. Connection strings must also be an exact match; keywords supplied in a different order for the same connection will be pooled separately.
In the following C# example, three new SqlConnection objects are created, but only two connection pools are required to manage them. Note that the first and second connection strings differ by the value assigned for Initial Catalog.
using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=Northwind"))
{
connection.Open();
// Pool A is created.
}
using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=pubs"))
{
connection.Open();
// Pool B is created because the connection strings differ.
}
using (SqlConnection connection = new SqlConnection(
"Integrated Security=SSPI;Initial Catalog=Northwind"))
{
connection.Open();
// The connection string matches pool A.
}
If MinPoolSize is either not specified in the connection string or is specified as zero, the connections in the pool will be closed after a period of inactivity. However, if the specified MinPoolSize is greater than zero, the connection pool is not destroyed until the AppDomain is unloaded and the process ends. Maintenance of inactive or empty pools involves minimal system overhead.
Removing Connections
The connection pooler removes a connection from the pool after it has been idle for a long time, or if the pooler detects that the connection with the server has been severed. Note that a severed connection can be detected only after attempting to communicate with the server. If a connection is found that is no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connection pool only when they are closed or reclaimed.
If a connection exists to a server that has disappeared, this connection can be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. This is the case because the overhead of checking that the connection is still valid would eliminate the benefits of having a pooler by causing another round trip to the server to occur. When this occurs, the first attempt to use the connection will detect that the connection has been severed, and an exception is thrown.
Clearing the Pool
ADO.NET 2.0 introduced two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections being used at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.
Transaction Support
Connections are drawn from the pool and assigned based on transaction context. Unless Enlist=false is specified in the connection string, the connection pool makes sure that the connection is enlisted in the Current context. When a connection is closed and returned to the pool with an enlisted System.Transactions transaction, it is set aside in such a way that the next request for that connection pool with the same System.Transactions transaction will return the same connection if it is available. If such a request is issued, and there are no pooled connections available, a connection is drawn from the non-transacted part of the pool and enlisted. If no connections are available in either area of the pool, a new connection is created and enlisted.
When a connection is closed, it is released back into the pool and into the appropriate subdivision based on its transaction context. Therefore, you can close the connection without generating an error, even though a distributed transaction is still pending. This allows you to commit or abort the distributed transaction later.
Controlling Connection Pooling with Connection String Keywords
The ConnectionString property of the SqlConnection object supports connection string key/value pairs that can be used to adjust the behavior of the connection pooling logic. For more information, see ConnectionString.
Pool Fragmentation
Pool fragmentation is a common problem in many Web applications where the application can create a large number of pools that are not freed until the process exits. This leaves a large number of connections open and consuming memory, which results in poor performance.
Pool Fragmentation Due to Integrated Security
Connections are pooled according to the connection string plus the user identity. Therefore, if you use Basic authentication or Windows Authentication on the Web site and an integrated security login, you get one pool per user. Although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. It also results in at least one connection per user to the database server. This is a side effect of a particular Web application architecture that developers must weigh against security and auditing requirements.
Pool Fragmentation Due to Many Databases
Many Internet service providers host several Web sites on a single server. They may use a single database to confirm a Forms authentication login and then open a connection to a specific database for that user or group of users. The connection to the authentication database is pooled and used by everyone. However, there is a separate pool of connections to each database, which increase the number of connections to the server.
This is also a side-effect of the application design. There is a relatively simple way to avoid this side effect without compromising security when you connect to SQL Server. Instead of connecting to a separate database for each user or group, connect to the same database on the server and then execute the Transact-SQL USE statement to change to the desired database. The following code fragment demonstrates creating an initial connection to the master database and then switching to the desired database specified in the databaseName string variable.
// Assumes that command is a SqlCommand object and that
// connectionString connects to master.
command.Text = "USE DatabaseName";
using (SqlConnection connection = new SqlConnection(
connectionString))
{
connection.Open();
command.ExecuteNonQuery();
}
Application Roles and Connection Pooling
After a SQL Server application role has been activated by calling the sp_setapprole system stored procedure, the security context of that connection cannot be reset. However, if pooling is enabled, the connection is returned to the pool, and an error occurs when the pooled connection is reused. For more information, see the Knowledge Base article, "SQL application role errors with OLE DB resource pooling."
Subscribe to:
Posts (Atom)