SQL Q+A Temp Tables, 64-bit Processing, Mirroring, and More
As per SQL Server™ Books Online:
- A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch it is declared in. Within its scope, a table variable can be used like a regular table.
- Table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined.
- Table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used.
- Table variables require fewer locking and logging resources.
- In some cases, performance may improve, though stats aren't kept.
Figure 1 Out-of-Memory Error
2006-06-23 14:41:40.72 spid77 WARNING: Failed to reserve contiguous memory of Size= 24641536. 2006-06-23 14:41:40.85 spid77 Buffer Distribution: Stolen=4800 Free=1744 Procedures=39391 Inram=0 Dirty=90621 Kept=0 I/O=0, Latched=99, Other=3063345 2006-06-23 14:41:40.85 spid77 Buffer Counts: Commited=3200000 Target=3200000 Hashed=3154065 InternalReservation=587 ExternalReservation=0 Min Free=1024 Visible= 173320 2006-06-23 14:41:40.85 spid77 Procedure Cache: TotalProcs=9555 TotalPages=39391 InUsePages=1031 2006-06-23 14:41:40.85 spid77 Dynamic Memory Manager: Stolen=44191 OS Reserved=7648 OS Committed=7619 OS In Use=7603 Query Plan=39088 Optimizer=0 General=4183 Utilities=140 Connection=7651 2006-06-23 14:41:40.85 spid77 Global Memory Objects: Resource=1617 Locks=313 SQLCache=1836 Replication=204 LockBytes=2 ServerGlobal=42 Xact=63 2006-06-23 14:41:40.85 spid77 Query Memory Manager: Grants=0 Waiting=0 Maximum=94533 Available=94533 2006-06-23 14:41:40.88 spid77 Error: 17803, Severity: 20, State: 12 2006-06-23 14:41:40.88 spid77 Insufficient memory available..
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.