Just a quick tidbit today, but a very useful one.
Ever need to do SELECT Count(*) on a very large table? It can take ages.
Instead, use sp_spaceused
EXEC sp_spaceused 'MyTable'
It returns you a useful result set like
|MyTable||19690290||13637488 KB||3001688 KB||9986416 KB||649384 KB|
Not only does it also give you extra useful information about the table, but it comes back virtually instantly.
name – name of the object requested
rows – the number of rows in the table (or service broker queue)
reserved – the total reserved space for the object
data – amount of space used by the data
index_size – amount of spaced used by indexes in the object
unused – total space reserved for the object but not yet used
You can also use sp_spaceused without any parameters, and it will return you the stats for the whole database.