Data World

Archive for the ‘DBA’ Category

MSSSQL Server Tuning – Find Wait stats

Posted by Pramod Singla on March 5, 2017


Problem Statement: Script to get the SQL Server wait stats. You can find the significance of most common waits below it.
Solution: Knowing resource waits is the good way to start the performance tuning.Following is the good script i got from SQL skills.

WITH [Waits]
AS (SELECT [wait_type],
[wait_time_ms] / 1000.0                             AS [WaitS],
( [wait_time_ms]  [signal_wait_time_ms] ) / 1000.0 AS
[ResourceS],
[signal_wait_time_ms] / 1000.0                      AS [SignalS]
,
[waiting_tasks_count]
AS [WaitCount],
100.0 * [wait_time_ms] / Sum ([wait_time_ms])
OVER()                   AS
[Percentage],
ROW_NUMBER()
OVER(
ORDER BY [wait_time_ms] DESC)                   AS [RowNum]
FROM   sys.dm_os_wait_stats
WHERE  [wait_type] NOT IN (
N’BROKER_EVENTHANDLER’, N’BROKER_RECEIVE_WAITFOR’,
N’BROKER_TASK_STOP’,
N’BROKER_TO_FLUSH’,
N’BROKER_TRANSMITTER’, N’CHECKPOINT_QUEUE’,
N’CHKPT’,
N’CLR_AUTO_EVENT’,
N’CLR_MANUAL_EVENT’, N’CLR_SEMAPHORE’,
— Maybe uncomment these four if you have mirroring issues
N’DBMIRROR_DBM_EVENT’, N’DBMIRROR_EVENTS_QUEUE’,
N’DBMIRROR_WORKER_QUEUE’, N’DBMIRRORING_CMD’,
N’DIRTY_PAGE_POLL’,
N’DISPATCHER_QUEUE_SEMAPHORE’,
N’EXECSYNC’, N’FSAGENT’,
N’FT_IFTS_SCHEDULER_IDLE_WAIT’,
N’FT_IFTSHC_MUTEX’,
— Maybe uncomment these six if you have AG issues
N’HADR_CLUSAPI_CALL’,

,
N’HADR_LOGCAPTURE_WAIT’,
N’HADR_NOTIFICATION_DEQUEUE’,
N’HADR_TIMER_TASK’, N’HADR_WORK_QUEUE’,
N’KSOURCE_WAKEUP’,
N’LAZYWRITER_SLEEP’
,
N’LOGMGR_QUEUE’, N’MEMORY_ALLOCATION_EXT’,
N’ONDEMAND_TASK_QUEUE’,
N’PREEMPTIVE_XE_GETTARGETSTATE’,
N’PWAIT_ALL_COMPONENTS_INITIALIZED’,
N’PWAIT_DIRECTLOGCONSUMER_GETNEXT’,
N’QDS_PERSIST_TASK_MAIN_LOOP_SLEEP’
,
N’QDS_ASYNC_QUEUE’,
N’QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP’,
N’QDS_SHUTDOWN_QUEUE’,
N’REDO_THREAD_PENDING_WORK’,
N’REQUEST_FOR_DEADLOCK_SEARCH’,
N’RESOURCE_QUEUE’, N’SERVER_IDLE_CHECK’,
N’SLEEP_BPOOL_FLUSH’,
N’SLEEP_DBSTARTUP’,
N’SLEEP_DCOMSTARTUP’, N’SLEEP_MASTERDBREADY’,
N’SLEEP_MASTERMDREADY’,
N’SLEEP_MASTERUPGRADED’,
N’SLEEP_MSDBSTARTUP’, N’SLEEP_SYSTEMTASK’,
N’SLEEP_TASK’,
N’SLEEP_TEMPDBSTARTUP’,
N’SNI_HTTP_ACCEPT’, N’SP_SERVER_DIAGNOSTICS_SLEEP’,
N’SQLTRACE_BUFFER_FLUSH’,
N’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’,
N’SQLTRACE_WAIT_ENTRIES’, N’WAIT_FOR_RESULTS’,
N’WAITFOR’,
N’WAITFOR_TASKSHUTDOWN’,
N’WAIT_XTP_RECOVERY’, N’WAIT_XTP_HOST_WAIT’,
N’WAIT_XTP_OFFLINE_CKPT_NEW_LOG’,
N’WAIT_XTP_CKPT_CLOSE’,
N’XE_DISPATCHER_JOIN’, N’XE_DISPATCHER_WAIT’,
N’XE_TIMER_EVENT’ )
AND [waiting_tasks_count] > 0)
SELECT Max ([W1].[wait_type])
AS
[WaitType],
Cast (Max ([W1].[WaitS]) AS DECIMAL (16, 2))
AS [Wait_S],
Cast (Max ([W1].[ResourceS]) AS DECIMAL (16, 2))
AS [Resource_S],
Cast (Max ([W1].[SignalS]) AS DECIMAL (16, 2))
AS [Signal_S],
Max ([W1].[WaitCount])
AS [WaitCount],
Cast (Max ([W1].[Percentage]) AS DECIMAL (5, 2))
AS [Percentage],
Cast (( Max ([W1].[WaitS]) / Max ([W1].[WaitCount]) ) AS DECIMAL (16, 4))
AS
[AvgWait_S],
Cast (( Max ([W1].[ResourceS]) / Max ([W1].[WaitCount]) ) AS
DECIMAL (16, 4)) AS
[AvgRes_S],
Cast (( Max ([W1].[SignalS]) / Max ([W1].[WaitCount]) ) AS
DECIMAL (16, 4))   AS
[AvgSig_S],
Cast (https://www.sqlskills.com/help/waits/’
+ Max ([W1].[wait_type]) AS XML)
AS [Help/Info URL]
FROM   [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP  BY [W1].[RowNum]
HAVING Sum ([W2].[Percentage])  Max([W1].[Percentage]) < 95; — percentage threshold

GO

Common Significant Wait types explanations are as:

Sno. Wait Type Description
1. LCK_M_IX Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock
2. LCK_M_IU Occurs when a task is waiting to acquire an Intent Update (IU) lock
3. LCK_M_S Occurs when a task is waiting to acquire a Shared lock
4. ASYNC_NETWORK_IO Occurs on network writes when the task is blocked behind the network
5. ASYNC_IO_COMPLETION Occurs when a task is waiting for I/Os to finish
6.  

IO_COMPLETION

Occurs while waiting for I/O operations to complete.                       This wait type generally represents non-data page I/Os. Data page I/O completion waits appear
7. PAGEIOLATCH_SH Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
8.  

 

PAGEIOLATCH_EX

 

Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.

 

9. WRITELOG

 

Occurs while waiting for a log flush to complete.Common operations that cause log flushes are checkpoints and transaction commits.
10. PAGELATCH_EX

 

Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.
11. BACKUPIO

 

Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data
12. SOS_SCHEDULER_YIELD

 

Occurs when a task voluntarily yields the scheduler for other tasks to execute.  During this wait the task is waiting for its quantum to be renewed.
13. THREADPOOL

 

Occurs when a task is waiting for a worker to run on. This can indicate that the maximum worker setting is too low, or that batch executions are taking  unusually long, thus reducing the number of workers available to satisfy other batches.
14. CX_PACKET

 

Occurs when trying to synchronize the query processor exchange iterator
15. ONDEMAND_TASK_QUEUE

 

Occurs while a background task waits for high priority system task requests. Long wait times indicate that there have been no high priority requests to process, and should not cause concern. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem
Advertisements

Posted in DBA, Performance Tuning | Tagged: , , , , | 1 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 »

MSSQL Server : Automatically Restore DBs from backup files

Posted by Pramod Singla on March 4, 2017


Problem Statement:Many a times we need to restore multiple dbs from the backfiles on disk. We must write scripts to restore each DB. Rather use the shared script to do the automatic restore.
Solution: 

Note:Change the DB name,data file path,log file path and backup file path before using this scirpt.


BEGIN try
DECLARE curDBNames CURSOR FOR
SELECT dbname
FROM   (
--Change DB1 and Db2 with actual db names and more dbs as per you requirement
SELECT 'DB1' AS dbname
UNION
SELECT 'DB2' AS dbname) a
LEFT JOIN sys.databases d
ON a.dbname = d.NAME
WHERE  d.NAME IS NULL
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @v_DBName SYSNAME

OPEN curDBNames

FETCH NEXT FROM curDBNames INTO @v_DBName

WHILE @@Fetch_Status = 0
BEGIN
BEGIN try
SET nocount ON

DECLARE @p_strDBNameFrom           SYSNAME =@v_DBName,
@p_strDBNameTo             SYSNAME =@v_DBName,

–Change the data file,log file and backup file locations
@p_strDataFileMoveToFolder VARCHAR(255)=‘H:\MSSQL\DATA’,
@p_strLogFileMoveToFolder  VARCHAR(255)=‘O:\MSSQL\log’,
@p_strFQNBackupFileNames   NVARCHAR(max)=
‘E:\backupFileLocation\’ + @v_DBName +
‘.bak’,
@v_strDBFilename           VARCHAR(100),
@v_strDBLogFilename        VARCHAR(100),
@v_strDBDataFile           VARCHAR(100),
@v_strDBLogFile            VARCHAR(100),
@v_strExecSQL              NVARCHAR(1000),
@v_strKillConSQL           NVARCHAR(1000),
@v_strExecSQL1             NVARCHAR(1000),
@v_strMoveSQL              NVARCHAR(4000),
@v_strREPLACE              NVARCHAR(50),
@v_strTEMP                 NVARCHAR(1000),
@v_strListSQL              NVARCHAR(4000),
@v_strServerVersion        NVARCHAR(20),
@v_strFromDiskSQL          NVARCHAR(max)

SET @v_strREPLACE = 

IF EXISTS (SELECT NAME
FROM   sys.databases
WHERE  NAME = @p_strDBNameTo)
SET @v_strREPLACE = ‘, REPLACE’

IF Object_id(‘tempdb..#Backupfiles’) IS NOT NULL
DROP TABLE #Backupfiles

SELECT *
INTO   #Backupfiles
FROM   dbo.splitstring(@p_strFQNBackupFileNames)

IF Object_id (‘tempdb..#FILE_LIST’) IS NOT NULL
BEGIN
DROP TABLE #FILE_LIST
END

CREATE TABLE #FILE_LIST
(
LogicalName          VARCHAR(64),
PhysicalName         VARCHAR(130),
[Type]               VARCHAR(1),
FileGroupName        VARCHAR(64),
Size                 DECIMAL(20, 0),
MaxSize              DECIMAL(25, 0),
FileID               BIGINT,
CreateLSN            DECIMAL(25, 0),
DropLSN              DECIMAL(25, 0),
UniqueID             UNIQUEIDENTIFIER,
ReadOnlyLSN          DECIMAL(25, 0),
ReadWriteLSN         DECIMAL(25, 0),
BackupSizeInBytes    DECIMAL(25, 0),
SourceBlockSize      INT,
filegroupid          INT,
loggroupguid         UNIQUEIDENTIFIER,
differentialbaseLSN  DECIMAL(25, 0),
differentialbaseGUID UNIQUEIDENTIFIER,
isreadonly           BIT,
ispresent            BIT,
TDEThumbpr           DECIMAL
)

———————–create from disk query ————————-
DECLARE curFileLIst CURSOR FOR
SELECT NAME
FROM   #Backupfiles –where [Type]=’D’
SET @v_strFromDiskSQL = 

OPEN curFileList

FETCH NEXT FROM curFileList INTO @v_strTEMP

WHILE @@Fetch_Status = 0
BEGIN
SET @v_strFromDiskSQL = @v_strFromDiskSQL + ‘,Disk=”’ +
@v_strTEMP
+ ””

FETCH NEXT FROM curFileList INTO @v_strTEMP

PRINT @v_strFromDiskSQL
END

CLOSE curFileList

DEALLOCATE curFileList

SET @v_strFromDiskSQL= Stuff(@v_strFromDiskSQL, 1, 1, )

—————–create  with move query——————————
INSERT INTO #FILE_LIST
EXEC (‘RESTORE FILELISTONLY FROM  ‘ + @v_strFromDiskSQL + )

DECLARE curFileLIst CURSOR FOR
SELECT ‘MOVE N”’ + LogicalName + ”’ TO N”’
+ iif(type=‘L’, @p_strLogFileMoveToFolder,
@p_strDataFileMoveToFolder)
+ Replace(RIGHT(PhysicalName, Patindex(‘%\%’, Reverse(
PhysicalName)
)),
@p_strDBNameFrom, @p_strDBNameTo)
+ ””
FROM   #FILE_LIST –where [Type]=’D’
SET @v_strMoveSQL = 

OPEN curFileList

FETCH NEXT FROM curFileList INTO @v_strTEMP

WHILE @@Fetch_Status = 0
BEGIN
SET @v_strMoveSQL = @v_strMoveSQL + @v_strTEMP + ‘, ‘

FETCH NEXT FROM curFileList INTO @v_strTEMP
END

CLOSE curFileList

DEALLOCATE curFileList

PRINT
‘———————————————————————————————————————————–‘

PRINT ‘Restoration Started for”‘ + @p_strDBNameTo
+ ‘” database from “‘
+ @p_strFQNBackupFileNames + ‘ at ‘
+ Cast(Getdate() AS VARCHAR(30))

PRINT
‘———————————————————————————————————————————–‘

SET @v_strExecSQL = ‘RESTORE DATABASE [‘ + @p_strDBNameTo + ‘]’
SET @v_strExecSQL = @v_strExecSQL + ‘ FROM  ‘ + @v_strFromDiskSQL
+ 
SET @v_strExecSQL = @v_strExecSQL + ‘ WITH FILE = 1,’
SET @v_strExecSQL = @v_strExecSQL + @v_strMoveSQL
SET @v_strExecSQL = @v_strExecSQL + ‘ NOREWIND, STATS=10,’
SET @v_strExecSQL = @v_strExecSQL + ‘ NOUNLOAD ‘
SET @v_strExecSQL = @v_strExecSQL + @v_strREPLACE

PRINT @v_strExecSQL

PRINT ‘Killing active connections to the “‘
+ @p_strDBNameTo + ‘” database’

— Create the sql to kill the active database connections
SET @v_strKillConSQL = 

SELECT @v_strKillConSQL = @v_strKillConSQL + ‘kill ‘
+ CONVERT(CHAR(10), spid) + ‘ ‘
FROM   master.dbo.sysprocesses
WHERE  Db_name(dbid) = @p_strDBNameTo
AND DBID <> 0
AND spid <> @@spid

EXEC (@v_strKillConSQL)

EXEC sp_executesql
@v_strExecSQL

PRINT
‘———————————————————————————————————————————‘

PRINT ‘Restoration Completed FOR “‘
+ @p_strDBNameTo + ‘” database from “‘
+ @p_strFQNBackupFileNames + ‘ at ‘
+ Cast(Getdate() AS VARCHAR(30))

PRINT
‘———————————————————————————————————————————‘
END try

BEGIN catch
SELECT 1

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage,— Message text.  
1,@ErrorState — State.  
);

SELECT 2
END catch

SELECT @v_DBName

FETCH NEXT FROM curDBNames INTO @v_DBName
END
END try

BEGIN catch
SELECT 3

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

— Use RAISERROR inside the CATCH block to return error  
— information about the original error that caused  
— execution to jump to the CATCH block.  
RAISERROR (@ErrorMessage,— Message text.  
1,— Severity.  
@ErrorState — State.  
);

SELECT 4
END catch

CLOSE curDBNames

DEALLOCATE curDBNames

Posted in DBA, SQL Server Backups | Tagged: , , , | Leave a Comment »

SQL Server:Write on “???” failed: 1453(Insufficient quota to complete the requested service.

Posted by Pramod Singla on August 3, 2016


Error:Write on “???” failed: 1453(Insufficient quota to complete the requested service.

solution: Restrict the max server memory setting of the Sql server to allow other application to run properly.

Untitled

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

SQL Server Always On-Secondary database is NOT Synchronizing

Posted by Pramod Singla on August 1, 2016


Due to disk space crunch secondary database had gone into not synchronizing mode and it remained into that state even I reclaimed the space. Tried below command and it worked.

DBCC traceon(3605,-1)

ALTER DATABASE yourDB SET HADR RESUME

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