Common steps for handling SQL Server exceptions

processing SQL Server Common steps for exceptions

SQL Server common problems are mainly caused by SQL problems, the main is the CPU is too high and blocking.

First, the problem of high CPU

1. Query system Dynamic View query execution time long SQL statement

With Processcte (blocked) as

(

SELECT spid from sys.sysprocesses WHERE cpu>500

)

SELECT distinct a.*

From (

SELECT text,aa.* from Sys.sysprocesses AA

Cross APPLY sys.dm_exec_sql_text (Aa.sql_handle)

) A

JOIN processcte bucte with (NOLOCK)

On Bucte.blocked=a.spid

--where loginame = ' tcscenery '

ORDER by A.cpu

Second, blocking problems

1. Query system Dynamic View query blocked SQL statement

With Processcte (blocked) as

(

SELECT blocked from sys.sysprocesses WHERE blocked>0

Union

SELECT blocked from sys.sysprocesses WHERE blocked>0

)

SELECT distinct a.*

From (

SELECT text,aa.* from Sys.sysprocesses AA

Cross APPLY sys.dm_exec_sql_text (Aa.sql_handle)

) A

JOIN processcte bucte with (NOLOCK)

On Bucte.blocked=a.spid

ORDER by a.blocked

2. Using the system's own stored procedures

Sp_who2 and sp_lock, as well as using DBCC INPUTBUFFER (SPID), can also be used to analyze blocking

Sp_who can return the following information: (optional parameter loginname, or active represents the number of active sessions)
Spid (System process ID)
Status (Process state)
Loginame (User login name)
Hostname (User host name)
Blk (the SPID that blocks the process)
DBName (the name of the database the process is using)
CMD (the type of command currently being executed)

SP_WHO2 Displays the following information in addition to the output information shown above sp_who: (optional parameter loginname, or active on behalf of active sessions)
CPUTime (total CPU time consumed by the process)
Diskio (total number of process-to-disk reads)
Lastbatch (the time the customer last called the stored procedure or executed the query)
ProgramName (the name of the application used to initialize the connection, or host name)

Here is the usage of sp_who, sp_who2 similar to this

A. List all current processes

The following example uses sp_who with no parameters to report all current users.

Use master;

GO

execsp_who;

GO

B. Listing processes for a specific user

The following example shows how to view information about a single current user through the login name.

Use master;

GO

EXEC sp_who ' janetl ';

GO

C. Displaying all active processes

Use master;

GO

EXEC sp_who ' active ';

GO

D. Displaying the specific process identified by the session ID

Use master;

GO

EXEC sp_who '--specifies the process_id;

GO

Sp_lock Usage Notes

sp_lock [[@spid1 =] ' session ID1 '] [, [@spid2 =] ' session ID2 '] [;]

[@spid1 =] ' Session ID1 '

The database engine session ID number of the sys.dm_exec_sessions from which the user wants to lock information. The sessionID1 data type is int and the default value is NULL. Execute sp_who to get process information about the session. If session ID1 is not specified, information about all locks is displayed.

[@spid2 =] ' Session ID2 '

Another database engine session ID number from sys.dm_exec_sessions, which may have a lock at the same time as session ID1, and the user needs information about it. The sessionID2 data type is int and the default value is NULL.

In the sp_lock result set, each lock that is held by the session specified by the @spid1 and the @spid2 parameter corresponds to one row. If neither @spid1 is specified and no @spid2is specified, the result set reports the locks for all sessions that are currently active in the instance of the database engine.

Column Name	Data type	Description
spid	smallint	The database engine session ID number of the process that requested the lock.
dbid	smallint	The identification number of the database that holds the lock. You can use the db_name () function to identify the database.
ObjId	Int	The identification number of the object holding the lock. You can use the object_name () function in a related database to identify an object. A value of 99 o'clock is a special case that represents a lock that is used to record one of the system pages in the database for page allocation.
Indid	smallint	The identification number of the index holding the lock.
Type	NCHAR (4)	Type of Lock:
		RID = The lock of a single row in the table, identified by a row identifier (RID).
		Key = The lock within the index that protects a series of keys in a serial transaction.
		PAG = a lock on a data page or index page.
		EXT = lock on a zone.
		TAB = The lock for the entire table, including all data and indexes.
		db = The lock for the database.
		FIL = The lock of the database file.
		App = The lock for the specified application resource.
		MD = lock for metadata or directory information.
		HBT = lock for heap or B-tree index. This information is not complete in SQL Server.
		AU = The lock of the allocation unit. This information is not complete in SQL Server.
Resource	NCHAR (32)	Identifies the value of the locked resource. The format of the value depends on the type of resource identified by the type column:
		Type Value:Resource value
		RID: The format of the Fileid:pagenumber:rid identifier, where Fileid identifies the file that contains the page, PageNumber identifies the page that contains the row, and the RID identifies a specific row on the page. The Fileid matches the file_id column in the sys.database_files catalog view.
		KEY: The hexadecimal number used internally by the database engine.
		PAG: A number formatted as Fileid:pagenumber, where Fileid identifies the file that contains the page, pagenumber the identity page.
		EXT: The number that identifies the first page in the area. The format of the number is fileid:pagenumber.
		TAB: No information is provided because the table is already identified in the ObjId column.
		DB: No information is provided because the database has been identified in the dbid column.
		FIL: The identifier of the file that matches the file_id column in the sys.database_files catalog view.
		App: Unique identifier of the application resource being locked. Format is the first 2 to 16 characters >< hash value > of the dbprincipleid:< resource string.
		MD: Varies with resource type. For more information, see the description of the Resource_description column in sys.dm_tran_locks (Transact-SQL).
		HBT: no information provided. Use sys.dm_tran_locks dynamic management view instead.
		AU: no information provided. Use sys.dm_tran_locks dynamic management view instead.
Mode	nvarchar (8)	The requested lock mode. Can be:
		NULL = Do not grant access to the resource. Used as a placeholder.
		Sch-s = schema stability. Ensures that the schema element is not deleted when any session holds a schema stability lock on a schema element, such as a table or index.
		sch-m = Schema modification. Must be held by any session for which you want to change the specified resource schema. Ensure that no other session is referencing the indicated object.
		S = shared. Grants the session holding the lock shared access to the resource.
		U = update. Indicates the update lock acquired for the resource that may eventually be updated. Used to prevent a common deadlock that occurs when multiple sessions lock resources to update resources at a later time.
		X = Exclusive. Grants the session holding the lock exclusive access to the resource.
		is = intent to share. Indicates that the S lock is intentionally placed on a subordinate resource in the lock hierarchy.
		IU = Intent Update. Indicates that you intentionally place a U lock on a subordinate resource in the lock hierarchy.
		IX = Intent Exclusive. Indicates that an X lock is intentionally placed on a subordinate resource in the lock hierarchy.
		SIU = Shared Intent update. Indicates shared access to a resource that intentionally acquires an update lock on a subordinate resource in the lock hierarchy.
		SIX = Shared Intent exclusive. Indicates shared access to a resource that intentionally acquires an exclusive lock on a subordinate resource in the lock hierarchy.
		UIX = Update Intent exclusive. Indicates an update lock held on a resource that intentionally acquires an exclusive lock on a subordinate resource in the lock hierarchy.
		BU = Bulk Update. For bulk operations.
		ranges_s = Shared key range and shared resource lock. Indicates a serializable range scan.
		Ranges_u = Shared key range and update resource lock. Indicates that a serial update scan is available.
		Rangei_n = Insert key range and Null resource lock. Used to test the scope before inserting a new key into the index.
		rangei_s = key range conversion lock. Created by the overlap of rangei_n and S locks.
		Rangei_u = key-range conversion lock created by overlap of rangei_n and U-locks.
		rangei_x = key-range conversion lock created by overlapping of rangei_n and X-locks.
		rangex_s = key-range conversion lock created by overlap of rangei_n and ranges_s locks.
		Rangex_u = key-range conversion lock created by overlap of rangei_n and Ranges_u locks.
		rangex_x = Exclusive key range and exclusive resource lock. This is the conversion lock that is used when the keys in the range are updated.
State	nvarchar (5)	Request status of the lock:
		CNVRT: The lock is transitioning from another mode, but the conversion is blocked by another process that holds the lock (the pattern is conflicting).
		GRANT: The lock has been acquired.
		WAIT: The lock is blocked by another process that holds the lock (mode conflict).

DBCC InputBuffer

Displays the last statement sent from the client to Microsoft®sql Server™.

Grammar

DBCC InputBuffer (SPID)

Parameters

spid

is the user-connected system process ID (SPID) that is displayed in the output of the sp_who system stored procedure.

Result set

DBCC InputBuffer Returns a rowset that contains the following.

Column Name	Data type	Describe
EventType	nvarchar (30)	Event type, for example: RPC, language, or no event.
Parameters	Int	0 = text 1-n = parameter
EventInfo	nvarchar (255)	The Eventtype,eventinfo for RPC contains only the procedure name. For languages or eventtype with no events, only the first 255 characters of the event are displayed.

For example, when the last event in the buffer is Dbccinputbuffer (11), DBCC InputBuffer returns the following result set.

EventType Parameters EventInfo

------------------------ ---------------------

Language Event 0 DBCC inputbuffer (11)

(1 row (s) affected)

The above information about sp_who, Sp_who2, sp_lock and Dbccinputbuffer can be obtained from the MSDN website http://msdn.microsoft.com/en-us/library/ms187961.aspx.

Common steps for handling SQL Server exceptions