Shulou(Shulou.com)05/31 Report--

How to achieve exception handling in SqlServer, I believe that many inexperienced people do not know what to do. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The common problems in SQL Server are mainly caused by SQL problems, and the common problems are high CPU and blocking.

The problem of excessively high CPU

1. Query system dynamic view query sql statements with long execution time

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, the problem of congestion

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. Use the stored procedures that come with the system

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 status) loginame (user login name) hostname (user hostname) blk (blocking process SPID) dbname (database name in use) Cmd (type of command currently executing)

In addition to displaying the output of the above sp_who, sp_who2 also displays the following information: (optional parameter LoginName, or active represents the number of active sessions) CPUTime (total CPU time taken by the process) DiskIO (total number of reads by the process to disk) LastBatch (time the customer last called the stored procedure or executed the query) ProgramName (the name of the application used to initialize the connection, or hostname)

Here is the use of sp_who, and sp_who2 is similar

a. List all current processes

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

USE master;GOEXEC sp_who;GO

b. List the processes for a specific user

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

USE master;GOEXEC sp_who 'janetl';GO

c. Show all active processes

USE master;GOEXEC sp_who 'active';GO

d. Displays the specific process identified by the session ID

USE master;GOEXEC sp_who '10'-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 whose information the user wants to lock. The data type of session ID1 is int, and the default value is NULL. Execute sp_who to get 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 with session ID1 at the same time, and the user needs its information. The data type of session ID2 is int, and the default value is NULL.

In the sp_lock result set, each lock held by the session specified by the @ spid1 and @ spid2 parameters corresponds to one row. If neither @ spid1 nor @ spid2 is specified, the result set reports locks for 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 holds the lock. You can use the DB_NAME () function to identify the database.

ObjId

Int

The identification number of the object that holds the lock. You can use the OBJECT_NAME () function to identify objects in related databases. A value of 99 is a special case that indicates the lock used to record one of the system pages allocated by the page in the database.

IndId

Smallint

The identification number of the index that holds the lock.

Types

Nchar (4)

Type of lock:

RID = the lock of a single row in the table, identified by the row identifier (RID).

KEY = a lock within an index that protects a series of keys in a serializable transaction.

PAG = the lock for the data page or index page.

EXT = the lock on an area.

TAB = lock for the entire table, including all data and indexes.

DB = the lock of 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 = the lock of the heap or B-tree index. This information is incomplete in SQL Server.

AU = the lock of the 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: resources value

RID: an identifier in the format fileid:pagenumber:rid, where fileid identifies the file that contains the page, pagenumber identifies the page that contains rows, and rid identifies a specific line 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 in the format fileid:pagenumber, where fileid identifies the file that contains the page and pagenumber identifies the 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 identified in the ObjId column.

DB: no information is provided because the database is 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 DbPrincipleId:.

MD: varies according to resource type. For more information, see the description of the resource_description column in sys.dm_tran_locks (Transact-SQL).

HBT: no information is provided. Use sys.dm_tran_locks dynamic management views instead.

AU: no information is provided. Use sys.dm_tran_locks dynamic management views instead.

Pattern

Nvarchar (8)

The requested lock mode. It can be:

NULL = No access to resources is granted. Used as a placeholder.

Sch-S = architectural 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. Make sure that no other session is referencing the indicated object.

S = shared. The session that holds the lock is granted 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 a resource for later updates.

X = exclusive. The session that holds the lock is granted exclusive access to the resource.

IS = intention to share. Indicates that the S lock is intentionally placed on a dependent resource in the lock hierarchy.

IU = intended update. Indicates that the U lock is intentionally placed on a dependent resource in the lock hierarchy.

IX = intention to exclude. Indicates that the X lock is intentionally placed on a dependent resource in the lock hierarchy.

SIU = share intention updates. Indicates shared access to resources that intend to acquire update locks on dependent resources in the lock hierarchy.

SIX = shared intention exclusive. Indicates shared access to resources that intend to acquire exclusive locks on dependent resources in the lock hierarchy.

UIX = exclusive intention to update. Indicates an update lock held on a resource intended to acquire an exclusive lock on a dependent resource in the lock hierarchy.

BU = bulk update. For high-capacity operations.

RangeS_S = shared key scope and shared resource lock. Indicates that the serial range can be scanned.

RangeS_U = shared key range and update resource lock. Indicates that the scan can be updated serially.

RangeI_N = insert key range and Null resource lock. Used to test the range 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 the overlap of RangeI_N and U locks.

RangeI_X = key range conversion lock created by the overlap of RangeI_N and X locks.

RangeX_S = key range conversion lock created by the overlap of RangeI_N and RangeS_S locks.

RangeX_U = key range conversion lock created by the 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 keys in the scope.

Status

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 (the pattern conflicts).

GRANT: lock 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 connection system process ID (SPID) shown in the output of the sp_who system stored procedure.

Result set

DBCC INPUTBUFFER returns a rowset such as the following.

Column name

Data type

Description

EventType

Nvarchar (30)

Event type, such as 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 event-free EventType, only the first 255characters 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)

After reading the above, have you mastered how to implement exception handling in SqlServer? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.