1. What is an Index?
A database
index is a data structure that improves the speed of data retrieval operations
on a database table at the cost of additional writes and storage space to
maintain the index data structure. Indexes are used to quickly locate data
without having to search every row in a database table every time a database
table is accessed. Indexes can be created using one or more columns of a
database table.
In SQL
Server, a clustered index determines the physical order of data in a table.
There can be only one clustered index per table (the clustered index IS the
table).
It does
this by reducing the number of database data pages that have to be
visited/scanned.
2. What are the various Types of indexes available
in SQL Server?
- Clustered
- NonClustered
- Unique
- Columnstore
- Index
with included columns
- Index
on computed columns
- Filtered
- Spatial
- XML
- Full-text
3. What is Cluster index?
A clustered index is a
type of index where the table records are physically re-ordered to match the
index. A clustered index sorts and stores the data rows of the table or view in
order based on the clustered index key.
The clustered index is implemented as a B-tree
index structure that supports fast
retrieval of the rows, based on their clustered index key values.
4. What is Non-Cluster index?
A non-clustered index (or regular b-tree index) is
an index where the order of the rows does not match the physical order of the
actual data. It is instead ordered by
the columns that make up the index. In a
non-clustered index, the leaf pages of the index do not contain any actual
data, but instead contain pointers to
the actual data. These pointers
would point to the clustered index data page where the actual data exists (or
the heap page if no clustered index exists on the table).
4A. Why create non-clustered indexes
The main benefit to having a non-clustered index on
a table is it provides fast access to data.
The index allows the database engine to locate data quickly without
having to scan through the entire table.
As a table gets larger it is very important that the correct indexes are
added to the table, as without any indexes query performance will drop off
dramatically.
4B. When should non-clustered indexes be created
There are two cases
where having a non-clustered index on a table is beneficial. First, when there is more than one set of
columns that are used in the WHERE clause of queries that access the table. A second index (assuming there is already a
clustered index on the primary key column) will speed up execution times and
reduce IO for the other queries. Second,
if your queries frequently require data to be returned in a certain order,
having an index on these columns can reduce the amount of CPU and memory
required as additional sorting will not need to be done since the data in the
index is already ordered.
-- Adding
non-clustered index
CREATE NONCLUSTERED INDEX
IX_Person_LastNameFirstName ON Person.Person(LastName ASC,FirstName ASC);
4C.Non-clustered indexes relation to clustered
index
As described above, the
clustered index stores the actual data of the non-key columns in the leaf nodes
of the index. The leaf nodes of each non-clustered index do not contain
any data and instead have pointers to the actual data page (or leaf node) of the
clustered index. The diagram below illustrates this point.
5. What is Unique Index?
A unique index ensures
that the index key contains no duplicate
values and therefore every row in the table or view is in some way unique.
Uniqueness can be a property of both clustered and Non-Clustered indexes.
6. What is Columnstore index?
A Columnstore index stores data in a column-wise (columnar)
format, unlike the traditional B-tree structures used for clustered and NonClustered
rowstore indexes, which store data row-wise (in rows). A Columnstore index
organizes the data in individual columns that are joined together to form the
index. This structure can offer significant performance gains for queries that
summarize large quantities of data, the sort typically used for business
intelligence (BI) and data warehousing.
7. What is Index with Included columns?
A NonClustered index that is extended to include
non-key columns in addition to the key columns.
8. What is Filtered index?
A filtered index is a
special index type where only a certain portion of the rows of the table are
indexed. Based on the filter criteria
that is applied when the index is created only the remaining rows are indexed
which can save on space, improve on query performance and reduce maintenance
overhead as the index is much smaller.
8A. Why use it?
Filtered indexes are
useful when you are creating indexes on tables where there are a lot of NULL
values in certain columns or certain columns have a very low cardinality and
you are frequently querying a low frequency value.
A filtered index is
created simply by adding a WHERE clause to any non-clustered index creation
statement.
CREATE NONCLUSTERED INDEX
IX_SalesOrderHeader_OrderDate_INC_ShipDate ON Sales.SalesOrderHeader
(OrderDate ASC) WHERE ShipDate IS
NULL;
9. What is spatial index?
A spatial index provides
the ability to perform certain operations more efficiently on spatial objects
(spatial data) in a column of the geometry data type. The spatial
index reduces the number of objects on which relatively costly spatial
operations need to be applied.
10. What is an XML index?
An XML index is an index
type that is specifically built to handle indexing XML type columns. There are two different types of XML indexes,
primary and secondary.
11. What is a Full text index?
A special type of token-based functional index that
is built and maintained by the Microsoft Full-Text Engine for SQL Server. It
provides efficient support for sophisticated word searches in character string
data.
12. What is the difference between Cluster and Non
Cluster Index?
Cluster Index
·
A clustered index is a special type of index that
reorders the way records in the table are physically stored.
·
Table can have only one clustered index and this is
usually made on the primary key.
·
The leaf nodes of a clustered index contain the
data pages.
·
A cluster index is a form of tables which consist
of column and rows.
·
Cluster index exists on the physical level
·
it sorts the data at physical level
Non Cluster Index
·
A non-clustered index is a special type of index in
which the logical order of the index does not match the physical stored order
of the rows on disk.
·
The leaf node of a non-clustered index does not
consist of the data pages. Instead, the leaf nodes contain index rows.
·
It does not sort the data at physical level
·
A table has 255 non clustered indexes
·
A table has many non-clustered indexes.
·
It work on the order of data
13. What is a Super Key?
Super key is a set of
one or more than one keys that can be used to identify a record uniquely in a
table. Example: Primary key, Unique key, Alternate key are subset of Super
Keys.
14. What is a Candidate
Key?
A Candidate Key is a set
of one or more fields/columns that can identify a record uniquely in a table.
There can be multiple Candidate Keys in one table. Each Candidate Key can work
as Primary Key.
15. What is an Alternate
Key?
An Alternate key is a
key that can be work as a primary key. Basically it is a candidate key that
currently is not primary key.
16. What is PRIMARY KEY
constraint?
Primary key is a set of
one or more fields/columns of a table that uniquely identify a record in
database table. It cannot accept null, duplicate values. Only one Candidate Key
can be Primary Key.
17. What is UNIQUE KEY constraint?
Unique key is a set of
one or more fields/columns of a table that uniquely identify a record in
database table. It is like Primary key but it can accept only one null value
and it cannot have duplicate values.
18. What is FOREIGN KEY constraint?
Foreign Key is a field
in database table that is Primary key in another table. It can accept multiple
null, duplicate values.
Example: We can
have a DeptID column in the Employee table which is pointing to DeptID column
in a department table where it a primary key.
19. What is the difference between Primary Key and
Unique Key?
|
Primary Key
|
Unique Key
|
|
Primary Key can’t accept null values.
|
Unique key can accept only one null value.
|
|
By default, Primary key is clustered index and
data in the database table is physically organized in the sequence of
clustered index.
|
By default, Unique key is a unique non-clustered
index.
|
|
We can have only one Primary key in a table.
|
We can have more than one unique key in a table.
|
|
Primary key can be made foreign key into another
table.
|
In SQL Server, Unique key can be made foreign key
into another table.
|
20. By default which index is created when we
create a Primary Key column on a table?
By default Primary Key column is created with a
Cluster Index.
21. By default which index is created when we
create a Unique Key column on a table?
By default Unique Key column is created with a
non-cluster Index.
22. What are the different index configurations a
table can have?
A table can have one of the following index
configurations:
- No
indexes
- A
clustered index
- A
clustered index and many NonClustered indexes
- A
NonClustered index
23. How to create a multi-column
index in SQL Server database?
CREATE
INDEX name_index
ON
Employee (Employee_Name, Department)
24. Explain the structure of Index in SQL server?
An index is structured
by the SQL Server Index Manager as a balanced tree (or Btree). A B-tree is
similar to an upside-down tree, means with the root of the tree at the top, the
leaf levels at the bottom, and intermediate levels in between.
Each object in the tree
structure is a group of sorted index keys called an index page.
All search requests begin at the root of a B-tree and then move through the tree to the appropriate leaf level.
All search requests begin at the root of a B-tree and then move through the tree to the appropriate leaf level.
25. How to Disable Indexes?
--Disable Index
ALTER INDEX
[IndexName] ON TableName DISABLE
GO
26. How to Enable Indexes?
--Enable Index
ALTER INDEX
[IndexName] ON TableName REBUILD
GO
27. Can we Insert Data if Clustered Index is
disabled?
No, we cannot insert data if Clustered Index is
disabled because Clustered Indexes are in fact original tables which are
physically ordered according to one or more keys (Columns).
28. Why can there be only one Clustered Index and
not more than one?
Clustered index defines
the way in which data is ordered physically on the disk. And there can only be
one way in which you can order the data physically. Hence there can only be one
clustered index per table.
29. What is the Maximum Number of
Index per Table?
|
Object
|
SQL Server
|
|||
|
7
|
2000
|
2005
|
2008
|
|
|
Clustered Index
|
1
|
1
|
1
|
1
|
|
Non-clustered Index
|
249
|
249
|
249
|
999
|
30. How many maximum columns can be a part of on an
index?
Maximum of 16 columns can be a part of an index.
31. What is the Heap?
A heap is a table
without a clustered index. Data is stored in the heap without specifying an
order. Usually data is initially stored in the order in which is the rows are
inserted into the table, but the Database Engine can move data around in the
heap to store the rows efficiently; so the data order cannot be predicted.
32. What are the Advantages of
Indexes?
- Query
optimization: Indexes make search queries much faster.
- Uniqueness:
Indexes like primary key index and unique index help to avoid duplicate
row data.
- Text
searching: Full-text indexes in SQL Server, users have
the opportunity to optimize searching against even large amounts of text
located in any field indexed as such.
- Sort
Operations: Can be used to avoid a sort operation by
providing the data pre-sorted
33. What are the Disadvantages of Indexes?
- Additional Disk Space
- Insert Update and Delete statements can become
slow
- Adding indexes to the schema increases the
overhead on the database because the indexes will require ongoing
maintenance.
34. What is Fill factor in indexes?
Fill factor is a value that
defined the
percentage of space to be filled with data in a leaf level page. The
remaining space left is used for future growth of data in the page. Fill factor
is a number from 1 to 100. Its default
value is 0, which is same as 100.
So when we say fill factor is 70 means, 70% of space is filled with data and
remaining 30% is vacant for future use. So higher the fill factor, more data is
stored in the page. Fill factor setting is applied when we create/rebuild
index.
- The 'fill factor' option indicate how full SQL
Server will create each index page.
- When the index page doesn’t have free space
for inserting a new row, SQL Server will create new index page and
transfer some rows from the previous index page to the new index page.
This process is called page split.
- If we want to reduce the number of page splits
then we can use Fill factor option. Using Fill factor SQL will
reserve some space on each index page.
- The fill factor is a value from 1 through 100
that indicates the percentage of the index page to be left empty. The
default value for fill factor is 0.
- If the table contains the data which is not
changed frequently then we can set the fill factor option to 100. When the
table's data is modified frequently, we can set the fill factor option to
80% or as we want.
35. What are Points to remember while using the
FILLFACTOR Argument?
Below are the criteria
you need to consider while choosing fill factor.
- For
static/look-up table: these types of
tables have static data means data changes very rarely in the table. So we can set high value of fill factor
means 100 or 0.
- For
Dynamic table: In this type of table, data get changes (inserted/updated)
frequently. So we need to set low fill factor, between 80 to 90.
- For
Table with Clustered Index on Identity Column:
Here the data is inserted at the end of the table always. So we can have
higher value of fill factor between 95 to 100.
36. How to set Fill
Factor?
Server level: A generic
fill factor setting is applied at the server level for all table/index. We can
set a server level default fill factor by using sp_configure with a parameter
as below script.
EXEC sys.sp_configure
'fill factor', 90
GO
RECONFIGURE WITH OVERRIDE
GO
At Index/Table level:
While creating/rebuilding index we can set a specific fill factor. We can use
below script to set fill factor while rebuilding index.
ALTER INDEX Index_name
ON [SchemaName].[TableName]
REBUILD WITH
(FILLFACTOR = 80);
GO
37. What is Pad index in Index?
This option specifies
index padding. When turned ON, it uses the percentage specified by FILLFACTOR
is applied to the intermediate-level and root level pages of an index.
PAD_INDEX = { ON | OFF }
When ON, the percentage of free space specified by FILLFACTOR is applied to the
intermediate level pages of the index. When OFF or a FILLFACTOR value is not
specified, the intermediate level pages are filled to near capacity leaving
enough space for at least one row of the maximum size the index can have,
considering the set of keys on the intermediate pages. The default is OFF.
38. Can we have indexes
on Views?
Indexes can be defined
on views. Indexed views are a method of storing the result set of the view in
the database, thereby reducing the overhead of dynamically building the result
set. An indexed view also automatically reflects modifications made to the data
in the base tables after the index is created.
39. What is Index
Fragmentation?
Index fragmentation is
an “expected” and “unavoidable” characteristic of any OLTP environment.
Fragmentation is defined as any condition which causes more than the optimal amount
of disk I/O to be performed in accessing a table, or causes the disk I/Os that
are performed to take longer than they optimally would.
40. What are the Causes
of Index Fragmentation?
- Insert and Update operations causing Page
splits
- Delete operations
- Initial allocation of pages from mixed extents
- Large row size
41. How many types of Index Fragmentation are there
in SQL Server?
Various Types of Index
Fragmentation
- Internal Fragmentation
- Logical Fragmentation
- External Fragmentation
42. What is Internal Fragmentation?
When pages are less than
fully used, the part of each page that is unused constitutes a form of
fragmentation, since the table’s or index’s rows are no longer packed together
as tightly as they could be. This is known as Internal Fragmentation.
43. How Internal Fragmentation occurs?
It usually occurs due to
–
- Random deletes resulting in empty space on
data pages
- Page-splits due to inserts or updates
- Shrinking the row such as when updating a
large value to a smaller value
- Using a fill factor of less than 100
- Using large row sizes
44. What is Logical Fragmentation?
SQL Server uses 8KB
Pages to store data on disk. When a clustered index is created on a table,
SQL Server creates a b-tree data structure for the index and links all data
pages at the leaf level of the tree in a logical order by using a doubly linked
list. Logical fragmentation occurs when the pages in this doubly linked list
are not contiguous in the index, meaning that indexes have pages in which the
logical ordering of pages, which is based on the key value, does not match the
physical ordering inside the data file.
45. How Logical Fragmentation occurs?
This could happen due to
–
- Page-splits due to inserts or updates
- Heavy deletes that can cause pages be removed
from the page chain, resulting in dis-contiguous page chain
46. What is External Fragmentation?
Extent fragmentation
occurs when the extents of a table or index are not contiguous with the
database leaving extents from one or more indexes intermingled in the file.
47. How External Fragmentation occurs?
This can occur due to:
- Random deletes, which could leave some of the
pages in an extent unused while the extent itself is still reserved as
part of the table’s space allocation. Think of it like Internal
fragmentation, but in extents instead of pages
- Deletes on ranges of contiguous rows within
the table, causing one or more entire extents to become de-allocated, thus
leaving a gap between the surrounding extents of the table or index
- Interleaving of a table’s data extents with
the extents of other objects
48. How to get information about the Index
Fragmentation in a database?
We can use sys.dm_db_index_physical_stats DMF.
49. What all modes are available to get a report
about Index fragmentation?
DMF
“sys.dm_db_index_physical_stats” has three modes –
- DETAILED
– reads all data and index pages. Be careful with using this options since
it causes the entire index be read into memory and may result in IO/Memory
issues
- SAMPLED:
reads 1% of the pages if more than 10,000 pages
- LIMITED:
only reads the parent level of b-tree (same as DBCC SHOWCONTIG WITH FAST).
Limited option doesn’t report page density, since it does not read the
leaf level pages
50. How to resolve Fragmentation in Indexes?
- Rebuild
– ALTER INDEX … REBUILD (replaces DBCC
REINDEX)
- Reorganize
– ALTER INDEX … REORGANIZE (replaces DBCC INDEXDEFRAG)
51. What is the difference between Index Rebuild
and Index Reorganize?
|
Characteristic
|
Alter Index REORGANIZE
|
Alter Index REBUILD
|
|
Online or Offline
|
Online
|
Offline (unless using the Online keyword)
|
|
Address Internal Fragmentation
|
Yes (can only raise page density)
|
Yes
|
|
Address Logical Fragmentation
|
Yes
|
Yes
|
|
Transaction Atomicity
|
Small Discrete Transactions
|
Single Atomic Transaction
|
|
Statistics Automatically
|
No
|
Yes
|
|
Parallel Execution in multi-processor machines
|
No
|
Yes
|
|
Untangle Indexes that have become interleaved
within a data file
|
No
|
Yes
|
|
Transaction log space used
|
Less
|
More
|
|
Additional free space required in the data file
|
No
|
Yes
|
52. What is the difference between Online and
Offline operations?
Online rebuild is a process where while the index is rebuild, it can
still be used. It is like part by part is copied in memory as a temporary
index and once everything is done, the actual index/table is locked for some
time during which old index is dropped and newly created temporary index is
renamed.
Offline is where the table/index has to be locked during the time of index rebuild. Bad about online rebuild is you need more memory but the best part is no impact or rather minimum impact on application and/or users. Good part of offline index is no extra memory needed but worst part is table/index is locked during the process of rebuild.
You should always try to rebuild index online but if it is failing then you have to go with offline rebuild.
Offline is where the table/index has to be locked during the time of index rebuild. Bad about online rebuild is you need more memory but the best part is no impact or rather minimum impact on application and/or users. Good part of offline index is no extra memory needed but worst part is table/index is locked during the process of rebuild.
You should always try to rebuild index online but if it is failing then you have to go with offline rebuild.
52-A. code for offline index & online index?
USE <database>
ALTER INDEX IX_LOGGING
ON [dbo].[Logging]
REBUILD
--(ONLINE=ON)
53. How to resolve Fragmentation for a heap table?
SQL Server 2008 onwards
we can use REBUILD command to remove fragmentation from heap.
ALTER TABLE…REBUILD (SQL
2008+).
54.
What are missing indexes in a database?
When the query optimizer
generates a query plan, it analyses what are the best indexes for a particular
filter condition. If the best indexes do not exist, the query optimizer
generates a suboptimal query plan, but still stores information about these
indexes. The missing indexes feature enables you to access information about
these indexes so you can decide whether they should be implemented.
55.
Which DMV is used to identify missing Indexes in a database?
sys.dm_db_missing_index_details
56.
How can we identify Index usage history?
sys.dm_db_index_usage_stats – Returns counts of
different types of index operations and the time each type of operation was
last performed.
57.
What is SORT_IN_TEMPDB option while creating or rebuilding an Index?
When we create or
rebuild an index, by setting the SORT_IN_TEMPDB option to ON we can direct the
SQL Server Database Engine to use tempdb to store the intermediate sort results
that are used to build the index. Although this option increases the amount of
temporary disk space that is used to create an index, the option could reduce
the time that is required to create or rebuild an index when tempdb is on a set
of disks different from that of the user database.
58.
What is data compression in a SQL server database?
SQL Server 2012 supports row and page compression
for tables and indexes. You can use the data compression feature to
help compress the data inside a database, and to help reduce the size of the
database. In addition to saving space, data compression can help improve
performance of I/O intensive workloads because the data is stored in fewer
pages and queries need to read fewer pages from disk. However, extra CPU
resources are required on the database server to compress and decompress the data,
while data is exchanged with the application.
59.
What all database objects can be configured with Data compression?
Data compression can be
configured for the following database objects:
- A whole table that is stored as a heap.
- A whole table that is stored as a clustered
index.
- A whole NonClustered index.
- A whole indexed view.
- For partitioned tables and indexes, the
compression option can be configured for each partition, and the various
partitions of an object do not have to have the same compression setting.
60.
What are the Limitations and Restrictions of Data Compression?
- System tables cannot be enabled for
compression.
- If the table is a heap, the rebuild operation
for ONLINE mode will be single threaded. Use OFFLINE mode for a
multi-threaded heap rebuild operation.
- You cannot change the compression setting of a
single partition if the table has nonaligned indexes.
61.
Various types of compression in SQL Server database?
Data compression is
implemented at two levels: ROW and PAGE.
62.
What are the benefits of Column Stored index?
The benefits of using a
non-clustered Columnstore index are:
- Only the columns needed to solve a query are
fetched from disk (this is often fewer than 15% of the columns in a
typical fact table)
- It is easier to compress the data due to the
redundancy of data within a column
- Buffer hit rates are improved because data is
highly compressed, and frequently accessed parts of commonly used columns
remain in memory, while infrequently used parts are paged out.
63. What is Index with included columns?
A non-clustered index
that is extended to include nonkey columns in addition to the key columns.
64. Can you create a Non cluster index on primary
key?
Yes, by default a
clustered index is created on primary key. However, a noncluster index can be
created on Primary Key.
65. What is index Seek?
Index seek is an
operation that only touches rows that qualify and pages that contain these
qualifying rows.
66. What is index Scan?
Index scan is an
operation that touches every row in the table whether or not it qualifies.
67.
What is difference between Index Seek vs. Index Scan?
- Index
Seek and Index Scan are operation for query tuning in execution plans.
- Table
Scan scans every record of the table. So the cost of proportional is the
number of rows of that table.
- The
Index Scan is preferred only when the table is small.
- Index
Seek only touches the rows which qualify and the pages that contain that
qualifying rows, so the cost of proportional is the number of qualifying
rows and pages instead of the number of rows in the table.
- Index
seek is preferred for highly sensitive queries.
68. What
are the index designing recommendations in sql server? Or what are the criteria
for index creation in sql server?
The main recommendation for index designing in SQL
Server are as follows:
1. Analyze the where clause and sql join criteria columns in the query.
2. Use narrow indexes on the tables.
3. Analyze column uniqueness in the table.
4. Analyze the column data type.
5. Consider column order in the table.
6. Consider the type of index (Clustered Vs. Non-Clustered)
7. Prefer int data type column and primary key columns when creating the index on a table.
1. Analyze the where clause and sql join criteria columns in the query.
2. Use narrow indexes on the tables.
3. Analyze column uniqueness in the table.
4. Analyze the column data type.
5. Consider column order in the table.
6. Consider the type of index (Clustered Vs. Non-Clustered)
7. Prefer int data type column and primary key columns when creating the index on a table.
69. Can you create index on the BIT data type
column in SQL Server ?
Yes we
can, Microsoft SQL Server provides the facility to create the index on the BIT
data type column. But there is not a significant advantage to create the index
on BIT data type column as this type of column will have maximum two unique
values and also the selectivity on this column is very low in most of the
scenarios.
70. How do you find the Index Fragmentation
level in SQL Server
A. The Index
fragmentation level in SQL Server can be determined using 2 ways. One by using DBCC SHOWCONTIG
& sys.dm_db_index_physical_stats DMV.
-----Find
database id with this query----
select * from sys.sysdatabases
----Find
Fragmentation Level by using Command Prompt SQL Server---
select * from sys.dm_db_index_physical_stats(7,0,null,null,'detailed')
Generally
if the Fragmentation level of an Index in less than 30% then it is advised to
Re-Organise an Index and If the Fragmentation level of an Index is More than
30% then it is recommended that you ReBuild the Index.
71. What is Fragmentation? How to detect
fragmentation and how to eliminate it?
Storing
data non-contiguously on disk is known as fragmentation. Before learning to
eliminate fragmentation, you should have a clear understanding of the types of
fragmentation. We can classify fragmentation into two types:
· Internal Fragmentation: When records are stored non-contiguously
inside the page, then it is called internal fragmentation. In other words,
internal fragmentation is said to occur if there is unused space between
records in a page. This fragmentation occurs through the process of data
modifications (INSERT, UPDATE, and DELETE statements) that are made against the
table and therefore, to the indexes defined on the table. As these modifications
are not equally distributed among the rows of the table and indexes, the
fullness of each page can vary over time. This unused space causes poor cache
utilization and more I/O, which ultimately leads to poor query performance.
· External Fragmentation: When on disk, the physical storage of pages
and extents is not contiguous. When the extents of a table are not physically
stored contiguously on disk, switching from one extent to another causes higher
disk rotations, and this is called Extent Fragmentation.
Index
pages also maintain a logical order of pages inside the extent. Every index
page is linked with previous and next page in the logical order of column data.
However, because of Page Split, the pages turn into out-of-order pages. An
out-of-order page is a page for which the next physical page allocated to the
index is not the page pointed to by the next-pagepointer in the current leaf
page. This is called Logical Fragmentation.
Ideal
non-fragmented pages are given below:
Statistics
for table scan are as follows:
- Page read requests:
2
- Extent switches: 0
- Disk space used by
table: 16 KB
- avg_fragmentation_in_percent:
0
- avg_page_space_used_in_percent: 100
Following are fragmented pages:
In
this case, the statistics for table scan are as follows:
- Page read requests:
6
- Extent switches: 5
- Disk space used by
table: 48 KB
- avg_fragmentation_in_percent
> 80
- avg_page_space_used_in_percent:
33
How to detect Fragmentation: We
can get both types of fragmentation using the DMV:
sys.dm_db_index_physical_stats.
For
the screenshot given below, the query is as follows:
SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_countFROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')ORDER BY avg_fragmentation_in_percent DESC
Along
with other information, there are two important columns that for detecting
fragmentation, which are as follows:
- avg_fragmentation_in_percent:
This is a percentage value that represents external fragmentation. For a
clustered table and leaf level of index pages, this is Logical
fragmentation, while for heap, this is Extent fragmentation. The lower
this value, the better it is. If this value is higher than 10%, some
corrective action should be taken.
- avg_page_space_used_in_percent: This
is an average percentage use of pages that represents to internal
fragmentation. Higher the value, the better it is. If this value is lower
than 75%, some corrective action should be taken.
Reducing
fragmentation:
- Reducing Fragmentation in a Heap: To
reduce the fragmentation of a heap, create a clustered index on the table.
Creating the clustered index, rearrange the records in an order, and then
place the pages contiguously on disk.
- Reducing Fragmentation in an Index: There
are three choices for reducing fragmentation, and we can choose one
according to the percentage of fragmentation:
If avg_fragmentation_in_percent > 5% and < 30%, then use ALTER
INDEXREORGANIZE: This statement is replacement for DBCC INDEXDEFRAG to reorder
the leaf level pages of the index in a logical order. As this is an online
operation, the index is available while the statement is running.
If avg_fragmentation_in_percent > 30%,
then use ALTER INDEX REBUILD: This is replacement for DBCC DBREINDEX to
rebuild the index online or offline. In such case, we can also use the drop and
re-create index method.
(Update: Please note this option is
strongly NOT recommended)Drop and re-create the clustered index: Re-creating a
clustered index redistributes the data and results in full data pages. The
level of fullness can be configured by using the FILLFACTOR option in CREATE
INDEX.
72 Is it possible to create index on more than
one column?
A Yes, Its possible. We can include maximum 16 columns as key columns while creating index. Sum of bytes should not be more than 900bytes.
73 In SQL Server, on a primary key column, is it possible to create Non-Clustered index
A Yes. Its possible to create the Non-Clustered Index on a primary key column. We can use below syntax to create Non-CI on primary key column while creating table itself.
1.Create table Customers
2.(
3.CID int identity(1,1)
primary key Nonclustered
74 What is difference between View and
Materialized view?
View result set doesn’t
save anywhere on disk and executes the query definition whenever they are
called, while materialized view are disk based and its result set table is
updated periodically.
Materialized view is
similar to regular views but the output of select query has been saved to a
table.
View shows the latest data all the time while
the materialized view only shows the fresh data after its result table is
updated either by setting a schedule or based on the change in the underlying
tables.
The
performance of the view depends on how good the selected statement the view
has. If the select statement has too many joins then it the view will perform
poorly.
While in the case of
materialized view, we are querying a table, which may also be indexed, that
increase its performance.
75 What is CTE (Common Table Expression)?
When a complex SQL
statement has number of joins then it can be made easier by using Common Table
Expression.
Consider the following SQL statement.
SELECT * FROM
(
SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp
left join Department
dept on emp.DeptID = dept.DeptID) E
WHERE E.Gender = ‘Male’
ORDER BY T.EmpName
The syntax of CTE is as
follow
- The CTE Name (followed
by WITH keyword)
- The Column List
(Optional)
- The Query (Appears
within parentheses after the AS keyword)
If we write the above
messy query using CTE it would be like
With E(EmpName, Department,
Gender)
AS
(
SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp
left join Department
dept on emp.DeptID = dept.DeptID
)
SELECT * FROM E
WHERE E.Gender = ‘Male’
ORDER BY E.EmpName
This way the query can
be made more readable and easy to understand.
No comments:
Post a Comment