August 1, 2019 at 12:00 am
Comments posted to this topic are about the item How to Unclog SQL Server
August 1, 2019 at 1:54 pm
Is it really fair to not create a PK on the table variable and create one on the temp table and compare the query plan?
You CAN create a primary key on table variables. In your examples, you are basically comparing a query against a heap to a query against a table with a clustered index. You'd get the same plan with the temp table had you not created the primary key. This is not a fair comparison.
August 1, 2019 at 3:17 pm
Hi, @dbishop!
So, when you do that, what happens on my machine is the execution time actually goes up by a second (to generate the PK), and still it estimates 1 row with 39 actual rows executed. So I felt it was fair to give the table variable the best chance possible with going with the 9 second execution (leaving the PK out) since it didn't affect the row counts.
August 1, 2019 at 10:32 pm
Hello Pamela! Thank you for the article. I do have one question. In the query, which collects the table row counts, you have this in the SELECT clause: SUM(ps.row_count) AS [RowCount], but at the same time you GROUP BY on the same column: GROUP BY t.schema_id, t.name, ps.row_count. This doesn't seem right. Could you comment?
August 2, 2019 at 12:59 pm
Hi Mischa,
I had to do it that way because some of the tables on my server are very large and partitioned. I didn't want the row counts per partition, I just wanted one row count of each table. So I took the initial output (from the part of the script that you are describing), and then clean them up with the insert into the last table. It is probably not the only way to do it (just my way), so if you have a way that works better, please let me know. We can learn from each other that way!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply