A closer look at everything related to SQL Server

I am hearing a lot about GDPR (General Data Protection Regulation) in past few months. I decided to do some digging up to know how it affects us in the United States. Those of you who directly want to go to the source of my information and avoid my 2 cents are welcome to check out these 2 links. This Law is explained in very easy terms for a layperson.

https://www.red-gate.com/simple-talk/opinion/opinion-pieces/questions-gdpr-shy-ask/?utm_source=simpletalk&utm_medium=pubemail&utm_content=20171121-slota2&utm_term=simpletalkmain

https://www.csoonline.com/article/3202771/data-protection/general-data-protection-regulation-gdpr-requirements-deadlines-and-facts.html

Those of you who are short on time and want to just get the skinny version, then keep on reading.

  1. This law is about protecting European Citizens from data breaches of companies inside and outside Europe
  2. This Law may affect us if and when we store data of EU citizens.
  3. This is for EU consumer safety.
  4. In case of a data breach, the companies even outside EU can become part of the lawsuit.
  5. To avoid the lawsuit, a USA based company have to provide the EU country representative with a notification within 3 days of a data breach.
  6. Companies can be fined if the governing body finds the proof that adequate data protection measures were not taken by the company whose data was breached.

It is good to keep this EU law on our radar even if our company is not storing EU consumer data. I think something like this law will become the norm in next 5 years across the global communities. After all, it is the responsibility of the company who collects and stores consumer data to provide safeguards to protect the data. The basic rationale of this law is simple. Since companies make a profit by using a consumer information, it is fair that they should spend a part of that profit to protect that information. This provides clear accountability on one hand and provides peace of mind to the citizens on the other hand. In my opinion, this is a win-win situation.

I would appreciate your comments on this topic.

Advertisements

What you like to LEARN?

Here I am putting down a simple method of removing SQL Server 2016 named or default instance. I am assuming here that there are no user databases on this instance. I stopped all SQL  Server services before I started this uninstall process.

  1. To begin the uninstall process, go to Control Panel and then Programs and Features.
  2. Select Microsoft SQL Server 2016 and select Uninstall. Then click Remove. This starts the Remove SQL Server 2016 Wizard.

                                                              

  1. Setup Support Rules runs to verify your computer configuration. Since there is only one instance of SQL server 2016 on this machine, wizard selects it for you. Otherwise, you can use the drop-down menu to select your instance for removal. To continue, click Next.

4. After hitting NEXT, you will get Ready to Remove dialog box showing the summary of Engine and features that are going to be removed. Click Remove. The removal process will begin and it may take several minutes depending on your installation.

  1. If everything goes smoothly, you will receive a success message. You can go to the hyperlinked text file to see the report of the whole process of removal. Click Close to complete the removal of SQL Server 2016 instance.

In the end, you can refer to this TechNet document for more detailed instructions. https://technet.microsoft.com/en-us/windows/ms143412(v=sql.90)

 

IS IT ONE MORE BORING BLOG?

I am writing this blog to remind myself how the query processing work. It is a forgettable item because it is different from the way you write a query. To begin with let me write a simple query.

Select * from AdventureWorks2012.Person.Person

SIMPLE ANALYSIS

We have 3 elements in this simple query. First is the keyword “SELECT”. This keyword means you are retrieving data from the table (as oppose to inserting or updating data in a table). A Second element is “*” which denotes the list of columns in a table. The third element is the “FROM” clause which tells you to retrieve data from a particular table.

When SQL Server Query Optimizer receives this query it processes it in a logical order instead of a physical order. Most logical and cost-effective thing is to first check if the table exists or not. So the “FROM” clause will be processed first. Next, it checks for columns requested also exist or not. If a table exists and the columns requested also exist then Query Optimizer looks at “SELECT” keyword and retrieves the data for you.

Look at the direction of a blue arrow in the execution plan of this query. It shows, that Query Optimizer scans the table first (using the FROM clause) and then do the SELECT.

Logical query processing 1

This is how the flow diagram will look

Flow Diagram 1

Flow Diagram 1

Let’s add some more spice to this query.

Select * from AdventureWorks2012.Person.Person where Lastname = ‘Ford’

This query returns only 3 rows. “WHERE” clause in the query is filtering the table data on Lastname column. Query optimizer first checks the physical objects (tables and columns) exist and then see the filter and uses the index if there is one on the filtering column. Note the key_lookup operator in the below execution plan.  The reason is that the Non-Clustered index chosen in the Seek operation is not the covering index (remember we are using * ). In that case clustered index of the table is used to point to the data which generates key_lookup operator. It is an important consideration when you are doing query tuning. But for our purpose here it is not important. Another point to note is the size of arrows. Arrows are thin in this execution plan because the query is returning only 3 rows compared to previous plan where the query was returning about 20K rows.

Logical Query Processing 2

Logical Query Processing 2

Flow Diagram 2

Flow Diagram 2

Next Step:

Having a clear concept of how the query is logically processed will help you write more efficient and Optimizer friendly queries. In my next blog, I will take the learning done here and extend it to other query clauses such as GROUP BY and ORDER BY.

Yesterday I attend this session after work and took some notes. Sian is a great presenter and possesses wealth of information.

Topic: How Mind Works Under Stress

Presenter: Sian Beilock (Author of books Choke, How the body knows the mind)

Venue: Heller Auditorium Francis Parker School.

Notes

What happens when we fail to reach our potential? Do we feel like we are choking under the pressure? Why we fail to perform under pressure? Sian’s research is about finding techniques to overcome this choking effect and perform better.

Frontal cortex, front part of brain, is much bigger in humans than any other primates. It defines us as humans. This defines what we are capable of. FC communicates with other parts of the brain. Under stress FC stops this communication and results in weird consequences. During stressful situations, FC goes haywire but it does that in a predictable way. We have to find that pattern; Paralysis by Analysis.

What happens under stress situation is that we second guess our ability and pay detail attention to things that should be on autopilot. We become very conscious of ourselves. We start breaking down the details of our skills. Focusing on the outcome instead will give better results. All we want to do is sail rather than fail.

A Study was done at Cornell University with medical students. They divided the group of medical students of the same age and level into 2 groups. One who is preparing to take their board exams and one not. They measured the brain activities showing how the brain gets ready for board exam compared with other people not having exam. What changes in the brain? Study showed people who were excessively worried about the exam performed bad at critical thinking problems. Study also concludes that stress in one area of life effects other parts of the lives.

Now let us talk about what can we do help our brain perform at its peak.

  • People are more likely to get the answers if they walk away from the difficult problem for a while. This is taking a step back and view the problem differently. Mind resets and you get the connection back.
  • Sleep is an important for our life. Mind does not stop working even when we are sleeping but it works on its on things. It makes new connections from the information we gathered in our waking hours.
  • Talking out your problems to someone who does not know the answer will also help you solve the problem on your own.
  • Our emotions also messes with Frontal Cortex’s functions. When we are under pressure, FC does not keep the negative emotions away and gets us. In teens, FC is under developed, it completes in 20s.

fMRI, Functional MRI (Magnetic resonance Imaging) is one way of testing brain activities. It shows functioning of brain in real time. People who are nervous about Math problems were told they are going to solve a math problem. The part of brain that lights up when we are physically harmed lighted up for these people who were fearing Math problems. To them anticipating Math was as painful as they have been pricked by a needle. This is caused by lack of blood supply to Neurons in the brain. If you tell them ahead about the problem, fear shuts down FC and they don’t perform better.

What can we do about this negativity? Every time negative emotion comes, practice changing it with one thing that you can change. The practice is important. Behavior will change gradually. How we think about ourselves matters very much. If we think we are better at something, chances are we will do better in that in that task. Asian girls did better on Math problems when they thought they are Asian and therefore better at Math than when they thought they are girls and therefore not good at Math.

  • Taking walks in nature is beneficial for brain and helps focus our attention. Even looking at pictures of nature will give some of the benefits.
  • Practice being in the stressful situation and when actual stressful condition happens you are more ready to cope and perform better in it. Simulating the actual situation will help.
  • Your body language send other messages weather you are ready or not. It also gives message to your own brain. So keeping your posture upright also keeps away negative emotions.
  • Journaling is best way to get the negativity out. This opportunity to download you thoughts on paper helps you give perspective.

 10-07-2015 

Brief Recap of the session:

This technical briefing’s main objective was to demonstrate the latest and greatest features of SQL Server 2016 and how running it on SanDisk hardware multiplies the benefits of new features.

This demo was conducted in Microsoft Technology Center in Chicago and was jointly presented by Microsoft and SanDisk representatives. It was a 300-level advanced preview of the hardware and software without any sales pitch.

Their Fusion IoMemory card goes directly in the PCI slot on the server’s motherboard and they have parity NAND chips on that card for fault tolerance. Price went down considerably for the same product since the April 2015 for two reasons. First their Shanghai, Chaina NAND chip manufacture has become more efficient in producing high quality chips and secondly partnership with Microsoft and other hardware vendors such as Dell and HP.

20151007_110956

These are some of the numbers we get with SanDisk flash when tables are stored as normal (i.e; on Sandisk without using columnstore feature of SQL Server).

20151007_110408

SQL server 2016 has opened the clogged artery meaning flash drive performance is out of the charts. They were talking in terms of IOPS, throughput and latency. Now SQL server 2016 (compared to sql server 2014) using Sandisk or any flash drive can deliver IOPS in millions, Throughput in GBs and latencies in microseconds. The enhancement is due to the improved ColumnStore feature in SQL Server 2016. IO bottleneck for OLTP or OLAP load almost vanishes.

20151007_115143

These improvements directly affects the HA and DR environments. For example Always on Availability Group. Log transfer from one database to another, on the secondary, is much faster because of separating the redo process at the secondary server. AG, in synchronized mode, waits for the confirmation from the secondary server. Before sql server 2016, a record has to be written to the disk on the secondary and applied to the database before confirmation is sent to the Primary node. Now confirmation is sent as soon as the records is written to the disk. Redo process than run in the background at secondary server to apply the changes to the database. SQL 2016 AG now have load balancing feature included.

20151007_113725

Another benefit is cost savings in SQL server license. Because most of the time sql load is IO bound and CPU hardly goes above 25% on average. These flash drives will make CPU work for what they are worth. So only 2 CPUs are needed for server load as big as 10 TB Datawarehouse.

My impressions:

Since our databases are not even 1 TB, the whole critical databases in production can be stored on these flash drives. Or we can have our log files and tempdb files on flash drives. There are lot of options. And when these cards are purchased with VM vendors, there is warranty included which replaces these cards when it fails without cost to us.

They also have caching feature, I think this a separate software based product that needed to be purchased separately and maybe useful for application level processes to run faster.

Another point is that we are skipping upgrade to SQL server 2014 so we are naturally going to upgrade to SQL server 2016 in year 2017. I think we can plan in 2016 for testing and purchasing these flash drives.

Additionally, these will also make our HA and DR very reliable in terms of failover.

In my opinion, we can take advantage of flash drives for production load in our VMs when we upgrade SQL Server to 2016.

FileStream directory

The file stream network path technically is this \\SQL Server name\Network Share Name\Database File stream Directory Name\<File Table Name>

  1. SQL Server Name – (starting with \\)
  2. Windows Share Name – It is visible in SQL Server Configuration tool -> SQL Server properties -> FileStream tab.
  3. Database File stream Directory Name – It can be seen in Database Properties -> Options -> FILESTREAM Directory Name
  4. The last part is the file table name.

Some helpful FileStream queries

Exploration Queries:

SELECT DB_NAME(database_id) AS DatabaseName,non_transacted_access_desc, directory_name  FROM sys.database_filestream_options WHERE directory_name IS NOT NULL

SELECT directory_name,is_enabled,filename_collation_name FROM sys.filetables;

SELECT * FROM sys.tables WHERE is_filetable = 1

SELECT object_id, OBJECT_NAME(object_id) AS ‘Object Names’    FROM sys.filetable_system_defined_objects

— To get the root level UNC path of a file table

SELECT FileTableRootPath()

SELECT FileTableRootPath(N’file_table_name’)

Enable Disable Queries:

https://msdn.microsoft.com/en-us/library/gg492061.aspx#HowToDisable

— Disable Non-Transactional write access.

ALTER DATABASE database_name

SET FILESTREAM ( NON_TRANSACTED_ACCESS = READ_ONLY );

— Disable non-transactional access.

ALTER DATABASE database_name     SET FILESTREAM ( NON_TRANSACTED_ACCESS = OFF );

— Enable full non-transactional Access

ALTER DATABASE database_name     SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL );

Find locks held by filestream queries:

Open handles to the files stored in a FileTable can prevent the exclusive access that is required for certain administrative tasks. To enable urgent tasks, you may have to kill open file handles associated with one or more FileTables.

SELECT * FROM sys.dm_filestream_non_transacted_handles;

— To identify open files and the associated locks

SELECT opened_file_name    FROM sys.dm_filestream_non_transacted_handles

WHERE fcb_id IN        ( SELECT request_owner_id FROM sys.dm_tran_locks );

— Kill all open handles in all the filetables in the database.

EXEC sp_kill_filestream_non_transacted_handles;

— Kill all open handles in a single filetable.

EXEC sp_kill_filestream_non_transacted_handles @table_name = ‘filetable_name’;

— Kill a single handle.

EXEC sp_kill_filestream_non_transacted_handles @handle_id = integer_handle_id;

Code Re-usability:

— Use the relative path for portable code

https://msdn.microsoft.com/en-us/library/gg492087(v=sql.110).aspx

DECLARE @root nvarchar(100);

DECLARE @fullpath nvarchar(1000);

SELECT @root = FileTableRootPath();

SELECT @fullpath = @root + file_stream.GetFileNamespacePath() FROM EDMS

WHERE name = N’document_name’;

PRINT @fullpath;

How FileStream works with some of the High Availability Features of SQL Server 2012?

 AlwaysOn Availability Groups and FileTables

https://msdn.microsoft.com/en-us/library/gg492086.aspx#alwayson

When the database that contains FILESTREAM or FileTable data belongs to an AlwaysOn availability group:

  • FileTable functionality is partially supported by AlwaysOn Availability Groups. After a failover, FileTable data is accessible on the primary replica, but FileTable data is not accessible on readable secondary replicas.
  • The FILESTREAM and FileTable functions accept or return virtual network names (VNNs) instead of computer names.
  • All access to FILESTREAM or FileTable data through the file system APIs should use VNNs instead of computer names.

Replication and FileTables

Replication and related features (including transactional replication, merge replication, change data capture, and change tracking) are not supported with FileTables.