Infrastructure at your Service

Tag

SQL Server Archives - Blog dbi services

Steven Naudet

SQL Server: Find who forced a plan in Query Store with this new XEvent

By | Database Administration & Monitoring, SQL Server | No Comments

The latest Cumulative Update for SQL Server 2019 has been released this week on Monday. It brings many bug fixes and some small improvements. One of these improvements is the addition of an extended event to identify the users forcing or unforcing an execution plan via the Query Store. In this blog post, I will test this new XEvent. For details about the latest CU see: KB5011644 – Cumulative Update 16 for SQL Server 2019…

Read More
Steven Naudet

SQL Server: Automatic Soft-NUMA and uneven CPU load

By | Database Administration & Monitoring, SQL Server | No Comments

SQL Server has CPU limitations depending on the Edition. If your VM is not configured properly you can reach these limits very easily. In a previous post, I described the effect VM misconfigurations can have on performance because not all available cores are used as expected. See: SQL Server CPU limitation for Express and Standard Edition In this article I will share a similar case but this time in a context where the Automatic soft-NUMA…

Read More
Stéphane Savorgnano

Striim, real-time data integration

By | Cloud, Database management, SQL Server, Technology Survey | No Comments

In my first blog-post about Striim, I showed you how to create an Initial Load from an on-premise database to an Azure SQL DB. In this second blog post I will create an application which will stream Data Changes from a CDC-Enabled on-premise SQL Database to my Azure SQL Database. The first step is to enable CDC (Chane Data Capture) on the on-premise SQL Server database. CDC uses the SQL Server agent to record insert,…

Read More
Stéphane Savorgnano

How to solve Stored Procedure parameter sniffing problems

By | Database Administration & Monitoring, Database management, SQL Server | One Comment

Since Tuesday, I have the chance to follow the SQLBits 2022 conference. Sadly, I have to do it remotely this year but I promise next time I will come in-person 😉 SQLBits is one of the largest SQL Server conference in the world with some famous speakers like Brent Ozar, Bob Ward, Erik Darling… In this blog post, I will come back on one of the tips Erik gave us during his training session of…

Read More
Steven Naudet

SQLBits 2022 – Levelling up my Performance Tuning skills

By | SQL Server | No Comments

As a consultant at dbi services, I am lucky enough to have time dedicated to training and conferences. This week I have the chance to attend one of the most important conferences for Data professionals: SQLBits 2022. The SQLBits conference is currently taking place at ExCel London and features over 300 sessions over 5 days. Tuesday and Wednesday, the pre-conference, were dedicated to full-day training sessions. Wednesday to Thursday are the general sessions, the conference…

Read More
Nathan Courtine

Access Check Results entries too large, resulting in a memory pressure

By | Database Administration & Monitoring | No Comments

As part of a MSSQL consolidation project, with application servers located in one domain and the databases in another, we received an alert indicating that some databases were not available. After starting the investigation, we discovered that the Availability Group (AAG), to which these databases belong, was temporary taken in RESOLVING_NORMAL: The state of the local availability replica in availability group ‘xxxxxxxx has changed from ‘PRIMARY_NORMAL’ to ‘RESOLVING_NORMAL’. The state changed because the lease between…

Read More
Stéphane Haby

SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss

By | Database Administration & Monitoring, Database management | No Comments

For a customer, I want to see all last duration for a job and have a good format for it. The query to do it is very simple (at the beginning…) but when I try to have a better view it was more complicated. Let me try to explain you. As you now, to have the history of all jobs on SQL Server, we use system views dbo.sysjobhistory and dbo.sysjobs I use this query to…

Read More
Stéphane Haby

SQL Server: msdb too big with the table dbo.sysmaintplan_logdetail

By | Database Administration & Monitoring, Database management, SQL Server | No Comments

Today, I was by a new customer and the I see that the msdb data file was over 4GB. A little too big… To see which table are so big, in SSMS, I select the msdb database, right-click, go in Reports>Standards Reports> Disk usage by Top Tables As you can see, the table dbo.sysmainplan_logdetail is the big one with a size of 2,3GB and 13205 records. This table is the log for the maintenance plan…

Read More
Stéphane Haby

SQL Server: Cannot execute as the database principal “dbo” does not exist

By | Database Administration & Monitoring, Database management, SQL Server | One Comment

By a customer this month, I see a very big big ErrorLog file (more than 1,5GB). When I open the file, every 5 seconds, the following message is added: The activated proc ‘[dbo].[IdentityServerNotificationCleanup]’ running on queue ‘<database>.dbo.IdentityServerNotificationsQueue’ output the following:  ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’ My first reflex was to open SSMS and right-click…

Read More
Stéphane Haby

SQL Server: how to have the number of core-based licenses used on my environment?

By | Database Administration & Monitoring, Database management, Development & Performance, SQL Server | No Comments

This week, a customer asks me to find out how many SQL Server Standard licenses are used in their environment. The licensing is core based and to not be used for Sserver + Cals licenses. To have an idea, I create a script following the Microsoft document about the licensing here. This script is valid for the SQL Server 2016, SQL Server 2017 and SQL Server 2019. If you have another version of SQL Server,…

Read More