SQL Server Performance

Few useful commands

Discussion in 'Contribute Your SQL Server Scripts' started by skeezwiz, Aug 29, 2006.

  1. skeezwiz New Member

    -- Rebuild Indexes
    exec sp_msforeachtable 'DBCC DBREINDEX (''?'','' '',90)'

    ##################################################################
    ##################################################################
    ##################################################################

    -- check space on all tables

    dbcc updateusage(0) with count_rows


    create table #tablerows(
    tabname varchar(50),
    rc varchar(50),
    reserved varchar(50),
    data varchar(50),
    indexes varchar(50),
    unused varchar(50)
    )

    insert into #tablerows exec sp_msforeachtable 'sp_spaceused ''?'''

    select
    tabname,
    sum(cast(rc as int)) as rows,
    sum(cast(replace(reserved,' KB','') as int)) as reserved,
    sum(cast(replace(data,' KB','') as int)) as data,
    sum(cast(replace(indexes,' KB','') as int)) as indexes,
    sum(cast(replace(unused,' KB','') as int)) as unused
    from #tablerows
    group by tabname
    order by 4 desc

    drop table #tablerows


    ##################################################################
    ##################################################################
    ##################################################################


    -- display tables with low scan density

    -- Create the table
    CREATE TABLE #fraglist (
    ObjectName CHAR (255),
    ObjectId INT,
    IndexName CHAR (255),
    IndexId INT,
    Lvl INT,
    CountPages INT,
    CountRows INT,
    MinRecSize INT,
    MaxRecSize INT,
    AvgRecSize INT,
    ForRecCount INT,
    Extents INT,
    ExtentSwitches INT,
    AvgFreeBytes INT,
    AvgPageDensity INT,
    ScanDensity DECIMAL,
    BestCount INT,
    ActualCount INT,
    LogicalFrag DECIMAL,
    ExtentFrag DECIMAL)


    insert into #fraglist exec sp_msforeachtable 'dbcc showcontig (''?'') with tableresults'

    select * from #fraglist order by ScanDensity

    drop table #fraglist

  2. ranjitjain New Member

    Hi,<br />sp_msforeachtable stored procedure is undocumented, so its not recommended by Microsoft to be used by anyone[<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by skeezwiz</i><br /><br />-- Rebuild Indexes<br />exec sp_msforeachtable 'DBCC DBREINDEX (''?'','' '',90)'<br /><br />##################################################################<br />##################################################################<br />##################################################################<br /><br />-- check space on all tables<br /><br />dbcc updateusage(0) with count_rows<br /><br /><br />create table #tablerows(<br />tabname varchar(50), <br />rc varchar(50), <br />reserved varchar(50), <br />data varchar(50), <br />indexes varchar(50), <br />unused varchar(50)<br />)<br /><br />insert into #tablerows exec sp_msforeachtable 'sp_spaceused ''?'''<br /><br />select <br />tabname,<br />sum(cast(rc as int)) as rows, <br />sum(cast(replace(reserved,' KB','') as int)) as reserved, <br />sum(cast(replace(data,' KB','') as int)) as data, <br />sum(cast(replace(indexes,' KB','') as int)) as indexes, <br />sum(cast(replace(unused,' KB','') as int)) as unused <br />from #tablerows<br />group by tabname<br />order by 4 desc<br /><br />drop table #tablerows<br /><br /><br />##################################################################<br />##################################################################<br />##################################################################<br /><br /><br />-- display tables with low scan density<br /><br />-- Create the table<br />CREATE TABLE #fraglist (<br /> ObjectName CHAR (255),<br /> ObjectId INT,<br /> IndexName CHAR (255),<br /> IndexId INT,<br /> Lvl INT,<br /> CountPages INT,<br /> CountRows INT,<br /> MinRecSize INT,<br /> MaxRecSize INT,<br /> AvgRecSize INT,<br /> ForRecCount INT,<br /> Extents INT,<br /> ExtentSwitches INT,<br /> AvgFreeBytes INT,<br /> AvgPageDensity INT,<br /> ScanDensity DECIMAL,<br /> BestCount INT,<br /> ActualCount INT,<br /> LogicalFrag DECIMAL,<br /> ExtentFrag DECIMAL)<br />insert into #fraglist exec sp_msforeachtable 'dbcc showcontig (''?'') with tableresults'<br />select * from #fraglist order by ScanDensity<br />drop table #fraglist<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  3. Adriaan New Member

    Ranitjain,

    I know it is not polite to make a joke about small mistakes people make when using a foreign language, but this one is really funny:
    quote:its not recommended to be used by Microsoft
    Agreed: Microsoft should not use undocumented stored procedures.
  4. FrankKalis Moderator

    LOL [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. ranjitjain New Member

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br />it's not because of foreign language, its when you are a bit distant[<img src='/community/emoticons/emotion-4.gif' alt=':p' />]

Share This Page