Uncategorized

Can you disable the CEIP Service?

Yes – if you’re using SQL Server Enterprise Edition or Standard Edition

no –if you’re using SQL Server Developer Edition or Express Edition

The CEIP Service is the Customer Experience Improvement Program that sends telemetry data back to Microsoft. you can’t disable the service but you can make it inactive by providing the wrong credentials to the service.

When to use Snapshots/use cases for database Snapshot SQL server

A database snapshot provides a read-only, static view of a source database.

Use cases:

  1. When performing updates on table (drop / structure changes).
    • For example, before doing major updates, such as a bulk update or a schema change, create a database snapshot on the database protects data. If you make a mistake, you can use the snapshot to recover by reverting the database to the snapshot. Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward.

Why? It takes very minimal disk space and restoration is fast to rollback the changes. When the databases are huge if you want to go with traditional backup and restore methods, the rollback will take hours. We can eliminate the downtime with the Snapshots.

2. Snapshots can be used for reporting purposes, but they will have the date till the time of snapshot creation only.

Note:  Snapshots do not protect against disk errors or data corruption. If the source database becomes corrupt or unavailable for any reason all snapshots also become unavailable. So, snapshots are not a substitute for backups.

Testing Snapshot:

–creating a new database
Create database snaps

— creating table
use snaps
create table st (id int, dates date)
insert into st values ( 1, getdate()),(2,getdate())

create table st2 ( id int, dates datetime)
insert into st2 values ( 1, getdate()),(2,getdate())

–select data
use snaps
select * from st
go
select * from st2

— get the file info of snaps database
sp_helpdb ‘snaps’

— creating snapshot on DB
create database snapshot_snaps on
( name =’snaps’ — name is the logical filename fo the snaps.mdf file
,filename=’D:\SQLBackup\snaps.ss’)
as SNAPSHOT OF snaps; — snaps is the source db name
go

–verify the sparse by going into the properies of a snapshot file and db file

–Drop the table on snaps database
use snaps
go
drop table st

— read the sanpshot databse
use snapshot_snaps
select * from st
select * from st2 — even if the source table is not exit, we can access the table from the snapshot
— restore from the snapshot
use master
go
restore database snaps
from Database_snapshot = ‘snapshot_snaps’

— drop sanpshot
drop database snapshot_snaps

AUTO_UPDATE_STATISTICS_ASYNC TRUE vs AUTO_UPDATE_STATISTICS

when we enable AUTO_UPDATE_STATISTICS =TRUE we also need to enable AUTO_UPDATE_STATISTICS_ASYNC =TRUE. otherwise the query will wait until statistics got updated and it leads to query timeouts / slow down the execution process.

when Auto_update_statistics is set the Query optimizer will automatically update the statistics when statistics are stale and the query execution will be “held” until the statistics got updated.

When the Asynchronous setting is set the query will run like it is until all statistics its uses are up-to-date, then it will run with the new numbers. It does not have to wait for all the new numbers to be updated to run. That’s where you get your performance boost, by not having to wait.

Cannot connect to proxy.’, OSError(‘Tunnel connection failed: 403 Forbidden’,)

"msg": "ntlm: HTTPSConnectionPool(host='199.199.11.xx', port=5986): Max retries exceeded with url: /wsman (Caused by NewConnectionError('<urllib3.connection.HTTPSConnection object at 0x7fc3027842b0>: Failed to establish a new connection: [Errno 111] Connection refused',))",

when I tried to ping a Windows host from the Ansible server got the above error, was able to run the ping after using the option

export no_proxy=’*’

Auto Update Statistics Asynchronously – SQL Server 

It is recommended to disable the AUTO_UPDATE_STATISTICS_ASYNC option when your SQL Server executes operations that significantly change the distribution of your data, like truncating a table or running bulk updates on a large percentage of the rows. Because it uses stale execution plans and leads to performance issues when we have bulk updates on the table.