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

Is everymorning Healhcheck absolutely necessary??

Somebody recently made me thinking if everyday moning database healthcheck is absolutely necessary??? or should we only react when we get alerts or warnings.....

I think it depends on organisation to organisation. Some organisation these days are talking big about "VISIBILITY" and no doubt we have process like ITIL to accompolish their goals of visibility...

But what exactly is visibility means here??? does it mean there should be something like morning news time where DBA tells all developers and other stakeholders about the databases for last 24 hrs..

Can it be annoying?? or is it something which can be apprecited!!!

I think it all depends on your environment. When things are newly set up and everybody expect somethings coming from every specialist then it all looks good.

My personal view is that it is important regardless if anybody is keen to look at them or not. But again thats personal preference.

But if you want to place a process of distributing daily DBA reports ....better ask your boss and make sure people appreciate :)

or may be just create a space on sharepoint and upload there directly without actually sending email.

What do you say ???? I would love to hear your opinion on everyday DBA report

TempDB and Allocation Page Contention in simple words

We all know how importnt our tempdb is , especially for SQL Server 2005 and onward suits of product.
It is so important to make sure that your tempdb is always in healthy state.

One of the most common issue you may come across when monitoring TEMPDB is "Allocation Page contention".

I am sure this might be a new terminology for those  who do not deal much with SQL Internals. Let me explain this concept in a very simple words and to show what exactly you can look at to make sure your tempdb is not showing this symptom.

When user creates a temporary table in one of his script then what happens!!!. Your temporary table also need to be assign space also!!! right :)

Now SQL Server has mechanism to do so and thats where three concept comes into highlight:

PFS, GAM and SGAM.
So what happens when sql server sees create table #temp.... in your application?

1. It reads the SGAM page and look for free space in a mixed extent(group of 8 pages shared by more than one object)

2. while doing this SQL Server engine will put exclusive latch on SGAM page and will move to PFS page to find the free page for its object.

3. It will then place latch on PFS page as well and this latch will stay there till page is allocated to the table.


So this is way your temporary table get space in database. So now the first  question  what is an issue here?????

The issue is when your tempdb gets overloaded with allocation requests.

Second question is how do you find if there is a contention in allocating page?

The answer is very simple, Use our favourite "sys.dm_os_wait_stats" and look for a PAGELATCH waits on resource 2:1:1.
2:1:1 is a PFS page and too many PAGELATCH on this recource means there is too much allocation page contention.

Finding an issue is all good but you will only get medal if you fix this :) . So the last question is how do you fix allocation page contention?

Have multiple tempdb files
and follow tempdb best practice guide :) (papers are availaible from MS).

cheers
Puneet

Duplicate Indexes......Good or Bad!!!

This is not uncommon for you to come across a large table with lot of indexes. But Have you ever tried to see if there is any duplicate Indexes exists for that table?

For example If I have a table say "Customer" and define one Non clustered Index on FirstName and then I define another index on FirstName and LastName then Firstname appears twice and the first Index can be called redundant and is not required.

Disadvantages of having duplicate index is extra overhead(extra page). So this means any insertion will take more time.

Little things like this get easily un-noticed.

So go today and find out how many duplicate indexes are in your databases :)

Have fun !