Accurate statistics about the data held in tables are used to provide the best execution strategy for SQL queries. but if the statistics don't accurately reflect the current contents of the table you'll get a poorly-performing query. How do you find out if statistics are correct, and what can you do if the automatic update of statistics isn't right for the way a table is used? … Read more
Normally, the query Optimiser won't consider 'bushy' plans, where both operands to a join operator could be intermediate results from other joins. This means that it won't produce good query plans for some types of query. Hints, by themselves won't do it. More powerful magic is required.… Read more
The performance of 'extract, transform, load' (ETL) processes for large quantities of data can always be improved by objective testing and experiment with alternative techniques. The cause of poor performance can sometimes be surprising.… Read more
If you need to optimise SQL Server performance, it pays to understand SQL Server Statistics. Grant Fritchey answers the 18 most frequently-asked questions about SQL Server Statistics: the ones we somehow feel silly asking in public, and think twice about doing so.… Read more
SQL Server Indexes need to be effective. It is wrong to have too few or too many. The ones you create must ensure that the workload reads the data quickly with a minimum of I/O. As well as a sound knowledge of the way that relational databases work, it helps to be familiar with the Dynamic Management Objects that are there to assist with your indexing strategy.… Read more
There are seven common antipatterns in TSQL coding that make code perform badly, and three good habits which will generally ensure that your code runs fast. If you learn nothing else from this list of great advice from Grant, just keep in mind that you should 'write for the optimizer'.… Read more
Prefetching can make a surprising difference to SQL Server query execution times where there is a high incidence of waiting for disk i/o operations, but the benefits come at a cost. Mostly, the Query Optimizer gets it right, but occasionally there are queries that would benefit from tuning. … Read more
When healing a sick SQL Server, you must forget the idea that there could ever be a simple correspondence between symptom and disease: The art of troubleshooting is much more the art of discovering, and assembling, the various pieces of the puzzle so that you have a complete understanding of what is going on inside of a server… Read more
While developing their new SQL Source Control tool, a team at Red Gate learned a lot about Agile development, as well as the benefits (and challenges) of rapid, regular Early Access releases. Stephanie Herr (project manager for SQL Source Control) and Mark Wightman (head of development at Red Gate Software) tell us more.… Read more
It is dangerous to assume that your data is sound. SQL already has intrinsic ways to cope with missing, or unknown data in its comparison predicate operators, or Theta operators. Can SQL be more effective in the way it deals with data quality? Joe Celko describes how the SQL Standard could soon evolve to deal with data in ways that allow aggregation and windowing in cases where the data quality is less than perfect
… Read more
Since the introduction of SQL Server 2005, there is a simple lightweight trace that is left running by default on every SQL Server. This provides some very valuable information for the DBA about the running server, but it isn't well-documented. Feodor reveals many of the secrets of this facility and shows how to get reports from it.
… Read more
Once, when disk space was at a premium, DBAs fought hard to keep the size of their database down. Now there seems less motivation to 'fight the flab' of a database. Fabiano Amorim was watching television recently when the subject matter, cosmetic surgery, gave him the theme and inspiration for this guide to keeping your database fit and trim.
… Read more
The UNION, EXCEPT and INTERSECT operators of SQL enable you to combine more than one SELECT statement to form a single result set. The UNION operator returns all rows. The INTERSECT operator returns all rows that are in both result sets. The EXCEPT operator returns the rows that are only in the first result set but not in the second. Simple? Rob Sheldon explains all, with plenty of examples… Read more
Occasionally, when tracking down a performance problem, you have to have information over time on the values of particular sets of performance counters. To track down the more insidious or sporadic problem, it is best to have the data in a SQL Server table, so you can query it. Feodor Georgiev explains the command-line way of doing this.
… Read more
Dynamic Management Views and Functions aren't always easy to understand. However, they are the easiest way of finding out which of your stored procedures are using up the most resources. Greg takes the time to explain how and why these DMVs and DMFs get their information. Suddenly, it all gets clearer.… Read more
While we all know that the Query Optimizer uses Indexes to produce better execution plans, we don't all know exactly which indexes will give the best results. Benjamin Nevarez has done the research, and in this sample from his upcoming book, "Inside the SQL Server Query Optimizer", he helps us to understand how the Query Optimizer selects indexes to improve execution plans.… Read more
SQL Server Statistics assist the query optimiser to calculate the best way of running the query. Holger describes every common way that things can go wrong with statistics, and how to put matters right.
… Read more
In a perfect world, we should just be able to write queries and leave the RDBMS to decide the perfect strategy to get the data. Until then we have to understand the Query Optimizer and the way it uses distribution statistics. … Read more
On Ask.SQLServerCentral.com, a group of people interested in experimenting with heavily optimised SQL techniques try them out on a problem, using reasonbly large amounts of data. They aren't so interested in explaining the techniques, so Kathi continues on her mission to explain the lessons learned and the tips that can be derived.… Read more
The SQL Speed Freak Challenge is a no-holds-barred competition to find the fastest way in SQL Server to perform a real-life database task. It is the programming equivalent of drag racing, but without the commentary box. Kathi has stepped in to explain what happened with the second challenge and why some SQL ran faster than others.… Read more
Subscribe for more articles
Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.