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