SQL Server Performance Blog where milliseconds matter

Hi Guys, I would say a big thanks to Jeff Moden who have commented my previous post SQL Server, statistics, performance and the Ascending key problem . He made me think more deeply about the statistics and so i decided to do this second post just to clarify and add some thoughts. Enjoy the reading!   Statistic, cardinality and indexes From the point of view of returning a correct cardinality estimate value (and therefore having a precise execution plan), reading my previous post in the example paragraph, we referred to the existence of a clustered index on the table. It should be noted that it does not matter whether there is a clustered index or a not clustered one . I will demonstrate this with an example.   Example Suppose we create a heap table (therefore without any index) Suppone our table will have an id field and a value field. Now let's fill our table with 100 rows by putting a value from 1 to 100 in both the id and value fields. Create Table Example (id in...

Hi guys! After the simplification phase discussed in the last article, today we will speak about another fundamental step called Cardinality Estimation . During this step the optimizer try to predict the number of rows returned by each node of the execution plan. As you can imagine this is a step of primary importance because a good prediction generate an accurate execution plan with a lower processing cost to execute. The statistics In order to estimate the number of rows returned by a query the Cardinality Estimator use so called statistics . By default for each column and each index created, SQL Server create it's relative statistic. But what is a statistic? A statististic for query optimization is a binary large object called BLOBs that contain statistical information about the distribution of values for one o more columns of a table. We can observe statistics through the T-SQL command: DBCC SHOW_STATISTICS ( <TABLE> , <FIELD_NAME> ...