Clone your SQL Database instantly with new DBCC command CLONEDATABASE

 

Based on MS documentation this is a quick way to create a copy of database (only Schema) including statistics and Indexes of source database this was released in SQL 14 service pack 2.

When this command is issued SQL Server creates an internal snapshot of source database just like how it creates for checkdb and drops this snapshot when the cloning process is done but during the cloning process it holds a shared lock on source database and X lock on target database and it leaves target database in read only mode although you can change the state of the target database if you intent to add data or modify.

So why do you need to clone database.

According to MS DBCC CLONEDATABASE should be used to create a schema and statistics only copy of a production database in order to investigate query performance issues.”. The original intention of the feature is to diagnose any performance issues of a production database with out needing to effect the production database. Although this is so late in the game its never to late for new feature.

Don’t confuse with Database Snapshots that’s totally different concept.
dbcc clonedatabase([AdventureWorks2014],[AdventureWorks2014_Clone])

 

dbcc clone 1

 

dbcc clone 2

https://support.microsoft.com/en-us/kb/3177838

Bottom line: Saves a lot of time when you are debugging execution plans and performance related issues.

High CPU Usage SQL Server (One Bad Query)

My colleague reported to me that one of our database server is reporting consistent high CPU usage so I looked at it I noticed CPU was at 100% from last one week when I contacted the application owner and I foundthat they implemented a new feature that polls the database for every second to ensure the data collection process is running properly as it was necessary to ensure that we are under compliance in terms of reporting and auditing. So I ran a query to pull the queries with high cpu utilization with execution count. I certainly noticed a query running more often with high cpu usage.

 

Exec stats

I know that above highlighted query is causing the high cpu usage, next I looked at query stats and noticed this query is running twice every second, so I looked at the plan

Select top 1 col1 from table order by 1

Table is clustered and col1 is not part of clustered index and does not have an index. simple enough SQL server decides to do Clustered index scan and sorts(fully blocking) col1 and selects 1 row with no predicate SQL server doesn’t think its missing an index.

PLan 1

PLan 1 properties .jpg
SQL Server Execution Times: with out non clustered index
CPU time = 2296 ms, elapsed time = 658 ms.

So I created a non clustered index on col1 in desc on the table

plan 2.jpg
SQL Server Execution Times: with non clustered index
CPU time = 0 ms, elapsed time = 0 ms.

Cpu Usage dramatically reduced

Cpu usage.jpg

Bottom line:

Its very important to understand no matter how much physical resources you might have on a  server its very important understand that one bad query can literally bring the server down to it knees.

 

Search a Column from a table in SQL Database

SELECT
SCHEMA_NAME(schema_id)+'.'+[name] as objectname
,type_desc
,referenced_schema_name AS SchemaName
,referenced_entity_name AS TableName
,referenced_minor_name  AS ColumnName
FROM [sys].[all_objects] ob cross apply sys.dm_sql_referenced_entities ( SCHEMA_NAME(schema_id)+'.'+[name], 'OBJECT') e
where is_ms_shipped = 0 and type_desc in ('AGGREGATE_FUNCTION'
,'SQL_SCALAR_FUNCTION'
,'SQL_INLINE_TABLE_VALUED_FUNCTION'
,'SQL_STORED_PROCEDURE'
,'SQL_TABLE_VALUED_FUNCTION'
,'SQL_TRIGGER'
,'VIEW')
and name !='sp_upgraddiagrams'
and referenced_entity_name  = 'table name'
and referenced_minor_name = 'columnname'

Download free Microsoft EBooks Including: Windows 10, Windows 8.1, Windows 8, Windows 7, Office 2013, Office 365, SharePoint 2013, Dynamics CRM, PowerShell, Exchange Server, Lync 2013, System Center, Azure, Cloud, SQL Server

https://blogs.msdn.microsoft.com/mssmallbiz/2015/07/07/im-giving-away-millions-of-free-microsoft-ebooks-again-including-windows-10-windows-8-1-windows-8-windows-7-office-2013-office-365-sharepoint-2013-dynamics-crm-powershell-exchange-se/

 

 

Aurdino & Servo with Potentiometer

Servo Potentio meter Sketch_bb1

#include <Servo.h>
Servo myservo;
void setup() {
Serial.begin(9600);
myservo.attach(5);
pinMode(A0,INPUT);
}

void loop() {
// put your main code here, to run repeatedly:
int sensorValue = analogRead(A0);
myservo.write(map(sensorValue, 0, 1023, 0, 180)); // tell servo to go to position in variable ‘pos’
Serial.println(sensorValue);
delay(1);
}

 

 

 

 

Comparing sys.dm_db_index_physical_stats mode levels with Heap and Clustered Index

Create a table with some data

drop table t3
CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);
GO
--BEGIN TRAN
DECLARE @idx int = (select max(isnuLL(col1,0))+1 from t3);
WHILE @idx < 1000000
BEGIN
BEGIN TRAN
    INSERT INTO t3 (col1, col2) 
    VALUES (@idx+convert(int,rand() * 100), 
    REPLICATE ('a', 100) + CAST (@idx as varchar(10)) + REPLICATE ('a', 380))
    SET @idx = @idx + 1
	COMMIT
END
--COMMIT;
GO

 

Check Fragmentation using sys.dm_db_index_physical_stats

 

SELECT 'Limited' as Mode,page_count, compressed_page_count as cm_page_count,index_type_desc
,index_depth,index_level,avg_fragmentation_in_percent,record_count
,fragment_count,object_name(object_id) as Object_name
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'LIMITED');

SELECT 'Sampled' as Mode,page_count, compressed_page_count as cm_page_count
,index_type_desc,index_depth,index_level,avg_fragmentation_in_percent,record_count
,fragment_count,object_name(object_id) as Object_name
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'SAMPLED');

SELECT 'Detailed' as Mode,page_count, compressed_page_count as cm_page_count
,index_type_desc,index_depth,index_level,avg_fragmentation_in_percent,record_count
,fragment_count,object_name(object_id) as Object_name
FROM sys.dm_db_index_physical_stats (db_id(),
object_id ('t3'), null, null, 'DETAILED');

Fragmentation output

Mode: Limited

  • Only looks at leaf level pages
  • Checks the logical fragmentation at leaf level (Out of Order pages)/ out-of-order extents in the leaf pages of a heap
  • Doesn’t look into the actual pages.

Mode: Sampled

  • Only looks at leaf level pages
  • Checks the logical fragmentation at leaf level (Out of Order pages) out-of-order extents in the leaf pages of a heap
  • Scans a sample of leaf pages (10 % approximately)  it may randomly these pages.

Mode: Detailed

  • Does what limited mode normally do but scans every page in the index including the intermediate and root pages.
  • Check the logical fragmentation at every level of Index (Out of Order pages)/ out-of-order extents in the leaf pages of a heap
  • Scans the entire index.

Detailed mode is more accurate than any other mode because it scans every page of the index but this  might create some performance bottle neck. It depends on where each of this mode can be used if an index with high fanout (index key length) which is directly proportional to the number of levels , it makes sense to check the fragmentation at intermediate levels using detailed mode at least once a week as part of Index Maint plan.

 

Terms : 

  •  Out of Order pages : An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.
  • Out-of-order extent: An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

 

B-Tree Index Structure:

Heap:

Heap

Non Clustered or Clustered Index

Non Clustered or Clustered Index

 

 

Aurdino based led dimmer

Goal: Control the brightness of the led using Aurdino and a potentiometer.

We achieve this by reading output potentiometer using analogRead with output range between 0 to 1023 and convert this into analog signal between 0 to 255, to do this we use a map function to transform the values 0 to 1023 to 0 to 255 and write to a pwm pin no 5.

IMG_7144

Capture

 

Capture2

 

Snap

void setup() {
// put your setup code here, to run once:
Serial.begin(9600);
pinMode(5,OUTPUT);
pinMode(A0,INPUT);
}

void loop() {
// put your main code here, to run repeatedly:
int sensorValue = analogRead(A0);
int outputValue = map(sensorValue, 0, 1023, 0, 255);
//Write Output value to pwm pin 5
analogWrite(5,outputValue);
// print out the value you read:
Serial.println(sensorValue);
delay(1);
}