SQLINFO – We play with data and technology

While I am working on terabyte database architecture, I am forced to load data into the lookup tables. The T-SQL File which contains insert statements was a big file.   When I tried to insert data from the File, I got an error showing that “Insufficient system resources exist to complete the requested service”. I told to my manager that we are running out of sufficient RAM. He told me to restart the SQL Server after running every script. The Idea behind is good but It is a not a good solution, as it is not possible to restart SQL Server frequently, since it contain other databases too. Another way to clear the buffer is,

DBCC FREEPROCACHE:

• Removes all elements from Plan cache.
• Removes specific Plan from the Plan cache.
• Removes all cache entries with Resource Pool.

DBCC DROPCLEANBUFFERS:

• Removes all clean buffers from the Pool.

Note:

• We can use this while Performance Tuning to check the performance of the Query.
• We should not use this statement (DBCC FREEPROCACHE) in Production environment because freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.