MSSQL Performance

 

 

  • 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.

 


 

 

 

 

 

 

 

 

 

Creative Commons license icon Creative Commons license icon