Category: SQL Server

SQL Server Failed logins: TLS Handshake issues
By: Date: January 20, 2020 Categories: connectivity_ring_buffer_recorded,extended events,SQL Server,TLS Handshake

In most cases information about failed logins can be found in the errorlog of SQL Server. However, if your SQL Server is working with TLS then if the login fails with a handshake error, you will not find this failed login attempt in the errorlog of SQL Server. You can find these types of login…

Read More →
Login timeouts in SQL Server due to delayed SSPI handshakes
By: Date: March 10, 2019 Categories: extended events,procmon,SQL Server,wait_info_external

Rarely occurring login issues in SQL Server are hard to explain when there is no significant load on the Instance or its host. A good idea for troubleshooting is using extended events, symbol files and the sysinternals tool, procmon. For some applications it is crucial to have a login timeout of less than 5 seconds….

Read More →
A lightweight alternative to Query Store
By: Date: November 17, 2018 Categories: Query Store,SQL Server

The aim of this post is to present a lightweight alternative to the SQL Server Query Store. You can find the full code on my Github Page here: https://github.com/bbalazs83/SQL-Server-Query-Statistics With a heavy adhoc workload and a lot of parsing, query store can cause major memory bottlenecks by consuming a lot of MEMORYCLERK_QUERYDISKSTORE_HASHMAP memory. Although there…

Read More →
Issues with monitoring open transactions: Schema lock
By: Date: January 10, 2018 Categories: LCK_M_SCH_S,open transaction,Schema Lock,SQL Server

Monitoring open transactions is beneficial for the performance of a database. There are many nice SQL queries which show the locked objects but there might be some special situations where these queries do not work. They get blocked by the open transaction of another session. Such a scenario can occur when one of the sessions…

Read More →
SQL Server Management Studio: scripting does not show Data_Compression by default
By: Date: November 16, 2016 Categories: DATA_COMPRESSION,SQL Server,SSMS

With this post I aim to highlight a feature of  SQL Server Management Studio (SSMS) regarding the DATA_COMPRESSION property when scripting objects. First I create a table and an index with PAGE compression: use TESTDB GO CREATE TABLE People ( PersonID int, LastName varchar(200), FirstName varchar(200), Address varchar(200), Phone varchar(20) ); CREATE NONCLUSTERED INDEX [comp_index_LastName] ON [dbo].[People]…

Read More →