Try

SELECT maintable.pk,
		(select COUNT(*) from childtable1 where pk = maintable.pk) as a,
		(select COUNT(*) from childtable2 where pk = maintable.pk) as b,
		(select COUNT(*) from childtable3 where pk = maintable.pk) as c,
		(select COUNT(*) from childtable4 where pk = maintable.pk) as d,
		(select COUNT(*) from childtable5 where pk = maintable.pk) as e,
		(select COUNT(*) from childtable6 where pk = maintable.pk) as f
	from maintable
	where maintable.pk = @lnPK

>I've written a query with several nested joins to check status of records being transferred successfully to archive tables. It looks something like this:
>

>SELECT maintable.pk
>		,a.rcount
>		,b.rcount
>		,c.rcount
>		,d.rcount
>		,e.rcount
>		,f.rcount
>	from maintable
>		left join (select COUNT(*) as rcount from childtable1 where pk=12345) as a on 1=1
>		left join (select COUNT(*) as rcount from childtable2 where pk=12345) as b on 1=1
>		left join (select COUNT(*) as rcount from childtable3 where pk=12345) as c on 1=1
>		left join (select COUNT(*) as rcount from childtable4 where pk=12345) as d on 1=1
>		left join (select COUNT(*) as rcount from childtable5 where pk=12345) as e on 1=1
>		left join (select COUNT(*) as rcount from childtable6 where pk=12345) as f on 1=1
>	where maintable.pk=12345
>

>this works fine and runs instantly.
>
>If I change it to a variable like:
>
>

>DECLARE @lnPK as int=12345
>SELECT maintable.pk
>		,a.rcount
>		,b.rcount
>		,c.rcount
>		,d.rcount
>		,e.rcount
>		,f.rcount
>	from maintable
>		left join (select COUNT(*) as rcount from childtable1 where pk=@lnPK) as a on 1=1
>		left join (select COUNT(*) as rcount from childtable2 where pk=@lnPK) as b on 1=1
>		left join (select COUNT(*) as rcount from childtable3 where pk=@lnPK) as c on 1=1
>		left join (select COUNT(*) as rcount from childtable4 where pk=@lnPK) as d on 1=1
>		left join (select COUNT(*) as rcount from childtable5 where pk=@lnPK) as e on 1=1
>		left join (select COUNT(*) as rcount from childtable6 where pk=@lnPK) as f on 1=1
>	where maintable.pk=@lnPK
>

>Performance goes to hell. It takes 45 seconds to complete and the execution plan is completely different.
>
>What's going on here? Is there a better way to do this with a variable?