I have a table with 15 million rows, it's a parent table with 12 child tables.
Even for a simple count query, it is taking hour's to complete.
select count(1) from table where col_filter >= 'number'
table contains 40 columns and
col_filter is having
varchar data type.
col_filter is not indexed.
- What should I check to find the potential issues with my table setup?
- I am using Microsoft SQL server management studio 18, is there any tool that I can use to understand and get some recommendations to optimize the performance?
- If indexing is the solution, is it possible to calculate how much extra space the index creation will occupy?
This table is part of other data fetch query, which contains,
inner joins and then using
cte as base table which perform group by operations, the table which I posted in post is the base table inside cte, as it's taking long time even for count, I thought it would be good start to debug. Here is the full query with estimated execution plan and actual execution plan which runs for 18+hours.
Note: Actual execution plan was taken from the running query as shown in this SO answer
select count(1) from table where col_filter >= 'number':
(35ms)PAGEIOLATCH_SH:dev-db:1(*) (26ms)PAGEIOLATCH_SH:dev-db:1(*) (86ms)PAGEIOLATCH_SH:dev-db:1(*) (9ms)PAGEIOLATCH_SH:dev-db:1(*)
and it's actual execution plan
Any suggestions would be really appreciable.