All the Internet giants, including Microsoft, now supplement their CPUs with graphics processing units, chips designed to render images for games and other highly visual applications. When these companies train their neural networks to, for example, recognize faces in photos—feeding in millions and millions of pictures—GPUs handle much of the calculation.

Some giants like Microsoft are also using alternative silicon to execute their neural networks after training. And even though it’s crazily expensive to custom-build chips, Google has gone so far as to design its own processor for executing neural nets, the tensor processing unit.

Lock Pages in Memory and Instant File Initialization privileges are couple of configuration settings which every DBA, SQL Server consultant or enthusiast have it in their checklist to ensure they see a predictable performance for their SQL Server instance. While Lock Pages in Memory privilege information is logged in SQL Error log, Instant File initialization information was first introduced in SQL Errorlog starting SQL Server 2016 RTM and later added to SQL Server 2014 with SP2.

When you are managing, administering or monitoring large deployment of SQL Servers, it is still cumbersome to programmatically query SQL Error log to check if these permissions are enabled for the SQL Server service account. With SQL Server 2016 SP1, we have added new columns in the DMV which now makes it easy to develop scripts to programmatically query and report whether Lock Pages in Memory and instant file initialization privileges are enabled on a given instance of SQL Server.

SQL Server uses memory to store in-transit rows for hash join and sort operations. When a query execution plan is compiled for a statement, SQL Server estimates both the minimum required memory needed for execution and the ideal memory grant size needed to have all rows in memory. This memory grant size is based on the estimated number of rows for the operator and the associated average row size. If the cardinality estimates are inaccurate, performance can suffer:

For cardinality under-estimates, the memory grant can end up being too small and the rows then spill to disk, causing significant performance degradation compared to a fully memory-resident equivalent.

For cardinality over-estimates, the memory grant can be too large and the memory goes to waste. Concurrency can be impacted because the query may wait in a queue until enough memory becomes available, even though the query only ends up using a small portion of the granted memory.

With SQL Server on Linux, Microsoft brings SQL Server’s core relational database engine to the growing enterprise Linux ecosystem. Both High Availability and Disaster Recovery (HADR) and security are aspects of SQL Server that are critically important for enterprises. This article highlights the HADR and security solutions for SQL Server on Linux that are available today, as well as the roadmap for what’s coming soon.

Today, developers can use SQL Server in a variety of environments including on-premises, in datacenters, in virtual machines, in clouds such as Azure, AWS and Google, and also as a Platform as a Service (PaaS) offering with Azure SQL Database and Azure SQL Data Warehouse.

We recently announced SQL Server v.Next CTP1 on Linux and Windows, which brings the power of SQL Server to both Windows — and for the first time ever — Linux. Developers can now create applications with SQL Server on Linux, Windows, Docker, or macOS (via Docker) and then deploy to Linux, Windows, or Docker, on-premises or in the cloud.

As part of this announcement, we have released new SQL tools and also updated existing SQL tools. Developers can use these tools to connect to and work with SQL running anywhere, including SQL Server on Linux, Windows or Docker.

Saturday, November 26, 2016

I was working on a post about the new columns sql_memory_model, sql_memory_model_desc columns in DMV sys.dm_os_sys_info. These columns allow DBAs to programmatically identify if Lock Pages in Memory (LPIM) privilege is in effect at the service startup time.

The Visual QuickStart book is for the total beginner, the How Linux Works book will go into more detail. Either book is perfect if the person has never touched a *nix system before.
Both books are nicely formatted, clear and concise, once the person is done with one of these books, the person can get himself/herself one of those 1000 page tomes

The next item is a book by an author whose previous work I have read and enjoyed

I noticed that Benjamin Nevarez's latest book is available now on the Apress site for $9.99 in epub and pdf format, this price is valid through November 29, 2016

I didn't read this book yet, so here is what the website has to say about this book

What You Will Learn
Understand SQL Server's database engine and how it processes queries
Configure instances in support of high-throughput applications
Provide consistent response times to varying user numbers and query volumes
Design databases for high-throughput applications with focus on performance
Record performance baselines and monitor SQL Server instances against them
Troubleshot and fix performance problems

Here is a breakdown of the chapters

How SQL Server Works
Pages 1-19

Analyzing Wait Statistics
Pages 21-43

The Query Store
Pages 45-69

SQL Server Configuration
Pages 71-84

tempdb Troubleshooting and Configuration
Pages 85-99

SQL Server In-Memory Technologies
Pages 101-129

Performance Troubleshooting
Pages 131-154

Indexing
Pages 155-180

SQL Server Storage
Pages 181-196

There you have it...one of these will make a nice gift for your friendly DBA....

I blogged about this before but someone ran into this issue again at work so I thought it made sense to revisit this.

I prefer to use BULK INSERT or bcp as much as possible, this is especially true if all I need to do is dump the file into a table. If there is a need to do complex stuff, I will use SQL Server Integration Services or Informatica. These days files are generated by all kinds of systems, these can be Windows, *nix, Mac, Raspberry Pis, IoT systems and many other systems.

If you get an error importing one of these files, your first course of action is to open one of these files in something like Notepad++ or Editplus. Notepad++ and Editplus have more functionality and are many times faster than notepad. The nice thing about either of these is that you can see control characters.

See how you can see the linefeeds here? You cannot do that in notepad

So let's say you get a file where the row terminator is a linefeed, how would you specify that as a row terminator in BULK INSERT?

We are getting ready to update the SQL Server Management Packs family. Please install and use this public preview and send us your feedback (sqlmpsfeedback@microsoft.com)! We appreciate the time and effort you spend on these previews which make the final product so much better.

The SQL Product team has identified the following known issues which the customers may encounter after installing SQL Server 2016 SP1. The following post will help the customers plan and prepare their environment for the following known issues while installing SQL Server 2016 SP1.

Using our good old buddy the StackOverflow demo database, let’s look at the Users table. I want to get the first 10 users in the table ordered by Id, the primary clustered key, which is an identity field.

Ooo – clustered index scan – that’s bad, right?

Not so fast. Click on the plan to view it in PasteThePlan.com, and hover your mouse over the Clustered Index Scan:

In the latest generation of SQL Server Management Studio, we moved to the Visual Studio 2015 Isolated Shell. While this provides SSMS a modern IDE foundation for many functional areas, it also had some consequences. Specifically, the integration with source control systems in SSMS no longer works the way it did in SSMS 2014 and prior. Previously, one could install the Visual Studio MSSCCI provider and then integrate with various source control systems. Visual Studio 2015 does not support MSSCCI so that is no longer an option to use in SSMS.

Of course, the good news is that Visual Studio 2015 includes TFS and Git source control integration. With the move to VS 2015 Isolated Shell, SSMS should be able to use these packages as well, right? The answer is…yes…but! The issue for SSMS is that the TFS source control integration package VS provides also includes the entire suite of TFS integration features. If we include this package by default, SSMS will have Team Explorer in its entirety which includes things such as work item tracking, builds, etc. This doesn’t fit in the overall experience SSMS is designed for, so we aren’t going to include this package as part of SSMS. The full TFS integrated experience is included as part of SQL Server Data Tools which is designed for a more developer-centric set of scenarios.

That said, if source code integration is an important aspect of how you use SSMS, you can enable the Visual Studio packages manually.

Sunday, November 20, 2016

I decided to play around with installing SQL Server vNext on Linux today. I decided to pick the Ubuntu distribution. I created a VM in VirtualBox and then installed Ubuntu 16.04. After that was done, it was time to install SQL Server.

All this stuff on this page runs on Linux, there are no windows components, if you have just a Linux desktop/server, you are all set

I finally installed SQL Server on Linux, while I was messing around with some queries, I noticed the following command fails with an error

exec xp_readerrorlog

The error is

Started executing query at Line 1
Msg 22004, Level 16, State 1, Line 0 Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0 Error log location not found.
Total execution time: 00:01:01.34

So how do you then look at the log on Linux? I don't have SSMS installed on the host machine where the Linux Virtual Machine lives. SQLCMD gives the same error. I don't know if it even works from SSMS from a windows box.

Saturday, November 19, 2016

The SQL Server AMA thread on reddit, has finished. I have put 8 most interesting answers here and then gave my take on it, the questions will be linked straight to the question on reddit. The answer will be in italics and my take on it will be below in regular font

I guess you can always debate whether it is better to have an abstraction layer or do a full port. I am a practical person, and like to judge based on results. I myself am not on the Linux team, but am really impressed with what the team has managed to deliver thus far, both in terms of feature set and in terms of performance. A full port would have taken much longer and would have led to a code base that is much harder to maintain.

I never heard of Drawbridge either. From the Microsoft Drawbridge research page: Drawbridge combines two core technologies: First, a picoprocess, which is a process-based isolation container with a minimal kernel API surface. Second, a library OS, which is a version of Windows enlightened to run efficiently within a picoprocess.

We are collecting feedback and we will decide what would be the next step. Current NVARCHAR representation enables us to integrated JSON with in-memory, column store, does not require new types in drivers, etc. Also, some experiments show that text representation is even faster than XML type in some scenarios. The next step would be better integration with native modules that will speed up queries. We would like to know how your are using current JSON, what are limitations, and then we will decide what would be the next step.

It makes sense that they decided to use nvarchar since you can then use it with in-memory OLTP and Columnstore. We currently store json in some of our tables but we never search on it or manipulate it in the DB

Two things - Satya Nadella, cloud economics - changed a lot of the way we think about things here at Microsoft now. The way I like to think of this strategy of bringing SQL Server to Linux is that it maps to Microsoft's new mission statement - "Empower every person and every organization to achieve more". SQL Server on Linux is about bringing the really powerful capabilities of SQL Server to everybody, not just those that are using Windows/Windows Server and seeing what people can do with it. It's inspiring for example to think about what the scientific or academic communities (which are oftentimes on Linux) could now achieve with SQL Server. Let's call it Microsoft's new 'Embrace, Enable, and Empower' strategy. :)

Haha I knew this would be asked, great answer also. We all remember the Ballmer Linux is a cancer quote, how things have changed

We do not plan to port SSMS "as is" to Linux/macOS for 2 main reasons: 1) SSMS on Windows depends on the VS2015 Isolated shell which is not available on Linux/macOS and 2) a direct port of SSMS will not align with the design guidelines & information architecture of apps that natively run on Linux/macOS (e.g. buttons, menus, etc. are different, the window chrome is different, etc.)

That said, we are working on a new set of multi-platform SQL client tools that DBAs and database developers can use on Linux/macOS/Windows. The new 'mssql' extension for Visual Studio Code is our first attempt in this area and we are using it to prove out the multi-platform SQL tools stack "backend" we've created.

We don't have specific dates yet but stay tuned for more details in the coming months. Meanwhile, you can try the mssql extension for VS Code on Linux/macOS/Windows from here: https://aka.ms/mssql-marketplace

We plan to have a cross-platform tool that will be Linux native. We don't have an exact date set but it's in the works for 2017. In the meantime, you can connect to SQL Server on Linux through the Visual Studio Code mssql extension as well as SSMS.

So it looks like there will be a cross-platform tool, maybe something like Visual Studio Code, make it web based. As long as it is not based on Java and has that nasty purple shiny look and feel. But in reality, all the people I worked with that used *nix always used windows and then would use PuTTY or something similar to SSH into the box and just do everything from the command line.

This is something that we are looking into. It would require creating a new appx-style package for deployment. That in itself would be a good thing actually that could make it easier to install SQL Server (like the package based install for SQL Server on Linux) but it would be a very large development effort. Spending dev effort on that would take away from other things we would like to do. It would also mean that we end up having two different methods of patching SQL Server (.msp and appx) which could further complicate servicing. On the other hand, WS Nano is really well suited for SQL Server due to it's higher availability (less patching), smaller attack surface area, fewer services running, etc. Those are the trade offs we are thinking through, but no concrete plans one way or another right now. Let us know what you think we should do!

Installing SQL Server on Linux is really nice, here is what it looks like on redhat

Great minds think alike! We actually just this Wednesday posted our first set of developer tutorials for lots of different languages (node, python, C#, Java, and more) and platforms (macOS, Docker, Linux, and Windows). They cover how to get SQL Server installed, get some tools, get your first basic app going, and how to use some powerful features like columnstore. There is a tutorial for R language/services too. If that seems to be a popular thing, we'll keep going and add more to it. Check it out and spread the word! http://aka.ms/sqldev

The trick with GPUs is balancing the cost of moving data onto the GPU vs the compute gains. While I can't comment on the future, we are always looking at ways to improve the performance of SQL Server and leverage the latest hardware to its fullest potential. I'd encourage you to take a look at the "SQL 2016 - It Just Runs Faster" blog series for examples of what the team has done recently.

Hardware acceleration for encryption features like TDE by leveraging Intel's AES-NI instruction set (link)

We evaluate hardware advancements periodically for SQL Server. The new Microsoft ML library that is available with SQL Server vNext uses GPU for the neural net algorithms so you may see such capabilities in other areas of the product.

This is definitely an area we are looking at. But we don't have any concrete at the moment.

I remember when postgres did this, the performance improvement was impressive, you can read about that here PG-STROM

Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016. However, SQL Server Management Studio (SSMS) did not expose this information, and so users had to look in the Showplan XML, in the context of the appropriate node and operator.

This caused some friction, and so we are happy to announce that in the latest (October) release of SSMS, these attributes are now readily available in the Properties window of an Actual Execution Plan, in the scope of each operator.

You need a lot of energy to run a data center. Especially when your name is Microsoft, and you’re the biggest technology company on the planet. But this doesn’t necessarily have to be environmentally harmful.

The company just announced that it has inked deals with two wind farms, with the aim of entirely powering its Cheyenne, Wyoming data center from renewable sources.

Microsoft has contracted Bloom Wind farm in Kansas to provide 178 megawatts, and the Silver Sage and Happy Jack farms in Wyoming to provide an additional 59 megawatts.

A user called in for help because he wasn’t able to drop a user from a database. The error message is below

Msg 15136, Level 16, State 1, Line 2The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.

From the error, the logical starting point is sys.sql_modules because functions, stored procedures have entries in this catalog view. Additionally, it has a column called execute_as_principal_id

So I looked there but found no entries whose execute_as_principal_id is the same user id of the user this customer tried to drop. Next I started to look at source code and found out this error is raised in a couple of other places. It gave me the clue that I didn’t have to do source code research, TSQL is all I needed to solve the problem.

With cloud first strategy, the SQL Product Team has observed great success and adoption of SQL Server 2016 compared to any previous releases. Today, we are even more excited and pleased to announce the availability of SQL Server 2016 Service Pack 1 (SP1). With SQL Server 2016 SP1, we are making key improvements allowing a consistent programmability surface area for developers and organizations across SQL Server editions. This will enable you to build advanced applications that scale across editions and cloud as you grow. Developers and application partners can now build to a single programming surface when creating or upgrading intelligent applications, and use the edition which scales to the application’s needs.

In addition to a consistent programmability experience across all editions, SQL Server 2016 SP1 also introduces all the supportability and diagnostics improvements first introduced in SQL 2014 SP2, as well as new improvements and fixes centered around performance, supportability, programmability and diagnostics based on the learnings and feedback from customers and SQL community.

SQL Server vNext represents a major step towards making SQL Server a platform that enables choices of development languages, data types, on-premises and in the cloud, and across operating systems by bringing the power of SQL Server to Linux, Linux-based Docker containers, and Windows.

You can find all the new stuff at that page

Now pay attention to the next two links... did you noticed it was getting colder since Wednesday? That is because Hell just froze over!!!!

Google is pleased to be joining the Technical Steering Group of the .NET Foundation. .NET is a key component in the modern enterprise, and the Google Cloud Platform (GCP) team has worked hard to ensure that .NET has first-class support on Google’s infrastructure, including excellent infrastructure for Windows. For years, Google has offered .NET libraries for more than 200 of its cloud services. More recently, we’ve built native GCP support for Visual Studio and PowerShell.

The Linux Foundation, the nonprofit advancing professional open source management for mass collaboration, today announced that Microsoft has joined the organization at a Platinum member during Microsoft’s Connect(); developer event in New York.

From cloud computing and networking to gaming, Microsoft has steadily increased its engagement in open source projects and communities. The company is currently a leading open source contributor on GitHub and earlier this year announced several milestones that indicate the scope of its commitment to open source development. The company released the open source .NET Core 1.0; partnered with Canonical to bring Ubuntu to Windows 10; worked with FreeBSD to release an image for Azure; and after acquiring Xamarin, Microsoft open sourced its software development kit. In addition, Microsoft works with companies like Red Hat, SUSE and others to support their solutions in its products.

On SQL Server 2016 SP1 and SQL Server vNext (neither available from drop down, so I picked 2016 RTM) When executing CREATE OR ALTER with a DDL trigger, it will run fine the first time, when you execute the script again, you will get an error like the following

Msg 2714, Level 16, State 2, Procedure safety, Line 1 [Batch Start Line 77] There is already an object named 'safety' in the database.

So either don't allow CREATE OR ALTER when the trigger is a DDL trigger or fix it so it doesn't throw the error if you run it more than once

And to finish it of, here are some of my blog posts about SQL Server vNext and SQL Server 2016 Service Pack 1 from this week

I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you"