Monday, April 10, 2017

NOTE:Please be aware that all scripts work only as of publishing date and with SQL Server on Linux CTP 1.4. In future releases some features/bugs may disappear.

As you might know Linux file system is different from Windows.
Linux does not have drive letters like "A:\","B:\","C:\","D:\","E:\" etc.
It has only root folder: "/" and all devices, services, mapped network resources are linked as underlying sub-folders.

SQL Server files are located under Linux following folder: "/var/opt/mssql/".
Knowing that, you can reference SQL server files in that "Linux way".
However, Microsoft did extremely good job to satisfy current customers, it also translated "Linux path" in "Windows way" just by adding drive "C:\" letter instead of root folder. Now "C:\" is our root!

So, in that manner, SQL Server files will be located in "c:\var\opt\mssql\" directory!
Isn't it simple?!!!
There will be no effort at all to refurbish old SQL SQL Server code for new Linux platforms!

Here I'll give you an example of Database creation using different addressing methods:
1. I use Windows notation for data file and Linux notation for log file.
2. Because Linux systems are case sensitive it is very important to know it when you work with Linux. However, Microsoft allows you to be reluctant. You can use upper or lower case in the order you want.

Do you think SQL Server just converted upper case to lower case, but still reported to us that folder names "USERDATA" and "USERLOGS" in Upper case?

It is kind of true, but still wrong. SQL Server does something completely different.
It looks like if file system has two folders with similar names, which differentiate only by case than SQL Server chooses case insensitively the one, but there is no correlation which one will be chosen.

When you type would say "W10" (wait for 10 seconds) then select it by a cursor and press Ctrl+6 the script will capture Wait statistics, essential SQL metrics and I/O metrics. Then script waits for 10 seconds and compares previous values with the new ones and reports any difference.
That option can bring a lot of information telling you what SQL Server is doing right now and what potential bottleneck can be.

That is CPU usage diagram you get after simple "Ctrl+6". After it changed to reverse order it is easier to define timing. For instance on the diagram below you can see that SQL Server experienced very heavy CPU usage about 260-270 minutes ago.

Ctrl+8 Changes/Improvements

- Fixed diagram's scale by implementing logarithmic measuring;
- Top 10 diagram includes "Distinct Top 10" combination of MAX CPU, MAX I/O, & MAX # of executions. Number of top queries can potentially rise up to 30;

Now Query executions' diagram can have more than 10 queries and because it is in logarithmic scale now there are no hassle with circle sizing.
Axis-X represents amount of CPU used by a query
Axis-Y represents amount of I/O used by a query
Circle radius represents number of query executions.
If you move cursor to a particular circle you'll see Queries metrics

To see more details on the query you want to research, such as query text, execution plan, etc.
You have to copy-paste "query_hash" value to an editor window, select it and press Ctrl+8 again.