Infrastructure at your Service

Tag

Performance Archives - Blog dbi services

Jérôme Dubar

Statspack with Oracle Database 19c and Multitenant

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

Introduction When it comes to performance analysis on Oracle databases, everybody think about Diagnostic Pack. This is a very complete tool and it brings much more metrics than a DBA can analyze. But there is a big drawback with this tool: it’s not included in Standard Edition and it’s an option with Enterprise Edition. If you don’t have this tool, it’s still possible to do performance analysis using older Statspack tool. Statspack is nothing else…

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
Steven Naudet

SQL Server: Is Fill Factor applied during INSERT?

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

The Fill factor setting controls the amount of space that is left available on the index data pages. The goal is to allow new rows to be inserted or existing ones to change size (UPDATE)  without resulting in a page split. By default, the server setting is set to “0” which means that the leaf-level pages are filled to their maximum capacity (8060 bytes). The Fill Factor setting is not applied when data are inserted…

Read More
Stéphane Haby

SQL Server: Using Client Statistics to validate a query regression after a change in the database compatibility level

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

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…

Read More
Steven Naudet

SQL Server: table variables performance limitations

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

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….

Read More
Stéphane Haby

SQL Server SCOM Alert Analysis: The Average Wait Time of SQL instance “xxx” on computer “xxx” is too high

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

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…

Read More
Open source Team

Google Cloud SQL Insights: ASH, plans and statement tagging

By | Cloud, Postgres | No Comments

By Franck Pachot . Looking at database performance has always been necessary to optimize the response time or throughput, but when it comes to public cloud where you are charged by resource usage, performance tuning is critical for cost optimization. When looking at host metrics, you see only the symptoms and blindly guess at some solutions: add more vCPU if CPU usage is high, more memory if I/O wait is high. And this can be…

Read More
Jérôme Dubar

Oracle Database Appliance vs Oracle Cloud Infrastructure

By | Cloud, Database Administration & Monitoring, Database management, Development & Performance, Hardware & Storage, Operation systems, Oracle | No Comments

Introduction Oracle Database Appliances are very popular these days. And not only among new customers for this kind of engineered systems. Almost all customers already using old generation ODAs are renewing their infrastructure by choosing again ODAs, meaning that the solution is good enough and probably better than anything else. But now, public clouds are a real alternative to on-premise servers, and Oracle Cloud Infrastructure is a solid competitor vs Amazon and Azure public clouds….

Read More
Christian Weinfurtner

Upgrade to Oracle 19c – performance issue

By | Database Administration & Monitoring, Database management, Oracle | One Comment

In this blog I want to introduce you to a workaround for a performance issue which randomly appeared during the upgrades of several Oracle 12c databases to 19c I performed for a financial services provider. During the upgrades we ran into a severe performance issue after the upgrades of more than 40 databases had worked just fine. While most of them finished in less than one hour, we run into one which would have taken…

Read More
Oracle Team

Troubleshooting performance on Autonomous Database

By | Oracle | One Comment

By Franck Pachot . On my Oracle Cloud Free Tier Autonomous Transaction Processing service, a database that can be used for free with no time limit, I have seen this strange activity. As I’m running nothing scheduled, I was surprised by this pattern and looked at it by curiosity. And I got the idea to take some screenshot to show you how I look at those things. The easiest performance tool available in the Autonomous…

Read More