Obtaining Table SMO object causes table scan to be recorded

[chadbaldwin]

I'm not sure what the fix is here, but, that CASE statement does not short-circuit. Even if the IF condition is true, it still executes the subquery in the ELSE clause.

This means a table scan on one of the indexes (or heap) is recorded.

In most cases, this is probably not a big deal...but if you're using SMO objects to create backup scripts for unused indexes you are getting ready to drop...it's kind of a problem when all of a sudden SMO starts using those unused indexes, causing them to be excluded from the unused index detection queries.

sqlmanagementobjects/src/Microsoft/SqlServer/Management/SqlEnum/PostProcessTable.cs

Lines 81 to 83 in d6f3e68

	string queryRowCount = String.Format(CultureInfo.InvariantCulture, @"(CASE WHEN (tbl.is_memory_optimized=0)
	THEN ISNULL((SELECT SUM (spart.rows) FROM sys.partitions spart WHERE spart.object_id = tbl.object_id AND spart.index_id < 2), 0)
	ELSE ISNULL((SELECT COUNT(*) FROM [{0}].[{1}]), 0) END)", Util.EscapeString(this.schemaName, ']'), Util.EscapeString(this.tableName, ']'));

[chadbaldwin]

Well, maybe this isn't as big of an issue as I first thought it was.

I ran into this issue while using dbatools. But then I thought about it and realized that the RowCount filed is likely not populated at initialization, and more likely populated when the field value is requested, which it turns out it is.

dbatools includes the RowCount field as part of its default display view for Table SMO objects.

So as long as I don't display the RowCount field, it does not cause that code to run...that said, I still think it's an issue that it is not built to short-circuit and maybe another method should be considered to prevent unnecessary table scans.

[shueybubbles]

I'm not TSQL fluent enough to know what the fix would be. Would it need to use dynamic sql to run the ELSE select in isolation?

[chadbaldwin]

I'm not TSQL fluent enough to know what the fix would be. Would it need to use dynamic sql to run the ELSE select in isolation?

If you have access to the IsMemoryOptimized property, that would probably be ideal, but I'm not experienced enough with C# to know whether that's available at the point where the RowCount query is being run just by looking at the code.

The only other solution I can think of would be to convert it to use IF/ELSE instead.

[chadbaldwin]

Also, just realized this, but you may also want to use COUNT_BIG instead. If anyone out there has a memory optimized table with over 2bn rows, COUNT(*) will break.

It may also be worth looking into using this for the standard table row count...

CONVERT(bigint, OBJECTPROPERTYEX(OBJECT_ID('[{0}].[{1}]'), 'Cardinality'))

EDIT: The above snippet is wrong because it won't properly escape identifiers that contain square brackets. It's probably a rare situation, but still a situation that should be supported.

[shueybubbles]

When this code is running it has no knowledge of any other property values, and it tries to minimize the number of round trips to the server for performance.
It's unfortunate that TSQL needs to scan indexes before it actually runs the query on either part of an CASE block. Maybe using IF/ELSE will help on the server side.

thx for the other suggestions, if time permits I will give them a try.