data-konzept

'Time Zones were definitely being a drag today. I got an email from one of the developers at work asking about the performance difference between 2 queries. The only difference between the 2 queries is that one of them uses the AT TIME ZONE clause that was added in SQL Server 2016. I have not played around with this particular clause, but we do store quite a bit of data in the datetimeoffset data type. In the table in the developer’s queries, the data is all stored in the Eastern time zone, but they are considering storing it in additional time zones and will want to be able to display it in the Eastern time zone even if not stored that way. Thus, AT TIME ZONE.

When the developer was testing the conversion function, he noticed that the query slowed waaaayyyyy down when he added AT TIME ZONE. Before adding AT TIME ZONE to the query, STATISTICS TIME for the query was: CPU time: 145549 ms, elapsed time: 21693 ms.. It returned 8,996 rows, but if I removed the DISTINCT, it returned over 72M rows. That’s a lot of clams … er, data.'...

http://sqlsoldier.net/wp/sqlserver/timezonesareadragseriously

'It’s not that I don’t like partitioning

It’s just that most of my time talking about it is convincing people not to use it.

They always wanna use it for the wrong reasons, and I can sort of understand why.

Microsoft says you can partition for performance.

Partitioning large tables or indexes can have the following manageability and performance benefits.

How?

You may improve query performance, based on the types of queries you frequently run and on your hardware configuration. For example, the query optimizer can process equi-join queries between two or more partitioned tables faster when the partitioning columns in the tables are the same, because the partitions themselves can be joined.

Takeaway: PARTITION EVERYTHING

But…

When SQL Server performs data sorting for I/O operations, it sorts the data first by partition. SQL Server accesses one drive at a time, and this might reduce performance. To improve data sorting performance, stripe the data files of your partitions across more than one disk by setting up a RAID. In this way, although SQL Server still sorts data by partition, it can access all the drives of each partition at the same time.

Takeaway: This was written before anyone had a SAN, I guess?

Ooh ooh but also!

In addition, you can improve performance by enabling lock escalation at the partition level instead of a whole table. This can reduce lock contention on the table.

Takeaway: Except when it causes deadlocks

HoBT-level locks usually increase concurrency, but introduce the potential for deadlocks when transactions that are locking different partitions each want to expand their exclusive locks to the other partitions. In rare instances, TABLE locking granularity might perform better.'...

https://www.brentozar.com/archive/2017/11/partition-level-locks-confusing/?utm_source=DBW&utm_medium=pubemail

'During PASS we announced changes to how users will be able to make parallelism waits more actionable – specifically the “infamous” CXPACKET. This change effectively splits CXPACKET waits into an actionable wait (CXPACKET) and a negligible wait (CXCONSUMER). This change will be effective starting with upcoming SQL Server 2017 CU3 and SQL Server 2016 SP2 (this wait type is already present in Azure SQL Database).

So why split?

It’s important to mention a couple notes before getting into details:

1. Parallelism use is intrinsically a benefit for costly queries, bound by the Cost Threshold for Parallelism and Max Degree of Parallelism server configurations among other considerations. See the Query Processing Guide for more in-depth information on how SQL Server implements parallelism. Also refer to Craig Freedman’s blog series on the topic.

2. Waits are a normal part of the waits and queues model, allowing SQL Server to concurrently execute many more requests than there are schedulers available. See more about waits and queues here,

It’s also important to understand that parallelism is implemented as if it were two operators. There’s the producers that push data to consumers, and the consumers that may have to wait for data from producers.

And so it becomes easier to understand how producer waits are the ones that may require attention, while consumer waits are inevitable as a passive consequence of longer running producers.

What is what going forward?'...

https://blogs.msdn.microsoft.com/sql_server_team/making-parallelism-waits-actionable/

'In the previous articles from the Always Encrypted blog series, we demonstrated how to configure Always Encrypted using SQL Server Management Studio. In this article, we will show you how to configure Always Encrypted from the command line, using PowerShell.'...

https://blogs.msdn.microsoft.com/sqlsecurity/2017/01/04/getting-started-with-always-encrypted-using-powershell/

'SQL Server 2017 and Azure SQL Database introduce a new generation of query processing improvements that will adapt optimization strategies to your application workload’s runtime conditions. For this first version of this adaptive query processing feature family, we have three new improvements: batch mode adaptive joins, batch mode memory grant feedback, and interleaved execution for multi-statement table valued functions.

'...

https://blogs.technet.microsoft.com/dataplatforminsider/2017/09/28/enhancing-query-performance-with-adaptive-query-processing-in-sql-server-2017/

'We are pleased to announce the latest generally-available (GA) of Microsoft Kerberos Configuration Manager for SQL Server.

Get it here: Download Microsoft Kerberos Configuration Manager for SQL Server

Note : this replaces the previously released v4.0.

Why Kerberos?

Kerberos authentication provides a highly secure method to authenticate client and server entities (security principals) on a network. To use Kerberos authentication with SQL Server, a Service Principal Name (SPN) must be registered with Active Directory, which plays the role of the Key Distribution Center in a Windows domain. In addition, many customers also enable delegation for multi-tier applications using SQL Server. In such a setup, it may be difficult to troubleshoot the connectivity problems with SQL Server when Kerberos authentication fails.

Here are some additional reading materials for your reference.

• Kerberos Authentication Overview

• How to use Kerberos authentication in SQL Server

• Register a Service Principal Name (SPN) for Kerberos Connections 

• Delegating authentication

• Troubleshooting Kerberos Delegation

• Solving Connectivity errors to SQL Server'...

https://blogs.msdn.microsoft.com/sqlreleaseservices/released-microsoft-kerberos-configuration-manager-for-sql-server-4-1/

'Get SQL Operations Studio (preview) for Windows

1. Download SQL Operations Studio (preview) for Windows.

2. Browse to the downloaded file and extract it.

3. Run \sqlops-windows\sqlops.exe

Get SQL Operations Studio (preview) for macOS

1. Download SQL Operations Studio (preview) for macOS.

2. To expand the contents of the zip, double-click it.

3. To make SQL Operations Studio (preview) available in the Launchpad, drag sqlops.app to the Applications folder.

Get SQL Operations Studio (preview) for Linux

1. Download SQL Operations Studio (preview) for Linux.

2. To extract the file and launch SQL Operations Studio (preview), open a new Terminal window and type the following commands...'...

https://docs.microsoft.com/en-us/sql/sql-operations-studio/download