Home » Storage Internals

Storage Internals

sql server DBA 999

Parameter Sniffing in Stored Procedures in SQL Server 2008/2008 R2

Parameter Sniffing in Stored Procedures in SQL Server 2005/2008/2008 R2
Today let me cover one of a good topic on Parameter Sniffing in Stored Procedures:

Paramater Sniffing is not a unexpected behaviour when passing parameters into Store Procedure.


CREATE procedure [dbo].[Usp_Test_SP]
@Category as [nvarchar](60)=null
as
select Category ,Description,WorkOrder,Date Added from TestTable
where (sc.session_class_desc like @Category +'%' or @Category is null)

Say about SP causes a index seek + bookmark lookup then this compiled plan will be cached and reused for subsequent executions of the procedure.

But when sometime if rows returned are very high like 30% of Table rows then compiled plan may result in Full table scan and this is stored for future use.… Read the rest

sql server DBA 999

SQL Server 2008/2005 Storage Internals, GAM, SGAM, PFS, IAM

Today lets discuss some of the SQL Server Storage internals like what is GAM, SGAM, IAM etc
Basically in short words:
1.GAM(Global Allocation Map) tracks around a 64,000 extents(extent is 64KB which consists of 8 pages) which is around 4GB of space. GAM has bit indicating if the extent is currently available for allocation or not. GAM bit for a extent is 1 only if it’s not being used at the moment.
2.SGAM(Shared Global Allocation Map) tracks mixed extents with free space. A mixed extent is used by multiple objects like table, index etc. SGAM bit is 1 for a extent only if it has free space in it and it is mixed extent. Even SGAM tracks 64,000 extents same … Read the rest