Infrastructure at your Service

Performance Archives - Blog dbi services

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 | One Comment

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
David Barbarin

Partitioning – When data movement is not performed as expected

By | Database Administration & Monitoring | No Comments

This blog is about an interesting partitioning story and curious data movements during merge operation. I was at my one of my customer uses intensively partitioning for various reasons including archiving and manageability. A couple of days ago, we decided to test the new fresh developed script, which will carry out the automatic archiving stuff against the concerned database in quality environment. Let’s describe a little bit the context. We used a range-based data distribution…

 
Read More
David Barbarin

When an index seek operator is not always your friend

By | Development & Performance | No Comments

Did you ever consider an index seek as an issue? . Well, let’s talk about a story with one of my customers where the context is pretty simple: a particular query that is out of the range of the application’s performance requirements (roughly 200ms of execution time in average). The execution plan of the query was similar to what you may see in the picture below:   At first glance, no obvious way to improve…

 
Read More
David Barbarin

SQL Server AlwaysOn: troubleshooting improvements with new service packs

By | Database Administration & Monitoring | No Comments

As promised in my latest blog, I will talk about improvements in the detection of the availability group replication latency. As a reminder, replication latency between high available replicas may be monitored in different manners so far. Firstly, in my experience I had the opportunity to use different performance counters as “Database Replica:Transaction Delay” and “Databas Replica:Mirrored Write Transactions/sec” in order to get a good picture of the latency over the time after implementing an…

 
Read More
David Barbarin

SQL Server 2016 AlwaysOn: Direct seeding and performance considerations

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

This blog post follows the previous one about new direct seeding feature shipped with SQL Server 2016. As a reminder, I had some doubts about using direct seeding with large databases because log stream is not compressed by default but I forgot the performance improvements described into the Microsoft BOL. I also remembered to talk about it a couple of months ago in this blog post. So let’s try to combine all the things with the…

 
Read More
David Barbarin

SQLSaturday #510–Locks, latches et spinlocks

By | Technology Survey | No Comments

J-16 avant le prochain SQL Saturday à Paris et il est encore temps de s’inscrire.  Le prochain SQL Saturday en quelques chiffres c’est: 4 parcours 20 sessions + Chalk-Talk 9 nationalités 15 speakers francophones 10 speakers anglophones de réputation internationale En résumé un événement à ne pas louper! Pour ma part j’aurais l’opportunité de parler du verrouillage logique et physique sous SQL Server (dernière session de la journée). En guise d’appetizer: débogage de problèmes de latch…

 
Read More
David Barbarin

SQL Server 2016: new database-scoped configuration parameters part 2

By | Database Administration & Monitoring | No Comments

In this blog post, let’s continue with other database-scoped options shipped with the SQL Server 2016 RC0. We will first begin with the first one and my preferred option: setting the MAXDOP value for an individual database. I really hope that applications like SharePoint that requires to limit the MAXDOP to 1 at the server level will use this kind of options in the future. As a reminder, changing this configuration parameter at the server…

 
Read More
David Barbarin

SQL Server 2016 : nonclustered columnstore index and aggregate pushdown

By | Development & Performance | 2 Comments

Did you miss aggregate pushdown capability shipped with columnstore index? Well, I remember the first time I heard about it is was when I read the very interesting blog post of Niko Neugebauer here with a very good covering of the topic (principle, advantages and limitations). So why to write a new blog post here? Well because since the last Niko’s blog article, this feature has been improved and I wanted to test it with…

 
Read More