Sql-server – Is high index fragmentation an issue on performance

Will this cause any performance issues?

I would start by quoting from Old Books Online for SQL Server 2000 version. Microsoft Books Online (The SQL Server 2000 BOL is now removed by MS so the link is not present. Although its for SQL Server 2000 but gives very accurate definition )

Fragmentation affects disk I/O. Therefore, focus on the larger indexes because their pages are less likely to be cached by SQL Server. Use the page count reported by DBCC SHOWCONTIG to get an idea of the size of the indexes (each page is 8 KB in size). Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages).

Plus and fact being that since index is small pages are most likely to be in memory so no physical I/O would be required to get page and hence fragmentation would not come in picture.

So the small indexes which have page_count <1500 or for that sake < 3000 is hardly going to cause any performance issue, it is quite likely that pages would be in memory and physical I/O would not be required hence fragmentation would actually not matter. If I look at your output only two indexes with page_count 66K+ should really cause some performance issue if SQL Server has to scan index pages. People can argue that page_count of 5000 could also be deciding factor and to which I would say "not really", considering the definition from old books online quoted above. You must also note that the fragmentation we are talking is logical fragmentation where leaf level page order does not matches that of clustered index key.

So performance issue, if any, could only come from large fragmented index not the smaller ones.