How many tasks are currently waiting?

select
count(*)
from
sys.dm_os_waiting_tasks

This query will give you an idea of how many tasks are waiting in the system. You can use this information to understand blocking characteristics of your load

How many sockets does my machine have in SQL Server?

select

cpu_count/hyperthread_ratio AS sockets

from

sys.dm_os_sys_info

Buffer Cache Hit Ratio

Buffer Cache Hit Ratio shows how SQL Server utilizes buffer cache
“Percent of page requests satisfied by data pages from the buffer pool”

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'

The recommended value for Buffer Cache Hit Ratio is over 90. A lower value indicates a memory problem.

Page Life Expectancy

“Duration, in seconds, that a page resides in the buffer pool”

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'

The normal values are above 300 seconds (5 minutes) and the trend line should be stable. If the value is below 300, it’s a clear indication that something is wrong
Also, a value drop of more than 50% is a sign for deeper investigation.

Get table row count for all tables in a database

SELECT o.name, ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY row_count DESC