Almost every major software and product is written in C,C++

You know what, Oracle RDBMS is written in C.

Unix OS is written in C,C++

Linux is written in C

MS Windows is written in C,C++

VB is written in Basic (exception)

MAC OS is written in C,C++

Java Compiler is written in C

There are other high level languages like COBOL, FORTRON, BASIC etc.

'll find out more soon

sys.dm_os_buffer_descriptors

select d.database_id,d.file_id,d.page_id,d.page_type,d.row_count,object_name(p.object_id),a.allocation_unit_id
from sys.dm_os_buffer_descriptors  d
left outer join sys.allocation_units a
on d.allocation_unit_id=a.allocation_unit_id
inner join sys.partitions p
on a.container_id=p.partition_id
where d.database_id>4
group by
d.database_id,d.file_id,d.page_id,d.page_type,d.row_count,object_name(p.object_id),a.allocation_unit_id

This query will give you all the objects in Buffer group by Database.

Remember, db_id of 32767 is Resource DB

Also just posted a question to Paul, waiting for a response from him:

When I the run the following query in sql server 2005

select * from sys.dm_os_buffer_descriptors where is_modified<>0

database_id	file_id	page_id	page_level	allocation_unit_id	page_type	row_count	free_space_in_bytes	is_modified
2	1	28919	0	1.48948E+14	INDEX_PAGE	0	8096	1
2	1	143	0	1.48948E+14	INDEX_PAGE	0	8096	1
2	1	152	0	5.6295E+14	INDEX_PAGE	135	3230	1
2	1	153	0	2.81475E+14	INDEX_PAGE	3	8045	1
2	1	154	0	2.81475E+14	INDEX_PAGE	3	8045	1

what does “is_modified” column signify?

Does it mean that the data has been modified and still not committed?

Also I could see rows with file_id=2(ldf files) when I run the below:

select * from sys.dm_os_buffer_descriptors where file_id>1

Tlog doesn’t have pages, so I could see page_id As zero but the page_types are confusing.

database_id	file_id	page_id	page_level	allocation_unit_id	page_type	row_count	free_space_in_bytes	is_modified
7	2	0	0	6488064	FILEHEADER_PAGE	2	6892	0
2	2	0	0	2.81475E+14	DATA_PAGE	5	4411	0
5	2	0	0	7.20576E+16	DATA_PAGE	16	192	0
4	2	0	0	6488064	FILEHEADER_PAGE	2	6900	0
10	2	0	0	6488064	FILEHEADER_PAGE	2	6884	0
1	2	0	0	7.20576E+16	IAM_PAGE	2	6	0

When I ran the below query to find out the objects these allocations are associated to I didn’t get anything.

select d.database_id,d.allocation_unit_id,

(

select object_id from sys.partitions where

partition_id=(select container_id from sys.allocation_units where allocation_unit_id=d.allocation_unit_id)

)

 from sys.dm_os_buffer_descriptors d where file_id>1

database_id	allocation_unit_id	(No column name)
7	6488064	NULL
3	6488064	NULL
4	1.92626E+14	NULL
10	2.81475E+14	60
2	2.81475E+14	60
1	7.20576E+16	NULL
5	7.20576E+16	NULL

sysdatabases

Found this column very interesting.

select * from sysdatabases ususually the category column shows as 0 but understood now it is used for replication.

interesting notes from MSDN:

For transactional and snapshot publishing databases, the category bit is set to 1. For merge publishing databases, the category bit is set to 4.

Its mentioned that we need to be careful while restoring databases in Replication, the db to be restored has this category column set correctly based on the replication settings.

Refer MSDN article here:

http://64.4.11.252/en-us/library/aa236981%28SQL.80%29.aspx

also just found out that sysdatabases is a view which refers sys.databases system table.
All the information, various columns etc. are in Sys.Databases


Even Sys.Databases is a view, When I see the helptext for this, its joining the following tables and pulling the results:
FROM master.sys.sysdbreg d OUTER APPLY OpenRowset(TABLE DBPROP, d.id) p 
 LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB 
 LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state  


But you can't query these tables with just a simple select query, 


select * from master.sys.syspalvalues won't work;

unknown Background processes running in sql server

If you see sp_who2 or run the below query

select * from sysprocesses where spid<50 you can the following processes running in sql server background.

There might be more, I'll add as soon as I see the new ones;

Currently I don't have the exact description of what each process does

RESOURCE MONITOR
LAZY WRITER     - this is the one which writes the committed portion of Transaction log to disk
LOG WRITER      - this is the one which writes the transactional changes from memory to Tlog??
LOCK MONITOR   
SIGNAL HANDLER 
TASK MANAGER   
TRACE QUEUE TASK
UNKNOWN TOKEN  
BRKR TASK      
TASK MANAGER   
CHECKPOINT      - this is well known to everyone, this flushes out the data from memory to disk
BRKR EVENT HNDLR
BRKR TASK      
TASK MANAGER    

I'll try to find out the remaining and 'll post updates