SqlServer-QA.net

When it comes to resolving table fragmentation, the basic checkout you perform is to run DBCC INDEXDEFRAG or even run DBCC DBREINDEX statements. As per the default configuraiton SQL Database Engine allocates a new extent to an allocation unit only when it cannot quickly find a page in an existing extent with sufficient space to hold the row being ...

This was the question from SSP forums, I’m trying to rebuild few fragmented indexes on a table that is updated on regular basis. The “dbcc showcontig” gave this result: Table: 'Confidential' (999999999); index ID: 5, database ID: 5LEAF level scan performed.- Pages Scanned................................: 86- Extents ...

Recently we have seen a negative performance for a query that is running against a 500GB size of database, where AUTO UPDATE STATISTICS is enabled and we perform weekly reindexing on this database.  In addition to this I perform intermittent update statistics against set of tables that has frequently update/deletes. As usual I have ...

Yet another important factors that every DBA needs to concerned about table & index fragmentation within their SQL Server environment. Refer to the article about DetectTableFragmentation  in both 2000 and 2005 version.   Fragmentation occurs due to updates and delets on the table and the golden rule is that ...

In SQL Server 2005 Dynamic Management Views (DMV) & DM Functions (DMF) allows you the look inside the server to return the required information. It will also helps you to monitor the server instance health and performance to diagnose the problem. Database administrators familiar with previous versions of SQL Server ...

In SQL 2000 version days you could take help of DBCC SHOWCONTIG statement, but this is deprecated in SQL Server 2005, so how to go about it. You can take help DMVs & DMFs in this case, DMV - Dynamic Management View and DMF - Dynamic Management Function, both of them help DBAs to determine & discover the database system usage. DMVs and ...