Infrastructure at your Service

Performance Archives - Page 3 of 9 - Blog dbi services

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

SQL Server 2016 : availability groups and performance improvements

By | Development & Performance, Hardware & Storage | No Comments

During the last SQL Saturday in Paris, I had the opportunity to co-present the new SQL Server 2016 features and one of my topic concerned the availability group’s enhancements. I mentioned the replication throughput improvement but without giving more details and this is the idea of this blog post. First of all, let’s remind that the network throughput is part of the synchronous replication process and it is directly tied to the application transaction performance….

 
Read More
David Barbarin

SQL Server 2016 query store

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

One of the most new interesting features that will be introduced by SQL Server 2016 is certainly the query store that will complete the performance tuning toolbox of database administrators. Basically, the query store will track queries, their query plans and runtime statistics as well. It will be a great tool to detect regressing queries. I remember a real customer case a couple of months ago where I was involved in a database migration project…

 
Read More
Stéphane Haby

SSISDB is too big and it is not every time the retention period’s fault!

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

In all blogs that I read, the solution is just to change the retention period to a reasonable value. Yes, it is true but in some case you need to go deeper in the analysis… Like you, I changed the retention period, ran the cleanup but finally, I had always a big database… Why, Why, Why? My first step for this analysis, is to see in the SSISDB, the Disk Usage by Table Reports from…

 
Read More
David Barbarin

Want to benchmark your storage? It’s time to switch to DiskSpd tool!

By | Development & Performance | No Comments

As you certainly know, the well-known tool SQLIO is officially deprecated since few months. From my part, I had the opportunity (maybe the last) to use SQLIO for a customer project in order to benchmark the storage for SQL Server AlwaysOn and availability groups. Now it’s time to change for a new benchmark tool : DiskSpd. First of all, DiskSpd is an open source and free based utility, provided by the Windows development team, it…

 
Read More
David Barbarin

SQL Server AlwaysOn: number of databases and the hadr worker pool

By | Database Administration & Monitoring | No Comments

Often, when I’m in charge to implement an SQL Server AlwaysOn infrastructure with availability groups, customers ask me if it exists some best practices and recommendations about the maximum number of databases in an availability group planning. In fact, we have to keep in mind the relation between databases and the SQL Server worker pool in this architecture. Remember that the SQL Server AlwaysOn and availability group feature is an extension of the SQL Server…

 
Read More
David Barbarin

Join transitive closure and query simplification considerations

By | Development & Performance | No Comments

Yesterday, I had a discussion with one of my friend who works with Oracle about some different behaviours between each query optimizer against one particular query. You can read his blog post and get the repro script here. I decided to write a blog post because our discussion has introduced some interesting concepts and I wanted to explain more things from the SQL Server side. First of all let me set the scene that is…

 
Read More