Infrastructure at your Service

Tag

Performance Archives - Blog dbi services

David Barbarin

RCSI with foreign keys, NULL values and paramater sniffing behavior

By | Development & Performance, SQL Server | No Comments

In this blog post let’s go back to the roots (DBA concern) with a discussion with one of my friends about a weird transaction locking issue. In fact, this discussion was specifically around two questions. The first one was why SQL Server continues to use shared locks in RCSI mode leading to blocking scenarios and the second one was about compiled objects with weird NULL value parameter sniffing behavior. This discussion was very funny for…

Read More
Petre Radut

When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is not modified

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

Below, I will try to explain, a particular case for the general error : ORA-01555 snapshot too old error.. Normally, when we have this error, we are trying to adapt the retention parameters or to tune our queries. SQL> show parameter undo; NAME TYPE VALUE ———————————— ———– —————————— temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 3600 –extended from 900, undo_tablespace string UNDOTBS1 But, are some scenarios where the above rule is not working . We…

Read More
Oracle Team

Lighty for PostgreSQL

By | Database Administration & Monitoring, Postgres | No Comments

By Franck Pachot . If you follow this blog, you should know how I like Orachrome Lighty for Oracle, for its efficiency to monitor database performance statistics. Today Orachrome released the beta version of Lighty for Postgres: https://orachrome.com/news/la-beta-de-lighty-for-postgresql-est-ouverte/ The Cloud is perfect to do short tests with more resources than my laptop, especially the predictability of performance, then I started a Bitnami Postgres Compute service on the Oracle Cloud and did some tests with pgbench…

Read More
Stéphane Haby

SQL Server 2017: Can I run a “standard” rebuild on the index after a Resumable Online Index Rebuild is suspended?

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

This article is an additional article to my precedent about “SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?” The goal is to see after I suspend the rebuild, if I can run a traditional rebuild without the option RESUME.   I start an index rebuild with the option Resumable: ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction] REBUILD WITH (RESUMABLE = ON, ONLINE = ON) GO   In a new query window,…

Read More
Stéphane Haby

SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?

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

Last week, I presented this option in our internal event, the dbi xChange. My colleague David Barbarin asked me this question, what’s append if an interruption occurs (reboot, SQL Server service stopped,…) during a Resumable Online Index Rebuild? I was not able to answer because I forgot to do this test. The new enhancement for an index rebuild provides the capability to suspend, resume or abort an online index operation (msdn reference). I always use…

Read More
Clemens Bleile

CPUs: Cores versus Threads on an Oracle Server

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

When doing a performance review I often do talk with the DBA about the CPU utilization of the server. How reliable is the server CPU utilization with tools like top or the host CPU utilization in the AWR-report? E.g. on an Linux Intel x86-64 server with 8 Cores and 16 logical CPUs (Intel Hyperthreading), what does a utilization of 50% mean? As I had an ODA X7-M in a test lab available, I thought I’ll…

Read More
David Barbarin

Index seek operator and residual IO

By | Database Administration & Monitoring | No Comments

This blog post draws on the previous article about index seek and gotchas. I encountered another interesting case but it is not so much because of the nature of the problem I decided to write this article but rather the different ways that exist to troubleshoot it. Firstly, let’s set the scene: A simple update query and its corresponding execution plan that tend to say the plan is efficient  in terms of performance declare @P0…

Read More
David Barbarin

Experiencing update statistics on a big table with circuitous ways

By | Database Administration & Monitoring | No Comments

This is my first blog of this new year and since a while by the way. Indeed, last year, I put all my energy to realign my Linux skills with the new Microsoft’s strategy that opens SQL Server to Open Source world. But at the same time, I did a lot of interesting stuff at customers shops and I decided to write about one  of them to begin this new year 2018. In this blog…

Read More
David Barbarin

SQL Server 2016: distributed availability groups and cascaded replicas

By | Database Administration & Monitoring | No Comments

During the last MVP summit, we had some interesting discussions about availability groups with the SQL Server team and I remember someone asked for managing scenarios like Oracle cascaded destinations and the good news is that SQL Server 2016 already addresses this kind of scenario with distributed availability groups. For example, let’s say you have to manage heavily reporting activity on your application and a solution would be to offload this activity across several secondary…

Read More
David Barbarin

SQL Server AlwaysOn availability groups and statistic issues on secondaries

By | Database Administration & Monitoring | 9 Comments

I would like to share with you an interesting issue you may face while using SQL Server AlwaysOn availability groups and secondary read-only replicas. For those who use secondary read-only replicas as reporting servers, keep reading this blog post because it is about update statistics behavior on the secondary replicas and as you may know cardinality estimation accuracy is an important part of the queries performance in this case. So a couple of days ago,…

Read More