Mustafa EL-Masry Dedloack, Performance MSSQL

Monitoring and Tracking SQL Server Blocking & Deadlocks process


Introduction

SQL Server deadlock one of the issues that can be happened in any SQL Server, today in this article I will not explain what is Deadlock and How we can solve it but the main purpose of this article is sharing the scripts I am using it for monitoring this kind of process let us start

It will be great if you share with us your experiences and your scripts in the comment

  1. Demo Preparation
  2. Database Configuration
  3. Extended Event Implementation
  4. SQL Server Custom Jobs Implementation
  5. Deadlock Simulation
  6. Capture the Deadlock information using SQL Server profiler
  7. Capture the Deadlock information using SQL Server Error Log
  8. Capture the Deadlock information using SQL Server Extended Event
  9. Capture the Deadlock information using System_Health Default Extended event
  10. Capture the Deadlock counts per Day
  11. Capture the Deadlock information using SQL Server Custom Jobs
  12. Check the Row Lock Blocking Info
  13. Other Scripts for Monitoring
Continue reading “Monitoring and Tracking SQL Server Blocking & Deadlocks process” →
Mustafa EL-Masry SQL Server 2017 on Linux

SQL Server 2017 on Linux


Hello, followers, I need to share with you some tips in SQL Server 2017 on Linux

  • SQL Server 2017 supported Red Hat, SUSE and Ubuntu
  • You need 3.25 GB of memory to run SQL Server Enterprise on the Linux operating system
  • Not like Windows Server you can install multiple instances on the operation system, Linux supported only one SQL Server instance
  • Hostname of the SQL server should be less than 15 Characters
  • File System has a different configuration in Ubuntu and SUSE should be EXT4 but in Red Hat should be EXT4 and XFS Files.
  • Remote databases can’t be stored on NFS system
  • SQL Server Native GUI still not supported in Linux system till this moment
  • Cannot connect to the SQL instance on Linux from the management studio installed on Windows Server
  • The maintenance plan, Management Data Warehouse, distributed transactions, and the Data Collector are not supported in SQL Server /Linux
  • If you have SQL version less than 2017 and you need to migrate it to Linux , Recommended to upgrade it on Windows server to SQL 2017 than you can take backup from DB and restore it on Linux or Attach / De attach and in the restore DB from windows to Linux you need to use “WITH MOVE” T-SQL statement
  • SQL Server 2017 unsupported feature on Linux (transactional replication, merge replication, stretch DB, polybase, distributed queries with third-party connections, system extended stored procedures, file tables, CLR assemblies with external access or unsafe permissions set, and buffer pool extensions. Database mirroring, agent alerts, managed backups, the SQL Server browser and the machine learning services that include support for the R and Python programming languages, are also not supported at the time of this recording)

I know this a lot features in SQL Server 2017 not supported in Linux but personally, I am so proud about this results it is the first time for Microsoft to see SQL Server on another operating system (Red Hat, SUSE or Ubuntu)

References :

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-editions-and-components-2017?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-security-overview?view=sql-server-2017

https://www.theregister.co.uk/2017/09/27/sql_server_2017_whats_new_and_on_linux_whats_missing/