3 added 457 characters in body
source | link

Without further info, this is more of speculation but judging on what we have:

  • a table that is quite wide (1.3 to 4.0 rows per page on average)
  • the query that is slow is using:
    • only PWFID on the join condition,
    • two columns Title, SITime on the select list and
    • no other column anywhere (WHERE, HAVING etc.)

Then a covering non-clustered index on (PWFID) INCLUDE (SITime, Title) will probably improve the efficiency of the query as it will need to read a narrower index (and no lookups to the table, whether it's clustered or a heap). No idea how much improvement it would be, as the query involves joining of 30 something tables - and the index will not be that narrow either, with the included 500 character column.

 

About converting the table to a heap:

This makes me wonder if getting rid of the clustered index and make this table a heap makes sense or not, because I can just "save" the clustered index and free some space for having a covering nonclustered index?

This is irrelevant I think, at least for this and similar queries. It might change/improve the behaviour of insert queries (as no clustered index will have to be maintained) but it may also degrade performance for other queries that depend on finding more columns from the clustered index.

And you won't be saving any (or much) space. The data has to be stored somewhere, whether the table is a heap or clustered.


Adding a NC index is much less drastic change and I wouldn't expect any side effects - apart from the wanted use of it in the query - but still needs to be tested.

Removing the clustered index and converting a table to a heap is effectively changing the structure of all NC indexes and removing of (the clustered) one, so it may have several and more serious effects on many operations/queries performed and would need much more testing.

Without further info, this is more of speculation but judging on what we have:

  • a table that is quite wide (1.3 to 4.0 rows per page on average)
  • the query that is slow is using:
    • only PWFID on the join condition,
    • two columns Title, SITime on the select list and
    • no other column anywhere (WHERE, HAVING etc.)

Then a covering non-clustered index on (PWFID) INCLUDE (SITime, Title) will probably improve the efficiency of the query as it will need to read a narrower index (and no lookups to the table, whether it's clustered or a heap). No idea how much improvement it would be, as the query involves joining of 30 something tables - and the index will not be that narrow either, with the included 500 character column.

About converting the table to a heap:

This makes me wonder if getting rid of the clustered index and make this table a heap makes sense or not, because I can just "save" the clustered index and free some space for having a covering nonclustered index?

This is irrelevant I think, at least for this and similar queries. It might change/improve the behaviour of insert queries (as no clustered index will have to be maintained) but it may also degrade performance for other queries that depend on finding more columns from the clustered index.

And you won't be saving any (or much) space. The data has to be stored somewhere, whether the table is a heap or clustered.

Without further info, this is more of speculation but judging on what we have:

  • a table that is quite wide (1.3 to 4.0 rows per page on average)
  • the query that is slow is using:
    • only PWFID on the join condition,
    • two columns Title, SITime on the select list and
    • no other column anywhere (WHERE, HAVING etc.)

Then a covering non-clustered index on (PWFID) INCLUDE (SITime, Title) will probably improve the efficiency of the query as it will need to read a narrower index (and no lookups to the table, whether it's clustered or a heap). No idea how much improvement it would be, as the query involves joining of 30 something tables - and the index will not be that narrow either, with the included 500 character column.

 

About converting the table to a heap:

This makes me wonder if getting rid of the clustered index and make this table a heap makes sense or not, because I can just "save" the clustered index and free some space for having a covering nonclustered index?

This is irrelevant I think, at least for this and similar queries. It might change/improve the behaviour of insert queries (as no clustered index will have to be maintained) but it may also degrade performance for other queries that depend on finding more columns from the clustered index.

And you won't be saving any (or much) space. The data has to be stored somewhere, whether the table is a heap or clustered.


Adding a NC index is much less drastic change and I wouldn't expect any side effects - apart from the wanted use of it in the query - but still needs to be tested.

Removing the clustered index and converting a table to a heap is effectively changing the structure of all NC indexes and removing of (the clustered) one, so it may have several and more serious effects on many operations/queries performed and would need much more testing.

2 added 585 characters in body
source | link

Without further info, this is more of speculation but judging on what we have:

  • a table that is quite wide (1.3 to 4.0 rows per page on average)
  • the query that is slow is using:
    • only PWFID on the join condition,
    • two columns Title, SITime on the select list and
    • no other column anywhere (WHERE, HAVING etc.)

Then a covering non-clustered index on (PWFID) INCLUDE (SITime, Title) will probably improve the efficiency of the query as it will need to read a narrower index (and no lookups to the table, whether it's clustered or a heap). No idea how much improvement it would be, as the query involves joining of 30 something tables - and the index will not be that narrow either, with the included 500 character column.

ConvertingAbout converting the table to a heap:

This makes me wonder if getting rid of the clustered index and make this table a heap makes sense or not, because I can just "save" the clustered index and free some space for having a covering nonclustered index?

This is irrelevant I think, at least for this and similar queries. It might change/improve the behaviour of insert queries (as no clustered index will have to be maintained) but it may also degrade performance for other queries that depend on finding more columns from the clustered index.

And you won't be saving any (or much) space. The data has to be stored somewhere, whether the table is a heap or clustered.

Without further info, this is more of speculation but judging on what we have:

  • a table that is quite wide (1.3 to 4.0 rows per page on average)
  • the query that is slow is using:
    • only PWFID on the join condition,
    • two columns Title, SITime on the select list and
    • no other column anywhere (WHERE, HAVING etc.)

Then a covering non-clustered index on (PWFID) INCLUDE (SITime, Title) will probably improve the efficiency of the query as it will need to read a narrower index (and no lookups to the table, whether it's clustered or a heap). No idea how much improvement it would be, as the query involves joining of 30 something tables - and the index will not be that narrow either, with the included 500 character column.

Converting the table to a heap is irrelevant I think, at least for this and similar queries.

Without further info, this is more of speculation but judging on what we have:

  • a table that is quite wide (1.3 to 4.0 rows per page on average)
  • the query that is slow is using:
    • only PWFID on the join condition,
    • two columns Title, SITime on the select list and
    • no other column anywhere (WHERE, HAVING etc.)

Then a covering non-clustered index on (PWFID) INCLUDE (SITime, Title) will probably improve the efficiency of the query as it will need to read a narrower index (and no lookups to the table, whether it's clustered or a heap). No idea how much improvement it would be, as the query involves joining of 30 something tables - and the index will not be that narrow either, with the included 500 character column.

About converting the table to a heap:

This makes me wonder if getting rid of the clustered index and make this table a heap makes sense or not, because I can just "save" the clustered index and free some space for having a covering nonclustered index?

This is irrelevant I think, at least for this and similar queries. It might change/improve the behaviour of insert queries (as no clustered index will have to be maintained) but it may also degrade performance for other queries that depend on finding more columns from the clustered index.

And you won't be saving any (or much) space. The data has to be stored somewhere, whether the table is a heap or clustered.

1
source | link

Without further info, this is more of speculation but judging on what we have:

  • a table that is quite wide (1.3 to 4.0 rows per page on average)
  • the query that is slow is using:
    • only PWFID on the join condition,
    • two columns Title, SITime on the select list and
    • no other column anywhere (WHERE, HAVING etc.)

Then a covering non-clustered index on (PWFID) INCLUDE (SITime, Title) will probably improve the efficiency of the query as it will need to read a narrower index (and no lookups to the table, whether it's clustered or a heap). No idea how much improvement it would be, as the query involves joining of 30 something tables - and the index will not be that narrow either, with the included 500 character column.

Converting the table to a heap is irrelevant I think, at least for this and similar queries.