Evict and Destroy a Cluster

In powershell,

get-clusternode

#Please do the following steps first.
# Step 1. Pause and Drain Role on a secondary node
# Step 2. Stop Cluster service on the node to be evicted

#Now proceed to remove a secondary cluster Node

Remove-ClusterNode -Name ServerName

#Now on the last node, Remove the cluster and cleanup

Get-Cluster ClusterName | Remove-Cluster -Force -CleanupAD

How long did t take to load One point five Billion rows using ETL data flow ?

1.5 + Billion rows was Loaded to a table using ETL data flow. It took about 15 hrs 9 mins and 59 seconds.

Both data source and Destination are tables

2000 rows / batch

Table load - fast load

A few warning 

[SSIS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 5 buffers were considered and 5 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

[SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.

Rolling Cluster upgrade to Windows 2012R2 to Windows 2016 - (SQL Server availability group is a workload)

The cluster has two nodes and the workload is SQL server availability group.

Step 1

1. Drain role on one of the nodes
2. Evict the node from a cluster
3. Upgrade OS to Windows 2016
4. After upgrading successfully, add the node back to the cluster

SQL Listener

During the node 1 upgrade, SQL listener is online and application has no down time.

ClusterFunctionalLevel Verificaton

Open PowerShell , Run 

PS C:\WINDOWS\system32> get-cluster | select clusterfunctionallevel

ClusterFunctionalLevel

----------------------

                     8

you will get ClusterFunctionalLevel 8 ( which is a mixed mode clustering )

Step 2

1. Drain role on the second node
2. Evict the node from a cluster
3. Upgrade the node OS to Windows 2016
4. After upgrading successfully, add the node back to the cluster

Step 3

1. Run validate cluster
2. Check ClusterFunctionalLevel again

Open PowerShell , Run 

PS C:\WINDOWS\system32> get-cluster | select clusterfunctionallevel

ClusterFunctionalLevel

----------------------

                     8

you will get ClusterFunctionalLevel 8 ( which is a mixed mode clustering )

Final Step

PS C:\WINDOWS\system32> Update-ClusterFunctionalLevel

NOTE: after you run this command, you cannot rollback.

if it runs successfully, you will get 

PS C:\WINDOWS\system32> get-cluster | select clusterfunctionallevel

ClusterFunctionalLevel

----------------------

                     9

Summary

1. Windows Server 2016 makes upgrading clusters easy with rolling upgrades
2. Control when your cluster upgrade is complete with the Cluster Functional Level
3. Add and manage your Windows Server 2016 cluster from the up-level (2016) nodes

SQL Server 2016 - Query Store

I was taking a Plural sight introduction class. Here I am sharing what I learnt from the course.

SQL Server 2016 Query Store

1. Enable on a per database level
2. Data persisted in internal tables in the user databases
3. Restored databases will have a query store database

Enabling Query Store with GUI

Enable Query Store using Script

USE [master]

GO

ALTER DATABASE [DBA] SET QUERY_STORE = ON

GO

ALTER DATABASE [DBA] SET QUERY_STORE (OPERATION_MODE = READ_ONLY)

GO

USE [master];

GO

ALTER DATABASE [DBA] 

SET QUERY_STORE = ON;

Go

ALTER DATABASE [DBA] 

SET QUERY_STORE (

OPERATION_MODE = READ_WRITE, 

CLEANUP_POLICY = 

(STALE_QUERY_THRESHOLD_DAYS = 30), 

DATA_FLUSH_INTERVAL_SECONDS = 900,  

INTERVAL_LENGTH_MINUTES = 60, 

MAX_STORAGE_SIZE_MB = 100, 

QUERY_CAPTURE_MODE = ALL, 

SIZE_BASED_CLEANUP_MODE = AUTO, 

MAX_PLANS_PER_QUERY = 200);

GO

Query Store collection setting

• Operation Mode = Read_Write | Read Only
• Query_Caputre_Mode = ALL | AUTO | NONE
• Max Plans Per Query = 200 different plan for a given query (default)
• Max Storage Size MB = 2 GB (Start with)
• CleanUp_Policy = (Stale query threshold days) 30 days
• Sized based cleanup mode  = Auto | OFF

USE [master];

GO

ALTER DATABASE [DBA] SET QUERY_STORE (

OPERATION_MODE = READ_WRITE,

/*

READ_WRITE = data collection

(default)

READ_ONLY = no data collection 

*/

QUERY_CAPTURE_MODE = ALL,

/* 

are ALL queries captured, 

or only "relevant" ones? 

ALL = every query executed 

(default)

AUTO = infrequent & insignificant

NONE = nothing new added

*/

MAX_PLANS_PER_QUERY = 200);

/*

INT data type

*/

GO

NOTE : Changing query store setting in a database will flush all cached plan from the particular database.

Query Store GUI

Permission to use Query Store

• VIEW DATABASE STATE
• db_owner needed to force/unforced plans

What is Query Store capturing?

Runtime Stats Store(Performance data)

• Compile time
• last execution time
• Duration
• CPU
• logical reads
• Physical reads
• Write

Plan Store

• Query text
• Query plan

Script to check Query store Setting in DMV

SELECT 

[actual_state_desc], 

[readonly_reason], 

[desired_state_desc], 

[current_storage_size_mb], 

    [max_storage_size_mb], 

[flush_interval_seconds], 

[interval_length_minutes], 

    [stale_query_threshold_days], 

[size_based_cleanup_mode_desc], 

    [query_capture_mode_desc], 

[max_plans_per_query]

FROM [sys].[database_query_store_options];

GO

Remove everything from Query Store

--Truncating sys tables behind the scenes

ALTER DATABASE DBA  SET QUERY_STORE CLEAR;GO

Query store System Views

• sys.query_store_query
• sys.query_store_query_text
• sys.query_store_plan
• sys.query_store_runtime_stats

Query Store system views script

Query compile and optimization information

SELECT 
[qst].[query_text_id], 
[qsq].[query_id], 
[qsq].[object_id], 
[qsq].[context_settings_id], 
[qst].[query_sql_text], 
[qsq].[initial_compile_start_time], 
[qsq].[last_compile_start_time],
[qsq].[last_execution_time], 
[qsq].[avg_compile_duration], 
[qsq].[count_compiles], 
[qsq].[avg_optimize_cpu_time], 
[qsq].[avg_optimize_duration]
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id];
GO

Query plan and execution information

SELECT
[qsq].[query_id], 
[qsp].[plan_id],
[qsq].[object_id],
[qsq].[initial_compile_start_time],
[qsq].[last_compile_start_time], 
[rs].[first_execution_time],
[rs].[last_execution_time],
[rs].[avg_duration],
[rs].[avg_logical_io_reads],
TRY_CONVERT(XML, [qsp].[query_plan]),
[qsp].[query_plan],
[rs].[count_executions],
[qst].[query_sql_text]
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] 
ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs] 
ON [qsp].[plan_id] = [rs].[plan_id]
WHERE [qsp].[query_id] = 3

Queries executed in the last 8 hours with multiple plans

SELECT
[qsq].[query_id], 
COUNT([qsp].[plan_id]) AS [PlanCount],
[qsq].[object_id], 
MAX(DATEADD(MINUTE, -(DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())), 
[qsp].[last_execution_time])) AS [LocalLastExecutionTime],
MAX([qst].query_sql_text) AS [Query_Text]
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] 
ON [qsq].[query_id] = [qsp].[query_id]
WHERE [qsp].[last_execution_time] > DATEADD(HOUR, -8, GETUTCDATE())
GROUP BY [qsq].[query_id], [qsq].[object_id]
HAVING COUNT([qsp].[plan_id]) > 1;
GO

Confirm the plan that's being used now

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
SELECT 
[qs].[execution_count], 
[qs].[last_execution_time],
[s].[text], 
[qp].[query_plan], 
[qs].[plan_handle]
FROM [sys].[dm_exec_query_stats] AS [qs]
CROSS APPLY [sys].[dm_exec_query_plan] ([qs].[plan_handle]) AS [qp]
CROSS APPLY [sys].[dm_exec_sql_text]([qs].[plan_handle]) AS [s]
JOIN [sys].[dm_exec_cached_plans] AS [cp] 
ON [qs].[plan_handle] = [cp].[plan_handle]
WHERE [s].[text] LIKE '%CustomerTransactionInfo%';
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

GO

Query Store for a stored proc

SELECT

[qsq].[query_id], 

[qsp].[plan_id], 

[qsq].[object_id], 

[rs].[count_executions],

DATEADD(MINUTE, -(DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())), 

[qsp].[last_execution_time]) AS [LocalLastExecutionTime],

[qst].[query_sql_text], 

ConvertedPlan = TRY_CONVERT(XML, [qsp].[query_plan])

FROM [sys].[query_store_query] [qsq] 

JOIN [sys].[query_store_query_text] [qst]

ON [qsq].[query_text_id] = [qst].[query_text_id]

JOIN [sys].[query_store_plan] [qsp] 

ON [qsq].[query_id] = [qsp].[query_id]

JOIN [sys].[query_store_runtime_stats] [rs] 

ON [qsp].[plan_id] = [rs].[plan_id]

WHERE [qsq].[object_id] = OBJECT_ID(N'usp_GetMovie');

GO

Visual Studio 2017 Pro - SQL Schema Compare

SqlSchemaCompare 

SQL Schema Compare from VS 2017 will compare all the objects from Source database to Target database.

Click on Tools > SQL Server > New Schema Comparism

Enter Source and Target Server and Database name

Click on Compare

After clicking compare button, you will see the objects which needs to be deleted, added , chanaged in Target Database.

You can update the Target my clicking Update button.

To generate the script, click on generate Script button

Sometimes, we need to compare indexes and tables between two databases. This is a useful tool for us.

Day 4- R Data Structure

Data Structures

• Collection of data elements
• Grouped under one name
• Container

Homogeneouse data structure 
Atomic vector, Matrix, Array

Hetrogeneous data structure 
List, Data Frame

• Atomic vector
• 1 Dimension
• Matrix
• 2 Dimension
• Array
• n Dimension
• List
• 1 Dimension
• Data frame
• 2 Dimension

Basic Classes of Objects

Atomic Classes

Character

“A”, “c”

Numeric

4.36, 7.42

Integer

3,5

Logical

True, False

Complex

1 + 7i , 8 - 2i

Atomic Vector

•  Commonly knows as vectors
•  Homogeneous data structure

# Character

student.names <‐ c("Raj","Rahul","Priya","Poonam")

# Numeric

student.weights <‐ c( 60.5, 72.5 , 45.2, 47.5)

# Integer

student.physics.marks <‐ c( 70L , 75L , 80L, 85L)

# Logical

student.physics.interest <‐ c(FALSE, F, TRUE, T)

Common Operations on Atomic Vectors

• Arithmetic & logical Subsetting Coercion

Factor

• Special case of vector used to store nominal values

student.genders <‐ c(“Male",“Male",“Female",“Female")

student.genders <‐ factor(c(“Male",“Male",“Female",“Female"))

List

• Heterogeneous data structure
• Can contain items of different classes
• 1-Dimensional arrangement

Day 3 - R Variables and Operators

Variable

Name Convention

1. Valid names: letters, numbers , dots or underline characters
2. Do not user RESERVE 
3. Use lowercase letters
4. Separate words using dot(.)
5. Do not use underscores(_) or hyphens(-)

Custom Variable

Global Variable

Type of Vectorized Operation