In my book “Cost Based Oracle – Fundamentals” (Apress 2005) I described a problem with the cost calculation for the index fast full scan: When you generate statistics for a table, one of the results is the number of blocks below the high water mark. When you generate the statistics for an index, you get the number of leaf blocks… Read more
In an earlier article on investigating indexes I described some methods for looking at the internal structure of an index to get an idea of its current state of health. Although this type of knowledge helps you to make informed decisions about which indexes might need special treatment (like being permanently dropped, perhaps) it doesn’t immediately answer questions like: “what’s… 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
SQL Server filtered indexes can save space and improve performance if they are used properly. Under what circumstances can they be used? When are they most effective, and what sort of performance gain or space-saving is likely? How does a filtered index affect the choice of execution plan? Seth explores these questions with practical experiments… Read more
We decided to ask various well-known SQL Server people to write about their favourite SQL Server Howlers. These are the common misunderstandings about how SQL Server works that end in tears, and plaintive forum questions. Grant Fritchey opens the series with some of his favourite howlers.… Read more
In this final look into his everyday essentials, Laerte Junior provides some useful scripts for the DBA that use an alternative way of error-logging. He shows how to use a PowerShell script to check and, if necessary, to defragment your indexes, write data to a SQL Server table, and change the collation for a table. Being an exceptional DBA just got a little easier.… Read more
Laerte recently experienced an I/O nightmare, which, as a happy accident, gave him an opportunity to test out the 'Missing Indexes' DMVs and see if they were up to scratch. He solved his problems, ran a few tests to get a feel for how well the DMVs performed, and was very impressed. So much so, that he is sharing his findings with us.… Read more