SQL (Part 1)

List of all index & index columns in SQL Server DB

How to select the first 10 records for each table in the same database using sql

How to drop all tables from database with one SQL query?

 

Find Tables With Foreign Key Constraint in Database.sql

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
AS ReferenceColumnName
FROM  sys.foreign_keys AS f INNER JOIN
sys.foreign_key_columns AS
fc ON f.object_id = fc.constraint_object_id
order by TableName ASC

Search Text in Stored Procedure in SQL Server

select distinct id,object_name(id) objectName, text, *
from syscomments
where text like ‘%sample text here%’
order by object_name(id)

As a simple reminder, you can create an index in columns that are common in WHERE, ORDER BY and GROUP BY clauses. You may consider adding an index in columns that are used to relate other tables (through a JOIN, for example)

Create an index if you frequently want to retrieve less than 15% of the rows in a large table. The percentage varies greatly according to the relative speed of a table scan and how clustered the row data is about the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.

Index columns used for joins to improve performance on joins of multiple tables.

Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.

Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to large.

Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:

  • Values are relatively unique in the column.
  • There is a wide range of values (good for regular indexes).
  • There is a small range of values (good for bitmap indexes).
  • The column contains many nulls, but queries often select all rows having a value. In this case, a comparison that matches all the non-null values, such as:
  • WHERE COL_X > -9.99 *power(10,125) is preferable to WHERE COL_X IS NOT NULL

This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).

Columns with the following characteristics are less suitable for indexing:

There are many nulls in the column and you do not search on the non-null values.

You must weigh the performance benefit of indexes for queries against the performance overhead of updates. For example, if a table is primarily read-only, you might use more indexes; but, if a table is heavily updated, you might use fewer indexes.

You might drop an index if:

  • It does not speed up queries. The table might be very small, or there might be many rows in the table but very few index entries.
  • The queries in your applications do not use the index.
  • The index must be dropped before being rebuilt.

Finding missing indexes Needed Dynamic Management Views (DMV’s)

The data about missing indexes is stored in the following DMV’s which all exclude info about spatial indexes:

sys.dm_db_missing_index_groups

This DMV returns only 2 columns with information about which indexes are in which group.

sys.dm_db_missing_index_group_stats

This DMV returns information about each missing indexes group. It returns info like the estimated average impact or how many seeks, scans and compilations/recompilations would benefit from adding the missing indexes.

sys.dm_db_missing_index_details

This DMV returns detailed information about each missing index like table name that is missing an index and CSV’s of columns that the index would be beneficial on.

sys.dm_db_missing_index_columns

This a Dynamic Management Function (DMF) that accepts an index_handle parameter. It returns columns that should be in the suggested index identified with the index_handle that can be obtained from sys.dm_db_missing_index_details and sys.dm_db_missing_index_groups. It does not include spatial indexes. The column named column_usage returns info on how this column would benefit for a particular index. EQUALITY and INEQUALITY mean that the column would be used in a where clause predicate. INCLUDE means that the column should be an included column on an existing non-clustered index.

A simple example

— get the missing indexes that would be beneficial for speeding up above queries
SELECT  D.index_handle, [statement] AS full_object_name, unique_compiles, avg_user_impact, user_scans, user_seeks, column_name, column_usage
FROM    sys.dm_db_missing_index_groups G
       JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle
       JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle
       CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC
ORDER BY D.index_handle, [statement];

Limitations

This cool feature of course has some limitations. Some of those are:

  • Database Engine Tuning Advisor kicks its behind. Think of Missing Indexes DMV’s as a really lightweight DTA. So when doing a really in-depth index analysis, don’t rely only on the Missing Indexes DMV’s. Just start with them.
  • Missing Indexes DMV’s don’t provide any information about new index overhead like space or IO/CPU overhead on updates, inserts and deletes.
  • There’s no information about the column order in the suggested index or whether it should be clustered or non-clustered.
  • Missing Indexes DMV’s consider only per query indexes and not per workload indexes.
  • Missing Indexes DMV’s can track a maximum of 500 indexes.
  • Trivial execution plans (plans for really simple SQL Statements) are not considered.

The DMV’s return raw data so you’ll have to do some string magic to build the CREATE INDEX statements out of it.

Finding unused indexes

Since SQL Server keeps data of all used indexes, getting the unused indexes is a simple matter of comparing used indexes to all existing indexes. Those that exist but are not used are of course unused indexes.

SELECT  OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
       OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
       I.NAME AS IndexName        
FROM    sys.indexes I   
WHERE   — only get indexes for user created tables
       OBJECTPROPERTY(I.OBJECT_ID, ‘IsUserTable’) = 1
       — find all indexes that exists but are NOT used
       AND NOT EXISTS (
                   SELECT  index_id
                   FROM    sys.dm_db_index_usage_stats
                   WHERE   OBJECT_ID = I.OBJECT_ID
                           AND I.index_id = index_id
                           — limit our query only for the current db
                           AND database_id = DB_ID())
ORDER BY SchemaName, ObjectName, IndexName

 

identifies unused indexes based on this criteria and outputs them in a pretty nice format

http://www.foliotek.com/devblog/identifying-unused-indexes-in-a-sql-server-database/

 

— GET UNUSED INDEXES THAT APPEAR IN THE INDEX USAGE STATS TABLE   

DECLARE @MinimumPageCountINT

 

SET @MinimumPageCount = 500

 

SELECT databases.NAME                                                      AS

      [Database],

      Object_name(indexes.object_id)                                      AS

      [Table],

      indexes.NAME                                                        AS

      [Index],

      PhysicalStats.page_count                                            AS

      [Page_Count],

      CONVERT(DECIMAL(18, 2), PhysicalStats.page_count * 8 / 1024.0)      AS

      [Total Size (MB)],

      CONVERT(DECIMAL(18, 2), PhysicalStats.avg_fragmentation_in_percent) AS

      [Frag %],

      ParititionStats.row_count                                           AS

      [Row Count],

      CONVERT(DECIMAL(18, 2), ( PhysicalStats.page_count * 8.0 * 1024 ) /

                              ParititionStats.row_count)                  AS

      [Index Size/Row (Bytes)]

FROM   sys.dm_db_index_usage_stats UsageStats

      INNER JOIN sys.indexes Indexes

              ON indexes.index_id = UsageStats.index_id

                 AND indexes.object_id = UsageStats.object_id

      INNER JOIN sys.databases Databases

              ON databases.database_id = UsageStats.database_id

      INNER JOIN sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL,

                 NULL) AS

                 PhysicalStats

              ON PhysicalStats.index_id = UsageStats.index_id

                 AND PhysicalStats.object_id = UsageStats.object_id

      INNER JOIN sys.dm_db_partition_stats ParititionStats

              ON ParititionStats.index_id = UsageStats.index_id

                 AND ParititionStats.object_id = UsageStats.object_id

WHERE  UsageStats.user_scans = 0

      AND UsageStats.user_seeks = 0

      — ignore indexes with less than a certain number of pages of memory   

      AND PhysicalStats.page_count > @MinimumPageCount

      — Exclude primary keys, which should not be removed   

      AND indexes.type_desc != ‘clustered’

ORDER  BY [page_count] DESC

 

Unused Indexes with no entry in the Index Usage Stats Table

http://www.foliotek.com/devblog/identifying-unused-indexes-in-a-sql-server-database/

— GET UNUSED INDEXES THAT DO **NOT** APPEAR IN THE INDEX USAGE STATS TABLE   

DECLARE @dbid INT SELECT @dbid = db_id(db_name()) SELECT DATABASES.name AS

[Database], objects.name AS [Table], indexes.name AS [Index], indexes.index_id,

physicalstats.page_count AS [Page Count], CONVERT(DECIMAL(18, 2),

physicalstats.page_count * 8 / 1024.0) AS [Total Index Size (MB)], CONVERT(DECIMAL(18, 2),

physicalstats.avg_fragmentation_in_percent) AS [Fragmentation (%)] FROM

sys.indexes indexes INNER JOIN sys.objects objects ON indexes.object_id =

objects.object_id LEFT JOIN sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, NULL)

physicalstats ON physicalstats.object_id = indexes.object_id AND

physicalstats.index_id = indexes.index_id INNER JOIN sys.DATABASES DATABASES ON

DATABASES.database_id = physicalstats.database_id WHERE objects.type = ‘u’ IS user

TABLE AND indexes.is_primary_key = 0 AND indexes.index_id NOT IN ( SELECT

usagestats.index_id FROM sys.dm_db_index_usage_stats usagestats WHERE

usagestats.object_id = indexes.object_id AND indexes.index_id = usagestats.index_id AND

database_id = @dbid) ORDER BY physicalstats.page_count DESC, objects.name,

indexes.index_id, indexes.name ASC

Note that both these methods are only useful if your server has been running long enough and has been through peak usage. The data in the mentioned DMV’s is cleared on the next instance restart. If we want to save this information for further analysis we should create a scheduled job that periodically queries the DMV’s and saves the information to tables.

 

Leave a Reply

Your email address will not be published. Required fields are marked *