Translate

Monday, December 31, 2012

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. Winking smile

Here is how I recreate this.

image

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.

image

Friday, December 28, 2012

Wednesday, December 26, 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.

clip_image002

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.

clip_image004

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.

Saturday, December 15, 2012

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.

image

So let me try with two queries,

imageimage

Well, there is no truth what so ever.

Saturday, December 8, 2012

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.

Friday, December 7, 2012

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

image