How to Unclog SQL Server

  • pamelamooney1966

    SSC Veteran

    Points: 248

    Comments posted to this topic are about the item How to Unclog SQL Server

  • dbishop

    Mr or Mrs. 500

    Points: 565

    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.

  • pamelamooney1966

    SSC Veteran

    Points: 248

    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.

     

  • Misha_SQL

    SSCertifiable

    Points: 5386

    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?

    • This reply was modified 4 months, 1 week ago by  Misha_SQL.
  • pamelamooney1966

    SSC Veteran

    Points: 248

    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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply