Monday, September 12, 2011

Why I say leverage SQL Server data and backup compression in 2008 R2??

If we all  look back, one thing you all will surely notice that CPU power has increased and its getting better and better every year.

If you compare the growth in technology with other components like disk speed ,memory, networking components etc etc, certainly CPU always stands out.

When we talk about SQL Server performance, its not stopped at disk speed, throughput and other individual components but is a mix of everything.

In SQL Server 2008 r2 ,compression is one great way to take away the load from the disk and memory perspective. Its simple, data and backup compression means less space and more data in cache. There is a little trade off in terms of processing the compression but now you have great processing power by intel series 2 sockets processors.

So why not go ahead and try it by yourself and see how much benefit you can get by exploring this feature.

I wont suggest you to go and blindly start compressing everything : ) :) , but start calculating your workload and focus on tables and indexes which are IO intensive and analyze if those objects can be compressed and give you more benefits in terms of IO and memory.

Tuesday, September 6, 2011

Thickness of arrow between operators in execution plans

In SQL Server if you have ever analysed the plans (execution plans) using SSMS plan analysis(I am sure you have :) ), then one thing you might have noticed is thickness of arrow between the two operators.

Thickness of arrow has been used by many DBA's as an estimation of size of data.

To correct the myth/confusion, this thickness of arrow just represents the row count and not actually the size of the data.

This means you can see a thin arrow representing 4 rows, but behind that it could be xml data of  GB size

so do not just rely on thickness of arrow before picking expensive operator and expensive flow of data between two operators.





enjoy