Infrastructure at your Service

Performance Archives - Page 3 of 8 - Blog dbi services

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

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
Franck Pachot

DataBase Cloud Service performance – IOPS

By | Development & Performance, Oracle | No Comments

Having a database in the cloud is very nice. You add storage with a few clicks. You don’t have to think about the storage type (DAS, SAN, NAS), size of disks, stripe size, SSD, etc. But do you have the performance you expect? I’ve tested I/O in the Cloud that just opened for Europe, so that I get an image of performance when nobody’s there. SLOB If you want to test IOPS, the tool is…

 
Read More
David Barbarin

Don’t use the default database file autogrow settings !

By | Application integration & Middleware | No Comments

I often noticed default values for database file autogrow settings during my audits. You don’t trust me? Let me talk about a funny story about an incredible (and extreme) situation that concerns a transaction log file and its default settings. First of all, during my intervention, my customer complained of some performance issues, so I decided to take a look at different areas of its instance. I found some interesting records inside the SQL Server…

 
Read More
Franck Pachot

Does the block size matter?

By | Database management, Oracle | 20 Comments

The default block size is 8k in Oracle. This is the most common. Sometimes, people create the database with 16k block size for datawarehouses. You can also find some 32k block size, but less common which means more bugs. In our tuning workshop we recommend the default, 8k, for most of the cases. I’ll show here 3 tests to show what different block size change for full table scan and index access. Test case I…

 
Read More