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’)
Archive for the ‘DBA’ Category
SQL Server -DBA Must Know commands
Posted by Pramod Singla on March 28, 2017
Posted in DBA | Tagged: dba, sql server | 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: DMV, memory consuming queries, query tuning, slow running query, sql server tuning | 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: consistancy check, dbcc, sql server issue | 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: cpu intensive queries, DMV, Performance Tuning | 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: DMV, job run status, sql server agent job | Leave a Comment »
