SQLServer Interview Questions-2

1. What is the query store in SQL 2016?

2. How do you upgrade SQL server with minimum downtime?

3. Can we setup AlwaysOn availability group between Windows and linux cluster?

4.  How do you partition database?

5. What will be the best practice for selecting RANGE value(LEFT/RIGHT) when you create function?

    By default function will use LEFT RANGE value however best practice would be RIGHT range.

6. What is the Change Data Capture?

7. Can we restore database with CDC enabled from one server to another server

   Answer:-  Yes, we can restore database with "KEEP_CDC" option while restoring database on another server.  also we need to script out following job and create on server where you're restoring the database. or you can simply run below command so it will create job for you on new server.

Exec sys.sp_cdc_add_job 'capture'

go

Exec sys.sp_cdc_add_job 'cleanup'

go

 

8. What is temporal database? difference between CDC vs Temporal database

9. What is a contained database? 

     Independent to SQL server instance. we can use SQL server authentication to connect to the database
10. Estimated vs Actual query plan
11. How SQL server optimizer decides what physical join  will be used to create query plan?
12. Physical join summary:

	Nested Loops Join	Merge Join	Hash Join
Best for …	Relatively small inputs with an index on the inner table on the join key.	Medium to large inputs with indexes to provide order on the equijoin keys and/or where we require order after the join.	DW queries with medium to large inputs.  Parallel execution that scales linearly.
Concurrency	Supports large numbers of concurrent users.	Many-to-one join with indexes to provide order supports large numbers of concurrent users.	Best for small numbers of concurrent users with high throughput requirements.
Stop and go	No	No	Yes (build input only)
Equijoin required	No	Yes (except for full outer join)	Yes
Outer and semi-joins	Left joins only (full outer joins via transformation)	All join types	All join types
Uses memory	No	No (may require sorts which use memory)	Yes
Uses tempdb	No	Yes (many-to-many join only)	Yes (if join runs out of memory and spills)
Requires order	No	Yes	No
Preserves order	Yes (outer input only)	Yes	No

13. How to find out if  ColumnStore index is being used from Query plan?

14. Check AlwaysON synchronization status between replicas and find out if there's delay in synchronization

    Alwayson Dashboard

      or DMVs

    SELECT 

ar.replica_server_name, 

adc.database_name, 

ag.name AS ag_name, 

drs.is_local, 

drs.is_primary_replica, 

drs.synchronization_state_desc, 

drs.is_commit_participant, 

drs.synchronization_health_desc, 

drs.recovery_lsn, 

drs.truncation_lsn, 

drs.last_sent_lsn, 

drs.last_sent_time, 

drs.last_received_lsn, 

drs.last_received_time, 

drs.last_hardened_lsn, 

drs.last_hardened_time, 

drs.last_redone_lsn, 

drs.last_redone_time, 

drs.log_send_queue_size, 

drs.log_send_rate, 

drs.redo_queue_size, 

drs.redo_rate, 

drs.filestream_send_rate, 

drs.end_of_log_lsn, 

drs.last_commit_lsn, 

drs.last_commit_time

FROM sys.dm_hadr_database_replica_states AS drs

INNER JOIN sys.availability_databases_cluster AS adc 

ON drs.group_id = adc.group_id AND 

drs.group_database_id = adc.group_database_id

INNER JOIN sys.availability_groups AS ag

ON ag.group_id = drs.group_id

INNER JOIN sys.availability_replicas AS ar 

ON drs.group_id = ar.group_id AND 

drs.replica_id = ar.replica_id

ORDER BY 

ag.name, 

ar.replica_server_name, 

adc.database_name;