RAID 50 for database server!!! Good or Bad idea!

Just in case you wonder if RAID 50 is any good for database server!.

Dell says RAID 50 is great. Well It might be....... but not really for SQL database server. My and Microsoft view is that RAID 10 is a way to go because of excellent read and write performance.

RAID 50 is excellent for read but only “good” for random writes.

B.T.W , RAID 50 is a vendor specific configuration where multiple raid 5 arrays are striped across as well. It is still doing parity and now double the time. So how it can be good for write!!!..Not sure may be dell have done something else as well. In terms of performance it lies between RAID 10 and RAID 5.

 

According to equallogic , RAID Characteristics:

 

Table: RAID Level Characteristic Comparison
Workload Requirement	RAID10	RAID 50	RAID 5	RAID 6
Capacity	Average	Good	Excellent	Good
Availability	Excellent	Good	Average	Excellent
Sequential reads	Excellent	Excellent	Excellent	Excellent
Sequential writes	Good	Good	Good	Good
Random reads	Excellent	Excellent	Excellent	Excellent
Random writes	Excellent	Good	Average	Average
Performance impact of drive failure or RAID reconstruction	Minimal	Moderate	Moderate to heavy	Heavy

Random writes  are good in RAID 50 but excellent in RAID 10.

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.

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 

How and where to find latest and greatest updates for SQL Server

As a DBA, Your prime duty is to make sure SQL Server updates are not out of date.There are many places which can give you the sql server release calender details.

The one I trust is :

http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

This is safe as they also provide the matrix of build number and version number.

Enjoy :)  

Power management and OLTP workload

This article is specially for those who uses OLTP DBMS on windows.

These days power management is very commonly used . Basically when a processor has power management feature enabled then the clock speed of the processor varies with the load.

In windows server 2008 and above,  you can manage the power by using windows power plan.( Go to power option and check the settings)

By default windows uses “Balance power plan option”. This means if your Intel processor(5500, 5600 series..) has turbo boost technology, then it won’t use it.

So for the database servers(on windows), make sure this setting is set to “High performance mode”.

You should also check if there is any hardware power management settings as well, which can override the soft power settings as explained above.

If yes, then ask hardware guy to get into the BIOS system and suggest it to change it to “High performance mode” for your database server.

OLTP workload is of nature where you need a processing very fast. So don't forget to check this option especially if you are using windows server 2008

Is_Auto_Close_On , a killer option

If you are wondering why I called this option as "Killer Option" then here is an answer:

If you turn on this option then what happens is as soon as the last connection to the database is closed, All the resources(CPU,memory,IO) will be freed up and database will be closed.

When new connection is made then SQL Server restarts the database, reassign those resources to the below databases and will also perform checkdb for consistency checks.

This can cause huge performance issue on your production database at day time.

So please check if you have not turned on this option by mistake.

Merge Replication bug in SQL Server 2008 R2

Sometime when I see these kind of fatal errors, I started wondering what the hell QA department is doing at MS?

Last week I encountered this error while viewing conflicts in merge replication:

"GUID is neither a data column nor a data relation for table summary" 

I searched on internet every where about this error but no luck. I searched all great books on replication but again no luck.

Ultimately I decided to approach Microsoft.

And ...Guess what! This reason of this error was publication name.

If you have DOT(.) in your publication name, for example "MyPublication1.2.3" then you wont be able to view and resolve any conflicts.

So Microsoft guy came back to me and told me that only way to resolve is to drop and rename the publication without DOT(.).

Yes again huge outage for my client but the morale of the story is:
Make sure when you test something lke replication in your test lab, You name your publication and other configure other settings exactly same as you plan when move to production.

Puneet