SQL Server Performance Forum – Threads Archive

does use db consumes any memory??

I have some querries
1)if we write use master or use xyz database , does it consumes any memory.then we write use abc database ;does the memory keeps using the first db now also for infinite times.if yes the how to close or stop memory from using that for infinite times.?
2)what is the use of GO when we can run tsql without go in QA?
3)what is the use of ‘exec’ when we can run sp’s without that in QA?

1) Each connection allocates approx. 24 kb. I don’t think that changing only the db context will consume memory unless you fire some statements.
2) GO is a batch separator
3) See the SQL Server Books Online (BOL) for explanations.
—
Frank
http://www.insidesql.de

we use Exec sp_name when executing another SP in Nested SP.
while in QA its not reqd.
Likewise Go statement is reqd in SP building

To addup what said above: Complex query that involves a large number of OUTER JOINS consumes excessive amounts of Optimizer memory. When the query runs, watch the performance monitor counter, SQLServer:MemoryManager<img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />ptimizerQuery(KB), to see the high optimizer memory usage. As a result of this extreme consumption of optimizer memory, SQL Server can experience multiple memory related errors. SQL Server 2000 dynamically acquires and frees memory as needed. It is typically not necessary for an administrator to specify how much memory should be allocated to SQL Server, although the option still exists and is required in some environments. When running multiple instances of SQL Server on a computer, each instance can dynamically acquire and free memory to adjust for changes in the workload of the instance. <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

]]>