Saturday, 10 July 2021

How Online Index Operations will work

    This topic defines the structures that exist during an online index operation and shows the activities associated with these structures.

Online Index Structures

To allow for concurrent user activity during an index data definition language (DDL) operation, the following structures are used during the online index operation: source and preexisting indexes, target, and for clustered indexes, a temporary mapping index.

  • Source and preexisting indexes
    The source is the original table or clustered index data. Preexisting indexes are any nonclustered indexes that are associated with the source structure. For example, if the online index operation is rebuilding a clustered index that has four associated nonclustered indexes, the source is the existing clustered index and the preexisting indexes are the nonclustered indexes.
    The preexisting indexes are available to concurrent users for select, insert, update, and delete operations. This includes bulk inserts (supported but not recommended) and implicit updates by triggers and referential integrity constraints. All preexisting indexes are available for queries and searches. This means they may be selected by the query optimizer and, if necessary, specified in index hints.
  • Target
    The target or targets is the new index (or heap) or a set of new indexes that is being created or rebuilt. User insert, update, and delete operations to the source are applied by the SQL Server 2005 Database Engine to the target during the index operation. For example, if the online index operation is rebuilding a clustered index, the target is the rebuilt clustered index; the Database Engine does not rebuild nonclustered indexes when a clustered index is rebuilt.
    The target index is not searched while processing SELECT statements until the index operation is committed. Internally, the index is marked as write-only.
  • Temporary mapping index
    Online index operations that create, drop, or rebuild a clustered index also require a temporary mapping index. This temporary index is used by concurrent transactions to determine which records to delete in the new indexes that are being built when rows in the underlying table are updated or deleted. This nonclustered index is created in the same step as the new clustered index (or heap) and does not require a separate sort operation. Concurrent transactions also maintain the temporary mapping index in all their insert, update, and delete operations.

Online Index Activities

During a simple online index operation, such as creating a clustered index on a nonindexed table (heap), the source and target go through three phases: preparation, build, and final.

The following illustration shows the process for creating an initial clustered index online. The source object, the heap, has no other indexes. The source and target structure activities are shown for each phase; and concurrent user select, insert, update, and delete operations are also shown. The preparation, build, and final phases are indicated together with the lock modes used in each phase.


Source Structure Activities

The following table lists the activities involving the source structures during each phase of the index operation and the corresponding locking strategy.

SOURCE STRUCTURE ACTIVITIES
PhaseSource activitySource locks

Preparation

Very short phase

System metadata preparation to create the new empty index structure.

A snapshot of the table is defined. That is, row versioning is used to provide transaction-level read consistency. For more information, see Understanding Row Versioning-Based Isolation Levels.

Concurrent user write operations on the source are blocked for a very short period.

No concurrent DDL operations are allowed except creating multiple nonclustered indexes.

S (Shared) on the table*

IS (Intent Shared)

INDEX_BUILD_INTERNAL_RESOURCE**

Build

Main phase

The data is scanned, sorted, merged, and inserted into the target in bulk load operations.

Concurrent user select, insert, update, and delete operations are applied to both the preexisting indexes and any new indexes being built.

IS (Intent Shared)

INDEX_BUILD_INTERNAL_RESOURCE**

Final

Very short phase

All uncommitted update transactions must complete before this phase starts. Depending on the acquired lock, all new user read or write transactions are blocked for a very short period until this phase is completed.

System metadata is updated to replace the source with the target.

The source is dropped if it is required. For example, after rebuilding or dropping a clustered index.

INDEX_BUILD_INTERNAL_RESOURCE**

S (Shared) on the table if creating a nonclustered index.*

SCH-M (Schema Modification) if any source structure (index or table) is dropped.*

* The index operation will wait for any uncommitted update transactions to complete before acquiring the S-lock or SCH-M lock on the table.

** The resource lock INDEX_BUILD_INTERNAL_RESOURCE prevents the execution of concurrent DDL operations on the source and preexisting structures while the index operation is in progress. For example, this lock prevents concurrent rebuild of two indexes on the same table. Although this resource lock is associated with the Sch-M lock, it does not prevent data manipulation statements.

The previous table shows a single Shared (S) lock acquired during the build phase of an online index operation that involves a single index. When clustered and nonclustered indexes are built, or rebuilt, in a single online index operation (for example, during the initial clustered index creation on a table that contains one or more nonclustered indexes) two short-term Shared (S) locks are acquired during the build phase followed by long-term IS locks. One S lock is acquired first for the clustered index creation and when creating the clustered index is completed, a second short-term S lock is acquired for creating the nonclustered indexes. After the nonclustered indexes are created, the S lock is downgraded to an IS lock until the final phase of the online index operation.

Target Structure Activities

The following table lists the activities that involve the target structure during each phase of the index operation and the corresponding locking strategy.

TARGET STRUCTURE ACTIVITIES
PhaseTarget activityTarget locks

Preparation

New index is created and set to write-only.

IS (Intent Shared)

Build

Data is inserted from source.

User modifications (inserts, updates, deletes) applied to the source are applied.

This activity is transparent to the user.

IS (Intent Shared)

Final

Index metadata is updated.

Index is set to read/write status.

S (Shared)

or

SCH-M (Schema Modification)

The target is not accessed by SELECT statements issued by the user until the index operation is completed.

After the preparation and final phase is completed, the query and update plans that are stored in the procedure cache are invalidated. Subsequent queries will use the new index.

The lifetime of a cursor declared on a table that is involved in an online index operation is limited by the online index phases. Update cursors are invalidated at each phase. Read-only cursors are invalidated only after the final phase.

The most matrix comparing some of the key functional differences between the rebuild and reorganize options:

FunctionalityREBUILD / DBREINDEXREORGANIZE / DEFRAG
Online / OfflineOffline; Online possible with SQL 2005 & higher versions with Enterprise Edition onlyOnline
Faster when logical fragmentation isHighLow
Parallel ProcessingYesNo
Compacts PagesYesYes
Can be stopped/started without losing completed work to that pointNoYes
Able to untangle interleaved pagesYesNo
Additional free space required in data file for processYes - 1.2x - 2x existing sizeNo
Faster on larger indexesYesNo
Rebuilds StatisticsYesNo
Log Space UsedFull Recovery - High; Bulk/Simple Recovery - Low;Depends on work performed
May skip pages on busy systemNoYes
Can specify additional options (fill factor, etc.)
rebuild pause\resume
Yes

Yes (SQL 2017 & higher versions and less than SQL 2017 version not possible)
No

No

One other option to 'addressing' fragmentation could be to choose to not fix it. Hopefully you're not the type of engineer who simply rebuilds/defrags indexes every day just for the sake of doing so or because it makes you feel better - instead, try to work out a plan to understand which indexes in your schema actually matter the most, are impacted by the different kinds of fragmentation, etc. and those that aren't.

Let's take a look at the internals for defrag and reorganizing - reorganizing an index is performed in basically 2 stages:

  1. The page compaction stage - this stage is meant to try and make pages have a 'fullness' near the original fillfactor specification for the index. This operates at the leaf-level of the index only, and pages are compacted by shuffling rows towards the left-side of the B-tree and dropping ghosted records and freeing pages made empty.
  2. Page defrag-ing stage - this stage is meant to make the logical order of pages match the allocation order. Again this operates at the leaf-level only, by performing a logical-ordered scan and an allocation-ordered scan in lockstep with one another, re-establishing a scan position after every page. This is a totally online operation, aside from an eXclusive lock on each page for the duration of each given re-ordering. Remember that no new pages are allocated during a defrag operation (aside from the single page for temporary space), which means that logical re-ordering is achieved by shuffling pages that are already allocated to the index only - this is why a defrag operation can't solve interleaved pages, since only existing pages are shuffled.

Ref.https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms191261(v=sql.90)?redirectedfrom=MSDN

Ref:https://www.mssqltips.com/sqlservertip/2268/sql-server-fragmentation-how-to-address-it-part-8-of-9/




Friday, 26 July 2019

SQL Server Agent Jobs that Have No Notification and Notification Change


SQL Server Agent Jobs that Have No Notification and Notification Change

If you want to verify each of your SQL Agent jobs have email notification then you can run this code:


USE msdb
SELECT name, notify_email_operator_id  
FROM dbo.sysjobs 

This code will display each SQL Server Agent job that doesn’t have an operator associated with an email notification operator.

USE msdb
SELECT name, notify_email_operator_id  
FROM dbo.sysjobs 
WHERE notify_email_operator_id = 0;

OR


SELECT @@servername as Server_name, j.name AS JobName,
      e.name AS EmailOperator
         ,j.enabled As Enabled
FROM msdb.dbo.sysjobs j
  LEFT JOIN msdb.dbo.sysoperators e
     on j.notify_email_operator_id = e.id
        where j.enabled=1
1=enabled
0=Disabled

Based on requirement please change the where condition.

IF server doesn't have any operator use below script to create a new operator. Below script I created an operator named "DBA-SQL" and associated it with the email address “DBA-SQL@Company.com” or else use GUI from SSMS.

USE [msdb]

GO
EXEC msdb.dbo.sp_add_operator @name=N'DBA-SQL',
                    @enabled=1,
                    @email_address=N'DBA-SQL@compenay.com'
GO


Automatically Updating SQL Agent Jobs to Have Notification:


If you already have a number of SQL Server agent jobs in one server where notification hasn’t been set up you might want to set up each job to notify someone.   
If you have a lot of SQL Agent jobs it might take a while for you to go through each job to define an email notification operator manually through the SQL Agent GUI.  
A better way to change a bunch of SQL Agent jobs to send email notifications would be to run a script.  

Here is a sample script for defining an operator on each job that doesn’t already have an email operator:


USE [msdb]

SET NOCOUNT ON;
DECLARE @Operator varchar(50) = 'DBA-SQL' -- place your operator name here
SELECT 'EXEC sp_update_job @job_name = ''' + j.[name] +
''', @notify_email_operator_name = ''' + @Operator +
''', @notify_level_email = 2'-- 1=On Success, 2=On Faulure,3=always
FROM dbo.sysjobs j
WHERE j.enabled = 1
AND j.notify_level_email <> 1

This script doesn’t actually add the operators to SQL Agent jobs that don’t have email notification.   But instead it just creates a series of EXEC statements that when executed add the email notifications.  The series of EXEC statements that this script generates will run the sp_update_job system stored procedures to add an email operator.  This particular script only adds email notification on a job that doesn’t already have email notification.  Additionally, it only sets up notification to occur when the job execution fails.   If you should happen to want different operators to be notified for different jobs then you will either need to add some special code to place the appropriate operator on each job, or run the above script then modify the operators for each job prior to executing the generated script.


If you have multiple servers to change the notification use below script and run using batch command or power-shell.

set nocount on

DECLARE @strsql    NVARCHAR(MAX)
DECLARE @job_list cursor

set @job_list = cursor for
SELECT     'EXEC msdb..sp_update_job @job_name = ''' + j.[name] + ''', @notify_email_operator_name = ''DBA-SQL'', @notify_level_email = 2' [exec_sql]
FROM msdb.dbo.sysjobs j
  LEFT JOIN msdb.dbo.sysoperators e
     on j.notify_email_operator_id = e.id
        where j.enabled = 1 --Change according to you requirement
      
open @job_list
fetch next from @job_list into @strsql
while @@FETCH_STATUS = 0
begin
       exec sp_executesql @strsql
       fetch next from @job_list into @strsql
end
close @job_list
deallocate @job_list

Please test in test\dev server then use in prodcution.

Find the run-time status for all SQL Server Agent jobs

Use below script to Find the run-time status for all SQL Server Agent jobs

use msdb
go



SELECT
        sj.Name,
                 CASE
                      WHEN sja.start_execution_date IS NULL THEN 'Not running'
                      WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
                      WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
                      END  AS 'RunStatus'
FROM   msdb.dbo.sysjobs sj
JOIN   msdb.dbo.sysjobactivity sja
ON     sj.job_id = sja.job_id
WHERE  sja.session_id =
                    (SELECT MAX (session_id) FROM msdb.dbo.sysjobactivity)
order by RunStatus desc

Search SQL Agent Job using Specific keyword text or SP - 2000\2005\08\08R2\12\14\16\17\19

SQL Server 2000 Job Step Search

USE [msdb]
GO
SELECT    jb.job_id,
       jb.originating_server,
       jb.name,
       js.step_id,
       js.command,
       jb.enabled
FROM    dbo.sysjobs jb
JOIN    dbo.sysjobsteps js
       ON    js.job_id = jb.job_id
WHERE    js.command LIKE N'%KEYWORD_SEARCH%'

Search SQL Server Agent Job Steps for Specific Keyword Text or Stored Procedure-2005\08\08R2\12\14\16\17\19

SELECT 
       jb.job_id,
       js.srvname,
       jb.name,
       js.step_id,
       js.command,
       jb.enabled
FROM    msdb.dbo.sysjobs jb
JOIN    msdb.dbo.sysjobsteps js
       ON    js.job_id = jb.job_id
JOIN    master..sysservers s
       ON    js.srvid = jb.originating_server_id
WHERE    js.command LIKE N'%KEYWORD_SEARCH%'
GO



How to find Job owner for all SQL Server Agent Jobs and change the owner

How to find Job owner for all SQL Server Agent Jobs and change the owner

We got below error from so many servers in my organisation.

Error: "The job failed.  The owner '' of job '' does not have server access."

Causes: Most of the below cases.
1. Logins does not exists
2. Job owner left from the organization
3. Jobs owner disabled state.

That means a DBA had created a SQL Server Agent  job with his name as owner. Once he/she will leave the company and account will be removed from AD, the jobs will start failing.

It is good to use 'service account' or 'sa' as SQL Server Job owner, so you don't have to worry in case owner leave the company.

Fix:

Single job we can fix manually but multiple servers multiple jobs how can we fix this issue, so that we found a way using below script by joining the below tables and query.

master.sys.syslogins--Find the login Name
msdb..sysjobs--Find the jobs Name

Below query will return your server job names and respective job owners.


SELECT SJ.NAME AS JobName
    ,SL.NAME AS OwnerName
FROM msdb..sysjobs SJ
LEFT JOIN master.sys.syslogins SL ON SJ.owner_sid = SL.sid
  
Or

Adding little more stuff for serverName, job which is enabled\disabled state.

SELECT                     @@servername as Server_name,

                                 sj.name, CASE
                                                      WHEN sj.enabled = 1 THEN 'Enable'
                                                      ELSE 'Disable'
                                               END             AS JobStatus,
                                 SUSER_SNAME(sj.owner_sid) AS owner
from                msdb..sysjobs_view sj
left outer join master.sys.syslogins sl on sj.owner_sid = sl.sid
where               SUSER_SNAME(sj.owner_sid) <> 'sa'
or                         SUSER_SNAME(sj.owner_sid)  is null


Now if we would like to update all the job where owner is not 'sa', we can use below query. So you can also modify the query to filter the jobs for which you would like to update owner.

--Please provide the New Owner for The Jobs you like to Change, I am using sa

DECLARE @OWNERName VARCHAR(100)
SET @OWNERName = 'sa'--please change SA or SQL Instance service account
DECLARE @OldJobOwner VARCHAR(100)
DECLARE @JobName VARCHAR(1000)
DECLARE Job_Cursor CURSOR
FOR

--Change your Query as per requirements, I am selecting all Job where owner<>sa

SELECT SJ.NAME AS JobName
    ,SL.NAME
AS OwnerName
FROM msdb..sysjobs SJ
LEFT JOIN master.sys.syslogins SL ON SJ.owner_sid = SL.sid
WHERE L.NAME <> @OWNERName

OPEN Job_Cursor --Open a cursor
FETCH NEXT FROM Job_Cursor  --Fetch Data from cursor

INTO @JobName
    ,@OldJobOwner
WHILE (@@FETCH_STATUS <> - 1)

BEGIN
   
EXEC msdb..sp_update_job @job_name         = @JobName
                            ,@owner_login_name = @OWNERName

PRINT 'Ownerd Change for ' + @JobName + 'Job from ' + @OldJobOwner + ' to '
 + @OWNERName

FETCH NEXT
   
FROM Job_Cursor
   
INTO @JobName
        ,@OldJobOwner
END

CLOSE Job_Cursor -- Close the cursor
DEALLOCATE Job_Cursor --De-allocate the cursor

You can wrap up by creating the stored procedure with @OWNERName parameter and use that stored procedure as a job in every server to change the job owners to 'sa' or 'service account'. 

Just to be safe side please initially test is 'TEST\DEV' server and then move to production.

Disable CDC at table level

 How to Disable CDC( Change Data Capture) for tables Change Data Capture (cdc) property is disabled as default.  here I will Query you how t...