I want to share something today, one of my friends asked me the question about Index-the one which he asked me is that what happens if I disabled the clustered Indexes on table or view?
Start from beginning I was try to understand about the indexes --It was difficult to understand(I believe for the experience people as well).
But Iam much ok & got an good Idea on the Indexes.
So Iam starting this topic INDEXES in ALL angles -1 as an FAQ's for those who is preparing for the Interview (I will answers all questions shortly-sure If I get time).
In then Next year i,e next Month of Jan,Iam taking session on In and Out of Indexes in the office.(may be January 4th week).
Ok Lets start the Journey on Indexes.
1.What is Index and why it is required in RDBMS, How query optimizer will take advantage of good index choose by smart Developer or Vendor?Ofcourse DBA!
2.How Many types of indexes in SQL server 2000/2005/2008/R2/Denali/2012 & what are those?
3.What is B-tree structure and how you can identify how many b-structures you have & what are the levels it has(levels are like -Root node,Intermediate and Leaf level nodes)?
4.What are the major's differences B/w clustered and Non-clustered indexes?
5.What happens if I disable the clustered index on an table & what about the Non-clustered indexes?
6.Is there any way can I enable the clustered indexes, if yes how it is?
6.How query optimizer decides whether it has to go with Index scan or entire table scans?
7.Is that good option keeping too many indexes , because you know that the index is an good option for retrieving the data efficiently for the users as well as for the SQL server database engine (bcz Good index can
reduces the larger Physical I/O)?
8.Can always root node exists?
9.Will always Index get benefits for the users & database engine?
10.what is the meaning of Index page & data pages?
11.What is Keys? & How keys behaves in RDBMS?
12.Can I create an non-clustered index if the clustered index disabled?
13.What happens to the Non-clustered index,if I drop the clustered index?
14.Can I rename the Index, if so how & what is the causes if I rename the index?
15.How to: Move an Existing Index to a Different Filegroup?
16.How data will retrieve if no indexes exists in sql server?
17.why it is Index Sort Order?
Fragmentation's:
============
1.What is fragmentation in SQL server?
2.How many types of fragmentation's and why the fragmentation occurs?
3.What is the disadvantages of Fragmentation in SQL server?
4.You may have seen that after rebuilding the indexes still shows the fragmentation is same or High percentage, why is it so?
5.what is fill factor and how I can decide the proper fill factor?
6.When to Rebuild/Re-organize?
7.Can heavy Fragmentation dist rubes the business like timeout errors, takes more time to get the reports during urgent time?
8.How can I rebuild the indexes and how many modes we can rebuild the indexes.
9.What is the difference between Onilne rebuild /Off line rebuild index?
10.How internally works the rebuild index(you know that the rebuild indexes flows in three ways)?
11.What factors I need to consider before rebuilding the Indexes like as mentioned below -
A)How can we estimate the log file size before rebuilding the index?
B)what about the Filegroup size?
C)Proper tempdb space (if you have used the option sort_tempdb =on)?
D)how to decide the you need to rebuild online or offline?
E)Best practices?
12.what are all the Index Options available & how we will get benifits?
13.Why fill-factor , how page splitting occurs & how we can reduce?
14.What types of locks puts during the rebuild indexes(i,e here consider the 3phases)?
15.How to rebuild the indexes on single index or on entire tables?
16. My OS disk got fragmented then what is the Impact on the Indexes?
17.Whar Recovery Model I need to select for Index Operations and how it behaves?
----I will post some more FAQ's-------------
Bottom Line:
========
1.In SQL server 2008 what ever may be the recovery model the Index operation is full logged.
2.A)Online index operations are available only in SQL Server 2005 Enterprise Edition.
B)Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions --->Denali and SQL server 2008/R2
3..Index-Related Dynamic Management Views and Functions --in SQL server 2005/2008/R2 and Denali
sys.dm_db_index_operational_stats
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats
sys.dm_db_missing_index_columns
sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
4.In SQL server 2005 ->Number of clustered index is 1 and remaining 2 to 249 Non-clustered indexes ==>Similar in 32 bit & 64 Bit .
In SQL server 2008/R2/Denali ->Number of clustered index is 1 and remaining 2 to 999 Non-clustered indexes ==>Similar in 32 bit & 64 Bit .
All the Best Guys!!!!!
Start from beginning I was try to understand about the indexes --It was difficult to understand(I believe for the experience people as well).
But Iam much ok & got an good Idea on the Indexes.
So Iam starting this topic INDEXES in ALL angles -1 as an FAQ's for those who is preparing for the Interview (I will answers all questions shortly-sure If I get time).
In then Next year i,e next Month of Jan,Iam taking session on In and Out of Indexes in the office.(may be January 4th week).
Ok Lets start the Journey on Indexes.
1.What is Index and why it is required in RDBMS, How query optimizer will take advantage of good index choose by smart Developer or Vendor?Ofcourse DBA!
2.How Many types of indexes in SQL server 2000/2005/2008/R2/Denali/2012 & what are those?
3.What is B-tree structure and how you can identify how many b-structures you have & what are the levels it has(levels are like -Root node,Intermediate and Leaf level nodes)?
4.What are the major's differences B/w clustered and Non-clustered indexes?
5.What happens if I disable the clustered index on an table & what about the Non-clustered indexes?
6.Is there any way can I enable the clustered indexes, if yes how it is?
6.How query optimizer decides whether it has to go with Index scan or entire table scans?
7.Is that good option keeping too many indexes , because you know that the index is an good option for retrieving the data efficiently for the users as well as for the SQL server database engine (bcz Good index can
reduces the larger Physical I/O)?
8.Can always root node exists?
9.Will always Index get benefits for the users & database engine?
10.what is the meaning of Index page & data pages?
11.What is Keys? & How keys behaves in RDBMS?
12.Can I create an non-clustered index if the clustered index disabled?
13.What happens to the Non-clustered index,if I drop the clustered index?
14.Can I rename the Index, if so how & what is the causes if I rename the index?
15.How to: Move an Existing Index to a Different Filegroup?
16.How data will retrieve if no indexes exists in sql server?
17.why it is Index Sort Order?
Fragmentation's:
============
1.What is fragmentation in SQL server?
2.How many types of fragmentation's and why the fragmentation occurs?
3.What is the disadvantages of Fragmentation in SQL server?
4.You may have seen that after rebuilding the indexes still shows the fragmentation is same or High percentage, why is it so?
5.what is fill factor and how I can decide the proper fill factor?
6.When to Rebuild/Re-organize?
7.Can heavy Fragmentation dist rubes the business like timeout errors, takes more time to get the reports during urgent time?
8.How can I rebuild the indexes and how many modes we can rebuild the indexes.
9.What is the difference between Onilne rebuild /Off line rebuild index?
10.How internally works the rebuild index(you know that the rebuild indexes flows in three ways)?
11.What factors I need to consider before rebuilding the Indexes like as mentioned below -
A)How can we estimate the log file size before rebuilding the index?
B)what about the Filegroup size?
C)Proper tempdb space (if you have used the option sort_tempdb =on)?
D)how to decide the you need to rebuild online or offline?
E)Best practices?
12.what are all the Index Options available & how we will get benifits?
13.Why fill-factor , how page splitting occurs & how we can reduce?
14.What types of locks puts during the rebuild indexes(i,e here consider the 3phases)?
15.How to rebuild the indexes on single index or on entire tables?
16. My OS disk got fragmented then what is the Impact on the Indexes?
17.Whar Recovery Model I need to select for Index Operations and how it behaves?
----I will post some more FAQ's-------------
Bottom Line:
========
1.In SQL server 2008 what ever may be the recovery model the Index operation is full logged.
2.A)Online index operations are available only in SQL Server 2005 Enterprise Edition.
B)Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions --->Denali and SQL server 2008/R2
3..Index-Related Dynamic Management Views and Functions --in SQL server 2005/2008/R2 and Denali
sys.dm_db_index_operational_stats
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats
sys.dm_db_missing_index_columns
sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
4.In SQL server 2005 ->Number of clustered index is 1 and remaining 2 to 249 Non-clustered indexes ==>Similar in 32 bit & 64 Bit .
In SQL server 2008/R2/Denali ->Number of clustered index is 1 and remaining 2 to 999 Non-clustered indexes ==>Similar in 32 bit & 64 Bit .
All the Best Guys!!!!!
No comments:
Post a Comment