Get currently running process in SQL Server

Posted by on April 7, 2015 No comments

SELECT
session_id,status,
command,sql_handle,database_id
,(SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_requests r
WHERE session_id >= 51

select s.session_id, s.login_name, s.host_name, s.status,
s.program_name, s.cpu_time, s.last_request_start_time,
(SELECT text FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) AS query_text
from sys.dm_exec_sessions s, sys.dm_exec_connections c
where s.session_id = c.session_id and
s.session_id > 50
order by s.last_request_start_time desc

SELECT SERVERNAME SvrName, s.[Status], s.SPID, s.CPU, s.Physical_IO, s.WaitTime, s.Blocked,
Text LastCmd, SUBSTRING(s.HostName, 1, 36) HostName, SUBSTRING(s.Program_Name, 1, 100) PrgName,
SUBSTRING(s.nt_username, 1, 50) NtUserName, s.Login_Time, s.Last_Batch, s.Open_Tran
FROM master.dbo.sysprocesses s
CROSS APPLY ::fn_get_sql(s.sql_handle)
WHERE (blocked > 0
OR spid IN (SELECT blocked FROM master.dbo.sysprocesses (NOLOCK) WHERE blocked > 0)
OR open_tran > 0)
AND SPID <> SPID
AND Blocked > 0

How do you clear DMVs information

Posted by on April 7, 2015 No comments

2 ways:

    Restart SQL server
    DBCC SQLPERF (‘sys.dm_db_index_usage_stats’, clear)

How many cores are visible to SQL Server?

Posted by on April 7, 2015 No comments

SELECT COUNT(DISTINCT scheduler_id) AS TotalVisibleCores
FROM sys.dm_os_schedulers
WHERE scheduler_id < 1048576 — >= 1048576 = hidden

DMVs with SQLOS information

Posted by on April 7, 2015 No comments

select cpu_count/hyperthread_ratio AS sockets from sys.dm_os_sys_info — How many sockets does my machine have?
select hyperthread_ratio AS cores_or_logical_cpus_per_socket from sys.dm_os_sys_info — How many either cores or logical CPU share the same socket?
select physical_memory_in_bytes/1024 AS physical_memory_in_kb from sys.dm_os_sys_info — How much physical memory my machine has?
select max_workers_count from sys.dm_os_sys_info — How many threads/workers SQL Server would use if the default value in sp_configure for max worker threads is zero:
select bpool_visible from sys.dm_os_sys_info — What is a size of AWE window or what is the max amount of memory right now that can be used by QO, QE and Caches simultaneously when SQL Server running in AWE mode?

Why schemas are after all needed in SQL Server?

Posted by on April 7, 2015 No comments

To logically group tables, proc, views together, all employee related objects in the employee schema etc.,
possibility to assign permissions to a schema makes it worth it from an administration perspective.

create schemas like landing, staging depending on the specific domain
like CDC schema, it will NOT be confusing with real table names
similar to c# namespaces

in 2k, if joe created jobs it will be called as joe.jobs
what if joeleaves company?
then you will have to change the ownership from joeto dbo.jobs
huge administrative effort……so please be mindful