Category Archives: SQL

Microsoft SQL SQL Server

Find the name of SQL Server

select serverproperty (‘MachineName’) as Servername

SELECT SERVERPROPERTY(‘ServerName’) AS ServerName

Select SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as PhysicalName

SELECT @@SERVERNAME AS ServerNameGlobalVar

MySQL Open Source SQL Utils

SQLyog – GUI Tool for MySQL

  • The leading Free and Open Source MySQL GUI Tools for the last 5 years
  • Uses Native C MySQL Client – the fastest way to access MySQL

Enterprise Version vs Community version

Homepage: http://www.webyog.com

ASP.Net SQL SQL Server

Optimizando o pool de conecções com ADO.NET em aplicações ASP.NET

Tuning Up ADO.NET Connection Pooling in ASP.NET Applications
By Dmitri Khanine
Connection Pooling Basics
Opening a database connection is a resource intensive and time consuming operation. Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. Connection pool manager maintains a pool of open database connections. When a new connection requests come in, the pool manager checks if the pool contains any unused connections and returns one if available. If all connections currently in the pool are busy and the maximum pool size has not been reached, the new connection is created and added to the pool. When the pool reaches its maximum size all new connection requests are being queued up until a connection in the pool becomes available or the connection attempt times out.Connection pooling behavior is controlled by the connection string parameters. The following are four parameters that control most of the connection pooling behavior:

  • Connect Timeout – controls the wait period in seconds when a new connection is requested, if this timeout expires, an exception will be thrown. Default is 15 seconds.
  • Max Pool Size – specifies the maximum size of your connection pool. Default is 100. Most Web sites do not use more than 40 connections under the heaviest load but it depends on how long your database operations take to complete.
  • Min Pool Size – initial number of connections that will be added to the pool upon its creation. Default is zero; however, you may chose to set this to a small number such as 5 if your application needs consistent response times even after it was idle for hours. In this case the first user requests won’t have to wait for those database connections to establish.
  • Pooling – controls if your connection pooling on or off. Default as you may’ve guessed is true. Read on to see when you may use Pooling=false setting.
Common Problems and Resolutions
Connection pooling problems are almost always caused by a “connection leak” – a condition where your application does not close its database connections correctly and consistently. When you “leak” connections, they remain open until the garbage collector (GC) closes them for you by calling their Dispose method. Unlike old ADO, ADO.NET requires you to manually close your database connections as soon as you’re done with them. If you think of relying on connection objects to go out of scope, think again. It may take hours until GC collects them. In the mean time your app may be dead in the water, greeting your users or support personnel with something like this:


Exception: System.InvalidOperationException Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Source: System.Data at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() …

Exception: System.InvalidOperationException
Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Source: System.Data


at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()

 
Closing your connections
When you intend to close your database connection, you want to make sure that you are really closing it. The following code looks fine yet causes a connection leak:

SqlConnection conn = new SqlConnection(myConnectionString);
conn.Open();
doSomething();
conn.Close();
If doSomething() throws an exception – conn will never get explicitly closed. Here is how this can be corrected:
SqlConnection conn = new SqlConnection(myConnectionString);
try
{
conn.Open();
doSomething(conn);
}
finally
{
conn.Close();
}

or


using (SqlConnection conn = new SqlConnection(myConnectionString))
{
conn.Open();
doSomething(conn);
}

Did you notice that in the first example we called conn.Close() explicitly while in the second one we make the compiler generate an (implicit) call to conn.Dispose() immediately following the using block? The C# using block guarantees that the Dispose method is called on the subject of the using clause immediately after the block ends. Close and Dispose methods of Connection object are equivalent. Neither one gives you any specific advantages over the other.

When returning a connection from a class method – make sure you cache it locally and call its Close method. The following code will leak a connection:

OleDbCommand cmd new OleDbCommand(myUpdateQuery, getConnection());

intres = cmd.ExecuteNonQuery();

getConnection().Close(); // The connection returned from the first call to getConnection() is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.

If you use SqlDataReader, OleDbDataReader, etc., close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them.

Last but not the least, never Close or Dispose your connection or any other managed object in the class destructor or your Finalize method. This not only has no value in closing your connections but also interferes with the garbage collector and may cause errors. For more information see http://msdn.microsoft.com/library/en-us/cpguide/html/cpconprogrammingessentialsforgarbagecollection.asp.

 
Testing your changes
The only way to know the effect of your changes on connection pooling behavior is to load-test your application. If you have existing unit tests – use them. Running your unit tests repeatedly in a loop may create a fair bit of stress on application. If you don’t, use the Web load testing tool. There are plenty of commercial load testing tools on the market. If you prefer freeware, consider OpenSTA available at www.opensta.org. All you need to setup your load test is to install the tool, bring up your Web application and click your way through. OpenSTA will record your HTTP requests into test scenarios that you can run as part of your load test.Knowing that your application crashes under the load doesn’t often help to locate the problem. If the app crashes fairly quickly, all you may need to do is run several load tests – one for each module and see which one has a problem. However, if it takes hours to crash you will have to take a closer look.
Monitoring connection pooling behavior
Most of the time you just need to know if your application manages to stay within the size of its connection pool. If the load doesn’t change, but the number of connections constantly creep even after the initial “warm-up” period, you are most likely dealing with a connection leak. The easiest way to monitor the number of database connections is by using the Performance Monitor available under Administrative tools on most Windows installations. If you are running SQL Server, add SQL Server General Statistics -> User Connections performance counter (The counter is available on the SQL Server machine so you may need to put its name or IP address into the Select Counters From Computer box). The other way to monitor the number of database connections is by querying your DBMS. For example, on SQL Server run:

EXEC SP_WHO

Or on Oracle, run:

SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL

.NET CLR Data performance counters

In documentation you may run into .Net CLR Data performance counters. They are great if you know what they can and cannot do. Keep in mind that they do not always reset properly. The following KB article sheds some light on the problem but in my opinion does not cover all the issues: http://support.microsoft.com/default.aspx?scid=kb;en-us;314429. Another thing to keep in mind is that IIS unloads app domains under stress so don’t be surprised when your number of database connections has dropped to zero while your min pool size is five!

 
Conclusion
In this article you’ve learned that the most common cause of connection pooling issues is database connections that are left open or not closed properly. You’ve learned that when you type “conn.Close()”, you almost always want to put that in the “Finally” block. You also learned not to interfere with the class destructor unless you use unmanaged resources. You’ve learned how to monitor your connection pool and diagnose a potential problem. You also learned how to keep a system with a connection leak in production if you really have to, until the problem is resolved. I hope this article has helped you resolve your connection pooling issue. However, there is more to connection pooling that is not covered in this article. Check out Bill Vaughn’s “Swimming in the .NET connection pool” at http://www.winnetmag.com/Article/ArticleID/38356/38356.html.
 
About the Author
Dmitri Khanine is senior web developer and architect working for a major Canadian Bank. His 10+ years of experience are mostly in backend and middle tier development of enterprise Web applications on Microsoft as well as J2EE platforms. Industry experience includes Banking, Finance, Automotive and software consulting. Dmitri’s areas of interest and expertise include rapid enterprise application development, MVC frameworks and code generation. Dmitri can be contacted at Khanine@hotmail.com.
 

SQL SQL Server

KILL (T-SQL)

Terminates a user process based on the system process ID (SPID). If the specified SPID has a lot of work to undo, the KILL may take some time to complete.
Syntax

KILL {spid}
Arguments

spid
Is the SPID of the process to terminate. The SPID value is a unique integer (smallint) assigned to each user connection when the connection is made, but the assignment is not permanent.

Remarks

KILL is commonly used to terminate a process that is blocking other important processes with locks, or to terminate a process that is executing a query that is using necessary system resources. System processes and processes running an extended stored procedure cannot be terminated.

Use KILL very carefully, especially when critical processes are running. You cannot kill your own process. Other processes not to kill are:

* AWAITING COMMAND
* CHECKPOINT SLEEP
* LAZY WRITER
* LOCK MONITOR
* SELECT
* SIGNAL HANDLER

Execute sp_who to get a report on valid SPID values. Use @@SPID to display the SPID value for the current session.
Permissions

KILL permissions default to the members of the sysadmin fixed database role, and are not transferable.
Examples

This example shows how to terminate SPID 10.

KILL 10

SQL SQL Server

sp_who

Provides information about current Microsoft® SQL Server™ users and processes. The information returned can be filtered to return only those processes that are not idle.

Syntax

sp_who [[@login_name =] login]

Arguments

[@login_name =] login

Is a user login name on SQL Server. login is sysname, with a default of NULL. If no name is specified, the procedure reports all active users of SQL Server. login can also be a specific process identification number (SPID). To return information on active processes, specify ACTIVE. ACTIVE excludes from the report processes that are waiting for the next command from the user.

Return Code Values

0 (success) or 1 (failure)

Result Sets

sp_who returns a result set with the following information.

MSDN LINK

read more »

SQL SQL Server

CAST and CONVERT (Transact-SQL)

Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )


 read more »
SQL

Send a Message To Users Who Access a Specified Database

Monitor the free disk space on your SQL Server and send an alert via email or net send if the available space on any of the drives SQL Server resides on is lower than the specified limit. The script could also be scheduled as a job to provide 24/7 monitoring. Supports SQL Server 7 or 2000.

Author: ShivaArtigo Completo