INDEXES in ALL angles -1

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!!!!!

Concurrent Administrative Operations..

I want to share about the Concurrent operations at database level i,e in an mind may arise questions like-
A)Can we do Backup and shrink at the same time?
B)Can we add additional Database files when Backup is running?
C)Can we do Full backup database along with the differential Backup?

As we know that during any Backup operation(either full,log or differential) it's not possible to do the add/remove additonal files, shrink, file modify(alter).

You can easily do in your test machine, how ever for Pictorical if you need then Please refer the below links

Link :http://technet.microsoft.com/en-us/library/ms189315%28SQL.90%29.aspx






  

TEMPDB is Not allowing to Shrink to release the Space to OS

We are getting  many alerts for one of the drive (it has user and System db files as well ---the MDF and LDF files shoudn't beon same  drive how ever it was built by some one(will take actions on this) and so my team met was planning to shrink the tempdb files, but it was not allowing him to shrink,he checked all the ways i,e checked

any Open transactions,any SPID is using tempdb,any Jobs are running etc.... nothing worked, even I also tried it
 it was not allowing , so I had 2 options in My mind 1.To restart the SQL server services 2.clear the procedure cache.

1st option I can use but due to weekend there were no business people on duty  and since ours is financila account it is an Lenghty processes , so at present to resolve I went with the Danger option of clearing the Procedure cache.

Note ->IThe DBCC FREEPROCCACHE command clears the procedure cache for all the procedures on the server. hence we need to use very care fully.

Why carefully?

"DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache causes, for example, an ad hoc SQL statement to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachstore in the plan cache, the SQL Server error log will contain the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations." This message is logged every five minutes as long as the cache is flushed within that time interval".

Since I have checked that all user db's are not in big size & also there were no user's activity during that point of time hence I have used   DBCC FREEPROCCACHE then did the shrink operation ->So then it allowed the shrink operation.