How can we see Health check ??

Question

How can we see Health check ??

jagpalsingh4

Ten Centuries

Points: 1311
More actions
April 9, 2008 at 5:38 am

#120923

Hi,
can anybody help me that how can we see sql server Health check with t-sql.Because i am trying to make script for this
Basically i need these info..
1.Current Date
2.Current time
3.Session,id
4.Session name
5.all locks
6.cpu utilization
7.how much disk space is used inside the database
8.how much space is free inside the database
9.Date read
10 Date write
11.how can we see process information with t-sql or with stored procedure
and i have solved 6 problems from this list i made queries for this .
will u plz help me
thaxxx
jagpal singh

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No need to start a second thread. People have already posted suggestions to your other one - http://www.sqlservercentral.com/Forums/Topic481421-338-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Manoj-485464 SSCrazy Points: 2717 More actions · Answer 2

Hi,

Please check this.....

sys.dm_exec_cached_plans,

sys.dm_exec_sql_text, (dm_exec_sql_text(sql_handle))

sys.dm_tran_locks ,

sys.dm_exec_sessions

--Top 5 CPU average CPU time

SELECT TOP 5

total_worker_time/execution_count AS [Avg CPU Time],

(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text

FROM sys.dm_exec_query_stats

ORDER BY [Avg CPU Time] DESC

--Batch execution statistics

SELECT

s2.dbid,

s1.sql_handle,

( SELECT TOP 1 SUBSTRING(s2.text, statement_start_offset / 2, ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS sql_statement,

execution_count, plan_generation_num, last_execution_time,

total_worker_time, last_worker_time, min_worker_time, max_worker_time,

total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads,

total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes

FROM

sys.dm_exec_query_stats s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

WHERE

( s2.objectid is null )

ORDER BY

s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset

Manoj

Manoj

MCP, MCTS (GDBA/EDA)

jagpalsingh4 Ten Centuries Points: 1311 More actions · Answer 3

Really thaxx for apply still this error comes out.can we see all details which i need with this query which u give me

Msg 102, Level 15, State 1, Line 23

Incorrect syntax near 'statement_end_offset'.