Skip to main content

Find Missing and Unused indexes with SqlDbAid

SQL Server's Dynamic Management Views (DMV) give you access to a lot of useful information.

The web is full of good scripts about SQL Server indexes, but if you prefer a friendly interface may be you could just use SqlDbAid application.

Lets create a test case.

Create and fill the following table

CREATE TABLE [dbo].[missing_idx]
(
[row_id] [bigint] NOT NULL,
[object_id] [int] NOT NULL,
[name] [nvarchar](128) NOT NULL,
[type] [char](2) NOT NULL,
CONSTRAINT [PK_missing_idx] PRIMARY KEY CLUSTERED ([row_id])
)

INSERT INTO dbo.missing_idx
SELECT TOP 20000 ROW_NUMBER() OVER (
                                    ORDER BY O.object_id) row_id,
                                                          O.object_id,
                                                          O.name,
                                                          O.type
FROM sys.all_objects O CROSS
JOIN sys.all_objects O1


then we could try to extract some data accessing the table using object_id column (no index there).

SELECT object_id,
       name,
       TYPE
FROM dbo.missing_idx
WHERE object_id IN
    (SELECT TOP 1 object_id
     FROM dbo.missing_idx)


Now run SqlDbAid, connect to your server and select the database where you created the table.

Select "Missing Indexes" from Tools --> Indexes menu.

You should see the following report:


Double click the row to get the create script:

CREATE NONCLUSTERED INDEX [IX_AT_dbo_missing_idx_1870364392] ON
[dbo].[missing_idx] ([object_id]) INCLUDE ([name], [type])



To find unused indexes from SqlDbAid select "Indexes Status" from Tools --> Indexes menu.

You should see the following report:


The read_vs_write column is the sum of all reads (scan, seek and lookup) minus the writes.
Look also to the date columns to know when was the last read.


Comments

Popular posts from this blog

SqlDbAid DBA tool

Latest version (v2.6.0.0 - 2017-09-03) SqlDbAid.zip
SqlDbAid is an intuitive standalone application that will enable you to easily script database items and data.


SqlDbAid is free for personal and commercial usage.

Main Features SQL Server 2005+ supportText search inside code definition with highlitghtingDBA reports (missing indexes, missing foreign key indexes, indexes status, top queries, table MBytes)One file per object or single file scripts (tables, views, triggers, procedures, functions, indexes...)Select, Insert and Update scriptsTable data insert script creationTable/View data exportOffline database compare tool
Requirements .Net Framework 2.0+ For questions and suggestions write to Miken.

Database Permissions Script

DatabasePermissions.zip

This time I want to share a simple script to extract database permissions.
The code is based on sys.database_persmissions system view.
It basically contains the object ids, principal ids and related permissions (GRANT, DENY, ...).


SELECTCASEWHEN P.class IN(0,3)THEN P.class_desc ELSE O.type_desc END type_desc,CASEWHEN P.class IN(0,3)THEN''ELSE SCHEMA_NAME(O.schema_id)END object_schema,CASEWHEN P.class =3THEN SCHEMA_NAME(P.major_id)WHEN P.class =1THEN OBJECT_NAME(P.major_id)ELSE''END object_name, ISNULL(C.name,'') column_name, P.class_desc, USER_NAME(P.grantee_principal_id) grantee, P.permission_name, P.state_desc, USER_NAME(P.grantor_principal_id) grantor,CASE P.state WHEN'W'THEN'GRANT'ELSE P.state_desc END+' '+CASEWHEN P.class =0THEN P.permission_name +' TO '+ QUOTENAME(USER_NAME(P.grantee_principal_id)) COLLATE database_default WHEN P.class =3THEN P.permission_name +' ON SCHEMA::'+ QUOTE…

Columns Inconsintency Check

Another hopefully useful script  from my toolbox.

Recently I had to check a quite denormalized database at the end of its development phase.

Frequently data types, length and other field attributes change during development, so I wrote a simple script that highlights possible mistakes (same column name but different definition).

UPDATE: system objects skipped

;WITH cte AS (SELECT c.NAME cl_name ,Schema_name(t.schema_id) +'.'+ t.NAME tb_name ,p.NAME [type],c.max_length ,c.scale ,c.PRECISION FROM sys.tables t INNER JOIN sys.columns c ON c.object_id = t.object_id INNER JOIN sys.types p ON p.user_type_id = c.user_type_id AND p.system_type_id = c.system_type_id WHERENOT( EXISTS (SELECT1FROM sys.extended_properties EP WHERE EP.major_id = t.object_id AND EP.name ='microsoft_database_tools_support')OR t.is_ms_shipped =1))SELECT DISTINCT x.*FROM cte x INNER JOIN cte y ON y.tb_name != x.tb_name AND y.cl_name = x.cl_name AND( y.max_length != x.max_length O…