Category Archives: SQL 2017

Links for new DMV’s in SQL 2017

Since we at SQLRX have been super busy this week and still need to do something for a blog post, I thought that doing a link round up of new dynamic management views that are going to be available in SQL 2017 would be a quick and good idea. I am really interested in sys.dm_db_log_info since it give VLF info and can be used for monitoring and alerting.

Enjoy!

Change:

A column on sys.dm_db_file_space_usage has been changed: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-file-space-usage-transact-sql

Brand new dmv’s:

sys.dm_db_log_stats https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-log-stats-transact-sql

sys.dm_db_log_info https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-log-info-transact-sql

sys.dm_db_stats_histogram https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-stats-histogram-transact-sql

sys.dm_tran_version_store_space_usage https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-version-store-space-usage

sys.dm_os_host_info https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-host-info-transact-sql

Blog_20170901_1

Yee Haw!!!!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Installing and Connecting to SQL 2017 on Ubuntu (Linux)

–By Lori Brown

SQL Server 2017 is available as a CTP for testing (https://www.microsoft.com/en-us/sql-server/sql-server-2017) and is able to be installed on a system running Linux. Because I have done all of my professional work on Windows systems, the leap to a Linux system was a bit of a stretch. I learned that while Linux is the OS kernel, Red Hat, SUSE, Ubuntu and Docker Engine are all flavors of Linux. A rough analogy might be that something like Ubuntu is sort of like a version of Windows. Regardless of how you think of it, it is a truly interesting experience to get SQL 2017 installed.

The first thing to do is get your friendly System Admin to stand up a Linux server. We used Ubuntu 16.04 but there are other flavors of Linux that can also be installed as listed below. Your Admin should provide you with a login and password that is an administrator so that you can get SQL running.

Blog_20170621_1

Since everything I have ever done on a computer has had a GUI, I decided to install Gnome to try to shorten my learning curve on Ubuntu. (https://ubuntugnome.org/download/ ) Truthfully, navigating in Gnome was not super intuitive to me but I was able to bumble around and get things done. When following the install instructions from Microsoft, there are places where commands are used to get things done that are labeled “bash”. It took me a bit to figure out that bash is a Unix shell or command prompt that is used to execute commands. I found a bash guide if anyone is interested. (http://www.tldp.org/LDP/Bash-Beginners-Guide/html/Bash-Beginners-Guide.html) Apparently you can use bash on Windows….I learn something new every day. (https://blogs.windows.com/buildingapps/2016/03/30/run-bash-on-ubuntu-on-windows/#igsmYPLj6ovEWf7Y.97)

In Gnome, I had to search for terminal to get the command prompt (bash) window. To get SQL installed, I followed the instructions from Microsoft and have taken screenshots of what it looks like after running commands. (https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu)

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –

Blog_20170621_2

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list

Blog_20170621_3

sudo apt-get update

Blog_20170621_4

sudo apt-get install -y mssql-server

Blog_20170621_5

During the setup completion step, you should be ready to provide the SA password when prompted for the SQL Server system administrator password.

sudo /opt/mssql/bin/mssql-conf setup

Blog_20170621_6

systemctl status mssql-server

Blog_20170621_7

Now install SQLCMD and BCP tools. (https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools#ubuntu)  I only installed SQLCMD but the BCP commands are similar.

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –

Blog_20170621_8Blog_20170621_9

sudo apt-get update

Blog_20170621_10

sudo apt-get install mssql-tools unixodbc-dev

You have to answer licensing questions here so be ready.

Blog_20170621_11

Blog_20170621_12

I also installed the SQL Server Agent. The commands are very similar to previous commands so I did not take any extra screen shots of those. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-sql-agent

Once the tools are installed, you must use SQLCMD to query. You cannot use SSMS on Ubuntu.

Blog_20170621_13

Since I am a creature of the Microsoft world and really want to use SSMS to do things, I need to be able to connect to SQL on Ubuntu using SSMS. So that you can connect remotely, I made sure to open SQL ports in Ubuntu. First you have to enable the firewall:

Blog_20170621_14

Then open ports 1433 & 1434:

Blog_20170621_15

To connect and query from a Windows machine….install SQL 2017 SSMS. (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms)

Connect to SQL as normal. I tried many times to get connected using my Ubuntu hostname but did not have luck. I was able to connect using the IP address however.

Blog_20170621_16

The Ubuntu hostname appears in the terminal connection. Use ifconfig to get the IP address. (http://www.aboutlinux.info/2006/11/ifconfig-dissected-and-demystified.html)

Blog_20170621_17

Once connected you can do most things as usual in SSMS.

Blog_20170621_18

Blog_20170621_19

Blog_20170621_20

Blog_20170621_21

As of this writing, I found several places where Microsoft employees warned that some things will likely break and that they are working on bugs. So, if you run into anything that you can’t find a way around, let them know. So far Microsoft seems to be very open to information and suggestions which makes me very hopeful that SQL 2017 will be released soon and will be much faster than previous versions.

For a taste of some of the new things in SQL 2017 see: https://blog.sqlrx.com/2017/05/19/sql-2017-is-on-the-way/

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

SQL 2017 Is On The Way!!

–By Lori Brown  @SQLSupahStah

SQL Server 2017 is rolling our way like a big ole train. And, with the new features that are going to be available there may be quite a few shops that want to jump onboard.

Blog_20170519_1

As of this writing SQL Server 2017 CTP 2.1 is available for download and testing. (https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2017-ctp/ ) I managed to recently attend a presentation by Denzil Ribeiro who is a manager with the SQLCAT team on some of the new features in SQL 2017. I tend to pay attention to the things that get the guys on the inside excited so here are some of the highlights with links that I could find and my notes on SQL 2017.

Resumable Online Index Rebuild

https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/20/resumable-online-index-rebuild-is-in-public-preview-for-sql-server-2017-ctp-2-0/

Index rebuilds can be paused and restarted. Since index must be created with ONLINE = ON then it is likely that this is an Enterprise Edition feature.

ALTER INDEX IDX_MyInx ON SomeTable

REBUILD WITH (RESUMABLE = ON, ONLINE = ON, MAX_DURATION = 1)

— Pause

ALTER INDEX IDX_MyInx ON SomeTable PAUSE

Use sys.index_resumable_operations to view the status of resumable index rebuilds. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-index-resumable-operations

Wait stats for per query executions

Available by default. Statistics info is now in query plans. Use sys.query_store_wait_stats to see wait info for a query plan. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql

Automatic Query Tuning

https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning

Recognizes plan regressions and will automatically force a good plan to be used. Database must be in 140 (SQL2017) compatibility. Must enable the Query Store for the database. Can then use sys.dm_db_tuning_recommendations (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-tuning-recommendations-transact-sql ) to get info on plans and recommendations that were used to fix regressions. Won’t force a good plan unless AUTOMATIC_TUNING = ON.

ALTER DATABASE current

SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Adaptive Query Processing

Interleaved Execution

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-interleaved-execution-for-multi-statement-table-valued-functions/

Available by default. Adjust plans by testing plan and then redesigning plan based on better row estimates. All while a query is executing.

Batch Mode Memory Grant Feedback

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/29/introducing-batch-mode-adaptive-memory-grant-feedback/

Adjust plan in cache if memory grant is not good enough (either too much or not enough).

Batch Mode Adaptive Joins

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-batch-mode-adaptive-joins/

After first join in a plan, better decision made to choose Hash Join or Nested Loop Join. If number of rows small then likely Nested Loop Join will be used

Graph Data Processing

https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/20/graph-data-processing-with-sql-server-2017/

Nodes and edges stored as tables. Many – many modeling. New MATCH clause in queries. https://docs.microsoft.com/en-us/sql/t-sql/statements/match-sql-graph

Blog_20170519_2

SELECT Person2.Name

FROM Person Person1, Friends, Person Person2

WHERE MATCH(Person1 – (Friends) -> Person2)

AND Person1.Name = ‘John’;

Python

https://blogs.technet.microsoft.com/dataplatforminsider/2017/04/19/python-in-sql-server-2017-enhanced-in-database-machine-learning/

Python is now integrated into SQL just like R Services and is used for advanced analytics. I have to admit that Python is way beyond my skillset right now but wanted to mention it.

Conclusion

A lot of these new features for SQL 2017 are evolving and more will come out I am sure. I am in the process of getting a VM with Linux installed on it so that I can check out installing and running SQL Server on Linux so expect a post on that soon.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!