Problem Statement: Script to find top 10 high memory consuming Queries. Solution: SELECT TOP 10 Object_name(qt.objectid) AS ‘SP Name’, Substring(qt.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN Datalength(qt.text) ELSE qs.statement_end_offset END[…]
Read moreLatest Posts
Azure 70-775: Big Data Engineering with HDInsight
1) Administer and Provision HDInsight Clusters Deploy HDInsight clusters Create a cluster in a private virtual network, create a cluster[…]
Read moreSQL Server – Database Consistency Check DBCC CheckDB
Problem: How to check the consistency of a SQL Server database. Solution: DBCC checkdb WITH no_infomsgs, all_errormsgs; This Command can put overhead on the[…]
Read moreSQL Server Tuning: Quick overview of SQL Server performance using Activity Monitor
Question:How to get a quick overview of SQL Server performance. Ans: Use activity monitor in SSMS to get the quick[…]
Read moreSQL Server Tuning: Find Slow running queries using the Reports in SSMS
Find Slow running queries using the Reports in SSMS 1) High CPU consuming Queries: To run the report right click[…]
Read moreSQL Server Tuning : Top 10 CPU Intensive Queries
Problem Statement: Script to find top 10 CPU intensive queries. Solution: SELECT TOP 10 Object_name(qt.objectid) AS ‘SP Name’, Substring(qt.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN –1 THEN Datalength(qt.text) ELSE qs.statement_end_offset END – qs.statement_start_offset ) / 2 ) + 1)[…]
Read moreSQL Server Agent- Get Avg Jobs Run Time
Problem Statement: Script to find average SQL Server Agent jobs run time. Solution: SET TRANSACTION isolation level READ uncommitted; WITH cte_AR AS (SELECT j.NAME AS ‘JobName’, Cast(( ( run_duration / 10000 * 3600 + ( run_duration / 100 )%100 * 60 + run_duration%100 + 31 ) / 60.0 ) AS DECIMAL(18, 2)) AS[…]
Read more
