One of the first things you learn when working with SQL Server, and other transactional based SQL systems, is that set based operations perform best. If you are querying data, a cursor pulling individual rows doesn’t perform as well as a single query. It doesn’t matter if that cursor is on the client side or … Read more
When inserting rows into a heap, SQL Server must sometimes update the PFS page which can PFS contention and affect performance. In this article, Uwe Ricken demonstrates how to measure PFS contention.… Read more
Indexes are expensive to maintain so we want to get the greatest benefit we can from them and make sure that Oracle uses them whenever it’s appropriate. Sometimes, though, the optimizer seems to ignore indexes that we think would be very effective and when this happens it’s often because the statistics that Oracle has gathered do not reflect the quality… Read more
Percentiles give meaning to measurements by telling you the percentage of the population being measured who get higher or lower values. They are now easier to calculate in SQL, and are useful for reporting; but are the new analytic functions faster and more efficient than the older methods? Dwain Camps demonstrates, and investigates their relative performance performance.… Read more
SQL Server batch processes are usually run from SQL Agent in background. They can take significant time and resources, especially if they are ETL tasks. Quite often, the responsibility for creating these tasks belongs entirely to the developer. Dennes demonstrates that DBAs can advise and assist with this type of batch job by bringing their expertise to bear on the problem of reducing their impact on the working system to a minimum.… Read more
When you're developing database applications, it pays to check for index scans in the SQL Server query plan cache. Once you've identified the queries, what next? Dennes Torres gives some preliminary guidelines on how to find out why these index scans are being chosen for these queries and how to make the queries run faster and more efficiently.… Read more
As developers we always want to produce efficient code, but efficient in what - development time or overall performance? In this article Jon Smith describes how to investigate and understand performance so that you can decide how to balance these two goals. The article starts with high level performance issues and drills down to detailed code tuning using examples taken from real applications.… Read more
The easiest way to make a responsive web application perform well is to minimize requests and the amount of data that is downloaded. The most dramatic way of doing this, for mobile applications particularly, is to download just the data you need to use. There are additional ways of doing this, such as 'Mobile first', Prioritized content, Intelligent markup and Compression, but the most important task is to minimize the data-download requirements.… Read more
When SQL Server 2014 was released, it included Hekaton, Microsoft's much talked about memory-optimized engine that brings In-Memory OLTP into play. With memory-optimized tables 30 times faster than disk-based tables, higher performance is promised - but at what cost? Jonathan Watts looks at the features that have improved, and those that need careful consideration.… Read more
Performance tuning and optimization definitely have their place in minimizing SQL Server Licensing costs - by helping keep CPU utilization low. But it's important to remember that the fastest and most efficient query possible is the one that you never execute against your SQL Server. That might sound trite, but it's at the heart of caching - which is key to helping organizations save significant money on SQL Server licensing costs while simultaneously enabling better application performance and increased scalability. … Read more
As a coder, the more you know about the business, the better code you're going to write. The more you know about the language in which you code regularly, the better code you're going to write. Similarly, if you're tasked with writing code against a relational database engine, you're going to need to know more about that engine. If you can examine and understand execution plans, you can achieve better understanding of the database system and you will write better database code. Grant Fritchey shows you how.… Read more
As a DBA, it is vital to manage transaction log growth explicitly, rather than let SQL Server auto-growth events "manage" it for you. If you undersize the log, and then let SQL Server auto-grow it in small increments, you'll end up with a very fragmented log. Examples in the article, extracted from SQL Server Transaction Log Management by Tony Davis and Gail Shaw, demonstrate how this can have a significant impact on the performance of any SQL Server operations that need to read the log.… Read more
Dwain Camps show that, depending on the size and characteristics of some hierarchical data, six different methods of traversal can each be the fastest at some point. He illustrates convincingly that It is dangerous to generalize from just one set of test data, and it is foolish to assume that, just because SQL code looks neat, it will perform well.… Read more
One of the first things one needs to understand well about SQL Server is indexes, but somehow many of the basic questions don't often get asked or answered much on forums. Rob Sheldon answers some of these shy-making questions about SQL Server Indexes and indexing: the ones we somehow feel silly asking in public, and think twice about doing so.… Read more
Relational division is used in SQL to select rows that conform to a number of different criteria. It is a neglected but effective technique for a number of tasks. Although the SQL can appear daunting and complex, it can perform very well if you reduce the rows as much as possible before applying the final logic. Dwain Camps explains how, and shows the performance gains.… Read more
Database administrators tend to rely more on science than intuition, and may therefore seem exasperatingly cautious to other IT people. A relational database is a complex machine, however, that is asked to deal with intricate problems. When things go wrong, it usually takes knowledge, deduction and a systematic approach to track down the root core of problems and to determine the best fix.… Read more
Imagine it. You've been asked to fix a dire performance problem with a SQL Server database. You find a severe case of 'Cache Bloat' due to ad-hoc queries, but you can't fix the code itself. What should you do? Specify forced parameterization? Perhaps a better idea would be to use guide plans.… Read more
When a SQL Server instance deadlocks, it can be anything from minor irritation to something far more severe. In this article, Gail Shaw looks at how you can identify common types of deadlock, the difference between a deadlock and severe blocking, and how to avoid and fix the most common deadlock types.… Read more
Alex Kuznetsov describes the agile principles, techniques and tools that allowed his development team to make frequent database refactoring a reality, without disrupting users. He explains how this allowed his team to apply an iterative, evolutionary approach to the design and development of their databases, as well as applications.… Read more
When you're instrumenting an ASP.NET MVC or Web API application to monitor its performance while it is running, it makes sense to use custom performance counters.There are plenty of tools available that read performance counter data, report on it and create alerts based on it. You can then plot application metrics against all sorts of server and workstation metrics.This way, there will always be the right data to guide your tuning efforts.… Read more
