Advanced Scanning : Merry Go Round;

#Advanced Scanning

Today I read a very interesting feature of SQL Enterprise. I must say it very intelligently named after the Merry go round (a continuous cycle of activities or events, especially when regarded as pointless.)
So this is basically an interesting feature of SQL enterprise that efficiently combined the multiple scan requests and manages the optimum use of the resources.

When there is a request from user to get the details that involve the scanning of the data pages and while the request and scan is in progress 2nd user initiates the request for the same data.

Point to note here:

1. If we keep 2nd user in a queue the user experiences a delay in process.
2. If we accept point 1 this implies: we are going to scan the same data pages again causing more Disk contention and damage: due to frequent movement of disk hand causing wear and tear.
3. Consuming CPU resources unnecessarily.
4. It will simply be inefficient.
5. It will also cause fight for buffer space. 

To avoid this there is a feature of advanced scanning; this feature solves all our problems and makes the system more efficient.

Explanation:

Refer Figure A: suppose we have a table that has odd 10,000 data pages. User 1 initiates a query and SQL server starts scanning now it has reached 2,500 page & User 2 initiate a query and this query requires the same data pages to be scanned so SQL server will join the scan for these 2 users. From 2,501 pages the scan results are shared between users. Not scanning reached 4,000 pages and User 3 requested the same data pages so again SQL server will scan data pages and share the results between User 1, 2 & 3. Not when the End of the scan is reached i.e. 10,000th (END) page is scanned, User1 will get the results and is out of the motion. User 2 & 3 scanning will bounce back to page 1 (start) and continue. When scanning reaches the 2,500th Page the User 2 is out or motion. But User 3 will continue till 4,000th page.

This scanning continues till all the scan requests are fulfilled. This is called ‘’Merry Go round ‘’.

Figure A

Without advanced scanning, each user would have to compete for buffer space and cause disk arm contention. The same pages would then be read once for each user, instead of read one time and shared by multiple users, slowing down performance and taxing resources.

Reference: MSDN

My LinkedIn;

Certification; My 70-462 Score card

 This is just kind of reminder and make it immediately available. In last few days i gave few interviews and was never asked and I never took stress in looking for the score since i passed some how (offcourse it was hard study from which i don't remember a few things of it) so could not tell the interviewer that i scored 800 marks... Hence this is the score card I upload to you all (also for myself....!;) ) 

Note: That aadhar card like pic is mine....I had the same reaction...lol!!

Factors That Can Delay Log Truncation

 #Factors That Can Delay Log Truncation

#I_Bet*1

We all must have seen log_reuse_wait_desc column from sys.databases catalog. But Log records can remain active under a variety of conditions, which are mentioned below and can delay log truncation...

But I bet you have missed all the values the log_reuse_wait_desc column can have....check it now!!

Description 

0
NOTHING 
Currently there are one or more reusable virtual log files.

1
CHECKPOINT 

No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models).

2
LOG_BACKUP
A log backup is required to move the head of the log forward (full or bulk-logged recovery models only).
When the log backup is completed, the head of the log is moved forward, and some log space might become reusable.
Note Log backups do not prevent truncation.

3
ACTIVE_BACKUP_OR_RESTORE
A data backup works like an active transaction, and, when running, the backup prevents truncation

4
ACTIVE_TRANSACTION
A transaction is active (all recovery models).
A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. For more information, see "Long-Running Active Transactions," later in this topic.

5
DATABASE_MIRRORING
Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only).

6
REPLICATION
During transactional replications, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only).

7
DATABASE_SNAPSHOT_CREATION
A database snapshot is being created (all recovery models).
This is a routine, and typically brief, cause of delayed log truncation.

8
LOG_SCAN 

A log scan is occurring (all recovery models).
This is a routine, and typically brief, cause of delayed log truncation.

9
OTHER_TRANSIENT 

This value is currently not used.

Source: MSDN Blog

My Linked-in;

#Change database Schema to your choice.

#Schema change

#Quick Trick#2

Today I got you another trick to generate schema change commands very very quickly and smartly.

We had a request to restore a database from Dev environment and change the schema on the destination server with some XYZ schema.

Now to generate the schema alteration command for all the tables functions etc. it would take proportional time with the size of database.

Remember Quick trick1 once we used to generate the command to change recovery model of all the databases on instance click here to see more . We used the same logic and generated the commands that I am sharing with you.

--This is the script I used to generate ALTER statements for all objects
-- the schema for each object in cc was changed to cx.
--please note the case for case sensitive servers.
--U=user tables,
-- P=Stored Procedures
--V=VIEW
--'FN', 'FS', 'FT'=Functions

SELECT 'ALTER SCHEMA CX TRANSFER [' + SysSchemas.name + '].[' +DbObjects.Name+ '];', SysSchemas.name,DbObjects.type,DbObjects.type_desc
FROM sys.objectsDbObjects
INNER JOIN sys.schemasSysSchemasONDbObjects.schema_id=SysSchemas.schema_id
WHERE SysSchemas.name = 'CC'
AND (DbObjects.typeIN ('U', 'P', 'V', 'FN', 'FS', 'FT'))
order bytype_desc, name

This will generate the Alter commands and you are good to execute.
For some you may get error but find out in the object type it must be already present, so you need to delete. 

Thankful: To Mahboob Haidari: (Expert SQL DBA L3++ ( more than that may be L5) from Downers grove Illinois,US)  for this knowledge.

NOTE; Do comment and let me know any topic you wish to see and your feedback is always welcome.

Altering TSQL to get codes where ever possible

Altering TSQL to get codes where ever possible;

#Generate quick commands 

#SQL code Trick

Quick Trick #1;

This is a Quick trick that I recently learned from one of my teammate, that I would like to share with you all.

We must have come across a lot of situation where we need to run multiple commands to get our work done, But we have to repeat the task multiple times to complete the task.

To simplify we will learn how to generate the queries from command itself that gives you upper hand on the task and smart working advantage.

Scenario:

We had to alter all the databases to simple recovery model from full since these were restored from the PROD server all were in full with agreed SLA.
So instead of running and altering the databases command we generated it with a trick.

Command;

select'alter database '+name+' set recovery simple',recovery_model_desc,*from sys.databases where database_id>4

/*-- Database_id>4 is to select only the user databases. --*/

Result :

alter database ReportServer set recovery simple

alter database ReportServerTempDB set recovery simple

Do let me know if this is useful to you. Comment and suggest topics for blog you wish to see.
Share with your DBA friends and help them work smartly.

Knowledge Shared by: Pramod Thikekar: SQL DBA with great 5 yrs of great experience.