Data World

Archive for the ‘Scripts’ Category

SQL Server Tuning – Top 10 high memory consuming Queries

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  
Advertisements

Posted in DBA, Interview Questions, Performance Tuning, Scripts | Tagged: , , , , | Leave a Comment »

SQL Server Agent- Get Avg Jobs Run Time

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: , , | Leave a Comment »

MSSQL Server: Get VLF counts of all Dbs

Posted by Pramod Singla on March 4, 2017


Problem Statement:Get the VLF count of all the dbs in a SQL server Instance.
Solution: 

--variables to hold each 'iteration'  
DECLARE @query VARCHAR(100)
DECLARE @dbname SYSNAME
DECLARE @vlfs INT
--table variable used to 'loop' over databases  
DECLARE @databases TABLE
(
dbname SYSNAME
)

INSERT INTO @databases
--only choose online databases  
SELECT NAME
FROM   sys.databases
WHERE  state = 0

--table variable to hold results  
DECLARE @vlfcounts TABLE
(
dbname   SYSNAME,
vlfcount INT
)
--table variable to capture DBCC loginfo output  
--changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version 
DECLARE @MajorVersion TINYINT

SET @MajorVersion = LEFT(Cast(Serverproperty('ProductVersion') AS NVARCHAR(max))
,
Charindex('.', Cast(
Serverproperty('ProductVersion') AS
NVARCHAR(max))) - 1)

IF @MajorVersion < 11 -- pre-SQL2012 
BEGIN
DECLARE @dbccloginfo TABLE
(
fileid       TINYINT,
file_size    BIGINT,
start_offset BIGINT,
fseqno       INT,
[status]     TINYINT,
parity       TINYINT,
create_lsn   NUMERIC(25, 0)
)

WHILE EXISTS(SELECT TOP 1 dbname
FROM   @databases)
BEGIN
SET @dbname = (SELECT TOP 1 dbname
FROM   @databases)
SET @query = 'dbcc loginfo (' + '''' + @dbname + ''') '

INSERT INTO @dbccloginfo
EXEC (@query)

SET @vlfs = @@rowcount

INSERT @vlfcounts
VALUES(@dbname,
@vlfs)

DELETE FROM @databases
WHERE  dbname = @dbname
END --while 
END
ELSE
BEGIN
DECLARE @dbccloginfo2012 TABLE
(
RecoveryUnitId INT,
fileid         TINYINT,
file_size      BIGINT,
start_offset   BIGINT,
fseqno         INT,
[status]       TINYINT,
parity         TINYINT,
create_lsn     NUMERIC(25, 0)
)

WHILE EXISTS(SELECT TOP 1 dbname
FROM   @databases)
BEGIN
SET @dbname = (SELECT TOP 1 dbname
FROM   @databases)
SET @query = 'dbcc loginfo (' + '''' + @dbname + ''') '

INSERT INTO @dbccloginfo2012
EXEC (@query)

SET @vlfs = @@rowcount

INSERT @vlfcounts
VALUES(@dbname,
@vlfs)

DELETE FROM @databases
WHERE  dbname = @dbname
END --while 
END

--output the full list  
SELECT dbname,
vlfcount
FROM   @vlfcounts
ORDER  BY dbname

Posted in DBA, Performance Tuning, Scripts | Tagged: , , , , | Leave a Comment »