First, open SQL Server Activity Monitor, you should wait some time before data is loaded
The Activity Monitor is available in SQL Server 2008 Management Studio
is a great tool which can be used to get a quick overview of SQL Server
2008 system performance. The Activity Monitor tool in the previous
version of SQL Server used to display information related to processes,
lock by objects and locks by process. There are many enhancements in
Activity Monitor in SQL Server 2008 like a graphical display of
processor time, waiting tasks, database I/O's, batch requests,
processes, resource waits, data file I/O's
and also information about the most expensive queries.
To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission
Plz Show Recent Expensive Queries( it Help for SQL Time Out Error Solve)
One of the first lines of defense in determining why a query is stuck is
to use sp_who2. It will shows all the sessions that are currently
established in the database. These are denoted as SPID’s, or server
process Id’s.
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.
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 another process. In
turn, the process that is being blocked, blocks others. This occurs down
a chain. It can be a messy situation. In order to rectify, you may have
to kill the lead blocker. If it happens often, you will want to
research why this particular process is blocking. So, before you kill
any process, find out what statement it is running first. To do this,
execute DBCC INPUTBUFFER
Once you find the blocker, you may need to kill it. To do so use the
kill command (replace the number below with the blocking SPID)
IMPORTANT: before the kill, keep a copy of the SQL statement being run for later investigation
sp_who2 does provide limited information regarding slowdowns.
For more information use the following script that shows the SQL
statement being run, only sessions that have a current executing
request, reads and writes for the current command, along with the number
of reads and writes for the entire SPID and the protocol being used
(TCP, NamedPipes, or Shared Memory)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT SPID = er.session_id ,
BlkBy = er.blocking_session_id ,
ElapsedMS = er.total_elapsed_time ,
CPU = er.cpu_time ,
IOReads = er.logical_reads + er.reads ,
IOWrites = er.writes ,
Executions = ec.execution_count ,
CommandType = er.command ,
ObjectName = OBJECT_SCHEMA_NAME(qt.objectid, dbid) + '.'
+ OBJECT_NAME(qt.objectid, qt.dbid) ,
SQLStatement = SUBSTRING(qt.text, er.statement_start_offset / 2,
( CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
* 2
ELSE er.statement_end_offset
END - er.statement_start_offset ) / 2) ,
Status = ses.status ,
[Login] = ses.login_name ,
HOST = ses.host_name ,
DBName = DB_NAME(er.database_id) ,
LastWaitType = er.last_wait_type ,
StartTime = er.start_time ,
Protocol = con.net_transport ,
transaction_isolation = CASE ses.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END ,
ConnectionWrites = con.num_writes ,
ConnectionReads = con.num_reads ,
ClientAddress = con.client_net_address ,
Authentication = con.auth_scheme
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY ( SELECT execution_count = MAX(cp.usecounts)
FROM sys.dm_exec_cached_plans cp
WHERE cp.plan_handle = er.plan_handle
) ec
ORDER BY er.blocking_session_id DESC ,
er.logical_reads + er.reads DESC ,
er.session_id
The following script will show the blocking processes (lead blocker)
SELECT spid ,
sp.status ,
loginame = SUBSTRING(loginame, 1, 12) ,
hostname = SUBSTRING(hostname, 1, 12) ,
blk = CONVERT(CHAR(3), blocked) ,
open_tran ,
dbname = SUBSTRING(DB_NAME(sp.dbid), 1, 10) ,
cmd ,
waittype ,
waittime ,
last_batch ,
SQLStatement = SUBSTRING(qt.text, er.statement_start_offset / 2,
( CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
* 2
ELSE er.statement_end_offset
END - er.statement_start_offset ) / 2)
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN ( SELECT blocked
FROM master.dbo.sysprocesses )
AND blocked = 0
The following script will find most expensive queries (remark the needed ORDER BY)
SELECT DISTINCT TOP 10
t.TEXT QueryName ,
s.execution_count AS ExecutionCount ,
s.max_elapsed_time AS MaxElapsedTime ,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime ,
s.creation_time AS LogCreatedOn ,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
ORDER BY s.max_elapsed_time DESC
LETS CHECK, do the following steps and run the above scripts when needed
|
-- open new query window (SESSION A) and run the following 3 lines
USE [AdventureWorks]
BEGIN TRANSACTION
UPDATE Sales.SalesOrderDetail
SET [UnitPrice] = 1
-- this update takes ~30sec
-- while the above update in progress, open a new query window (SESSION B) and run sp_who2
-- open another query window (SESSION C) and run the following line, it will be blocked by SESSION A because the transaction there is not committed yet
UPDATE Sales.SalesOrderDetail
SET [UnitPrice] = 2
-- while the above update in progress, go to SESSION B query window and run again sp_who2, you will see the blocking
-- return to SESSION A query window and rollback the transaction. This will free the blocking in SESSION C (after the update finishes, takes ~30sec)
ROLLBACK |
|
If you find this solution useful,. Please Like and comment of page. Thanks!
