![]() Statistics, Heuristics, and Row Estimations Instead there’s a Cardinality Estimator, which uses a whole pile of complex algorithms to estimate the row counts.įor further information on row estimation, This is a problem, because the optimizer can’t run the query to determine the row count, as an execution plan is needed to run a query and the row count is needed to generate the execution plan. To produce a good enough execution plan, the optimizer needs to know how many rows will be processed by each operator in the query. The importance of this will be apparent later in this article. That means that the optimizer will still generate an execution plan for a query such as shown in Listing 1, even though the IF condition means that the query will never execute. ![]() Control flow statements aren’t evaluated because the optimizer cannot execute T-SQL code. For the purpose of this article, I’m going to assume it’s a stored procedure, but the rules for the others are similar enough.ĭuring the query compilation process, the stored procedure passes from the parser to the algebrizer then to the optimizer, and the latter generates execution plans for all applicable statements in the procedure, prior to any of them being executed. This means either a stored procedure, function or a set of ad-hoc statements. The optimizer operates on a batch (unless there’s a recompile, but let’s ignore that for now). Let’s just look at the high-level process. There are several references available on the internals of the query optimizer, so I’m not going to go into a huge amount of detail. Great, but not so great unfortunately when the same principles are applied to the database They don’t work so well in T-SQL and they’re often not compatible with how the query optimizer behaves.Īnd so first, a brief, high-level diversion into how the query optimizer behaves. Removing duplication reduces the chance of bugs, such as if a change is made in one place but not the other, makes maintenance easier and allows future development to be faster by providing existing building blocks. If a similar task is performed in two places, the similarity should be refactored out and a function (module, method) created with the common code a function that is then called from the two places. Modern software engineering principles encourage code reuse. Part of the problem is that this is another place where good software design practices for front end languages aren’t always suitable for database queries. Those in turn lead to poor performance, unhappy users and late nights trying to figure out why that damn database call is timing out again. A confused optimizer generates sub-optimal or even plain wacky execution plans. ![]() Now, of course, confusing the optimizer isn’t something we generally want to do. There’s a very easy way to confuse the SQL Server query optimizer: write generic queries intended to be reused as much as possible. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |