SP_WHO3 – Running Queries (Toolbox)

SP_WHO3 – Running Queries (Toolbox)

Display all running queries

Source:
http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3

Code:

SELECT
     SPID                  = er.session_id
    ,BlkBy                 = 
                                CASE WHEN lb.lead_blocker = 1 
                                 THEN -1 
                                 ELSE er.blocking_session_id 
                                END
    ,ElapsedMS             = er.total_elapsed_time
    ,CPU                   = er.cpu_time
    ,IOReads               = er.logical_reads + er.reads
    ,IOWrites              = er.writes
    ,Executions            = ec.execution_count
    ,CommandType           = er.command
    ,LastWaitType          = er.last_wait_type
    ,ObjectName            = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
    ,SQLStatement          = qt.text
    ,STATUS                = ses.STATUS
    ,[Login]               = ses.login_name
    ,Host                  = ses.host_name
    ,DBName                = DB_Name(er.database_id)
    ,StartTime             = er.start_time
    ,Protocol              = con.net_transport
    ,transaction_isolation =
                                CASE ses.transaction_isolation_level
                                    WHEN 0 THEN 'Unspecified'
                                    WHEN 1 THEN 'Read Uncommitted'
                                    WHEN 2 THEN 'Read Committed'
                                    WHEN 3 THEN 'Repeatable'
                                    WHEN 4 THEN 'Serializable'
                                    WHEN 5 THEN 'Snapshot'
                                END
    ,ConnectionWrites      = con.num_writes
    ,ConnectionReads       = con.num_reads
    ,ClientAddress         = con.client_net_address
    ,Authentication        = con.auth_scheme
    ,DatetimeSnapshot      = GETDATE()
FROM      sys.dm_exec_requests                  er
LEFT JOIN sys.dm_exec_sessions                  ses  ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections               con  ON con.session_id = ses.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) qt
OUTER APPLY
(
    SELECT execution_count = MAX(cp.usecounts)
      FROM sys.dm_exec_cached_plans cp
     WHERE cp.plan_handle = er.plan_handle
)                                               ec
OUTER APPLY 
( 
    SELECT lead_blocker = 1 
      FROM master.dbo.sysprocesses sp 
     WHERE sp.spid IN 
                    (
                        SELECT blocked 
                          FROM master.dbo.sysprocesses WITH (NOLOCK) 
                         WHERE blocked != 0
                    ) 
       AND sp.blocked = 0 
       AND sp.spid = er.session_id
)                                               lb 

WHERE 
        er.sql_handle IS NOT NULL
    AND er.session_id != @@SPID
ORDER BY
    er.blocking_session_id DESC,
    er.logical_reads + er.reads DESC,
    er.session_id

Another query is available here: https://github.com/amachanic/sp_whoisactive/

More info: https://www.brentozar.com/archive/2019/07/sp_whoisactive-is-now-open-source/

Learn DAX from examples: Lazy DAX

Learn DAX from examples: Lazy DAX

Concrete cases in DAX

Working with Power BI (or SSAS Tabular) does not require much effort. It’s quite intuitive and easy to set up.
However, to fully enjoy it, it is recommended to take the time to learn DAX.

Create a measure, in the formula bar, is done through an expression written in this language.
Very powerful, the DAX contains some functions that will give all the interest to your beautiful data model.

It would be a shame to see him as a cherry on the cake! It would be rather a cherry cake over the cake!

Simple syntax, very powerful function in a few lines, but it can be complicated.

You will find here support with some concrete cases, from the simplest to the most complicated.

 

  • A Power BI Desktop file
  • A PowerPoint file with multiple explanations. You can use it to provide training or initiation to the DAX.

If you have new exciting measures, do not hesitate to send them (via LinkedIn), I will be happy to add them in the Power BI file.

 

My colleague Mathieu Ricour and myself are happy to share you another way to learn DAX.

In Lazy DAX, you will found:

A Power BI Desktop file

  • It contains multiple examples based on a simple and understandable set of data
  • With multiple clear examples, you can change the expressions / scripts and directly see the result. (It will help you to understand and develop in DAX)
  • For now it covers:
    • The basics functions (COUNT, DISTINCTCOUNT, SUM, …)
    • CALCULATE function
    • Time Intelligence
    • Semi Additive measures
    • Calculated Columns
    • Rank
    • EARLIER
    • Variables
    • A concreate example: Churn Rate

A PowerPoint file

  • In this power point we provide you some explanations to learn step by step.
  • You can use it to understand the basics (maybe give you a first view of what you can do with DAX and Power BI)
  • If you have to prepare your own DAX training, it could be a starter.

 

Our files are available for download on GitHub

https://github.com/arnaudgastelblum/LazySnail

Pin It on Pinterest