Infrastructure at your Service

Category Archives: Development & Performance

Pierre-Yves Brehier

DOAG 2018 – What to learn from a battle on IT technologies?

By | Development & Performance | No Comments

This year’s my 6th participation with dbi services at the DOAG Conference + Exhibition Nuremberg (as a “non-techie” attendee, no need to say), but it was my very first battle on IT technologies. And it was fun! On the DOAG 2018 Conference + Exhibition DOAG 2018 Conference + Exhibition is taking place November 20 – 23, 2018 in Nuremberg. Participants have the opportunity to attend a three-day lecture program with more than 400 talks and…

Read More
Christophe Cosme

Pass summit – day 4

By | Big Data, Business Intelligence, Cloud, Database Administration & Monitoring, Development & Performance | No Comments

Optimizing Multi-Billion Row Tables in Tabular in 2018   I wanted to attend the session moderated by Marco Russo to see his approach of optimizing performance in Tabular model The first thing to understand is how the data is stored and organized in a Tabular model. It is of course using the xVelocity in-memory capabilities with the Vertipac column storage layout engine. It organizes the data, compressing  it by column,  in combination with a dictionary…

Read More
Petre Radut

SQL Plan stability in 11G using stored outlines

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

Plan stability preserves execution plans in stored outlines. An outline is implemented as a set of optimizer hints that are associated with the SQL statement. If the use of the outline is enabled for the statement, then Oracle Database automatically considers the stored hints and tries to generate an execution plan in accordance with those hints (Oracle documentation). Oracle Database can create a public or private stored outline for one or all SQL statements. The…

Read More
Nicolas Penot

[Oracle 18c new feature] Quick win to improve queries with Memoptimized Rowstore

By | Database Administration & Monitoring, Development & Performance | One Comment

With its 18th release Oracle comes with many improvements. Some of them are obvious and some of them more discrete. This is the case of the new buffer area (memory area) called the Memoptimize pool. This new area, part of the SGA, is used to store the data and metadata of standard Oracle tables (heap-organized tables) to improve significantly the performance of queries having filter on PKs. This new MEMPTIMIZE POOL memory area is split…

Read More
Stéphane Haby

SQL Server Tips: How many different datetime are in my column and what the delta?

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

Few months ago, a customer asks me for finding in a column, how many rows exist with the same date & time and the delta between them. The column default value  is based on the function CURRENT_TIMESTAMP and used as key as well. This is obviously a very bad idea but let’s go ahead… This anti pattern may lead to a lot of duplicate keys and the customer wanted to get a picture of the…

Read More
David Barbarin

SQL Server on Linux – I/O internal thoughts

By | Database management, Development & Performance, SQL Server | 2 Comments

Let’s start the story from the beginning with some funny testing I tried to perform with SQL Server on Linux a couple of months ago. At that time, I wanted to get some pictures of syscalls from SQL Server as I already did in a past on Windows side with sysinternal tools as procmon and stack traces. On Linux strace is probably one of the best counterparts. Firstly, please note this blog is just from…

Read More
Nicolas Penot

How to fix your own SQL plan in Oracle ?

By | Database Administration & Monitoring, Database management, Development & Performance | One Comment

Method to build and fix your own SQL plans in Oracle and then you want to fix it for all next executions of the SQL by your application (thanks to SPM). In this post I show how fix a plan you have created by yourself. First we need to identified the query SQL> select /* INDEX_ACCESS */ line_item_id, product_id, unit_price, quantity from ORDER_ITEMS where order_id = 234098 ; LINE_ITEM_ID PRODUCT_ID UNIT_PRICE QUANTITY ———— ———- ———-…

Read More
Stéphane Haby

SP2 for SQL Server 2016 is available with new helpful DMVs

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

Last month (April 24, 2018), the Service Pack 2 for SQL Server 2016 was released and distributed. This Service Pack has new DMVs, already available in SQL Server 2017 RTM. In this article, I will just write few words about 2 DMVs (sys.dm_db_log_stats & sys.dm_db_log_info) and a new column (modified_extent_page_count) in the DMV sys.dm_db_file_space_usage that I presented during our last event about SQL Server 2017. I think they are really helpful for DBA. It’s also…

Read More
Mehdi Bada

Deploy a Cloudera cluster with Terraform and Ansible in Azure – part 2

By | Big Data, Cloud, Development & Performance | No Comments

In this part of the blog posts series, we will show how ansible helps us to configure our cluster and install all pre-requisite needed for Cloudera Manager. Ansible is one of the most important automation tools currently. Ansible will help us to configure all nodes for a manual installation using Cloudera Manager. Our playbook will contain the following roles: cm_repo: add the same C.M repo into all nodes. os_config: Adjust all OS parameter for installing…

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