Sql server how to find currently running query's

We will face an issue like a sql server memory will keep on increase, in that situation we can easily find the issue using following query. Following query will give how many time query executed and total elapsed time .

SELECT qs.execution_count, DatabaseName = DB_NAME(qp.dbid), ObjectName = OBJECT_NAME(qp.objectid,qp.dbid), StatementDefinition = SUBSTRING ( st.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1 ), query_plan, st.text, total_elapsed_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp WHERE st.encrypted = 0 ORDER BY qs.execution_count DESC

sql rows to columns comma separated values

We can convert rows to column using following querys.

 

in case we have more rows then we can use following query.( only 500000000 characters)

 

 

--Sql rows to column

--only 500000000 characters--If you want more rows,then you can use the following query

 

select SUBSTRING((SELECT(', ' + CAST(Columname AS varchar(1000)) ) FROM

tablename FOR XML PATH('')),3,500000000)

 

in case we have less rows then we can use following query.( only 8000 characters)

 

--If you want less rows , then you can use the following query--only8000 characters

DECLARE @retstr varchar(MAX) SELECT @retstr =

COALESCE(@retstr + ',','') + [Columname] FROM tablename

select @retstr

 Happy coding......