Infrastructure at your Service
For a customer, I do some research to find which TLS is used on the SQL Server environment. The only way is to create an Extended Event. A big limitation is that the event used is only available on SQL Server 2016 and +. Before use the Built-In Diagnostics (BID) traces. After I implanted the first TLS Monitoring on a SQL Server 2016 with the query: CREATE EVENT SESSION [TLS_monitoring] ON SERVER ADD EVENT…
Few days ago, after a reboot, we had this warning on the Agent Error Logs on many servers: Warning [396] An idle CPU condition has not been defined – OnIdle job schedules will have no effect “The CPU idle definition influences how Microsoft SQL Server Agent responds to events. For example, suppose that you define the CPU idle condition as when the average CPU usage falls below 10 percent and remains at this level for…
Few days ago, I have very poor performance on a database that I migrate from SQL 2012 to SQL 2019. The developer Team asks me to upgrade the number of CPU and the Memory to have better performance. First, I ask the Team to give me some queries sample to test the performance between the old environment in SQL server 2012 and the new one in SQL server 2019. To do the test, I use…
Doing some performance troubleshooting for one of my customers I identified some issues with very large table variables inside Stored procedures. Table variables limitations are not well understood by developers although they are now well documented. Table variable rows estimation Let’s have a look at an example with my customer context which is SQL Server 2016, so compatibility level 130 at the database level. You can reproduce this demo with the Wide World Importers database….
SQL Server 2019 added three new options in sp_configure and sys.configurations. First, how can we find the difference between these SQL Server Versions. This simple query will give us the number of options and the SQL Server Version: select count(*) as number_of_configurations, @@version FROM sys.database_scoped_configurations In SQL server 2016, we have 4 parameters for the database configuration: In SQL server 2017, we have 5 parameters for the database configuration: In SQL server 2019, we have…
I have blogged several times about unusually large MSDB databases like here. Most of the time the problem comes from the backup history which is never purged. This time it’s different. Try to guess, if not the backup history, what can cause MSDB to increase abnormally in size? Let’s start by looking at the total size of the database: It’s huge. The database size is almost 90GB. The 10% configuration on data files causes large…
SQL Server 2019 added new options in sp_configure and sys.configurations. First, how can we find the difference between these SQL Server Versions. This simple query will give us the number of options and the SQL Server Version: select count(*),@@version FROM sys.configurations In SQL server 2016, we have 74 parameters for the instance configuration: In SQL server 2017, we have 77 parameters for the instance configuration: In SQL server 2019, we have 84 parameters for the…
I have an interested alert on SCOM for SQL Server by a customer two weeks ago. In the SCOM interface, the warning on the server is MSSQL2016: The Average Wait Time of SQL instance “xxx” on computer “xxx” is too high. After right-click on the properties, go to the tab Alert Context. On this part, you can see that the Object Name is MSSQL$xxx:Locks. My first step is to go in the Reports>Standard Reports> Performance…
By a customer, the SCOM’s Database OperationManagerDW growth fast and becomes very big. To see what’s happens, in SSMS (SQL Server Management Studio), I right-click on the database open Reports>Standard Report> Disk Usage by Top Tables: You have many interesting reports with SSMS and I advise you to use it. In my case the “Disk Usage by Top Tables” report displays me many perf.perfHourly and State.StateHourly tables with a _guid(): After reading some blog’s, the…
Sometimes during an SQL Server audit, I check the collation of the server and the databases but every time separately. The goal of this blog is to give you a script to have both and compare it. First step is to have the server collation and the code page. The code page will help to see if the character set is the same. For example, for us, the 1252 is Latin/Western European and the 1258…
