April 8, 2008 at 5:23 am
hi ,
will u plz tell me that how can we see all this details from query or just tell me in which table i can go for thses details
current Date ,Session,current time ,cpu utilization,locks,disk space ,database space ,current sql run ,no.of gets,date write ,date read
i have query to see detail of database size getdate but i dont know how to see other details
and i have query to see all locks as well but i dont know to how to see other details
use master
GO
select getdate () as Date,@@servername as servername, name ,size*8/1024 as size_MB
from sys.master_files;
thaxx
April 8, 2008 at 5:36 am
Hi
GETDATE() is a function and even when u say select getdate() it will return the current datetime value.
Check out the DynamicManagementViews (DMV) in BOL for all the info that u need. These views are categorised in BOL and its easy to find which DMV u require.
"Keep Trying"
April 8, 2008 at 6:00 am
Hi :),
U Can See Yr Required Fields to Run Following Query.
SELECT * FROM SYS.dm_exec_sessions
Regards,
Kiruba sankar.S (MCTS)
April 8, 2008 at 7:30 am
thaxx ,
but i cant find my result actully i need these results in one table .
April 9, 2008 at 5:41 am
Hi
You wont find all the things u want in one table.
check out these DMV's - sys.dm_exec_query_stats , sys.dm_exec_cached_plans, sys.dm_exec_sql_text, sys.dm_tran_locks , sys.dm_exec_sessions .
"Keep Trying"
April 9, 2008 at 6:10 am
plz check this table its wrong
select *from sys.dm_exec_sql_text
whats the right table
April 9, 2008 at 11:59 pm
Hi
sys.dm_exec_sql_text is not a table, its a function. It requires a sql_handle as a parameter to display the sql text.
This sql_handle is got from sys.dm_exec_query_stats.
"Keep Trying"
April 10, 2008 at 10:25 am
really thaxx for reply but still i dont know how to use this query which u told me .will u plz clear it to me
so thaxxx
April 11, 2008 at 12:45 am
Books online is your friend.
Here's an simplified version of a sample query from the help page on sys.dm_exec_sql_text
SELECT s2.dbid,
s1.sql_handle,
s2.text 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 AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply