Infrastructure at your Service

Performance Archives - Page 2 of 8 - Blog dbi services

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
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
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
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
Clemens Bleile

Enable 10046 Tracing for a specific SQL

By | Development & Performance | 9 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
David Barbarin

SQL Server AlwaysOn and availability groups : session timeout parameter

By | Database Administration & Monitoring | 12 Comments

Have you ever faced to timeout issues with SQL Server availability groups? If you take a look at the timeout parameters for an availability group, you will find a plenty of configuration settings. Indeed, you have timeout parameters for the availability group itself, for the replicas and for cluster itself as well. Well …a lot of parameters in order to deal with timeout issues. In fact, some of them can be very helpful regarding the…

 
Read More
Stéphane Haby

SQL Server Collation for System Center Products

By | Application integration & Middleware, Development & Performance | No Comments

A customer asks me, what is the collation for System Center products? Do we have a general collation for all products? And what is the BP for Tempdb? And how work the licencing for SQL Server in System Center (is SQL Server included)??? I answer just “OK, OK, I understand, I wil check those points…” I  search those information on Microsoft TechNet website, blogs, presentation, etc.. and I  think that it is interesting to share…

 
Read More