SQL Server Performance

Understanding SQL commands

Discussion in 'Getting Started' started by meehanj, Dec 20, 2007.

  1. meehanj New Member

    I wish to run DBCC FLUSHPROCINDB on a database called 'Batch'. I found the following example:DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'database_name') DBCC FLUSHPROCINDB (@intDBID)But I don't understand it. Can someone please explain?Thanks
  2. ranjitjain New Member

    Hi,
    Thid dbcc command is used to clear out or free procedure cache for specific database by supplying database id. So in first statement you are setting the dbid to variable @intdbid and then passing it to dbcc command
  3. satya Moderator

    From one of the tIPS on this website:DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a specific database on a SQL Server, not the entire SQL Server. The database ID number to be affected must be entered as part of the command.You may want to use this command before testing to ensure that previous stored procedure plans won't negatively affect testing results.Example:
    DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'database_name')
    DBCC FLUSHPROCINDB (@intDBID)
  4. meehanj New Member

    Thanks for your help. I now understand this.
  5. satya Moderator

    In any case refrain using this on the production server, as it plays wtih cache and running this will have slow performance on huge procedures.
  6. sonnysingh Member

    I would like to know what is permission level to run this command as I intend to use in SP in certain situation as couldn't find in BOL.
  7. FrankKalis Moderator

    You should refrain from doing so, because this command is undocumented and therefore unsupported.

Share This Page