Too Many VLFs

Transaction Log consists of Virtual Log Files. When transaction log is increase virtual log files are crated according to following table. More info here

Log Size	# of VLFs
64MB and up to 64MB	4
64MB and up to 1GB	8
larger than 1GB	16

Too many VLFs will lead to some problems like, backups will take long time, replication latency will be high. In fact, I have a bad experience, when one of the database has high replication latency and reason for that is high VLFs.

In SQL Server 2012, there is a new feature when the VLFs are more than 10,000 there will be an warning in the error log once the server startup.

During the CTP3 of Denali, this threshold was set to 1,000 but was increased at the RTM. However, Microsoft has forgot to reset the warning message.

Here is how I recreate this.

I verified number of VLFs by DBCC LOGINFO(). I verified that there are no warning when log file reached the 1000 VLFs.

After reaching 10,000 I saw the error message but it says database has  more than 1000 virtual log files.

Top 10 Cloud Stories Of 2012

Here’s a list of the Cloud Computing Exchange’s 10 most-read stories in 2012:

How to measure the percentage of online index rebuild

 

Rebuilding indexes online is time consuming hence many DBAs need to monitor the event. There is a profiler event in SQL Profiler to capture the online index rebuilding.

In SQL Profiler, there is an event Progress Report: Online Index Operation under Progress Report category.

When this profiler is executing results can be obtained. Most of the columns are common like application name, database name spid , login etc. However, there are few uncommon columns which are shown in the below image.

Index ID – index ID from for the table.

Object Name : Index Name

BigIntData1: 0 = serial plan; otherwise, the thread ID during parallel execution.

BigIntData2: Number of rows inserted.

In the above example, non-clustered index BigintData2 in 0 which means index rebuild was done using a serial plan. In that example if you consider the BigintData1, it shows the cumulative rows rebuild. Since this table has 20,000 records last row for BigintData2 shows that number.

If you consider the clustered Index, there are 8 parallel plans have created to rebuild the indexes. If you sum all the max value for each plan, that again adds up to 20,000.

Left Outer Join , What is the Outer Join Table

I was little surprised when I heard the following statement which is,

When you are using Left Outer Join (even in Right Outer Join), Left joined table is defined by the ON condition not by the OUTER JOIN clause.

This means,

SELECT A.Column1, B.Column2

FROM A OUTER JOIN B ON A.ID = B.ID

Will give you every thing from the table A and from table B it gives you what only matches.

If you re-write the query, (You will notice that only change is, ON condition which is switched now)

SELECT A.Column1, B.Column2

FROM A OUTER JOIN B ON B.ID = A.ID

Will give you every thing from the table B and from table A it gives you what only matches.

What do you think?

Let us try it,

This is my sample data.

So let me try with two queries,

Well, there is no truth what so ever.

Using DBCC PAGE in Production Environment

We normally in the understanding that, we should never use undocumented commands in production like DBCC PAGE or DBCC IND etc. Recently, during a training, I got to know that there is no harm of using DBCC PAGE.

Only thing to worry here is, syntax or output or DBCC PAGE can be changed by Microsoft, WITHOUT SUPPORTING backward compatibility.

So it is perfectly safe to use valuable DBCC PAGE command but do not create automated task from DBCC PAGE, Use it for troubleshooting tasks.

Data Type Conversion Chart

This Illustrates all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types.

http://www.microsoft.com/en-us/download/details.aspx?id=35834