INDEX LAST REBUILT TIME

Hi folks,
Is there a way to find out when was the last time an index was rebuilt?
Is this information is available in any system tables?
Thx,
RamThat information is not stored.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A6D3D5D4-4678-4197-AEF6-2F0952E8DD43@.microsoft.com...
> Hi folks,
> Is there a way to find out when was the last time an index was rebuilt?
> Is this information is available in any system tables?
> Thx,
> Ram|||Heh heh, when you issue the DBCC command, write it down in your notebook.
:-)
SQL Server does not track this information. If you wish to track this
information, you'll have to do it yourself. For example, you could have a
stored procedure that issues the DBCC command(s) and logs to a table.
A
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A6D3D5D4-4678-4197-AEF6-2F0952E8DD43@.microsoft.com...
> Hi folks,
> Is there a way to find out when was the last time an index was rebuilt?
> Is this information is available in any system tables?
> Thx,
> Ram|||Why? If you're trying to schedule regular index rebuilds, do this by
examining actual fragmentation, rather than estimating the amout of time tha
t
should elapse between each defragmentation (rebuild).
See "DBCC SHOWCONTIG" in Books Online.
ML

Index granularity

Hi,

I was looking at the new index locking granularity option available in 2005. I did not understand in what case can this be a performance enhancement. Has anybody looked into this ?

Are you talking about sp_indexoption? It is an advanced tuning option and useful in cases where you understand your query pattern / data completely. You do not need to use it normally.|||

Can you post any examples of possible usage? I was seriously considering setting table granularity locks on my data warehouse type tables, especially during the day, when all access is wholly write during ETL, or wholly read during cube build.

Using a read only database is not really convienient, but this should give some of the same properties, right?

|||

Hi,

I am not talking about the sp_indexoption. I am asking about the new index locking granularity in sql server 2005. You have an option to either lock or unlock the index row or the index page. I did a few tests. I started one transaction(session 1) with isolation level as serializable. and executed a select statement.(for this session i set the index locks to off basically meaning that the index locking for row and page is not allowed) In another session(session 2) i tried to execute an update. It did not happen till i committed my query in session 1. This means that though you have requested not to lock the indexes, still the rules of the isolation level prevail. I did not check if table level locks were used. It might happen that when don't allow the row adn page level locks(setting the option to off) might be the locks are escalated to the table level. I am not able to make out as to for what circumstances this feature can be useful ?

Thanks

Sapna

|||Please post some sample code demonstrating the issues. The index options you talked about can be set via CREATE / ALTER INDEX also in SQL Server 2005. This is the only new change otherwise it was available in SQL Server 2000 too via sp_indexoption.|||

There is no issue as such. I just want to know when this option when set ON, can be of any advantage. And what is the situation when this can be a performance issue.

Thanks

Sapna

index fillfactor

How can you identify the current space available in a clustered index? I'm
aware of the commands to rebuild the index, but I'd like to be able to
monitor how "full" an index is relative to the original fill factor of 90%.
Thanks in advance.
MarkThanks Tom. Just what I was looking for. I pasted the results below. If
I'm reading the results correctly, the "Avg. Page Density (full)" indicates
that the table is currently existing with a fill factor of about 100%. In
other words, inserts wouldn't be pretty. Correct? Moreover, if a page is
roughly 8000 bytes, and only 75 or so of those bytes are free, that's again
saying that I have 1% or less of free space ... correct again?
Thanks again.
Mark
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (1672393027); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 166
- Extents Scanned.......................: 22
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 95.45% [21:22]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 4.55%
- Avg. Bytes Free per Page................: 75.8
- Avg. Page Density (full)................: 99.06%
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e7eROPRgDHA.2072@.TK2MSFTNGP10.phx.gbl...
Check out DBCC SHOWCONTIG in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mark" <field027_nospam_@.umn.edu> wrote in message
news:#5UwdNRgDHA.2984@.TK2MSFTNGP11.phx.gbl...
How can you identify the current space available in a clustered index? I'm
aware of the commands to rebuild the index, but I'd like to be able to
monitor how "full" an index is relative to the original fill factor of 90%.
Thanks in advance.
Mark|||This is a multi-part message in MIME format.
--=_NextPart_000_00F0_01C380F6.D2157E80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Your average page density says how full the pages are. Right after to =rebuild the index, this will be approximately equal to your fill factor. = the fact that you also have only 75 bytes free per page supports the =high page density that you have.
An insert is not necessarily going to lead to a page split. If you had =skinny rows, e.g. 20 bytes and inserted 2 rows, you would not get a page =split. Also, if the clustered index was on a monotonically increasing =value, you would be adding rows to the "end" of the table and would =simply pick up a new page.
Your table is so small that I would not worry about fragmentation.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mark" <field027_nospam_@.umn.edu> wrote in message =news:#hlqaWRgDHA.2260@.TK2MSFTNGP10.phx.gbl...
Thanks Tom. Just what I was looking for. I pasted the results below. =If
I'm reading the results correctly, the "Avg. Page Density (full)" =indicates
that the table is currently existing with a fill factor of about 100%. =In
other words, inserts wouldn't be pretty. Correct? Moreover, if a =page is
roughly 8000 bytes, and only 75 or so of those bytes are free, that's =again
saying that I have 1% or less of free space ... correct again?
Thanks again.
Mark
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (1672393027); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 166
- Extents Scanned.......................: 22
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 95.45% [21:22]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 4.55%
- Avg. Bytes Free per Page................: 75.8
- Avg. Page Density (full)................: 99.06%
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e7eROPRgDHA.2072@.TK2MSFTNGP10.phx.gbl...
Check out DBCC SHOWCONTIG in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mark" <field027_nospam_@.umn.edu> wrote in message
news:#5UwdNRgDHA.2984@.TK2MSFTNGP11.phx.gbl...
How can you identify the current space available in a clustered index? =I'm
aware of the commands to rebuild the index, but I'd like to be able to
monitor how "full" an index is relative to the original fill factor of =90%.
Thanks in advance.
Mark
--=_NextPart_000_00F0_01C380F6.D2157E80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Your average page density says how =full the pages are. Right after to rebuild the index, this will be approximately =equal to your fill factor. the fact that you also have only 75 bytes free =per page supports the high page density that you have.
An insert is not necessarily going to =lead to a page split. If you had skinny rows, e.g. 20 bytes and inserted 2 =rows, you would not get a page split. Also, if the clustered index was =on a monotonically increasing value, you would be adding rows to the "end" of =the table and would simply pick up a new page.
Your table is so small that I would =not worry about fragmentation.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mark" = wrote in message news:#hlqaWRgDHA.2260=@.TK2MSFTNGP10.phx.gbl...Thanks Tom. Just what I was looking for. I pasted the results =below. IfI'm reading the results correctly, the "Avg. Page Density (full)" indicatesthat the table is currently existing with a fill factor of =about 100%. Inother words, inserts wouldn't be pretty. Correct? Moreover, if a page isroughly 8000 bytes, =and only 75 or so of those bytes are free, that's againsaying that I =have 1% or less of free space ... correct again?Thanks again.MarkDBCC SHOWCONTIG scanning 'my_table' =table...Table: 'my_table' (1672393027); index ID: 1, database ID: 10TABLE level =scan performed.- Pages Scanned........................: 166- =Extents Scanned.......................: 22- Extent Switches.......................: 21- Avg. Pages per Extent..................: 7.5- Scan Density [Best Count:Actual =Count]......: 95.45% [21:22]- Logical Scan Fragmentation ..............: 0.00%- Extent Scan Fragmentation =...............: 4.55%- Avg. Bytes Free per Page................: 75.8- Avg. =Page Density (full)................: 99.06%"Tom Moreau" == wrote in messagenews:e7eROPRgDHA.2072=@.TK2MSFTNGP10.phx.gbl...Check out DBCC SHOWCONTIG in the BOL.-- Tom----=--Thomas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql"Mark" = wrote in messagenews:#5UwdNRgDHA.2984=@.TK2MSFTNGP11.phx.gbl...How can you identify the current space available in a clustered index? =I'maware of the commands to rebuild the index, but I'd like to be =able tomonitor how "full" an index is relative to the original fill =factor of 90%.Thanks in advance.Mark

---

--=_NextPart_000_00F0_01C380F6.D2157E80--