- Activity Monitor
- Data Collection
Activity Monitor (SQL Server 2008 Management Studio (SSMS))
Object Explorer:
right click the SQL Server 2008 Instance -> select Activity Monitory
SQL Server 2008 Management Studio:
toolbar -> click Activity Monitor
Five sections namely:
- Overview,
- Processes,
- Resource Waits,
- Data File I/O
- Recent Expensive Queries
Data Collection
Instance -> Management -> Data Collection -> Right click "Configure ..."
1. Create or upgrade a management data warehouse
- create a new database
- Map Logins and Users (mdw_admin, mdw_reader and mdw_writer )
2. Set up data collection
3. Usage:
- Start Data Colection Set
- (Instance -> Management -> Data Collection -> System Data Collection Set -> Right click ? select "Start Data Colection Set")
Report
Summary Report
- Collect and Upload Now
( System Data Collection Set -> Right click "item" -> Collect and Upload Now )
(all the necessary tables for a specific report are created the very first time when the collected data is uploaded. )
- Reports
( Right click "Data Collection" -> Report )
Query Statistics History Report
# set
"Data Collection" -> "System Data Collection Sets" -> right click "Query Statistics" -> select "Collect and Upload Now"
# view
"Management" -> right click "Data Collection" -> "Reports" -> "Management Data Warehouse" -> "Query Statistics History".
three different sections
CPU, Duration,
Total I/O,
Physical Reads and Logical Writes
Server Activity History Report
overview of resource consumption and server activity for the SQL Server instance and for the host operating system
# set
"Management" -> "Data Collection" -> "System Data Collection Sets" -> right click "Server Activity" -> "Collect and Upload Now"
# view
"Management" -> right click "Data Collection" -> "Reports" -> "Management Data Warehouse" -> "Server Activity History".
Managing the Data Retention Period in Management Data Warehouse (MDW)
* The default Data Retention Period in MDW is 4 weeks.
Management -> "Data Collections" -> "System Data Collections Sets" -> right click ? -> Properties
+ Collection Frequency (sec)
+ Retain data for 28 days.
SQL Server process CPU Usage
SPID
SQL Server Process ID number and is assigned by SQL Server to each new connection.
It starts with one and is globally unique.
SPID 1 through 50 are reserved for system uses and are not used for any user connections.
KPID
kernel-process ID. Under SQL Server for Windows this is the thread ID number,
also known as "ID Thread," and is assigned by Windows when the thread is created.
KPID is visible by querying the KPID column of master..sysprocesses.
perfmon
- % Processor Time
- ID Thread
- Thread State
- Thread Wait Reason
Ctrl+R
SELECT spid, kpid, dbid, cpu, memusage FROM sysprocesses WHERE kpid=872
SELECT spid, kpid, status, cpu, memusage, open_tran, dbid FROM sysprocesses WHERE spid=71
exact query that is running:
DBCC INPUTBUFFER (spid)
SQL Profiler
EventNumber
- 25 Lock:Deadlock
- 59 Lock:Deadlock Chain
Usage:
1. File -> New Trace
2. Connect to Server
3. Save to file
4. Events Selection
- tick "Show all events"
+ Locks(Deadlock graph, Lock:Deadlock, Lock:Deadlock Chain)
Save Setting:
Save As -> Trace Template
Other:
- SQL:BatchCompleted
- SQL:BatchStarting
Check session
select * from sys.dm_exec_sessions where is_user_process = 1;
remote query timeout
the remote query timeout value below is not a timeout setting for incoming connections to the server.
Clients need to set the query timeout in their connection setting.
It has no effect on incoming queries issued by clients connected to SQL Server.
Remote query timeout server configuration option in SQL Server specifies how much time in seconds will
SQL Server wait for a remote query of an outgoing connection before it times out.