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/

 

 

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);
}

 

 

 

 

 

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
TLS1.0

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

https://thedataspecialist.wordpress.com/2013/03/12/using-a-self-signed-ssl-certificate-with-sql-server/

http://kkaminsk.blogspot.com/2014/08/problems-starting-sql-server-unable-to.html

http://blogs.msdn.com/b/john_daskalakis/archive/2014/10/15/the-server-could-not-load-the-certificate-it-needs-to-initiate-an-ssl-connection-it-returned-the-following-error-0x8009030d-check-certificates-to-make-sure-they-are-valid.aspx

http://kalcik.net/2014/04/04/problems-with-the-installation-of-sql-server-2014-after-the-windows-8-1-update/

http://mssqlwiki.com/tag/and-it-is-not-possible-to-create-a-self-signed-certificate/

 

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.

https://drive.google.com/file/d/0B_77YgBEyf5mQXh4WEcxLWtCcnc/view?usp=sharing