This article shows the usage of sp_who2. To diagnose system slowdowns, see (Troubleshooting SQL Slowness). One of the first lines of defense in determining the causes of database slowdowns is to use sp_who2. sp_who2 shows all the sessions that are currently established in the database. These are denoted as SPID‘s, or Server process Id’s. Running sp_who2 is easy, all that is required is to type sp_who2 and execute it, however it’s a good idea to call it with EXEC. [cc lang=”sql”] EXEC sp_who2 [/cc] The first 50 results are system SPIDs. Generally these do not effect slowdowns of the system. These system events include the Checkpoint writer, Log writers and task schedulers. User processes are SPID numbers 50 and over. In diagnosing slowdowns it is these SPIDs that are the potential resource hogs. sp_who2 also takes a optional parameter of a SPID. If a spid is passed, then the results of sp_who2 only show the row or rows of the executing SPID. [cc lang=”sql”] EXEC sp_who2 243 [/cc] There are four main things to look for when when diagnosing slowdowns. Blocking High CPU usage High IO usage Multiple entries for the same SPID representing parallelism. When a number is shown in the column named BlkBy, this represents the SPID that is currently stopping the SPID in the row shown. Sometimes many rows will show SPID numbers in the BlkBy column. This is because there is a chain of blockers. The way this occurs usually starts with one “lead” blocker blocking […]
Continue reading ...
sp_who is a system stored procedure designed to return back information regarding the current sessions in the database. These sessions are commonly referred to as SPIDS (Server Process ID’s). While sp_who is sometimes used, it’s sister procedure sp_who2 is used far more often. This is because sp_who2 returns back more information than sp_who. Let’s look at a comparison. sp_who, and sp_who2 both have one optional parameter which is the session id. If you do not pass a session ID, then all sessions are returned. In order to execute, simply copy the text below. [cc lang=”sql”] — execute sp_who EXEC sp_who — execute sp_who2 EXEC sp_who2 [/cc] From these results we see that sp_who contains the DiskIO, CPUTime and ProgramName. There is essentially no reason why you would want to execute sp_who over sp_who2.
Continue reading ...
Sp_help is a system stored procedure that is similar to a ‘help’ command that is issued at a command prompt. This procedure is extremely useful in the database because almost any discreet object can be passed as a parameter in order to return back detailed information regarding the object. It should be noted that it will only return information for objects that are able to be referenced within the database you are currently in. Being that there are so many different result sets returned for sp_help, we will only cover the most common usages here, starting with the obvious, executing sp_help with no parameters. All Database Object Information When executing sp_help with no parameters, all major objects within the database are enumerated (except triggers and indexes). Let’s execute the procedure and see the result set. [cc lang=”sql”] EXEC sp_help [/cc] From this, we see two different result sets appear. The first result set contains a list of all major objects. These include: views tables constraints triggers functions stored procedures service broker queues and probably some other objects I’m missing.. The second result set is a listing of the user defined data types that are in the system. Returning table information Probably my most common use of sp_help is to return back all information regarding a table. This includes columns, datatypes, and index information (including the index columns). In order to retrieve this information, we simply pass in the table name as a parameter. [cc lang=”sql”] EXEC sp_help ‘SalesLT.Address’ [/cc] From […]
Continue reading ...
Real world questions asked to real interviewees. These interview questions cover the different areas of SQL Development, data flow, and Database Administration.
Continue reading ...
