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);
DECLARE @idx int = (select max(isnuLL(col1,0))+1 from t3);
WHILE @idx < 1000000
    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


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
,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
,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
,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:



Non Clustered or Clustered Index

Non Clustered or Clustered Index



Sponsored Post Learn from the experts: Create a successful blog with our brand new courseThe Blog is excited to announce our newest offering: a course just for beginning bloggers where you’ll learn everything you need to know about blogging from the most trusted experts in the industry. We have helped millions of blogs get up and running, we know what works, and we want you to to know everything we know. This course provides all the fundamental skills and inspiration you need to get your blog started, an interactive community forum, and content updated annually.

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.







void setup() {
// put your setup code here, to run once:

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
// print out the value you read:






FIX[solved]:Unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime, Fatal error: cannot create ‘R_TempDir’

I was trying out new R feature in SQL 2016 and encountered this error so I thought I would document this issue.

Resolution steps:

  1. Locate rlauncher config file: (this is used by the MSSQL Launch pad service)
    1. Its normally located in the Binn folder which is available in instance root directory in my case D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn
  2. View the contents of rlauncher and check the working directory which is where we are having issues creating temp dir.
    1. R does not like spaces in the working directory

R launcher 1






3. Create a folder in C:\ called temp “C:\temp”

4. Change the working directory to c:\temp


R launcher 2





5. Grant permissions for the service MSSQLaunchpad service account in my case its NT Service\MSSQLaunchpad

R launcher 3













Restart the SQL server services and SQL Launch pad services.


We can also fix this by using short paths by replacing the working directory with a short path

Eample below cmd will give us the short path for the location

cmd /c for %A in (“D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData”) do @echo %~sA


Fix: An TLS 1.0 connection request was received but none of the cipher suite support and SSL connection request has failed

If you encounter below error when using SQL Server and TLS protocol in conjunction with SSL .There is setting in the Group policy under Administrative Template/ Network / SSL Configuration Setting this should be disabled or left not be configured

Error :
An TLS 1.0 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The SSL connection request has failed.
A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS protocol defined fatal error code is 40. The Windows SChannel error state is 1205.

Changes required to fix this.

RUN -> gpedit.msc-> Administrative Template-> Network -> SSL Configuration-> SSL Cipher Suite Order-> Disable

FIX: TDSSNIClient initialization failed with error 0x80092004,SQL Server could not spawn FRunCommunicationsManager thread

How to fix

TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. 
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error.
Could not start the network library because of an internal error in the network library.

SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.


Make below registry changes.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server

Make sure TLS 1.0 Server and SSL3.0 Server are enabled


CHeck ssl


Check these links as well

Great post on how to setup TLS

Make sure you check the certificate is setup properly.

SQL SSL Check ( Use below link to check validity of the SSL available on the SQL Server)

Zip file contains an .exe file download and run in cmd window.


Convert Data Table to HTML Table with PowerShell and more

##function to convert any datatable to html table

function Convert-DatatablHtml ( $dt)


$html = "<!DOCTYPE html>
table, th, td {
    border: 1px solid black;
    border-collapse: collapse;
<table border=""1"" 

$hmtl +="<tr>"
for($i = 0;$i -lt $dt.Columns.Count;$i++)
$html += "<td>"+$dt.Columns[$i].ColumnName+"</td>"  
$html +="</tr>"

for($i=0;$i -lt $dt.Rows.Count; $i++)
$hmtl +="<tr>"
 for($j=0; $j -lt $dt.Columns.Count; $j++)
  $html += "<td>"+$dt.Rows[$i][$j].ToString()+"</td>"
 $html +="</tr>"

$html += "</table>

return $html


##How it works 

##creating a sample databtale to test
$dt = New-Object "System.Data.DataTable"


$dtnew = $dt.NewRow();
$ =1;
$dtnew = $dt.NewRow();
$ =1;

##Converting data table to html

$h = Convert-DatatablHtml -dt $dt 


##writing html table to a html file 

$h |out-file "c:\temp\test.html"

##Send an email with with formated html 

Send-MailMessage  -to "" -SmtpServer "smtpserver" -body $h -Subject test -From "" -BodyAsHtml 

Remove old backup files with PowerShell

Note: script looks at when file was last modified and also removes if any file exists in sub folders recursively.

$extension ="*.bak"

Get-ChildItem -Path $path -Filter $extension  -Recurse  | 
Where-Object{ $_.lastwritetime -lt (get-date).adddays(-$NumDaysToRetain)}  | 
ForEach-Object { Remove-item $_.FullName; write-host "File" $_.FullName "deleted"}

SQL 2016 DB engine features Compress and Decompress functions

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.

sql2016 compress

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.

sql2016 compress2


sql2016 compress3



IO and time test

sql2016 compress4


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.

sql2016 compress5

Working with data frames in R-1

fix: replacement has X row, data has X



Error in `$<`(`*tmp*`, “y”, value = 1) :
replacement has 1 row, data has 0

Trick here we should consider x as an dimensional matrix, so to work around this error we can use below methods to assign values to a data frame column

> x[1,"y"] <-1
> x[2,"y"] <-1
> x
1 1
2 1


x[1:10,“y”] <- 1:10

1   1
2   2
3   3
4   4
5   5
6   6
7   7
8   8
9   9
10 10


[1] "data.frame"


for(i in 10:20){ x[i,“y”]<- i}

1   1
2   2
3   3
4   4
5   5
6   6
7   7
8   8
9   9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20



Powershell To Deploy Sql scripts in a Directory

Source Code

  [Parameter(Mandatory=$True,ValueFromPipeline=$true,HelpMessage="Enter Source Instance")]
  [ValidateScript({ IF( $_ -eq $null) 
		{$false }  
	    elseif ($_.ToString().Trim().Length -eq  0 )
	    else {$true}} )]
  [Parameter(Mandatory=$True,ValueFromPipeline=$true,HelpMessage="Enter Source Source")]
  [ValidateScript({ IF( $_ -eq $null) 
		{$false }  
	    elseif ($_.ToString().Trim().Length -eq  0 )
	    else {$true}} )]


## Add Assemblies
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=, Culture=neutral, 
PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
Write-host $TargetInst $Targetdb $targetdir
$sourcedbs = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $TargetInst 
$sourcedbs.ConnectionContext.StatementTimeout = 0
$db = $sourcedbs.Databases[$TargetDB]
$files = Get-ChildItem -Path "$Targetdir"  -Recurse  | Where-Object{$_.Name -Like "*.sql"} | select FullName
Foreach($file in $files) 
$query= Get-Content $file.FullName | Out-String ; 
		Write-Host $file.FullName "Query Finished"     
      catch [Exception]

		Write-Host $_.Exception.GetType().FullName, $_.Exception.Message


How to use it

.\ExecuteSqlscripts.ps1 ServerName DatabaseName Directory

Note: it does a recursive check if there any folders it gets them all as well.