Find fragmentation of a db for all indexes

March 30, 2008 | Leave a Comment

SELECT ps.database_id, ps.OBJECT_ID,
            ps.index_id, b.name,
            ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
        AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO

and then we can rebuild :

 

Index Rebuild : This process drops the existing Index and Recreates the index.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO

Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%.

Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be t

urned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding

Posted by | Filed Under SQL 

Find most used stored procedure

March 28, 2008 | Leave a Comment

SELECT TOP 10 qt.TEXT AS ‘SP Name’,
            qs.execution_count AS ‘Execution Count’,
            qs.total_worker_time/qs.execution_count AS ‘AvgWorkerTime’,
            qs.total_worker_time AS ‘TotalWorkerTime’,
            qs.total_physical_reads AS ‘PhysicalReads’,
            qs.creation_time ‘CreationTime’,
            qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS ‘Calls/Second’
    FROM sys.dm_exec_query_stats AS qs
            CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    WHERE qt.dbid = (
    SELECT dbid
        FROM sys.sysdatabases
        WHERE name = ‘AdventureWorks’)
    ORDER BY qs.total_physical_reads DESC

 

You can change the name of the database from AdventureWorks to any database which you are curious about. If WHERE clause is completely removed it will give results for all the databases

Posted by | Filed Under SQL 

Add Column with default column constaint to table

March 20, 2008 | Leave a Comment

ALTER TABLE TestTable
ADD NewCol VARCHAR(50)
CONSTRAINT DF_TestTable_NewCol DEFAULT ” NOT NULL
GO

Posted by | Filed Under SQL 

Retrieve processes using specified database.

March 19, 2008 | Leave a Comment

USE master
GO
DECLARE @dbid INT SELECT @dbid = dbid
FROM sys.sysdatabases
WHERE name = ‘AdventureWorks’
IF EXISTS   (SELECT spid
           FROM sys.sysprocesses
           WHERE dbid = @dbid)
BEGIN
SELECT ‘These processes are using current database’ AS Note,
       spid, last_batch,
       status, hostname, loginame
FROM sys.sysprocesses
WHERE dbid = @dbid
END
GO

Posted by | Filed Under SQL 

Next Page »