SQL SERVER – CLEAN CACHE(Stored Procedure) AND BUFFER-DBCC FREEPROCACHE,DBCC DROPCLEANBUFFERS

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.

SQL SERVER – Line Number in SQL Server Management Studio(SSMS)

When we run a script in SQL Server we may face errors. To debug big script is a tedious task.Most of the  errors are in the form 

Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ‘selec’.

.Here SSMS is telling that error happened at  Line 6 (as figure below),But we cant find the Line no in SSMS.

T-SQL in SSMS Without Line Nos

Generate Line No:

  1. Open SSMS and choose tools tab.
  2. In tools choose options.
  3. Under options window choose Text Editor-> Transact SQL->General
  4. In General tab ,tick the check box for Line Numbers and click ok.

 

The Following digrams will help you,

Options Window in SSMS

 

 Look at the below Image , SSMS with LineNo

SSMS with Transact-SQL Line Numbers