Infrastructure at your Service

Performance Archives - Page 2 of 9 - Blog dbi services

Aug 02 2016
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
Jul 08 2016
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
Jun 08 2016
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
Mar 18 2016
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
Mar 14 2016
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
Mar 02 2016
David Barbarin

Sudden drop of the page life expectandy but don’t panic yet!

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

A couple of weeks ago, I had an interesting discussion with one of my customer about the page life expectancy (aka PLE). He asked me if monitoring the metric value is a best practice because he often noticed a big drop of the PLE under the recommended threshold during the night or sometimes during the business hours. In addition, he didn’t want to get false positive especially during the night when maintenance tasks are performed without…

Read More
Feb 26 2016
David Barbarin

Changing an existing partition configuration … Well, not so easy!

By | Development & Performance | No Comments

This time let’s talk about an interesting customer scenario where table partitioning was implemented on a table with 100 GB of data on SQL Server 2014. Partitioning in this context aimed to save disk space  (archive data were compressed) , help to reduce maintenance time and consumed resources as well (by using index and statistic maintenance operations on the active partition).  Finally, it will help to improve the queries performance on the concerned table that mainly focused…

Read More
Feb 15 2016
David Barbarin

NOLOCK is definitely not what you may expect …

By | Development & Performance | No Comments

For those who still believe that using nolock hint is a lock-free process, keep reading this blog post. I wrote a previous blog post 3 years ago, when I was at one of my customer and we had an interesting discussion about the nolock hint with reporting queries. I convinced him that nolock continues to generate locks even if the shared lock was evicted in this case. So this time, I experienced an interesting another…

Read More
Feb 05 2016
Clemens Bleile

Enable 10046 Tracing for a specific SQL

By | Development & Performance | 10 Comments

Available methods to enable 10046 trace are described in My Oracle Support Note 376442.1. You can enable 10046-tracing – on session level (alter session) – for other sessions (e.g. with oradebug, the package DBMS_MONITOR or DBMS_SYSTEM) What is not covered with the methods above is the possibility to trace a specific SQL-statement, which runs “somewhen” in the future on the database. E.g. a SQL, which runs during a next batch job. With the introduction of…

Read More