SAFE : Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry

EXTERNAL_ACCESS: These assemblies have the same permissions as SAFE assemblies, with the additional ability to access external system resources such as files, networks, environmental variables, and the registry

UNSAFE: unrestricted access to resources, both within and outside SQL Server. Code executing from within an UNSAFE assembly can also call unmanaged code.

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.

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.

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.

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.

SQL server added new function Compress and Decompress scalar valued function that take char/varchar as input and output a GZIP compressed varbinary data. This feature allows us to use column level compression of char/varchar data and reduces the cost of IO.

we can see we achieved almost 99% please note these results varies when data is stored in a table (row in flow)

Lets look at this in a detail example.

IO and time test

Summary of comparision matrix between compress function and regular table we achieve 99% savings on storage on certain situations and achieve even more space when used in conjunction with row and page compression.