Auto added space in field while updating table in database

You see the internal table DM_TRAN_CURRENT_TRANSACTION that is called by invoking OPENROWSET and an estimated row-count of 1000, which is far from reality.

Picture 2: Row-count estimation for TVF Solution: If possible, give the optimizer some support by specifying the row-count through the TOP(n) clause.

auto added space in field while updating table in database-8auto added space in field while updating table in database-46

Those operators have tied default cardinality estimations.

(Depending on the server version and the operator, these are 1, 1000, or 10000.) Have a look at the following query: Here’s what BOL states: “Returns a single row that displays the state information of the transaction in the current session.” But look at the execution plan in Picture 2. Apparently the optimizer does not take BOL content into account when generating the plan.

As a rule of thumb, use temporary tables (tables with a “#” as their name’s first character), rather than table variables, for temp tables with more than 100 rows.

Problem: SQL Server does not maintain statistics for XML and spatial data types. So, don’t try to find statistics for these columns, as they’re simply not there.

This will only be effective if n is less than the Estimated Number of Rows, 1000 in our example.

So, if you have a guess of how many rows your Oracle query is about to return, just add TOP(n) to your OPENQUERY statement.

The Database Engine keeps statistics about the distribution of the key values in each index of the table, and uses these statistics to determine which index or indexes to use in compiling the query plan.

If, however, there are problems with these statistics then the performance of queries will suffer.

Solution: If you experience performance problems with queries that have to search through XML data, or filter spatial columns e.g., XML or spatial indexes may help.

But that’s another story and beyond the scope of this article.

Problem: If you’ve set the option AUTO CREATE STATISTICS OFF and overlooked the task of creating statistics manually, the optimizer will suffer from missing statistics.

Tags: , ,