I have a server with a huge amount of memory and I am running SQL Server 2005 Enterprise. I need to know if it is possible to pin a group of most used SQL statements into memory. I know you can pin a table in memory, but is this also possible for just SQL statements.
I know you can pin a table in memory, but is this also possible for just SQL statements.
Actually you can't anymore. The command remains, and SQL Server will merrily tell you that the command executed correctly however it does not pin the table.
Originally Posted by BoL
Important: This functionality was introduced for performance in SQL Server version 6.5. DBCC PINTABLE has highly unwanted side-effects. These include the potential to damage the buffer pool. DBCC PINTABLE is not required and has been removed to prevent additional problems. The syntax for this command still works but does not affect the server.
As far as the statements are concerned - no you can't. Nor would you care. It is the execution plans you care about, not the statements. And these remain in memory until they are aged out\ marked for recompilation.
For more info check out the Lazy Writer, plan caching and syscacheobjects in BoL and via Google. www.sql-server-performance.com is a good place to look.