Data World

Archive for the ‘DBA’ Category

SQL Server -DBA Must Know commands

Posted by Pramod Singla on March 28, 2017


Following are couple of DBA must know commands.

–Get the db details
EXEC SP_HELPDB ‘Db_name’
–know the definition of the objects
EXEC SP_HELPTEXT ‘object_name’
–Identify the size of database and table
EXEC SP_SPACEUSED
–Know the details of the disk drives 
EXEC MASTER..XP_FIXEDDRIVES
—Quick view of SQL server sessions
EXEC SP_WHO2
–It’s advanced version of sp_who2 but not shipped with SQL server.Get it from the following link. 
EXEC SP_WHOISACTIVE
–Used in Query tuning for getting the logical reads
SET STATISTICS IO ON
–Used in Query tuning for getting the CPU time
SET STATISTICS TIME ON
–Clean the data buffers.Used in query tuning.
DBCC DROPCLEANBUFFERS
–Free the procedure cache.Used in query tuning.
DBCC FREEPROCCACHE
–Check the Error logs in efficient way
EXEC MASTER.DBO.XP_READERRORLOG 0, 1, ‘search String 1’, ‘search String 2’, ‘2017-01-10’, ‘2017-02-20’, N’desc’
–Check the query a session is running.
DBCC INPUTBUFFER(<session_id>)
–Get current log file path  
SELECT Serverproperty(‘ErrorLogFileName’)
–Database consistency check
DBCC CHECKDB WITH ESTIMATEONLY;

DBCC CHECKDB WITH NO_INFOMSGS;

DBCC CHECKDB(DBNAME) WITH NO_INFOMSGS, ALL_ERRORMSGS SELECT Databasepropertyex (‘kcc_dw’, ‘STATUS’)         

DBCC sqlperf(‘logspace’) 

Advertisements

Posted in DBA | Tagged: , | Leave a Comment »

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  

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

SQL Server – Database Consistency Check DBCC CheckDB

Posted by Pramod Singla on March 13, 2017


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 database.

  • Normally schedule to run at off peak hours.

  • See the database consistency history report by right click the database>select Reports>Select Standard Reports>Select Database Consistency history

Figure 1: Database Consistency History

 

Posted in DBA | Tagged: , , | Leave a Comment »

SQL Server Tuning : Top 10 CPU Intensive Queries

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: , , | 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 »