Common steps for SQL Server exception handling _mssql

A common problem with SQL Server is the SQL problem, which is mostly due to high CPU and blocking.

One, the problem of high CPU

1, query system Dynamic View query execution time of the SQL statement

With Processcte (blocked) as
(
  Select a spid from sys.sysprocesses WHERE cpu>500
)
SELECT DISTINCT a.*< C7/>from (
   SELECT text,aa.* from sys.sysprocesses AA
    CROSS APPLY sys.dm_exec_sql_text (aa.sql_handle)
    ) a< C11/>join processcte bucte with (NOLOCK) on Bucte.blocked=a.spid--where loginame
 = ' tcscenery ' ORDER by
 A.cpu

Second, the problem of obstruction

1, query system Dynamic View query blocked SQL statements

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, the use of the system's own stored procedures

Sp_who2 and Sp_lock and the use of 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 (SPID that blocks the process)
dbname (database name that the process is using)
CMD (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 session number Active)
CPUTime (total CPU time consumed by the process)
Diskio (total number of times the process is read to disk)
Lastbatch (the last time a customer calls a stored procedure or executes a query)
ProgramName (the name of the application used to initialize the connection, or the host name)

The following is the use 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
EXEC sp_who;
Go

B. Listing processes for specific users

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

Use master;
Go
EXEC sp_who ' janetl ';
Go

C. Show all active processes

Use master;
Go
EXEC sp_who ' active ';
Go

D. Show specific processes for session ID identification

Use master;
Go
EXEC sp_who '--specifies the process_id;
Go

Sp_lock usage Instructions

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

The database engine session ID number from the sys.dm_exec_sessions for which the user wants to lock information. The session ID1 data type is int and the default value is NULL. Execute sp_who to obtain process information about the session. If no session ID1 is 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 the session ID1, and the user needs information about it. The session ID2 data type is int and the default value is NULL.

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

Column Name	Data type	Description
spid	smallint	The database engine session ID number of the process requesting the lock.
dbid	smallint	The identification number of the database that retains 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 the related database to identify the object. A value of 99 o'clock is a special case that represents a lock on one of the system pages used to record page allocations in the database.
Indid	smallint	The identification number of the index that holds the lock.
Type	NCHAR (4)	Type of Lock:
		RID = a lock of a single row in a table, identified by a row identifier (RID).
		Key = The lock in the index that protects a series of keys in a serializable transaction.
		PAG = lock on a data page or index page.
		EXT = a lock on a zone.
		TAB = Lock the entire table (including all data and indexes).
		DB = Lock of database.
		FIL = Lock of database file.
		App = Lock of the specified application resource.
		MD = lock of metadata or directory information.
		HBT = lock for heap or B-tree index. This information is incomplete in SQL Server.
		AU = lock of allocation unit. This information is incomplete 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: a fileid:pagenumber:rid identifier in which the Fileid identifies the file that contains the page, PageNumber identifies the page that contains the row, and the RID identifies the specific row on the page. Fileid matches the file_id column in the Sys.database_files catalog view.
		KEY: 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 of the first page in the identity area. The number is formatted as Fileid:pagenumber.
		TAB: No information is provided because the table is already identified in the objid column.
		DB: No information was 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 locked application resource. The format is the first 2 to 16 characters >< hash operation 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 was provided. Use the sys.dm_tran_locks dynamic management view instead.
		AU: no information provided. Use the sys.dm_tran_locks dynamic management view instead.
Mode	nvarchar (8)	The requested lock mode. Can be:
		NULL = does not grant access to the resource. As a placeholder.
		Sch-s = schema stability. Ensure 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 sessions are referencing the indicated object.
		S = shared. Grant shared access to the resource for the session that holds the lock.
		U = update. Indicates an update lock obtained on a resource that may eventually be updated. Used to prevent a common deadlock that occurs when multiple sessions lock resources to update resources later.
		X = Exclusive. Grant exclusive access to a resource to a session that holds a lock.
		is = intent sharing. Indicates that an S lock is intended to be placed on a subordinate resource in the lock hierarchy.
		IU = intention update. Indicates that a U lock is intentionally placed 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 resources that are intended to obtain update locks on subordinate resources in the lock hierarchy.
		SIX = Shared Intent exclusive. Indicates shared access to resources that intentionally acquire exclusive locks on subordinate resources in the lock hierarchy.
		UIX = Update Intent exclusive. Indicates an update lock held on a resource that intends to acquire 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 a serializable update scan.
		Rangei_n = Insert key range and Null resource lock. Used to test a range before inserting a new key into the index.
		rangei_s = key range conversion lock. Created by overlapping 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 overlap 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 used when updating a key in the range.
State	nvarchar (5)	Request status of the lock:
		CNVRT: The lock is being converted from another mode, but the transformation is blocked by another process that holds the lock (schema conflict).
		GRANT: The lock was acquired.
		Wait: The lock is blocked by a process with another holding lock (schema conflict).

DBCC InputBuffer

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

Grammar

DBCC InputBuffer (SPID)

Parameters

spid

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

Result set

The DBCC InputBuffer returns a rowset containing 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 language or no event EventType, only the first 255 characters of the event are displayed.

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

EventType Parameters EventInfo
-------------- ---------- ---------------------
Language Event 0 DBCC inputbuffer (11)
(1 row (s) affected)