SQL Server IO

Excellent articles on SQL Server IO best practices and architecture are here:

SQL Server 2000 I/O Basics

http://technet.microsoft.com/en-au/library/cc966500.aspx

SQL Server IO Best Practices Article

http://technet.microsoft.com/en-au/library/cc966412.aspx

We can use the SQL Server IO stress tool to find out issues with IO subsystems on your servers:

SQLIOStress.exe

http://support.microsoft.com/default.aspx?scid=kb;en-us;231619

SQLIO.exe
http://download.microsoft.com/download/f/3/f/f3f92f8b-b24e-4c2e-9e86-d66df1f6f83b/SQLIO.msi

A few questions I have on the architecture:

Why sql server page size is 8Kb?

Why extend size is always 64 kb?

Heard that in oracle we can configure the page size, extent sizes for individual tables.

I still have to find out more about the IO disk subsystem, OS data blocks, Storage block sizes etc to find out why MS chose nonconfigurable sizes for pages and extents.

I'll updated this blog soon.

row_number() function in sql server to get row offsets

This function started in sql server 2005 onwards.

eg:

select

you will get the records with each row having a row number like this:

rowno objectid
1          371178
2          371179
3          371181
4          371182

This is good when you want in between rows; lets say you want rows between 125 and 226 rows.

Though you can use between but between actually searches the data between the mentioned columns on which we are searching/filtering.

row_number() over (order by projectid) as rowno,objectid,tid,name from csforum

How to find objects/plans in memory in sql server

select
this will give you the database id, page id,page_type, row count so you can identify which pages(data/index) are in buffer for that particular database

select
this will as well give you the cached objects (compiled plans, adhoc, extended procs)

or

select
this will give the same result as syscacheobjects

select * from sys.dm_exec_plan_attributes(pass the plan handle from the above query here) * from sys.dm_exec_cached_plans * from syscacheobjects * from sys.dm_os_buffer_descriptors

CLR App domains

Since sql server 2005 we got this new feature where we can load .net assemblies to the database and execute all our app functions.

You can view the existing app domains using the query:

select * from sys.dm_clr_appdomains

and the assemblies which are loaded can be seen here:
select * from sys.dm_clr_loaded_assemblies

The below error is due to insufficient CLR memory allocated:
to resolve this, you can use the startup parameter "-g" and give additional memory to the external assemblies and objects. By default sql server allocates 256 MB memory to external assemblies.

AppDomain 129 (testdb.dbo[runtime].135) is marked for unload due to memory pressure.