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 !

count (*) every where.......

It is not uncommon to come across this keywork in your tsql environment.But do we really know what exactly happens behind the scene when we use select count(*) ......?

One of the main reason I have seen developers using count(*) is to check for data existence. This is very resource intensive operation, why!!!! because count(*)  has to scan all the rows in a table.

So whats the alternative?There is another keyword which you can use and that is "EXISTS". Exists hardly scan everything and stop at the first record that matches.

Such a small thing can improve lot of performance in your system.

you cacn check this yourself

Add SET STATISTICS IO and SET STATISTICS TIME ON statements and compare the output..

You will see logical reads are far less when you use EXISTS comparison to COUNT(*) ...

# Temporary tables and "select into"

This is again one of those myth where developer think that  using select into much faster then insert into  table command.


It is not!!! in fact insert into is faster in  lot of scenarios.

Overall we should avoid using "select into" especially when selecting into temporary tables and also when you are dealing with large resultset.This is because select into locks the system tables.

 

 when you are doing "select into #" temporary tables, we are locking system objects in tempdb database. This can prevent other users from creating temporary tables unless you are done.

 

Imagine if your inserting into temp table some subset from the table which has million rows,the system tables will be locked for the amount of time that insertion is finish...

 

Please use simple "Insert statement" instead of "select into"