gjshah7254
6/20/2016 - 4:54 AM

SQL Debugging

SQL Debugging

select   
      sys.objects.name, sum(reserved_page_count) * 8.0 / 1024 [SizeInMB]
from   
      sys.dm_db_partition_stats, sys.objects
where   
      sys.dm_db_partition_stats.object_id = sys.objects.object_id
 
group by sys.objects.name
order by sum(reserved_page_count) DESC
select t.name ,s.row_count from sys.tables t
join sys.dm_db_partition_stats s
ON t.object_id = s.object_id
--and t.name = '%Fact_Visits%' --for a specific table only
and s.index_id = 1
ORDER BY s.row_count DESC --for getting ordering based on sized
--FInd the number of sessions that are running
exec sp_who2

--Following script find out which are the queries running currently on your server.
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time --microseconds
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

--While running above query if you find any query which is running for long time it can be killed using following command.
KILL [session_id]

--Find the history of queries that have run on the table
SELECT
 qryStats.last_execution_time AS [Time]
 ,qryText.TEXT AS [Query]
 ,DB_NAME(qryText.[dbid]) AS [Database]
 ,OBJECT_NAME(qryText.[objectid]) AS [TableName]
 FROM sys.dm_exec_query_stats AS qryStats
 CROSS APPLY sys.dm_exec_sql_text(qryStats.sql_handle) AS qryText
 WHERE qryText.TEXT LIKE '%TABLENAME%'
 ORDER BY qryStats.last_execution_time DESC
SELECT 
        sql.text AS statement_text 
FROM 
        sys.dm_exec_requests  AS req 
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as sql 
WHERE 
    req.session_id=51--51 is the session id whcih you can find from exec sp_who2