Visibility into SQL Server Parallelism and search indexing

(1) IO Completion, Asyn Network tells if SAN configuration would impact data retrival.
(2) CXPACKET and MAXDOP option would trial-error how many CPU usage would be efficient.
(3) Execution plan is a good view into what SQL server is doing: scan vs. seeking index, Loop join to walk over clustered to non-clustered.
(4) Indexed View not just assist as index for other select, itself is faster


-- OS related
select wait_type,* from sys.dm_os_wait_stats
where wait_type like '%IO_Completion%' or wait_type like '%Packet%'
or wait_type like '%async_Network%'

select * from Sales.SalesOrderDetail
option(MAXDOP 1)

set statistics io on  --logical, physical reads

exec sys.sp_configure N'Show advanced Options',1
reconfigure

exec sys.sp_configure N'cost threshold for parallelism', 11
reconfigure

-- Index--one clustered, size big as data. slow when insert
use tempdb
drop table dbo.test
truncate table test2
create table test
(
id  int,
firstname  varchar(100),
lastname varchar(100)
)
create table test2
(
id  int,
firstname  varchar(100),
lastname varchar(100)
)
create nonclustered index IX_name_test on test
(
 firstname asc,
 lastname asc
) on [primary] 

insert into test (id,firstname,lastname)
select top 100000 ROW_NUMBER() over (order by a.name) rowId,
'Bob',
case when ROW_NUMBER() over (order by a.name)%2=0 then 'Smith'
else 'Brown' end name
from sys.all_objects a
cross join sys.all_objects b

insert into test2 (id,firstname,lastname)
select top 100000 ROW_NUMBER() over (order by a.name) rowId,
'Bob',
case when ROW_NUMBER() over (order by a.name)%2=0 then 'Smith'
else 'Brown' end name
from sys.all_objects a
cross join sys.all_objects b

-- execution plan shows cost equal 50%=50% or index worse.
-- all table scan, no seek index, not even use non-clustered idx
-- table storage size: no-index=cluster index small, non-cluster index big as data
select * from test 
select * from test2 
select * from test with(index(IX_name_test)) -- when force index => worse cost 96%

use AdventureWorks2012
-- nested loop join (inner join) even for a simple slect
-- it actually join from non-clusered index to clustered, totally waste
select * from HumanResources.Employee where NationalIDNumber=295847284
create nonclustered index IX_hum on HumanResources.Employee
(
NationalIDNumber asc,
hiredate
)
-- non-clustered index must be column inclusive to work
select NationalIDNumber,hiredate from HumanResources.Employee where NationalIDNumber=295847284
-- still seek predicate in execution plan has implicit_conv NationIDNum is nvar not int

--index view, schemabinding restrict table changes
create view sd_v
with schemabinding
as
select sd.SalesOrderID,sd.OrderQty,sd.ProductID from Sales.SalesOrderDetail sd
inner join sales.SalesOrderHeader h on h.SalesOrderID=sd.SalesOrderID

--list all index
select i.name, t.name, i.type_desc from sys.tables t
inner join sys.indexes i on i.object_id=t.object_id
where t.name like '%cust%'
sp_helpindex 'PK_Customer_CustomerID';