Posts Tagged ‘DMV’
Posted by Pramod Singla on March 23, 2017
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
-
qs.statement_start_offset ) / 2 ) + 1)
AS statement_text,
total_logical_reads,
qs.execution_count
AS 'Execution Count',
total_logical_reads / qs.execution_count
AS 'AvgLogicalReads',
qs.execution_count / Datediff(minute, qs.creation_time, Getdate())
AS
'Calls/minute',
qs.total_worker_time / qs.execution_count
AS 'AvgWorkerTime',
qs.total_worker_time
AS 'TotalWorkerTime',
qs.total_elapsed_time / qs.execution_count
AS 'AvgElapsedTime',
qs.total_logical_writes,
qs.max_logical_reads,
qs.max_logical_writes,
qs.total_physical_reads,
qt.dbid,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.dbid = Db_id() -- Filter by current database
ORDER BY total_logical_reads DESC
Posted in DBA, Interview Questions, Performance Tuning, Scripts | Tagged: DMV, memory consuming queries, query tuning, slow running query, sql server tuning | Leave a Comment »
Posted by Pramod Singla on March 12, 2017
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)
AS statement_text,
total_logical_reads,
qs.execution_count
AS ‘Execution Count’,
total_logical_reads / qs.execution_count
AS ‘AvgLogicalReads’,
qs.execution_count / Datediff(minute, qs.creation_time, Getdate())
AS
‘Calls/minute’,
qs.total_worker_time / qs.execution_count
AS ‘AvgWorkerTime’,
qs.total_worker_time
AS ‘TotalWorkerTime’,
qs.total_elapsed_time / qs.execution_count
AS ‘AvgElapsedTime’,
qs.total_logical_writes,
qs.max_logical_reads,
qs.max_logical_writes,
qs.total_physical_reads,
qt.dbid,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS apply sys.Dm_exec_sql_text(qs.sql_handle) AS qt
OUTER apply sys.Dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.dbid = Db_id() — Filter by current database
ORDER BY totalworkertime DESC
Posted in DBA, Performance Tuning | Tagged: cpu intensive queries, DMV, Performance Tuning | Leave a Comment »
Posted by Pramod Singla on March 12, 2017
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
'RunDurationMin'
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
WHERE
--j.enabled = 1 and --Only Enabled Jobs
run_status = 1 --Successfull
AND step_id = 0
AND run_date >= '20161210'),
cte_std
AS (SELECT Jobname,
isnull(Cast(Stdev(RunDurationMin) AS DECIMAL(18, 2)), 0) AS
StddevMin
FROM cte_Ar --where ntiled between 2 and 9
GROUP BY Jobname),
cte_avg
AS (SELECT Jobname,
isnull(Cast(Avg(RunDurationMin) AS DECIMAL(18, 2)), 0) AS
StdAvgMin
FROM cte_Ar --where ntiled between 2 and 9
GROUP BY Jobname),
cte_Final
AS (SELECT cte_AR.Jobname,
Cast(Avg(RunDurationMin) AS DECIMAL(18, 2)) AS FinalAvginMin
--,count(1) cnt
FROM cte_AR
JOIN cte_std
ON cte_AR.jobname = cte_std.jobname
JOIN cte_avg
ON cte_AR.jobname = cte_avg.jobname
WHERE RunDurationMin BETWEEN StdAvgMin - StddevMin AND
StdAvgMin + StddevMin
GROUP BY cte_AR.Jobname)
--select *from cte_final
SELECT @@servername ServerName,
cte_ar.JobName,
FinalAvgInMin,
Count(1) RunCount
FROM cte_Final
RIGHT JOIN cte_AR
ON cte_AR.jobname = cte_Final.JobName
--WHERE cte_ar.jobname IN ('abc')
GROUP BY cte_ar.jobname,
FinalAvginMin
ORDER BY jobname
Posted in DBA, Scripts | Tagged: DMV, job run status, sql server agent job | Leave a Comment »
Posted by Pramod Singla on March 10, 2017
Problem Statement: Script to generate the scripts used to de-fragment indexes.
Solution: Use this script to find the fragmented indexes and following script can be used to de-fragment them.
SELECT ‘Alter index ‘ + i.NAME + ‘ on ‘
+ Quotename(s.NAME) + ‘.’ + Quotename(o.NAME) + CASE WHEN
stats.avg_fragmentation_in_percent >=60 THEN ‘ rebuild ‘ ELSE
‘ reorganize’ END
FROM sys.dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, ‘LIMITED’) AS
stats,
sys.objects AS o,
sys.schemas AS s,
sys.indexes AS i
WHERE o.OBJECT_ID = stats.OBJECT_ID
AND s.schema_id = o.schema_id
AND i.OBJECT_ID = stats.OBJECT_ID
AND i.index_id = stats.index_id
AND stats.avg_fragmentation_in_percent >= 20
AND stats.page_count >= 1000
ORDER BY stats.avg_fragmentation_in_percent DESC,
stats.page_count DESC
Posted in Performance Tuning, sql server | Tagged: defragment index, DMV | 1 Comment »
Posted by Pramod Singla on March 10, 2017
Problem Statement: Script to find fragmented Indexes.
Solution: No need to explain how killing fragmented indexes could be.Use following script to find the fragmented indexes and script to de-fragment them can be found here.
SELECT Db_name() AS DBname,
Quotename(s.NAME) AS CchemaName,
Quotename(o.NAME) AS TableName,
i.NAME AS IndexName,
stats.Index_type_desc AS IndexType,
stats.page_count AS [PageCount],
stats.partition_number AS PartitionNumber,
CASE
WHEN i.fill_factor > 0 THEN i.fill_factor
ELSE 90
END AS [Fill Factor],
stats.avg_fragmentation_in_percent,
stats.fragment_count,
CASE
WHEN stats.index_level = 0 THEN ‘Leaf Level’
ELSE ‘Nonleaf Level’
END AS IndexLevel
FROM sys.dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, ‘LIMITED’) AS
stats,
sys.objects AS o,
sys.schemas AS s,
sys.indexes AS i
WHERE o.OBJECT_ID = stats.OBJECT_ID
AND s.schema_id = o.schema_id
AND i.OBJECT_ID = stats.OBJECT_ID
AND i.index_id = stats.index_id
AND stats.avg_fragmentation_in_percent >= 20
AND stats.page_count >= 1000
ORDER BY stats.avg_fragmentation_in_percent DESC,
stats.page_count DESC
Posted in Performance Tuning, sql server | Tagged: DMV, fragmented indexes, Performance Tuning | 1 Comment »
