Data World

Archive for the ‘sql server’ Category

MSSQL Server: Find Missing Indexes

Posted by Pramod Singla on March 4, 2017

Problem Statement: Script to find the missing indexes using 3 different ways.
Solution: 1) Find missing indexes using DMVs

--Find top ten missing indexes using following DMV 
SELECT TOP 10 ( user_seeks ) * avg_total_user_cost * ( avg_user_impact * 0.01 ) 
              [index_advantage],--%advantage in the query 
              --Date and time of last seek caused by user queries that the recommended index in the group could have been used for.
              AS [Database.Schema.Table], 
              'Create index idx_' 
              + Parsename( mid.[statement], 1) + '_' 
              + Cast(row_number() OVER(ORDER BY (user_seeks) * 
              avg_total_user_cost * ( 
              avg_user_impact * 0.01 ) DESC) AS VARCHAR(150)) 
              + ' on ' + Parsename( mid.[statement], 1) + '(' ++ 
              equality_columns + ')' + CASE WHEN mid.included_columns IS NOT 
              NULL THEN 
              ' include ('+ mid.included_columns+')' ELSE '' END 
              'drop index idx_' 
              + Parsename( mid.[statement], 1) + '_' 
              + Cast(row_number() OVER(ORDER BY (user_seeks) * 
              avg_total_user_cost * ( 
              avg_user_impact * 0.01 ) DESC) AS VARCHAR(150)) 
              + ' on ' + Parsename( mid.[statement], 1) 
              --  Average cost of the user queries that could be reduced by the index in the group. 
--Average percentage benefit that user queries could experience, 
FROM   sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK) 
       INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) 
               ON migs.group_handle = mig.index_group_handle 
       INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) 
               ON mig.index_handle = mid.index_handle 
WHERE  mid.database_id = Db_id() -- Remove this to see for entire instance 
ORDER  BY index_advantage DESC 

2) Find missing indexes on FK

FROM   sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc
ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns AS fkc_c
ON fkc.parent_object_id = fkc_c.object_id
AND fkc.parent_column_id = fkc_c.column_id
LEFT JOIN sys.index_columns ic
JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
ON fkc.parent_object_id = ic.object_id
AND fkc.parent_column_id = ic.column_id
JOIN sys.objects AS o
ON o.object_id = fk.parent_object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id

3) Find missing indexes using stored query plans.

SELECT MissingIndexNode.value('(MissingIndexGroup/@Impact)[1]', 'float')     AS 
       Object_name(sub.objectid, sub.dbid)                                   AS 
       MissingIndexNode.value ('(MissingIndexGroup/MissingIndex/@Database)[1]', 
       + '.' 
       + MissingIndexNode.value ('(MissingIndexGroup/MissingIndex/@Schema)[1]', 
       + '.' 
       + MissingIndexNode.value ('(MissingIndexGroup/MissingIndex/@Table)[1]', 
       'VARCHAR(128)')                                                       AS 
       Stuff((SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)') 
              FROM   MissingIndexNode.nodes ( 
'MissingIndexGroup/MissingIndex/                             ColumnGroup[@Usage=''EQUALITY'']/Column'
) AS t ( c ) 
 FOR XML PATH('')), 1, 1, '')                                         AS 
Stuff((SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)') 
       FROM   MissingIndexNode.nodes ( 
'MissingIndexGroup/MissingIndex/                             ColumnGroup[@Usage=''INEQUALITY'']/Column'
) AS t ( c ) 
 FOR XML PATH('')), 1, 1, '')                                         AS 
Stuff((SELECT ',' + c.value('(@Name)[1]', 'VARCHAR(128)') 
       FROM   MissingIndexNode.nodes ( 
'MissingIndexGroup/MissingIndex/                             ColumnGroup[@Usage=''INCLUDE'']/Column'
) AS t ( c ) 
 FOR XML PATH('')), 1, 1, '')                                         AS 
sub.usecounts                                                         AS 
sub.refcounts                                                         AS 
qs.execution_count                                                    AS 
qs.last_execution_time                                                AS 
qs.total_logical_reads                                                AS 
qs.total_elapsed_time                                                 AS 
qs.total_physical_reads                                               AS 
qs.total_worker_time                                                  AS 
StmtPlanStub.value('(StmtSimple/@StatementText)[1]', 'varchar(8000)') AS 
                 OVER ( 
                   PARTITION BY qs.plan_handle 
                   ORDER BY qs.statement_start_offset ) AS StatementID, 
        FROM   sys.dm_exec_query_stats qs) AS qs 
       JOIN (SELECT x.query('../../..')                          AS StmtPlanStub 
                    AS MissingIndexNode, 
                    x.value('(../../../@StatementId)[1]', 'int') AS StatementID, 
             FROM   sys.dm_exec_cached_plans AS cp 
                    CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp 
                    CROSS APPLY qp.query_plan.nodes ( 
'/ShowPlanXML/BatchSequence/                                         Batch/Statements/StmtSimple/                                         QueryPlan/MissingIndexes/                                         MissingIndexGroup'
) mi ( x )) AS sub 
  ON qs.plan_handle = sub.plan_handle 
     AND qs.StatementID = sub.StatementID

Posted in Performance Tuning, sql server | Tagged: , , | Leave a Comment »

SQL Server-Interview Questions

Posted by Pramod Singla on May 17, 2011


  1. What is normalization and what are its pros and cons?
  2. What is demoralization and what are its pros and cons?
  3. What are ACID properties and how SQL server is maintaining these?
  4. What are DML and DDL commands?
  5. What is difference between a view and a table?
  6. What are synonyms?


  1. What is the difference between index rebuilding vs index reorganizing and in which scenarios each can be used?
  2. What is the default transaction isolation level used in SQL server?
  3. What is the difference between user and login?
  4. What  all permissions are required to execute scalar UDF and table valued UDF ?
  5. Where does the full text catalog created in the SQL server 2008?
  6. How to rename a table column?
  7. What is the difference between coalesce and isnull functions?
  8. What is the difference sp_executesql and exec?
  9. What are magic tables?
  10. How can you delete duplicate records from a table?
  11. How to find the third highest salary for an employee?Ans
  12. Which all recovery models are available in SQL Server?
  13. List all system DBs with their usage.
  14. What are the different isolations in SQL server and what’s the difference between them?
  15. What is the difference between intersect v/s except?
  16. Temp Table vs Temp variables.
  17. What is CTE?
  18. What will happen if rollback is executed in innermost transaction?
  19. What will happen if commit is executed in innermost transaction?
  20. What is OUTPUT clause in TSQL?
  21. What is the max size of varchar(max)?
  22. What is fill factor and how it is important?
  23. List few of  DMVs with their usage.
  24. What is difference between delete and truncate?
  25. What is sparse column and how is it useful?
  26. By default SQL server listens on which port?
  27. What is a heap?
  28. What is the difference between clustered and nonclustered indexes?
  29. What’s the latest version of SQL server?
  30. What’s row level compression and the page level compression?


  1. Can you take transnational or differential backup of master database?
  2. How to start SQL Server single user mode and when do you need it?
  3. What’s the use of browser service?
  4. How to make DAC connection?Ans
  5. What is the difference between index rebuilding and index reorganizing and in which scenario these can be used?
  6. What are the filtered indexes and why are they useful in some scenarios?
  7. What is a covering index?
  8. What can cause an index scan instead of index seek?
  9. Which port does DAC use?
  10. What all type of backups are available in SQL Server?
  11. What will you do to improve the performance of the tempdb?
  12. What is snapshot isolation level and what are its pros and cons?
  13. What will you do if log-files are over growing?
  14. Find the running queries and the resources it is waiting on.
  15. What are VLFs and why are they important to watch?
  16. How to find a backup is valid without restoring it?
  17. How exactly does SQL server cater a SELECT Query?
  18. How exactly does SQL server cater an UPDATE query?
  19. What is TDE and how you can set it up?
  20. What type of indexes are available in SQL server?


  1. What is difference between Control flow and Data Flow Engine?
  2. List the ways of deployment available in SSIS 2012?
  3. How to configure checkpoint in SSIS?
  4. How to debug a SSIS package?
  5. What’s new in SQL server 2012?
  6. What is fast parse?
  7. List 5 ways to speed up your SSIS package.
  8. List the ways you can execute a SSIS package.
  9. If you want to delete duplicate records that what option would you use in SSIS?
  10. Explain SCD task and its working.
  11. Explain Lookup task.
  12. Explain SCD type 1,2 and 3.
  13. How you would handle errors in SSIS?
  14. How to check the execution status of  a SSIS package,deployed in SSISDB, using system tables.Ans
  15. What is factless table?
  16. What is the difference between dimension table and fact table?
  17. What is the difference between star and snowflake schema?

Posted in Interview Questions, sql server | Tagged: , , , , | 1 Comment »

SQL Server – Cause Of Index Scan

Posted by Pramod Singla on May 16, 2011

Question: Why Index Scan is happening even thought index exists on predicate column?

Generally,Query Optimizer (QO) tries to use an Index Seek which means that optimiser has found a useful index to retrieve recordset. But sometimes despite of an index being created on a query predicate columns, Query Optimiser uses Index Scan instead of Index Seek. The causes of using index scan are explained below.

Causes of Index Scan

  • The index is not selective enough.Use DBCC show_statistics (‘tableName,’indexName’) to check the selectivity of an index. Selectivity is inversely proportional to the density. It means low density of the index is good.
  • The number of pages returned by the query have reached the tipping point.
  • The first column of the index is not included in the query predicate.
  • Statistics are stale.
  • Functions like upper, lower, substring are applied on the predicate columns of the query.
  • In like operator, % wild character is used as the first character in the search string, e.g.
    select *from Person. where City like ‘%Both’


Posted in Performance Tuning, sql server | Tagged: , , | 1 Comment »