Senior DBA Interview Questions

[Database Administrator]

Hi Serge

When a table is rebuilt, every row operation is written into the transaction
log, which generates massive amounts of tlog activity that can easily swamp
a DRP solution.

What's more, the amount of tlog space required to rebuild indexes on a table
can be far more than the size of the table itself (often many times). I was
working with a scenario recently where a customer table had ~400M rows and
was ~50Gb (the table only had a few colums) but the transaction logging
required to rebuild this table was something like 180Gb - many times the
size of the table..

Given that most database disk systems are sized so that tlogging has a
relatively smaller area of space to grow into than the actual database size,
this can easily (& oes often) cause DROP solutions to fail.

You said that you still need to defrag the database - can you explain why?
It is a very commonly held misconception that defragging databases is
necessary to maintain performance. Usually the real problem is that queries
are scanning tables which, if heavily fragmented on disk can degrade
performance but a far more effective way of dealing with this is simply to
index those queries properly.

Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com

"serge" wrote in message
news:FD489BD6-532D-4187-8804-EBEA1CA67461@microsoft.com...
> Thanks Greg/Uri for the answers.
>
>> DRP or report server synchronisations solutions such as Log Shipping,
>> Mirroring and Replication can be brought down by the massive levels of
>> tlogging generated by index rebuilds. This is a very common cause of lost
>> sleep amongst DBAs around the world.
>
> Soon I'll be dealing with what you just describe above. We have a 170GB
> db (we're working on reducing the size) that does replication to another
> SQL 2000 server for reporting purposes. We might start using Log
> Shipping and/or Database Mirroring also.
>
> What could really be the problem here when rebuilding indexes? Like
> what could go wrong? The replication, log shipping, database mirroring
> would fail because a rebuilding index is occuring? Or they would simply
> be out of synch because rebuilding a big table could take dozens of
> minutes and to synchronize all these changes could be GBs of transaction
> log files being generated? If that's what you mean as problems then I can
> see your point. I've experienced problems with Log Shipping with smaller
> sized databases where the time to copy/restore something would not be
> enough when the transaction log file was bigger than usual.
>
> Would this be the problem you mean?
>
> I would still need to at least defrag the database once as it's pretty
> fragmented
> right now; so I am not sure if I should stop the replication completely,
> once done with fully rebuilding all the indexes, set up a new replication
> altogether
> with such a big sized db? It's being replicated on the same subnet so LAN
> connection is the bandwidth so there's no worry for WAN bandwidth usage.
>
> Speaking of fragmentation, the mdf file Windows Analyze from Disk
> Defragmenter
> indicates as the file being fragmented to close to 50,000 pieces! The .mdf
> is
> stored on a Hitachi SAN. Some people say SAN don't have fragmentation
> while
> others say that's non-sense. I suspect it is non-sense and the .mdf is
> really
> terribly fragmented. Is there any system monitor counter I can use to
> demonstrate
> without a doubt that physical file fragmentation is causing a lot of I/O
> stress and
> slowing things down? Is there any DMV that could verify physical file
> fragmentation?
>
> BTW, how does the reindexing work internally?
>
> If I have a 100GB mdf file and a table that is 10GB and has a clustered
> index.
> If I rebuild the indexes on this table, the transaction log file will
> first grow to occupy
> the new sorted table, then SQL Server would search for a contiguous space
> in the mdf
> file that can contain this new table that is in the log file? If it
> doesn't find, it would
> increase the .mdf file at the end, enough space to fit the new table at
> the end, copy
> the data from the log file to mdf and then delete the old table in the mdf
> file?
> Is this how it would work internally?
>
> Thanks again.
>

[Database Administrator]

> 1) indexing can swamp replication and cause issues

Ok, it's very good to know about this. I'll have to keep a closer eye
and think carefully when I will have to rebuild indexes on a table.

> 2) ANY disk can get fragmentation and lots of file fragments are bad for
> performance. I had a client that picked up 18% throughput improvement
> with their sql server databases by defragging their drives.

Great, I can't wait for the day we'll defrag the db at the OS level
and see the result. I'm running system monitor on that SQL Server
so I'll try to see the numbers before and after when that day comes.

> 3) DMVs don't know about the OS-level physical file fragments to my
> knowledge.

> 4) Read Kalen's books on sql internals and query optimizing for
> information about indexing stuff.

I will read Kalen's book.

> 5) biggest thing to remember about index frag/defrag is that if you don't
> have sufficient free space in the database then you are mostly wasting
> your time because it won't get laid down sequentially anyway.

I understand.

A lot of info I'll have to go through.

Thank you Kevin.

[Database Administrator]

Hi Greg,

> When a table is rebuilt, every row operation is written into the
> transaction log, which generates massive amounts of tlog activity that can
> easily swamp a DRP solution.

Now I am starting to understand this better and I'll keep a closer eye
when I'll need to rebuild indexes on a table. I'll have to think more on
this
to decide if/when/why/how I'll have to do it next time.

> What's more, the amount of tlog space required to rebuild indexes on a
> table can be far more than the size of the table itself (often many
> times). I was working with a scenario recently where a customer table had
> ~400M rows and was ~50Gb (the table only had a few colums) but the
> transaction logging required to rebuild this table was something like
> 180Gb - many times the size of the table..

Wow. I don't know why - maybe I might have read it in a book or on a
newsgroup post - but in my head I had the number of needing to have extra
space for 1.2 x the table size. If the table was 10GB, I would need to have
around 12GB of free space for the tlog file to grow. Maybe it was a
real-life
experience watching a tlog file grow during a table reindexing that made me
keep this 1.2 x number in my head.

Now I can see that's not really a rule to follow. If 50GB can almost
quadruple
then I guess there are other factors to consider when rebuilding indexes on
a table. I guess having a lot of extra space would now be thought of before
proceeding with a table reindex.

> Given that most database disk systems are sized so that tlogging has a
> relatively smaller area of space to grow into than the actual database
> size, this can easily (& oes often) cause DROP solutions to fail.

It's so true your statement. I personally never considered having a bigger
disk space for any tlog compared to the actual database size!

Now, I'd have to change my thinking and planning.


> You said that you still need to defrag the database - can you explain why?
> It is a very commonly held misconception that defragging databases is
> necessary to maintain performance. Usually the real problem is that
> queries are scanning tables which, if heavily fragmented on disk can
> degrade performance but a far more effective way of dealing with this is
> simply to index those queries properly.

I understand your point very well and queries scanning tables being the
real problem.

> It is a very commonly held misconception that defragging databases is
> necessary to maintain performance.

So I would say the main reason I wanted to defrag the database is because
with my current knowledge having a defragged database to work with
is better than working with a heavily fragmented database. That's my number
one reason for wanting to defrag the database. I had run the DMV the
sys.dm_db_index_physical_stats() and I had seen a high number of tables
with a fragmentation percentage as high as 80-90%.
I instinctively said to myself "well, let's start by first defragging the
database".

I understand that there are a lot of things I can do to reduce/eliminate
any queries that are scanning tables and so on, but when you consider
that if I would require x number of days/weeks to achieve this, then
what do I lose by first starting to defrag the database and if there are
queries that are not properly optimized and doing table scans then I
have some breathing room by the time I get the time to work on fixing
each non-optimized query.

Up to this thread, for me defragging a database even regularly
(like weekly let's say, not daily) was a necessity to do.
Now along with your points, followed by a search on MSN:
"sql 2005 defrag or not to defrag"

and a quick read of Kalen Delaney's "Get REORGANIZEd" article
from the Jan 2006 edition of SQL Server Magazine, I realize that
there's a lot for me to read, research and understand on this subject.

However, what I can finish my thoughts with are: if the application has
a lot of queries that fetch more than a few records - sometimes pages
of records displayed on the asp page - and we won't be able to
change the logic to change this approach, isn't rebuilding the indexes
including clustered indexes to align the data contiguously reason enough
to do so in this case?

The end user wants to see a summary of all purchases
done this month as an example; this would be a range of data to
fetch and not a few records.

Even if a covering index is being used, wouldn't this index sooner
or later be physically fragmented inside the .mdf file?
If the application inserts/updates/deletes records, would SQL Server
internally choose a "forward" ahead physical page to store the data,
or it could choose to go in front of the index (physically speaking
on the internal of the hard disk) if it finds free space and write it there?
If that happens, isn't this a scenario where a regular rebuilding of
indexes could get the indexes aligned contiguously?

Another reason to separate the Index on its own filegroup and its
own RAID I presume.

Ultimately I can understand that if you take good care of the
database, configure everything properly, you could skip rebuilding
indexes and not have to do it. But, by the time we get to that level
which would involve a lot of changes and a lot of hours to achieve
it, I think some type of regular rebuilding indexes (if not every week,
at least 1-2 times every quarter) could be helpful rather than unhelpful.

I hope I was clear in my explanation as to why I think defragging
the database is currently required for me to do.

Thanks again Greg.

[Database Administrator]

Didn't SQL 6.5 have something about 1.2X size for reindexing?? LONG time
ago that memory though!

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"serge" wrote in message
news:76F24067-2207-4A49-A49E-31C40B1BD676@microsoft.com...
> Hi Greg,
>
>> When a table is rebuilt, every row operation is written into the
>> transaction log, which generates massive amounts of tlog activity that
>> can easily swamp a DRP solution.
>
> Now I am starting to understand this better and I'll keep a closer eye
> when I'll need to rebuild indexes on a table. I'll have to think more on
> this
> to decide if/when/why/how I'll have to do it next time.
>
>> What's more, the amount of tlog space required to rebuild indexes on a
>> table can be far more than the size of the table itself (often many
>> times). I was working with a scenario recently where a customer table had
>> ~400M rows and was ~50Gb (the table only had a few colums) but the
>> transaction logging required to rebuild this table was something like
>> 180Gb - many times the size of the table..
>
> Wow. I don't know why - maybe I might have read it in a book or on a
> newsgroup post - but in my head I had the number of needing to have extra
> space for 1.2 x the table size. If the table was 10GB, I would need to
> have
> around 12GB of free space for the tlog file to grow. Maybe it was a
> real-life
> experience watching a tlog file grow during a table reindexing that made
> me
> keep this 1.2 x number in my head.
>
> Now I can see that's not really a rule to follow. If 50GB can almost
> quadruple
> then I guess there are other factors to consider when rebuilding indexes
> on
> a table. I guess having a lot of extra space would now be thought of
> before
> proceeding with a table reindex.
>
>> Given that most database disk systems are sized so that tlogging has a
>> relatively smaller area of space to grow into than the actual database
>> size, this can easily (& oes often) cause DROP solutions to fail.
>
> It's so true your statement. I personally never considered having a bigger
> disk space for any tlog compared to the actual database size!
>
> Now, I'd have to change my thinking and planning.
>
>
>> You said that you still need to defrag the database - can you explain
>> why? It is a very commonly held misconception that defragging databases
>> is necessary to maintain performance. Usually the real problem is that
>> queries are scanning tables which, if heavily fragmented on disk can
>> degrade performance but a far more effective way of dealing with this is
>> simply to index those queries properly.
>
> I understand your point very well and queries scanning tables being the
> real problem.
>
>> It is a very commonly held misconception that defragging databases is
>> necessary to maintain performance.
>
> So I would say the main reason I wanted to defrag the database is because
> with my current knowledge having a defragged database to work with
> is better than working with a heavily fragmented database. That's my
> number
> one reason for wanting to defrag the database. I had run the DMV the
> sys.dm_db_index_physical_stats() and I had seen a high number of tables
> with a fragmentation percentage as high as 80-90%.
> I instinctively said to myself "well, let's start by first defragging the
> database".
>
> I understand that there are a lot of things I can do to reduce/eliminate
> any queries that are scanning tables and so on, but when you consider
> that if I would require x number of days/weeks to achieve this, then
> what do I lose by first starting to defrag the database and if there are
> queries that are not properly optimized and doing table scans then I
> have some breathing room by the time I get the time to work on fixing
> each non-optimized query.
>
> Up to this thread, for me defragging a database even regularly
> (like weekly let's say, not daily) was a necessity to do.
> Now along with your points, followed by a search on MSN:
> "sql 2005 defrag or not to defrag"
>
> and a quick read of Kalen Delaney's "Get REORGANIZEd" article
> from the Jan 2006 edition of SQL Server Magazine, I realize that
> there's a lot for me to read, research and understand on this subject.
>
> However, what I can finish my thoughts with are: if the application has
> a lot of queries that fetch more than a few records - sometimes pages
> of records displayed on the asp page - and we won't be able to
> change the logic to change this approach, isn't rebuilding the indexes
> including clustered indexes to align the data contiguously reason enough
> to do so in this case?
>
> The end user wants to see a summary of all purchases
> done this month as an example; this would be a range of data to
> fetch and not a few records.
>
> Even if a covering index is being used, wouldn't this index sooner
> or later be physically fragmented inside the .mdf file?
> If the application inserts/updates/deletes records, would SQL Server
> internally choose a "forward" ahead physical page to store the data,
> or it could choose to go in front of the index (physically speaking
> on the internal of the hard disk) if it finds free space and write it
> there?
> If that happens, isn't this a scenario where a regular rebuilding of
> indexes could get the indexes aligned contiguously?
>
> Another reason to separate the Index on its own filegroup and its
> own RAID I presume.
>
> Ultimately I can understand that if you take good care of the
> database, configure everything properly, you could skip rebuilding
> indexes and not have to do it. But, by the time we get to that level
> which would involve a lot of changes and a lot of hours to achieve
> it, I think some type of regular rebuilding indexes (if not every week,
> at least 1-2 times every quarter) could be helpful rather than unhelpful.
>
> I hope I was clear in my explanation as to why I think defragging
> the database is currently required for me to do.
>
> Thanks again Greg.
>
>

[Database Administrator]

I believe it was 1.2X for rebuilding the clustered index. - RLF

"TheSQLGuru" wrote in message
news:13p4lr0et0695db@corp.supernews.com...
> Didn't SQL 6.5 have something about 1.2X size for reindexing?? LONG time
> ago that memory though!
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "serge" wrote in message
> news:76F24067-2207-4A49-A49E-31C40B1BD676@microsoft.com...
>> Hi Greg,
>>
>>> When a table is rebuilt, every row operation is written into the
>>> transaction log, which generates massive amounts of tlog activity that
>>> can easily swamp a DRP solution.
>>
>> Now I am starting to understand this better and I'll keep a closer eye
>> when I'll need to rebuild indexes on a table. I'll have to think more on
>> this
>> to decide if/when/why/how I'll have to do it next time.
>>
>>> What's more, the amount of tlog space required to rebuild indexes on a
>>> table can be far more than the size of the table itself (often many
>>> times). I was working with a scenario recently where a customer table
>>> had ~400M rows and was ~50Gb (the table only had a few colums) but the
>>> transaction logging required to rebuild this table was something like
>>> 180Gb - many times the size of the table..
>>
>> Wow. I don't know why - maybe I might have read it in a book or on a
>> newsgroup post - but in my head I had the number of needing to have extra
>> space for 1.2 x the table size. If the table was 10GB, I would need to
>> have
>> around 12GB of free space for the tlog file to grow. Maybe it was a
>> real-life
>> experience watching a tlog file grow during a table reindexing that made
>> me
>> keep this 1.2 x number in my head.
>>
>> Now I can see that's not really a rule to follow. If 50GB can almost
>> quadruple
>> then I guess there are other factors to consider when rebuilding indexes
>> on
>> a table. I guess having a lot of extra space would now be thought of
>> before
>> proceeding with a table reindex.
>>
>>> Given that most database disk systems are sized so that tlogging has a
>>> relatively smaller area of space to grow into than the actual database
>>> size, this can easily (& oes often) cause DROP solutions to fail.
>>
>> It's so true your statement. I personally never considered having a
>> bigger
>> disk space for any tlog compared to the actual database size!
>>
>> Now, I'd have to change my thinking and planning.
>>
>>
>>> You said that you still need to defrag the database - can you explain
>>> why? It is a very commonly held misconception that defragging databases
>>> is necessary to maintain performance. Usually the real problem is that
>>> queries are scanning tables which, if heavily fragmented on disk can
>>> degrade performance but a far more effective way of dealing with this is
>>> simply to index those queries properly.
>>
>> I understand your point very well and queries scanning tables being the
>> real problem.
>>
>>> It is a very commonly held misconception that defragging databases is
>>> necessary to maintain performance.
>>
>> So I would say the main reason I wanted to defrag the database is because
>> with my current knowledge having a defragged database to work with
>> is better than working with a heavily fragmented database. That's my
>> number
>> one reason for wanting to defrag the database. I had run the DMV the
>> sys.dm_db_index_physical_stats() and I had seen a high number of tables
>> with a fragmentation percentage as high as 80-90%.
>> I instinctively said to myself "well, let's start by first defragging the
>> database".
>>
>> I understand that there are a lot of things I can do to reduce/eliminate
>> any queries that are scanning tables and so on, but when you consider
>> that if I would require x number of days/weeks to achieve this, then
>> what do I lose by first starting to defrag the database and if there are
>> queries that are not properly optimized and doing table scans then I
>> have some breathing room by the time I get the time to work on fixing
>> each non-optimized query.
>>
>> Up to this thread, for me defragging a database even regularly
>> (like weekly let's say, not daily) was a necessity to do.
>> Now along with your points, followed by a search on MSN:
>> "sql 2005 defrag or not to defrag"
>>
>> and a quick read of Kalen Delaney's "Get REORGANIZEd" article
>> from the Jan 2006 edition of SQL Server Magazine, I realize that
>> there's a lot for me to read, research and understand on this subject.
>>
>> However, what I can finish my thoughts with are: if the application has
>> a lot of queries that fetch more than a few records - sometimes pages
>> of records displayed on the asp page - and we won't be able to
>> change the logic to change this approach, isn't rebuilding the indexes
>> including clustered indexes to align the data contiguously reason enough
>> to do so in this case?
>>
>> The end user wants to see a summary of all purchases
>> done this month as an example; this would be a range of data to
>> fetch and not a few records.
>>
>> Even if a covering index is being used, wouldn't this index sooner
>> or later be physically fragmented inside the .mdf file?
>> If the application inserts/updates/deletes records, would SQL Server
>> internally choose a "forward" ahead physical page to store the data,
>> or it could choose to go in front of the index (physically speaking
>> on the internal of the hard disk) if it finds free space and write it
>> there?
>> If that happens, isn't this a scenario where a regular rebuilding of
>> indexes could get the indexes aligned contiguously?
>>
>> Another reason to separate the Index on its own filegroup and its
>> own RAID I presume.
>>
>> Ultimately I can understand that if you take good care of the
>> database, configure everything properly, you could skip rebuilding
>> indexes and not have to do it. But, by the time we get to that level
>> which would involve a lot of changes and a lot of hours to achieve
>> it, I think some type of regular rebuilding indexes (if not every week,
>> at least 1-2 times every quarter) could be helpful rather than unhelpful.
>>
>> I hope I was clear in my explanation as to why I think defragging
>> the database is currently required for me to do.
>>
>> Thanks again Greg.
>>
>>
>
>

[Database Administrator]

Yes, and it isn't past tense. The sort for building an index is done in the
database, plus the new table has to be built from all new pages. So you
needs lots of free space to build/rebuild a clustered index.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com


"Russell Fields" wrote in message
news:exC0KktWIHA.2000@TK2MSFTNGP05.phx.gbl...
>I believe it was 1.2X for rebuilding the clustered index. - RLF
>
> "TheSQLGuru" wrote in message
> news:13p4lr0et0695db@corp.supernews.com...
>> Didn't SQL 6.5 have something about 1.2X size for reindexing?? LONG time
>> ago that memory though!
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>>
>> "serge" wrote in message
>> news:76F24067-2207-4A49-A49E-31C40B1BD676@microsoft.com...
>>> Hi Greg,
>>>
>>>> When a table is rebuilt, every row operation is written into the
>>>> transaction log, which generates massive amounts of tlog activity that
>>>> can easily swamp a DRP solution.
>>>
>>> Now I am starting to understand this better and I'll keep a closer eye
>>> when I'll need to rebuild indexes on a table. I'll have to think more on
>>> this
>>> to decide if/when/why/how I'll have to do it next time.
>>>
>>>> What's more, the amount of tlog space required to rebuild indexes on a
>>>> table can be far more than the size of the table itself (often many
>>>> times). I was working with a scenario recently where a customer table
>>>> had ~400M rows and was ~50Gb (the table only had a few colums) but the
>>>> transaction logging required to rebuild this table was something like
>>>> 180Gb - many times the size of the table..
>>>
>>> Wow. I don't know why - maybe I might have read it in a book or on a
>>> newsgroup post - but in my head I had the number of needing to have
>>> extra
>>> space for 1.2 x the table size. If the table was 10GB, I would need to
>>> have
>>> around 12GB of free space for the tlog file to grow. Maybe it was a
>>> real-life
>>> experience watching a tlog file grow during a table reindexing that made
>>> me
>>> keep this 1.2 x number in my head.
>>>
>>> Now I can see that's not really a rule to follow. If 50GB can almost
>>> quadruple
>>> then I guess there are other factors to consider when rebuilding indexes
>>> on
>>> a table. I guess having a lot of extra space would now be thought of
>>> before
>>> proceeding with a table reindex.
>>>
>>>> Given that most database disk systems are sized so that tlogging has a
>>>> relatively smaller area of space to grow into than the actual database
>>>> size, this can easily (& oes often) cause DROP solutions to fail.
>>>
>>> It's so true your statement. I personally never considered having a
>>> bigger
>>> disk space for any tlog compared to the actual database size!
>>>
>>> Now, I'd have to change my thinking and planning.
>>>
>>>
>>>> You said that you still need to defrag the database - can you explain
>>>> why? It is a very commonly held misconception that defragging databases
>>>> is necessary to maintain performance. Usually the real problem is that
>>>> queries are scanning tables which, if heavily fragmented on disk can
>>>> degrade performance but a far more effective way of dealing with this
>>>> is simply to index those queries properly.
>>>
>>> I understand your point very well and queries scanning tables being the
>>> real problem.
>>>
>>>> It is a very commonly held misconception that defragging databases is
>>>> necessary to maintain performance.
>>>
>>> So I would say the main reason I wanted to defrag the database is
>>> because
>>> with my current knowledge having a defragged database to work with
>>> is better than working with a heavily fragmented database. That's my
>>> number
>>> one reason for wanting to defrag the database. I had run the DMV the
>>> sys.dm_db_index_physical_stats() and I had seen a high number of tables
>>> with a fragmentation percentage as high as 80-90%.
>>> I instinctively said to myself "well, let's start by first defragging
>>> the database".
>>>
>>> I understand that there are a lot of things I can do to reduce/eliminate
>>> any queries that are scanning tables and so on, but when you consider
>>> that if I would require x number of days/weeks to achieve this, then
>>> what do I lose by first starting to defrag the database and if there are
>>> queries that are not properly optimized and doing table scans then I
>>> have some breathing room by the time I get the time to work on fixing
>>> each non-optimized query.
>>>
>>> Up to this thread, for me defragging a database even regularly
>>> (like weekly let's say, not daily) was a necessity to do.
>>> Now along with your points, followed by a search on MSN:
>>> "sql 2005 defrag or not to defrag"
>>>
>>> and a quick read of Kalen Delaney's "Get REORGANIZEd" article
>>> from the Jan 2006 edition of SQL Server Magazine, I realize that
>>> there's a lot for me to read, research and understand on this subject.
>>>
>>> However, what I can finish my thoughts with are: if the application has
>>> a lot of queries that fetch more than a few records - sometimes pages
>>> of records displayed on the asp page - and we won't be able to
>>> change the logic to change this approach, isn't rebuilding the indexes
>>> including clustered indexes to align the data contiguously reason enough
>>> to do so in this case?
>>>
>>> The end user wants to see a summary of all purchases
>>> done this month as an example; this would be a range of data to
>>> fetch and not a few records.
>>>
>>> Even if a covering index is being used, wouldn't this index sooner
>>> or later be physically fragmented inside the .mdf file?
>>> If the application inserts/updates/deletes records, would SQL Server
>>> internally choose a "forward" ahead physical page to store the data,
>>> or it could choose to go in front of the index (physically speaking
>>> on the internal of the hard disk) if it finds free space and write it
>>> there?
>>> If that happens, isn't this a scenario where a regular rebuilding of
>>> indexes could get the indexes aligned contiguously?
>>>
>>> Another reason to separate the Index on its own filegroup and its
>>> own RAID I presume.
>>>
>>> Ultimately I can understand that if you take good care of the
>>> database, configure everything properly, you could skip rebuilding
>>> indexes and not have to do it. But, by the time we get to that level
>>> which would involve a lot of changes and a lot of hours to achieve
>>> it, I think some type of regular rebuilding indexes (if not every week,
>>> at least 1-2 times every quarter) could be helpful rather than
>>> unhelpful.
>>>
>>> I hope I was clear in my explanation as to why I think defragging
>>> the database is currently required for me to do.
>>>
>>> Thanks again Greg.
>>>
>>>
>>
>>
>
>

[Database Administrator]

If you specify to use tempdb for a clustered index rebuild (assuming this
set is compatible), will the sort for that clustered index be done in
tempdb? Just curious because due to the amount of data movement for a
clustered index build this could be an important piece of knowledge.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Kalen Delaney" wrote in message
news:eKcBwLuWIHA.1212@TK2MSFTNGP05.phx.gbl...
> Yes, and it isn't past tense. The sort for building an index is done in
> the database, plus the new table has to be built from all new pages. So
> you needs lots of free space to build/rebuild a clustered index.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
>
> "Russell Fields" wrote in message
> news:exC0KktWIHA.2000@TK2MSFTNGP05.phx.gbl...
>>I believe it was 1.2X for rebuilding the clustered index. - RLF
>>
>> "TheSQLGuru" wrote in message
>> news:13p4lr0et0695db@corp.supernews.com...
>>> Didn't SQL 6.5 have something about 1.2X size for reindexing?? LONG
>>> time ago that memory though!
>>>
>>> --
>>> Kevin G. Boles
>>> Indicium Resources, Inc.
>>> SQL Server MVP
>>> kgboles a earthlink dt net
>>>
>>>
>>> "serge" wrote in message
>>> news:76F24067-2207-4A49-A49E-31C40B1BD676@microsoft.com...
>>>> Hi Greg,
>>>>
>>>>> When a table is rebuilt, every row operation is written into the
>>>>> transaction log, which generates massive amounts of tlog activity that
>>>>> can easily swamp a DRP solution.
>>>>
>>>> Now I am starting to understand this better and I'll keep a closer eye
>>>> when I'll need to rebuild indexes on a table. I'll have to think more
>>>> on this
>>>> to decide if/when/why/how I'll have to do it next time.
>>>>
>>>>> What's more, the amount of tlog space required to rebuild indexes on a
>>>>> table can be far more than the size of the table itself (often many
>>>>> times). I was working with a scenario recently where a customer table
>>>>> had ~400M rows and was ~50Gb (the table only had a few colums) but the
>>>>> transaction logging required to rebuild this table was something like
>>>>> 180Gb - many times the size of the table..
>>>>
>>>> Wow. I don't know why - maybe I might have read it in a book or on a
>>>> newsgroup post - but in my head I had the number of needing to have
>>>> extra
>>>> space for 1.2 x the table size. If the table was 10GB, I would need to
>>>> have
>>>> around 12GB of free space for the tlog file to grow. Maybe it was a
>>>> real-life
>>>> experience watching a tlog file grow during a table reindexing that
>>>> made me
>>>> keep this 1.2 x number in my head.
>>>>
>>>> Now I can see that's not really a rule to follow. If 50GB can almost
>>>> quadruple
>>>> then I guess there are other factors to consider when rebuilding
>>>> indexes on
>>>> a table. I guess having a lot of extra space would now be thought of
>>>> before
>>>> proceeding with a table reindex.
>>>>
>>>>> Given that most database disk systems are sized so that tlogging has a
>>>>> relatively smaller area of space to grow into than the actual database
>>>>> size, this can easily (& oes often) cause DROP solutions to fail.
>>>>
>>>> It's so true your statement. I personally never considered having a
>>>> bigger
>>>> disk space for any tlog compared to the actual database size!
>>>>
>>>> Now, I'd have to change my thinking and planning.
>>>>
>>>>
>>>>> You said that you still need to defrag the database - can you explain
>>>>> why? It is a very commonly held misconception that defragging
>>>>> databases is necessary to maintain performance. Usually the real
>>>>> problem is that queries are scanning tables which, if heavily
>>>>> fragmented on disk can degrade performance but a far more effective
>>>>> way of dealing with this is simply to index those queries properly.
>>>>
>>>> I understand your point very well and queries scanning tables being the
>>>> real problem.
>>>>
>>>>> It is a very commonly held misconception that defragging databases is
>>>>> necessary to maintain performance.
>>>>
>>>> So I would say the main reason I wanted to defrag the database is
>>>> because
>>>> with my current knowledge having a defragged database to work with
>>>> is better than working with a heavily fragmented database. That's my
>>>> number
>>>> one reason for wanting to defrag the database. I had run the DMV the
>>>> sys.dm_db_index_physical_stats() and I had seen a high number of tables
>>>> with a fragmentation percentage as high as 80-90%.
>>>> I instinctively said to myself "well, let's start by first defragging
>>>> the database".
>>>>
>>>> I understand that there are a lot of things I can do to
>>>> reduce/eliminate
>>>> any queries that are scanning tables and so on, but when you consider
>>>> that if I would require x number of days/weeks to achieve this, then
>>>> what do I lose by first starting to defrag the database and if there
>>>> are
>>>> queries that are not properly optimized and doing table scans then I
>>>> have some breathing room by the time I get the time to work on fixing
>>>> each non-optimized query.
>>>>
>>>> Up to this thread, for me defragging a database even regularly
>>>> (like weekly let's say, not daily) was a necessity to do.
>>>> Now along with your points, followed by a search on MSN:
>>>> "sql 2005 defrag or not to defrag"
>>>>
>>>> and a quick read of Kalen Delaney's "Get REORGANIZEd" article
>>>> from the Jan 2006 edition of SQL Server Magazine, I realize that
>>>> there's a lot for me to read, research and understand on this subject.
>>>>
>>>> However, what I can finish my thoughts with are: if the application has
>>>> a lot of queries that fetch more than a few records - sometimes pages
>>>> of records displayed on the asp page - and we won't be able to
>>>> change the logic to change this approach, isn't rebuilding the indexes
>>>> including clustered indexes to align the data contiguously reason
>>>> enough
>>>> to do so in this case?
>>>>
>>>> The end user wants to see a summary of all purchases
>>>> done this month as an example; this would be a range of data to
>>>> fetch and not a few records.
>>>>
>>>> Even if a covering index is being used, wouldn't this index sooner
>>>> or later be physically fragmented inside the .mdf file?
>>>> If the application inserts/updates/deletes records, would SQL Server
>>>> internally choose a "forward" ahead physical page to store the data,
>>>> or it could choose to go in front of the index (physically speaking
>>>> on the internal of the hard disk) if it finds free space and write it
>>>> there?
>>>> If that happens, isn't this a scenario where a regular rebuilding of
>>>> indexes could get the indexes aligned contiguously?
>>>>
>>>> Another reason to separate the Index on its own filegroup and its
>>>> own RAID I presume.
>>>>
>>>> Ultimately I can understand that if you take good care of the
>>>> database, configure everything properly, you could skip rebuilding
>>>> indexes and not have to do it. But, by the time we get to that level
>>>> which would involve a lot of changes and a lot of hours to achieve
>>>> it, I think some type of regular rebuilding indexes (if not every week,
>>>> at least 1-2 times every quarter) could be helpful rather than
>>>> unhelpful.
>>>>
>>>> I hope I was clear in my explanation as to why I think defragging
>>>> the database is currently required for me to do.
>>>>
>>>> Thanks again Greg.
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[Database Administrator]

Kevin
I assume you are talking about using the SORT_IN_TEMPDB option , so the
tempdb database is used for temporary storage space.


"TheSQLGuru" wrote in message
news:13p59vu9qabk09b@corp.supernews.com...
> If you specify to use tempdb for a clustered index rebuild (assuming this
> set is compatible), will the sort for that clustered index be done in
> tempdb? Just curious because due to the amount of data movement for a
> clustered index build this could be an important piece of knowledge.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Kalen Delaney" wrote in message
> news:eKcBwLuWIHA.1212@TK2MSFTNGP05.phx.gbl...
>> Yes, and it isn't past tense. The sort for building an index is done in
>> the database, plus the new table has to be built from all new pages. So
>> you needs lots of free space to build/rebuild a clustered index.
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>>
>> "Russell Fields" wrote in message
>> news:exC0KktWIHA.2000@TK2MSFTNGP05.phx.gbl...
>>>I believe it was 1.2X for rebuilding the clustered index. - RLF
>>>
>>> "TheSQLGuru" wrote in message
>>> news:13p4lr0et0695db@corp.supernews.com...
>>>> Didn't SQL 6.5 have something about 1.2X size for reindexing?? LONG
>>>> time ago that memory though!
>>>>
>>>> --
>>>> Kevin G. Boles
>>>> Indicium Resources, Inc.
>>>> SQL Server MVP
>>>> kgboles a earthlink dt net
>>>>
>>>>
>>>> "serge" wrote in message
>>>> news:76F24067-2207-4A49-A49E-31C40B1BD676@microsoft.com...
>>>>> Hi Greg,
>>>>>
>>>>>> When a table is rebuilt, every row operation is written into the
>>>>>> transaction log, which generates massive amounts of tlog activity
>>>>>> that can easily swamp a DRP solution.
>>>>>
>>>>> Now I am starting to understand this better and I'll keep a closer eye
>>>>> when I'll need to rebuild indexes on a table. I'll have to think more
>>>>> on this
>>>>> to decide if/when/why/how I'll have to do it next time.
>>>>>
>>>>>> What's more, the amount of tlog space required to rebuild indexes on
>>>>>> a table can be far more than the size of the table itself (often many
>>>>>> times). I was working with a scenario recently where a customer table
>>>>>> had ~400M rows and was ~50Gb (the table only had a few colums) but
>>>>>> the transaction logging required to rebuild this table was something
>>>>>> like 180Gb - many times the size of the table..
>>>>>
>>>>> Wow. I don't know why - maybe I might have read it in a book or on a
>>>>> newsgroup post - but in my head I had the number of needing to have
>>>>> extra
>>>>> space for 1.2 x the table size. If the table was 10GB, I would need to
>>>>> have
>>>>> around 12GB of free space for the tlog file to grow. Maybe it was a
>>>>> real-life
>>>>> experience watching a tlog file grow during a table reindexing that
>>>>> made me
>>>>> keep this 1.2 x number in my head.
>>>>>
>>>>> Now I can see that's not really a rule to follow. If 50GB can almost
>>>>> quadruple
>>>>> then I guess there are other factors to consider when rebuilding
>>>>> indexes on
>>>>> a table. I guess having a lot of extra space would now be thought of
>>>>> before
>>>>> proceeding with a table reindex.
>>>>>
>>>>>> Given that most database disk systems are sized so that tlogging has
>>>>>> a relatively smaller area of space to grow into than the actual
>>>>>> database size, this can easily (& oes often) cause DROP solutions to
>>>>>> fail.
>>>>>
>>>>> It's so true your statement. I personally never considered having a
>>>>> bigger
>>>>> disk space for any tlog compared to the actual database size!
>>>>>
>>>>> Now, I'd have to change my thinking and planning.
>>>>>
>>>>>
>>>>>> You said that you still need to defrag the database - can you explain
>>>>>> why? It is a very commonly held misconception that defragging
>>>>>> databases is necessary to maintain performance. Usually the real
>>>>>> problem is that queries are scanning tables which, if heavily
>>>>>> fragmented on disk can degrade performance but a far more effective
>>>>>> way of dealing with this is simply to index those queries properly.
>>>>>
>>>>> I understand your point very well and queries scanning tables being
>>>>> the
>>>>> real problem.
>>>>>
>>>>>> It is a very commonly held misconception that defragging databases is
>>>>>> necessary to maintain performance.
>>>>>
>>>>> So I would say the main reason I wanted to defrag the database is
>>>>> because
>>>>> with my current knowledge having a defragged database to work with
>>>>> is better than working with a heavily fragmented database. That's my
>>>>> number
>>>>> one reason for wanting to defrag the database. I had run the DMV the
>>>>> sys.dm_db_index_physical_stats() and I had seen a high number of
>>>>> tables
>>>>> with a fragmentation percentage as high as 80-90%.
>>>>> I instinctively said to myself "well, let's start by first defragging
>>>>> the database".
>>>>>
>>>>> I understand that there are a lot of things I can do to
>>>>> reduce/eliminate
>>>>> any queries that are scanning tables and so on, but when you consider
>>>>> that if I would require x number of days/weeks to achieve this, then
>>>>> what do I lose by first starting to defrag the database and if there
>>>>> are
>>>>> queries that are not properly optimized and doing table scans then I
>>>>> have some breathing room by the time I get the time to work on fixing
>>>>> each non-optimized query.
>>>>>
>>>>> Up to this thread, for me defragging a database even regularly
>>>>> (like weekly let's say, not daily) was a necessity to do.
>>>>> Now along with your points, followed by a search on MSN:
>>>>> "sql 2005 defrag or not to defrag"
>>>>>
>>>>> and a quick read of Kalen Delaney's "Get REORGANIZEd" article
>>>>> from the Jan 2006 edition of SQL Server Magazine, I realize that
>>>>> there's a lot for me to read, research and understand on this subject.
>>>>>
>>>>> However, what I can finish my thoughts with are: if the application
>>>>> has
>>>>> a lot of queries that fetch more than a few records - sometimes pages
>>>>> of records displayed on the asp page - and we won't be able to
>>>>> change the logic to change this approach, isn't rebuilding the indexes
>>>>> including clustered indexes to align the data contiguously reason
>>>>> enough
>>>>> to do so in this case?
>>>>>
>>>>> The end user wants to see a summary of all purchases
>>>>> done this month as an example; this would be a range of data to
>>>>> fetch and not a few records.
>>>>>
>>>>> Even if a covering index is being used, wouldn't this index sooner
>>>>> or later be physically fragmented inside the .mdf file?
>>>>> If the application inserts/updates/deletes records, would SQL Server
>>>>> internally choose a "forward" ahead physical page to store the data,
>>>>> or it could choose to go in front of the index (physically speaking
>>>>> on the internal of the hard disk) if it finds free space and write it
>>>>> there?
>>>>> If that happens, isn't this a scenario where a regular rebuilding of
>>>>> indexes could get the indexes aligned contiguously?
>>>>>
>>>>> Another reason to separate the Index on its own filegroup and its
>>>>> own RAID I presume.
>>>>>
>>>>> Ultimately I can understand that if you take good care of the
>>>>> database, configure everything properly, you could skip rebuilding
>>>>> indexes and not have to do it. But, by the time we get to that level
>>>>> which would involve a lot of changes and a lot of hours to achieve
>>>>> it, I think some type of regular rebuilding indexes (if not every
>>>>> week,
>>>>> at least 1-2 times every quarter) could be helpful rather than
>>>>> unhelpful.
>>>>>
>>>>> I hope I was clear in my explanation as to why I think defragging
>>>>> the database is currently required for me to do.
>>>>>
>>>>> Thanks again Greg.
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[Database Administrator]

Yes. I didn't know if it really works for clustered indexes or if it is
recommended due to the volume of data that could be encountered in adjusting
the entire table's contents as opposed to a few columns for a non-clustered.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Uri Dimant" wrote in message
news:OsM6OdzWIHA.4808@TK2MSFTNGP05.phx.gbl...
> Kevin
> I assume you are talking about using the SORT_IN_TEMPDB option , so the
> tempdb database is used for temporary storage space.
>
>
> "TheSQLGuru" wrote in message
> news:13p59vu9qabk09b@corp.supernews.com...
>> If you specify to use tempdb for a clustered index rebuild (assuming this
>> set is compatible), will the sort for that clustered index be done in
>> tempdb? Just curious because due to the amount of data movement for a
>> clustered index build this could be an important piece of knowledge.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>>
>> "Kalen Delaney" wrote in message
>> news:eKcBwLuWIHA.1212@TK2MSFTNGP05.phx.gbl...
>>> Yes, and it isn't past tense. The sort for building an index is done in
>>> the database, plus the new table has to be built from all new pages. So
>>> you needs lots of free space to build/rebuild a clustered index.
>>>
>>> --
>>> HTH
>>> Kalen Delaney, SQL Server MVP
>>> www.InsideSQLServer.com
>>> http://blog.kalendelaney.com
>>>
>>>
>>> "Russell Fields" wrote in message
>>> news:exC0KktWIHA.2000@TK2MSFTNGP05.phx.gbl...
>>>>I believe it was 1.2X for rebuilding the clustered index. - RLF
>>>>
>>>> "TheSQLGuru" wrote in message
>>>> news:13p4lr0et0695db@corp.supernews.com...
>>>>> Didn't SQL 6.5 have something about 1.2X size for reindexing?? LONG
>>>>> time ago that memory though!
>>>>>
>>>>> --
>>>>> Kevin G. Boles
>>>>> Indicium Resources, Inc.
>>>>> SQL Server MVP
>>>>> kgboles a earthlink dt net
>>>>>
>>>>>
>>>>> "serge" wrote in message
>>>>> news:76F24067-2207-4A49-A49E-31C40B1BD676@microsoft.com...
>>>>>> Hi Greg,
>>>>>>
>>>>>>> When a table is rebuilt, every row operation is written into the
>>>>>>> transaction log, which generates massive amounts of tlog activity
>>>>>>> that can easily swamp a DRP solution.
>>>>>>
>>>>>> Now I am starting to understand this better and I'll keep a closer
>>>>>> eye
>>>>>> when I'll need to rebuild indexes on a table. I'll have to think more
>>>>>> on this
>>>>>> to decide if/when/why/how I'll have to do it next time.
>>>>>>
>>>>>>> What's more, the amount of tlog space required to rebuild indexes on
>>>>>>> a table can be far more than the size of the table itself (often
>>>>>>> many times). I was working with a scenario recently where a customer
>>>>>>> table had ~400M rows and was ~50Gb (the table only had a few colums)
>>>>>>> but the transaction logging required to rebuild this table was
>>>>>>> something like 180Gb - many times the size of the table..
>>>>>>
>>>>>> Wow. I don't know why - maybe I might have read it in a book or on a
>>>>>> newsgroup post - but in my head I had the number of needing to have
>>>>>> extra
>>>>>> space for 1.2 x the table size. If the table was 10GB, I would need
>>>>>> to have
>>>>>> around 12GB of free space for the tlog file to grow. Maybe it was a
>>>>>> real-life
>>>>>> experience watching a tlog file grow during a table reindexing that
>>>>>> made me
>>>>>> keep this 1.2 x number in my head.
>>>>>>
>>>>>> Now I can see that's not really a rule to follow. If 50GB can almost
>>>>>> quadruple
>>>>>> then I guess there are other factors to consider when rebuilding
>>>>>> indexes on
>>>>>> a table. I guess having a lot of extra space would now be thought of
>>>>>> before
>>>>>> proceeding with a table reindex.
>>>>>>
>>>>>>> Given that most database disk systems are sized so that tlogging has
>>>>>>> a relatively smaller area of space to grow into than the actual
>>>>>>> database size, this can easily (& oes often) cause DROP solutions to
>>>>>>> fail.
>>>>>>
>>>>>> It's so true your statement. I personally never considered having a
>>>>>> bigger
>>>>>> disk space for any tlog compared to the actual database size!
>>>>>>
>>>>>> Now, I'd have to change my thinking and planning.
>>>>>>
>>>>>>
>>>>>>> You said that you still need to defrag the database - can you
>>>>>>> explain why? It is a very commonly held misconception that
>>>>>>> defragging databases is necessary to maintain performance. Usually
>>>>>>> the real problem is that queries are scanning tables which, if
>>>>>>> heavily fragmented on disk can degrade performance but a far more
>>>>>>> effective way of dealing with this is simply to index those queries
>>>>>>> properly.
>>>>>>
>>>>>> I understand your point very well and queries scanning tables being
>>>>>> the
>>>>>> real problem.
>>>>>>
>>>>>>> It is a very commonly held misconception that defragging databases
>>>>>>> is necessary to maintain performance.
>>>>>>
>>>>>> So I would say the main reason I wanted to defrag the database is
>>>>>> because
>>>>>> with my current knowledge having a defragged database to work with
>>>>>> is better than working with a heavily fragmented database. That's my
>>>>>> number
>>>>>> one reason for wanting to defrag the database. I had run the DMV the
>>>>>> sys.dm_db_index_physical_stats() and I had seen a high number of
>>>>>> tables
>>>>>> with a fragmentation percentage as high as 80-90%.
>>>>>> I instinctively said to myself "well, let's start by first defragging
>>>>>> the database".
>>>>>>
>>>>>> I understand that there are a lot of things I can do to
>>>>>> reduce/eliminate
>>>>>> any queries that are scanning tables and so on, but when you consider
>>>>>> that if I would require x number of days/weeks to achieve this, then
>>>>>> what do I lose by first starting to defrag the database and if there
>>>>>> are
>>>>>> queries that are not properly optimized and doing table scans then I
>>>>>> have some breathing room by the time I get the time to work on fixing
>>>>>> each non-optimized query.
>>>>>>
>>>>>> Up to this thread, for me defragging a database even regularly
>>>>>> (like weekly let's say, not daily) was a necessity to do.
>>>>>> Now along with your points, followed by a search on MSN:
>>>>>> "sql 2005 defrag or not to defrag"
>>>>>>
>>>>>> and a quick read of Kalen Delaney's "Get REORGANIZEd" article
>>>>>> from the Jan 2006 edition of SQL Server Magazine, I realize that
>>>>>> there's a lot for me to read, research and understand on this
>>>>>> subject.
>>>>>>
>>>>>> However, what I can finish my thoughts with are: if the application
>>>>>> has
>>>>>> a lot of queries that fetch more than a few records - sometimes pages
>>>>>> of records displayed on the asp page - and we won't be able to
>>>>>> change the logic to change this approach, isn't rebuilding the
>>>>>> indexes
>>>>>> including clustered indexes to align the data contiguously reason
>>>>>> enough
>>>>>> to do so in this case?
>>>>>>
>>>>>> The end user wants to see a summary of all purchases
>>>>>> done this month as an example; this would be a range of data to
>>>>>> fetch and not a few records.
>>>>>>
>>>>>> Even if a covering index is being used, wouldn't this index sooner
>>>>>> or later be physically fragmented inside the .mdf file?
>>>>>> If the application inserts/updates/deletes records, would SQL Server
>>>>>> internally choose a "forward" ahead physical page to store the data,
>>>>>> or it could choose to go in front of the index (physically speaking
>>>>>> on the internal of the hard disk) if it finds free space and write it
>>>>>> there?
>>>>>> If that happens, isn't this a scenario where a regular rebuilding of
>>>>>> indexes could get the indexes aligned contiguously?
>>>>>>
>>>>>> Another reason to separate the Index on its own filegroup and its
>>>>>> own RAID I presume.
>>>>>>
>>>>>> Ultimately I can understand that if you take good care of the
>>>>>> database, configure everything properly, you could skip rebuilding
>>>>>> indexes and not have to do it. But, by the time we get to that level
>>>>>> which would involve a lot of changes and a lot of hours to achieve
>>>>>> it, I think some type of regular rebuilding indexes (if not every
>>>>>> week,
>>>>>> at least 1-2 times every quarter) could be helpful rather than
>>>>>> unhelpful.
>>>>>>
>>>>>> I hope I was clear in my explanation as to why I think defragging
>>>>>> the database is currently required for me to do.
>>>>>>
>>>>>> Thanks again Greg.
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

[Database Administrator]

I noticed a very huge set of DBA interview questions here at :-
http://www.kanbal.com/index.php?/Dat...ons-set-2.html

I cleared my interview based on these sets. there seem to a around 7 to 8 sents of such questions and answers. Happy learning.