MS SQL : How to identify fragmentation in your indexes?

Almost all of us know what fragmentation in SQL indexes are and how it can affect the performance. For those who are new Index fragmentation generally happens when any of the following conditions are met:

The logical order of pages in an index do not match with the physical order present in the data file.

By deletion of existing rows or updating existing values of the indexed columns.

To check if your MS SQL Server contains fragmentation in your indexes, execute the below query.

	SELECT  OBJECT_NAME(ind.OBJECT_ID) AS TableName,ind.name AS IndexName
	,indexstats.index_type_desc AS IndexType,
	indexstats.avg_fragmentation_in_percent,page_count  ,  DB_NAME(database_id)
	FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
	INNER JOIN sys.indexes ind  
	ON ind.object_id = indexstats.object_id
	AND ind.index_id = indexstats.index_id
	WHERE indexstats.avg_fragmentation_in_percent > 30
	ORDER BY indexstats.avg_fragmentation_in_percent DESC

Generally any fragmentation between 10 to 30 percent is considered fine. If the result shows fragmentation more than 30% on indexes than you really need to fix this as it might be main root cause for affecting application performance or slowness.

The Index Fragmentation issue can be solved by,

If the fragmentation levels are low, than you can defragment the indexes.

If the fragmentation levels are high, then ideally you should rebuild the indexes.

Git : Remove cached files from Repository

While working on a GIT repo, many times we face a issue where we have previously included some files in GIT and now we want to remove the same files from GIT for some reason. Also if we add those files to git ignore file but still sometime they are not ignored by GIT.

This happens sometimes due to the cache on our working GIT repo. To solve this issue permanently we can run the below given commands sequentially and step by step in GIT Bash or any other window which supports git commands.

	git rm -r --cached .
	git add .
	git commit -am 'Cleared GIT Cache'

After executing the above commands sequentially and step by step the GIT cache will get clear and the files will get removed from GIT. Do consider taking a backup of source code in case you want to revert again back to original as it was.

XAMPP : Upgrade PHP and MySQL to latest Version

Many of us work on multiple projects and on long term projects also. While working condition may arise to upgrade PHP version for a project on your local development server. We just can't remove our old installation as it would also impact our old projects as old projects might be completely lost in this process.

A better way to handle this is that we might want to upgrade the existing XAMPP version to match the required PHP and MySQL version. This can be done by following the below steps.

1. First of all take a backup of your existing htdocs and data folder (it is a subfolder of MySQL folder in XAMPP).
2. In case you have made some changes in the configuration files like php.ini, httpd.config or any other files, than please take backup of those files also.
3. Download the latest Version of XAMPP from their official website. (https://www.apachefriends.org/download.html)
4. Now, reinstall the downgraded version and replace those folders with the old ones.
5. After this replace the php.ini, httpd.config and other configuration files if you have taken backup.

With the above steps your PHP and MySQL Version will be upgraded to the latest version.