Category MSSQL

Find all indexes on a table

It is useful to be able to see all indexes on a table. There is the shipped stored procedure sp_helpindex but the following query can reveal some more inforamtion such as the Included and Filter columns.

Databases size

The following statement returns the size of all databases in current SQL Server instance in Megabytes. Information is separated in data and log information.

Non Clustered Primary Keys in current database

Creating a Primary Key in SQL Server, by default creates a Clustered Index. The following statement shows all Primary Keys in current database that are using nonclustered indexes instead of clustered. This might not be necessarily wrong, as a clustered…

Batch Requests / second

DECLARE @BRPS BIGINTSELECT @BRPS=cntr_valueFROM sys.dm_os_performance_countersWHERE counter_name LIKE ‘Batch Requests/sec%’WAITFOR DELAY ‘000:00:01’SELECT (cntr_value-@BRPS) AS “Batch Requests/sec”FROM sys.dm_os_performance_countersWHERE counter_name LIKE ‘Batch Requests/sec%’

Check for suspect pages

SQL Servers keeps record of the corrupted pages it accesses in msdb table dbo.suspect_pages. Note that table only holds 1000 rows. Then again you should not find any records in it whenever querying it!