PAE SQL server questions...

Hey all,

I'm wondering what command I could issue to see the amount of memory a named instance is using from within the instance itself. We've enabled address extensions (PAE), and task manager no longer shows the correct amount of mem being used by the process. Under perfmon, I've added the object MSSQL$INSTANCENAME:Memory and I'm looking at the Total Server Memory. I'm seeing 4 gigs, when max memory is capped at 2gigs. This must show the server memory and not the instance memory ? Is there some way to see the instance memory ?

Cheers,
-KilkaDBCC MEMORYSTATUS. It'll give you enough to start with. Coupled with MEMUSAGE, you can pi-point the exact object that hogs the memory.|||Thanks rdjabarov,

I'll have to check this out on our testing environments. I've read that MEMUSAGE can cause the instance to crash, I'll have to do some more testing when I get back to work to verify that it'll be safe to use on live.

Cheers,
-Kilka

Overhead for transactions

Hi all,
I am looking at the following codes. I thought that
defining the transaction is redundant. Delete by itself
IS a transaction. But what I don't know is: how much
extra overhead is added by explicitly defining the
tranaction?
Thanks in advance, Anna
--
BEGIN TRAN Del_C_ActiveBenefits
DELETE dbo.C_ActiveBenefits
WHERE ClientCode = @.ClientCode
AND PlanCode = @.PlanCode
AND ValDate = @.ValDate
If (@.@.ERROR <> 0)
BEGIN
Rollback TRAN Del_C_ActiveBenefits
--initialize Error Num from global variable
SET @.Error_Num = @.@.ERROR
-- initialize the Error description
SET @.Error_Desc = 'Error Deleting C_ActiveBenefits
Data From Target Table'
GOTO Error_Handler -- trap & handle the Error
generated
END
Commit TRAN Del_C_ActiveBenefits
--There's no extra overhead. You might end up in carrying locks over longer time, so you can
experience higher risk of blocking of course. Also, grouping several DML commands in one transaction
can increase performance as it will reduce I/O (each commit, implicit or explicit result in an I/O
to the t-log file). However, you don't want to do too many DML's in one transaction, say in a back
job, perhaps limit to 10000 DML's in one transaction.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Anna Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:063f01c3af0b$57505790$a401280a@.phx.gbl...
> Hi all,
> I am looking at the following codes. I thought that
> defining the transaction is redundant. Delete by itself
> IS a transaction. But what I don't know is: how much
> extra overhead is added by explicitly defining the
> tranaction?
> Thanks in advance, Anna
> --
> BEGIN TRAN Del_C_ActiveBenefits
> DELETE dbo.C_ActiveBenefits
> WHERE ClientCode = @.ClientCode
> AND PlanCode = @.PlanCode
> AND ValDate = @.ValDate
> If (@.@.ERROR <> 0)
> BEGIN
> Rollback TRAN Del_C_ActiveBenefits
> --initialize Error Num from global variable
> SET @.Error_Num = @.@.ERROR
> -- initialize the Error description
> SET @.Error_Desc = 'Error Deleting C_ActiveBenefits
> Data From Target Table'
> GOTO Error_Handler -- trap & handle the Error
> generated
> END
> Commit TRAN Del_C_ActiveBenefits
> --|||In addition to Tibor's comments may I suggest you review the error =handling code.
The line SET @.Error_Num =3D @.@.ERROR is after the rollback and hence =@.@.error will have been rest to zero by the rollback, so whatever is =using @.error_num is going to get zero. Safest way normally is imediately =after the statement you want to trap include:
Set @.errno =3D @.@.error,@.rcount =3D @.@.rowcount -- or similar names for =the variables
Then check @.rcount and/or @.errno for whatever you need.
Mike John
"Anna Lin" <anonymous@.discussions.microsoft.com> wrote in message =news:063f01c3af0b$57505790$a401280a@.phx.gbl...
> Hi all, > I am looking at the following codes. I thought that > defining the transaction is redundant. Delete by itself > IS a transaction. But what I don't know is: how much > extra overhead is added by explicitly defining the > tranaction?
> > Thanks in advance, Anna
> --
> BEGIN TRAN Del_C_ActiveBenefits
> DELETE dbo.C_ActiveBenefits
> WHERE ClientCode =3D @.ClientCode
> AND PlanCode =3D @.PlanCode
> AND ValDate =3D @.ValDate
> > If (@.@.ERROR <> 0)
> BEGIN > Rollback TRAN Del_C_ActiveBenefits
> --initialize Error Num from global variable
> SET @.Error_Num =3D @.@.ERROR > -- initialize the Error description > SET @.Error_Desc =3D 'Error Deleting C_ActiveBenefits > Data From Target Table'
> GOTO Error_Handler -- trap & handle the Error > generated > END > > Commit TRAN Del_C_ActiveBenefits
> --