Sunday, April 1, 2012

sp_server_diagnostics

 

/*

Captures diagnostic data and health information about SQL Server to detect potential failures.

The procedure runs in repeat mode and sends results periodically. It can be invoked from either a regular or a DAC connection.

 

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'

 

descriptions of the five components:

 

system: Collects data from a system perspective on spinlocks, severe processing conditions,

non-yielding tasks, page faults, and CPU usage. This information is produces an overall health state recommendation.

 

resource: Collects data from a resource perspective on physical and virtual memory, buffer pools, pages,

cache and other memory objects. This information produces an overall health state recommendation.

 

query_processing: Collects data from a query-processing perspective on the worker threads, tasks, wait types,

CPU intensive sessions, and blocking tasks. This information produces an overall health state recommendation.

 

io_subsystem: Collects data on IO. In addition to diagnostic data, this component produces a

clean healthy or warning health state only for an IO subsystem.

 

events: Collects data and surfaces through the stored procedure on the errors and events of

interest recorded by the server, including details about ring buffer exceptions,

ring buffer events about memory broker, out of memory, scheduler monitor, buffer pool,

spinlocks, security, and connectivity . Events will always show 0 as the state.

 

From a failure perspective, the system, resource, and query_processing components will be leveraged for failure detection while the io_subsystem and events components will be leveraged for diagnostic purposes only.

 

 

The following table maps the components to their associated health states.

 

Components

Clean (1)

Warning (2)

Error (3)

Unknowns (0)

system

x

x

x

  

resource

x

x

x

  

query_processing

x

x

x

  

io_subsystem

x

x

    

events

      

x

The (x) in each row represents valid health states for the component. For example, io_subsystem will either show as clean or warning. It will not show the error states.

 

 

*/

 

 

exec sp_server_diagnostics

clip_image002
Posted by at No comments:

Saturday, March 31, 2012

New DMV’s in SQL 2012

-- Windows Information

-- Returns one row that displays Windows operating system version information

select * from sys.dm_os_windows_info

windows_release

windows_service_pack_level

windows_sku

os_language_version

6.1

Service Pack 1

3

1033

---------------------------------------------------------------------------------------------------------------------------

/*

Returns information about the SQL Server, Full-Text, and SQL Server Agent services

in the current instance of SQL Server.

Use this dynamic management view to report status information about these services

*/

select * from sys.dm_server_services

 

servicename

startup_type

startup_type_desc

status

status_desc

process_id

last_startup_time

service_account

filename

is_clustered

cluster_nodename

SQL Server (SQL2012RTM)

2

Automatic

4

Running

1308

2012-04-01 09:07:42.5782389 +05:30

.\xyz

"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\Binn\sqlservr.exe" -sSQL2012RTM

N

NULL

SQL Server Agent (SQL2012RTM)

2

Automatic

4

Running

5044

2012-04-01 09:08:25.6187145 +05:30

.\xyz

"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\Binn\SQLAGENT.EXE" -i SQL2012RTM

N

NULL

SQL Full-text Filter Daemon Launcher (SQL2012RTM)

3

Manual

4

Running

5392

2012-04-01 09:08:53.9649644 +05:30

NT Service\MSSQLFDLauncher$SQL2012RTM

"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\Binn\fdlauncher.exe" -s MSSQL11.SQL2012RTM

N

NULL

-----------------------------------------------------------------------------------------------------------------------------------------

/*

Returns configuration and installation information that is stored in the

Windows registry for the current instance of SQL Server. Returns one row per registry key.

Use this dynamic management view to return information such as the SQL Server services

that are available on the host machine or network configuration values for the instance of SQL Server.

*/

select * from sys.dm_server_registry

registry_key

value_name

value_data

HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$SQL2012RTM

ObjectName

.\xyz

HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$SQL2012RTM

ImagePath

"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\Binn\sqlservr.exe" -sSQL2012RTM

HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$SQL2012RTM

Start

2

HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$SQL2012RTM

ObjectName

.\srinivasma

HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$SQL2012RTM

ImagePath

"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\Binn\SQLAGENT.EXE" -i SQL2012RTM

HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$SQL2012RTM

Start

2

HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$SQL2012RTM

DependOnService

MSSQL$SQL2012RTM

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\CurrentVersion

CurrentVersion

11.0.2100.60

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\Parameters

SQLArg0

-dC:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\DATA\master.mdf

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\Parameters

SQLArg1

-eC:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\Log\ERRORLOG

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\Parameters

SQLArg2

-lC:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\DATA\mastlog.ldf

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

TcpDynamicPorts

49517

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

DisplayName

TCP/IP

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Np

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Np

PipeName

\\.\pipe\MSSQL$SQL2012RTM\sql\query

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Np

DisplayName

Named Pipes

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Sm

Enabled

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Sm

DisplayName

Shared Memory

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Via

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Via

DefaultServerPort

0:1433

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Via

ListenInfo

0:1433

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Via

DisplayName

VIA

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp

Enabled

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp

ListenOnAllIPs

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp

KeepAlive

30000

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp

DisplayName

TCP/IP

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP1

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP1

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP1

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP1

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP1

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP1

IpAddress

fe80::947e:f986:d4a7:4b5c%11

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP2

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP2

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP2

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP2

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP2

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP2

IpAddress

169.254.75.92

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP3

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP3

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP3

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP3

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP3

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP3

IpAddress

fe80::50a9:44cc:af27:364d%10

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP4

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP4

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP4

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP4

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP4

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP4

IpAddress

169.254.54.77

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP5

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP5

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP5

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP5

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP5

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP5

IpAddress

::1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP6

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP6

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP6

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP6

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP6

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP6

IpAddress

127.0.0.1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP7

Enabled

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP7

Active

1

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP7

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP7

TcpDynamicPorts

0

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP7

DisplayName

Specific IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IP7

IpAddress

fe80::100:7f:fffe%12

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

TcpPort

 

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

TcpDynamicPorts

49516

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

DisplayName

Any IP Address

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\SQLServerAgent

ErrorLoggingLevel

3

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\SQLServerAgent

JobHistoryMaxRows

1000

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\SQLServerAgent

JobHistoryMaxRowsPerJob

100

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012RTM\SQLServerAgent

WorkingDirectory

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012RTM\MSSQL\JOBS

-------------------------------------------------------------------------------------------------------------------------------------------

/*

Returns information about the operating system volume (directory) on which the

specified databases and files are stored in SQL Server 2012. Use this dynamic management function

to check the attributes of the physical disk drive or return available free space information

about the directory.

*/

select database_id, f.file_id, volume_mount_point, cast(total_bytes/1024.00/1024.00 as int) Total_MB_Drive,

cast(available_bytes/1024.00/1024.00 as int) Available_MB_Drive

from sys.database_files as f

cross apply sys.dm_os_volume_stats(db_id(f.name), f.file_id);

 

database_id

file_id

volume_mount_point

Total_MB_Drive

Available_MB_Drive

1

1

C:\

455123

325097

------------------------------------------------------------------------------------------------------------------------------------

/*

Returns one row for each memory dump file generated by the SQL Server Database Engine.

Use this dynamic management view to troubleshoot potential issues.

The dump type may be a minidump, all-thread dump, or a full dump. The files have an extension of .mdmp

*/

 

select * from sys.dm_server_memory_dumps

 

 

file_name

creation_time

size_in_bytes

 

 

 

---------------------------------------------------------------------------------------------------------------------------

Posted by at No comments:
Subscribe to: Posts (Atom)