UPDATE STATISTICS - What/When to RUN?

When and what to run Update Statistics

In terms of updating statistics, have multiple options, including:

1. Update Statistics Task (Maintenance Plan)
2. sp_updatestats (DO NOT use this option and not accurate)
3. UPDATE STATISTICS

There are two kinds of statistics:
1. Index Statistics
2. Column Statistics

1) By default, the UPDATE STATISTICS statement updates both index and column statistics.

•      Using the COLUMNS option of this statement will update column statistics only.
•      Using the INDEX option will update index statistics only.

2) By default, the UPDATE STATISTICS statement uses only a sample of records of the table.

•     Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

3) Rebuilding an index, for example by using the ALTER INDEX … REBUILD statement,

•    Will update only index statistics with the equivalent of using WITH FULLSCAN.
•    Rebuilding indexes does not update any column statistics.

4) Reorganizing an index, for example using the ALTER INDEX … REORGANIZE statement,
     does not update any statistics.


Maintenance Tasks:
----------------
Use below 2 tasks in maintenance plan in any order to update index statistics and column statistics.

1.Rebuild index  --Rebuilt Index and update index stats
  ----------------
  1.Drop and re-create indexes in single transaction.
    (This removes fragmentation and reclaims DISK Space by compacting pages based on FILL factor value.
     Re-order index rows in contiguous pages)
  2.This operation can be ONLINE or OFFLINE
  3.Update INDEX statistics only with FULL SCANNING all the rows in table.
  4.DOES NOT update Column statistics

2.Update Statistics: COLUMN statistics
----------------------
  UPDATE STATISTICS with COLUMNS option of this statement will update column statistics only.

Additional notes:

1. If Avg Fragmentation level 5 percent greater then and less then 30 percent  ---Use Reorganize Index
2. If Avg Fragmentation level  greater then 30 percent  ---Use Rebuild index

sp_updatestats: 
Do not use sp_updatestats, this will not give 100% correct statistics.

The sp_updatestats command will only update statistics if data has changed,but the caveat is that only one (1) row has to have changed.

<30 index="" nbsp="" p="" reorganize="" use="">

Customer Experience Improvement Program (CEIP) service - How to Disable

If we Install SQL Server 2016 automatically turns on all "CEIP" service.
CEIP: Customer Experience Improvement Program
SQL CEIP is only used for usage data collection. It is not used for any other purpose.
These CEIP elements report back to Microsoft about various aspects of your installation experience as well as your feature usage.

WHY CEIP?
incident Management (CRIs, LSIs)
Alert management (proactive approach)
Automated management via bots (based on alerts)
Machine learning / data science
Investigating potential new features that can benefit a maximum of clients

Steps :
How to Deactivate the CEIP
How to set all CEIP registry keys to 0

How to configure SQL Server 2016 or later to send feedback to Microsoft :  
Click MS Link for more details

(1) How to Deactivate the CEIP
----------------------------------------
CEIP is present for 3 SQL server services:
DEngine: SQL Server CEIP service
SSAS:    SQL Analysis Services CEIP
SSIS:    SQL Server Integration Services CEIP service 13.0

1.Run PowerShell as Administrator and run these following command to have a status of these services
Get-Service |? name -Like "SQLTELEMETRY*" | select -property name,starttype,status
Get-Service |? name -Like "SSASTELEMETRY*" | select -property name,starttype,status
Get-Service |? name -Like "SSISTELEMETRY*" | select -property name,starttype,status

OR
--This will display all telemetry services(CEIP)
Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status

Stoop services
Get-Service |? name -Like "*TELEMETRY*" | ? status -eq "running" | Stop-Service
Disable services
Get-Service |? name -Like "*TELEMETRY*" | Set-Service -StartMode Disabled

Step by step  scripts:
# Disable CEIP services  #
Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status
# Stop all CEIP services
Get-Service |? name -Like "*TELEMETRY*" | ? status -eq "running" | Stop-Service
Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status
# Disable all CEIP services
Get-Service |? name -Like "*TELEMETRY*" | Set-Service -StartMode Disabled
Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status

OR
--All in once script (All CEIP services will stop and disable)
# Disable CEIP services  #
Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status
Get-Service -name "*TELEMETRY*" | Stop-Service -passthru | Set-Service -startmode disabled
Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status

(2)Set all CEIP registry keys to 0
----------------------------------------
This step is more complex because we have a lot of registry keys. Two parameters have to be set to 0:
CustomerFeedback
EnableErrorReporting

Example: registry keys
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\130\CustomerFeedback=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\130\EnableErrorReporting=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS13.MSSQLSERVER\CPE\CustomerFeedback=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS13.MSSQLSERVER\CPE\EnableErrorReporting=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS13.MSSQLSERVER\CPE\CustomerFeedback=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS13.MSSQLSERVER\CPE\EnableErrorReporting=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\CPE\CustomerFeedback=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\CPE\EnableErrorReporting=0
HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\130\CustomerFeedback=0
HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\130\EnableErrorReporting=0
Note: Search-and-replace MSSQLSERVER with your instance name.
      If any of the keys don't exist, then that's OK, because you apparently didn't install that feature.


To set all these keys to 0, I use “simply” PowerShell Commands:
#  Deactivate CEIP registry keys #
# Set all CustomerFeedback & EnableErrorReporting in the key directory HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server to 0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\***\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\***\EnableErrorReporting=0
# *** --> Version of SQL Server (100,110,120,130,140,...)
# For the Engine
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL**.\CPE\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL**.\CPE\EnableErrorReporting=0
# For SQL Server Analysis Server (SSAS)
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS**.\CPE\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS**.\CPE\EnableErrorReporting=0
# For Server Reporting Server (SSRS)
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS**.\CPE\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS**.\CPE\EnableErrorReporting=0
# ** --> Version of SQL Server (10,11,12,13,14,...)
##################################################
$Key = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server'
$FoundKeys = Get-ChildItem $Key -Recurse | Where-Object -Property Property -eq 'EnableErrorReporting'
foreach ($Sqlfoundkey in $FoundKeys)
{
$SqlFoundkey | Set-ItemProperty -Name EnableErrorReporting -Value 0
$SqlFoundkey | Set-ItemProperty -Name CustomerFeedback -Value 0
}
##################################################
# Set HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\***\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\***\EnableErrorReporting=0
# *** --> Version of SQL Server(100,110,120,130,140,...)
##################################################
$WowKey = "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server"
$FoundWowKeys = Get-ChildItem $WowKey | Where-Object -Property Property -eq 'EnableErrorReporting'
foreach ($SqlFoundWowKey in $FoundWowKeys)
{
$SqlFoundWowKey | Set-ItemProperty -Name EnableErrorReporting -Value 0
$SqlFoundWowKey | Set-ItemProperty -Name CustomerFeedback -Value 0
}

Note: As you can see,  use only the EnableErrorReporting key in the Where-Object clause to find the impacted keys. After running this script we have all CEIP registry key set to 0

SQL Server 2017 Installation on Linux steps

SQL Server 2017  Installation on Linux and follow below steps

SQL download and install
*************************
$ curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo

sudo yum install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup

$ sudo systemctl enable mssql-server
$ sudo systemctl start mssql-server


$ sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
$ sudo firewall-cmd --reload

$ rpm -qa | grep mssql


--Use the following command to view the status of the SQL Server service.
$ sudo systemctl status mssql-server -l


*******************************
SQLCMD/BCP install

$ sudo su
# curl https://packages.microsoft.com/config/rhel/7/prod.repo >
/etc/yum.repos.d/prod.repo
# exit

$ sudo yum -y install mssql-tools

*************************
sqlcmd Connection

$export PATH=$PATH:/opt/microsoft/bin
$export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/lib64
$/opt/mssql-tools/bin/sqlcmd -H -U -P  

Powershell script to re-start SQL Server services for multiple servers

Step1: Create folder D:\Test\
Step2:Open notepad and add all servers one after one as per below in "D:\Test\ServersList.txt" file
Server1
Server2
Server3
...etc

Step2: Create Test.ps1 powershell script file with below code in "D:\Test" folder
D:\Test\Test.ps1 file code:

Get-Content D:\Test\ServersList.txt | % {

 $Server=$_

 Write-Host  "Server Name " : $Server

If(Test-Connection $Server -Quiet)
{

 $Srv=Get-Service -Name MSSQLSERVER -ComputerName $Server

  If ($Srv.Status -eq "Running")
   {
Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Stop-Service -Verbose -Force
Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Select name, status

Get-Service -ComputerName $Server -Name MSSQLSERVER | Stop-Service -Verbose -Force
Get-Service -ComputerName $Server -Name MSSQLSERVER | Select name, status

Get-Service -ComputerName $Server -Name MSSQLSERVER | Restart-Service -Verbose -Force
Get-Service -ComputerName $Server -Name MSSQLSERVER | Select name, status

Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Restart-Service -Verbose -Force
Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Select name, status

    } Else {
      Write-Host  "SQL Server Service not running"
    }
 } else {
      Write-Host  "Failed to connec to servers:" $Server
  }| Export-Csv D:\Test\FailedConnLog.txt -NoTypeInformation
} | Export-Csv D:\Test\OutputLog.txt -NoTypeInformation


OR
$SList= Get-Content "D:\Test\ServersList.txt"
ForEach ($Server in  $SList)
{
  Write-Host $Server
#### Write code here ######
}

Command to execute or schedule in job:
D:\\powershell.exe -Noninteractive -Inputformat none -Executionpolicy Bypass -Command D:\Test\Test.ps1

SQL Server 2016 -- New features

New Features in SQL Server 2016
------------------------------------------------
Always Encrypted
Dynamic Data Masking
JSON Support --Java Script Object Notation
Multiple TempDB Database Files
PolyBase
Query Store
Row Level Security
In-database R language support: Microsoft acquired Revolution Analytics
       --R Comes to SQL Server-- advance analytics Big data & SQL Server
Stretch Database
Temporal Table
In-Memory Enhancements --supporting foreign keys, check and unique constraints and parallelism.                                               -- Tables up to 2TB are now supported.
Parallel Data Warehouse Appliance, for more easily managing relational and non-relational data
Enhanced server management for Master Data Services
Enhanced hybrid backup to Azure and faster restores to SQL Server in Azure virtual machines
Improvements on AlwaysOn Availability Groups:
  --3 sync replicas, up from 2 in SQL 2014.
  --Listener will be able to do round-robin load balancing of read-only requests on multiple secondaries
Improvements on Columnstore Indexes



Additional details: sql server 2016 Features

CSV file data load into SQL Server table Using BCP and schedule job

Using BCP, CSV file data load into SQL Server table

--1.Create table "BCPDataLoad" with required columns
example:
CREATE TABLE BCPDataLoad
(
id int,
Name varchar(20)
);

2.LoadData.txt
--Loadable data keep in "E:\Data\LoadData.txt" file
--data should be id,name columns wiht comma separately
example
id,name
11,aaaa
22,bbb
33,ccc

3.Create BAT file as "BCPLoad.bat" with below
(OPen notepad and copy paste below script and save as
 file name: BCPLoad.bat
 save as type: allfiles

--BCPLoad.bat
set log=E:\Data\BCPLoad_Log.txt

echo %date%. %time% before BCP data load >>%log%

REM  change the Database_Name" below as your actual database name

bcp Database_Name.dbo.BCPDataLoad_Name in E:\Data\LoadData.txt -c -t, -S ServerName -T >>%log%

echo %date%. %time% After BCP data load >>%log%

4.Schedule job using SQL Server agent job or any 3rd party tools for "BCPLoad.bat" file

OR
Manually run BAT file: You can run manually "BCPLoad.bat" file,
   go start ,right click run "cmd" enter,cd "E:\Data" (go to e drive with data folder)
   in commandline window E:\Data\BCPLoad.bat hit enter, then the data load will finish.

5.Check the logs in "E:\Data\BCPLoad_Log.txt" location
6.Check the data load in "BCPDataLoad" table
   select * from BCPDataLoad

output : should be
id,name
11,aaaa
22,bbb
33,ccc

Batch script -- using FOR loop for multiple servers list to run SQL script

1.Save all servers in "Serverlist.txt" file in "E:\batch" folder.
example: Serverlist.txt
server1
server2
server3

2.Save "sqlquery.sql" file in "E:\batch" folder

3.Save below script as Test.bat in "E:\batch" folder

set log=E:\batch\Test_log.txt
echo start for loop >>%log%
for /F "tokens=*" %%S in (E:\batch\Serverlist.txt) do (
e:\ give here sqlcmd location\sqlcmd -E -S %%S -i "E:\batch\sqlquery.sql"  -s "," -h-1 -W >>"E:\batch\output.csv"
IF NOT ERRORLEVEL 0 GOTO :ERR
)
echo End for loop>>%log%
echo !Success. sqlcmd ok. >>%log%

GOTO :EOP

:ERR
echo !Error. sqlcmd failed >>%log%

:EOP

4.Output will store in "E:\batch\output.csv" file.

Note:Include SET NOCOUNT ON and SET NOCOUNT OFF in your "sqlquery.sql" to avoid info message in your output.