Fragmentation – A culprit often blamed for slow server performance

Shedding more light on a frequent check up called ‘Fragmentation’ whenever we get issues on application slowness or bad server performance-

As the data in Microsoft SQL Server tables changes their indexes change. Over time these indexes become fragmented. This fragmentation will adversely affect performance.

Common questions that arrive in our minds is “How is SQL Server’s fragmentation affecting my Server?”

Fragmentation of SQL Server indexes mainly creates wasted space that can affect your server performance much more than one might expect.

Fragmentation of your SQL Server’s internal allocations and page structures result in ‘gaps’ or ‘void’ space that is dead weight carried along with valid data. Your backups, storage, I/O channels, buffer memory, cached data, logs, tempdb, CPU’s and query plans are impacted by these unnecessary voids.

SQL Server’s fragmentation continually eats away at these resources with nearly every update, delete, insert, and table/index change. If ignored, fragmentation can be the proverbial ‘death by a thousand cuts’ to a server’s performance and scalability.

What creates the voids and other adverse effects and how do I get a handle on them?

Typical, day to day activity causes SQL Servers to fragment over time. Changes to your data – inserts, updates, deletes, and even changing varchar values contribute to fragmentation. The full list of actions that cause fragmentation is long and the rate of fragmentation varies across different indexes and tables.

Sometimes there is a pattern resulting from seasonal or annual peaks (e.g., when financials run).
But more often than not, it is difficult to find, predict and proactively manage fragmentation manually.

Look for the index fragmentation levels by using SQL Server DMV’s and perform rebuild/reorganize operation on indexes.
Scheduling jobs on a timely basis, helps to improve the performance in a slight manner, but simultaneously we should look to fine tune the application queries as well (Are they running with bad execution plans) to serve a long term solution and keep the server performance tuned.

Aman Kharbanda


World of data is changing

The world of data is changing-

Changes in the way we do business are transforming the way we interact with data. Businesses face growth in the volume of information and in the types of data they encounter, from increased transactions to unstructured data, image files, and information from new sources such as sensor networks. We’re being asked new types of questions—as businesses go online, we are interacting through new channels that provide a whole new set of data and a greater urgency to move forward in real time. There is a new scope, scale, and diversity to the types and shapes of information that we now need to process, manage, and analyze for business insight.

The proliferation of data extends beyond traditional data types. According to Gartner, the total volume of data worldwide is growing at a rate of 59 percent per year. Furthermore, Gartner estimates that 70–85 percent of data is unstructured.

The dramatic shift from structured to unstructured and complex data types requires organizations to embrace back-end solutions that support complex data types and non-traditional data sources—such as Big Data.

The way we work, and our expectations about how we work, are changing.
With everything we are able to do on the Web and with the emergence of social media, we are discovering, sharing, and collaborating on information in whole new ways. Business intelligence is no different, in terms of how we access information, collaborate and work with others, and build on the work that other people have done.