How to verify an Active Directory installation

http://support.microsoft.com/kb/298143

It is important to verify a successful installation of Active Directory. After you have performed an upgrade, you can verify the promotion of a server to a domain controller by verifying the following items:

1. Default containers: These are created automatically when the first domain is created. Open Active Directory Users and Computers, and then verify that the following containers are present: Computers, Users, and ForeignSecurityPrincipals.
2. Default domain controllers organizational unit: This holds the first domain controller, and additionally serves as the default container for new Windows 2000 domain controllers. Open Active Directory Users and Computers, and then verify this organizational unit.
3. Default-First-Site-Name: During the promotion of a server to domain controller, the Dcpromo.exe program determines the site of which the domain controller can become a member. If the domain controller that is being created is the first in a new forest, a default site named "Default-First-Site-Name" is created and the domain controller becomes a member of this site until the appropriate subnets and sites are configured. You can verify this item by using Active Directory Sites and Services.
4. Active Directory database: The Active Directory database is your Ntds.dit file. Verify its existence in the %Systemroot%\Ntds folder.
5. Global catalog server: The first domain controller becomes a global catalog server, by default. To verify this item:
   1. Click Start, point to Programs, click Administrative Tools, and then click Active Directory Sites and Services.
   2. Double-click Sites to expand it, expand Servers, and then select your domain controller.
   3. Double-click the domain controller to expand the server contents.
   4. Below the server, an NTDS Settings object is displayed. Right-click the object, and then click Properties.
   5. On the General tab, you can observe a global catalog check box, which should be selected, by default.
6. Root domain: The forest root is created when the first domain controller is installed. Verify your computer network identification in My Computer. The Domain Name System (DNS) suffix of your computer should match the domain name that the domain controller belongs to. Also, ensure that your computer registers the proper computer role. To verify this role, use the net accounts command. The computer role should say "primary" or "backup" depending on whether it is the first domain controller in the domain.
7. Shared system volume: A Windows 2000 domain controller should have a shared system volume located in the %Systemroot%\Sysvol\Sysvol folder. To verify this item, use the net share command. The Active Directory also creates two standard policies during the installation process: The Default Domain policy and the Default Domain Controllers policy (located in the %Systemroot%\Sysvol\Domain\Policies folder). These policies are displayed as the following globally unique identifiers (GUIDs):
   {31B2F340-016D-11D2-945F-00C04FB984F9} representing the Default Domain policy
   {6AC1786C-016F-11D2-945F-00C04fB984F9} representing the Default Domain Controllers policy
8. SRV resource records: You must have a DNS server installed and configured for Active Directory and the associated client software to function correctly. Microsoft recommends that you use Microsoft DNS server, which is supplied with Windows 2000 Server as your DNS server. However, Microsoft DNS server is not required. The DNS server that you use must support the Service Resource Record (SRV RR) Requests for Comments (RFC) 2052, and the dynamic update protocol (RFC 2136). Use the DNS Manager Microsoft Management Console (MMC) snap-in to verify that the appropriate zones and resource records are created for each DNS zone. Active Directory creates its SRV RRs in the following folders:
   • _Msdcs/Dc/_Sites/Default-first-site-name/_Tcp
   • _Msdcs/Dc/_Tcp
   In these locations, an SRV RR is displayed for the following services:
   • _kerberos
   • _ldap

部分重要经济指标说明(转)

世界上绝大多数交易者都会在交易时采用这些宏观指标。 所发表数据的“质
量”或影响力可能在不同时间有所不同。 如果指标数据代表了新信息或者据此数据能
得出其它报告或数据无法得出的结论，那么该指标数据就是十分重要的.另外，如果人
们可以用某个指标来更好地预测未来趋势，那么这个指标就是极具价值的。

CCI - 消费者信心指数（Consumer Confidence Index） [高]
美国会议委员会会在每个月的最后一个星期二,美国东部时间早上10:00公布当月数据
        CCI是从5,000个美国家庭的抽样调查中得出，是反映消费者信心强弱的最准确
指标。消费者信心指数表明，当经济能确保带来更多的工作机会、更高的工资和较低的
利率时，消费者的信心和购买力就会增加。 受访者回答的问题包括其收入状况，对当
前市场的看法以及增加收入的可能性。信心指数是美联储决定利率的重要依据之一。由
于个人消费开支占美国经济总量的三分之二，信心指数对市场的影响很大。
CPI - 消费者物价指数（Consumer Price Index）; 核心消费者物价指数（Core-CPI）
[高]
美国劳动统计局会在每月20号左右，美国东部时间早上8:30公布上月数据
        CPI被广泛用于衡量通货膨胀，也是衡量政府政策有效性的重要指标之一。
CPI反映了一定时期内居民购买一篮子商品（和服务）的价格变化（不含税）。 CPI是
最受关注的经济指标之一，也是非常重要的市场驱动力。 CPI上升可能引起通货膨胀。
Core-CPI（CPI中扣除食品、能源和受季节波动影响的费用项目）能更好的反映物价形
式。
就业报告(Employment Report )[高]
美国劳工部会在每月的第一个星期五，美国东部时间早上8:30公布上月数据
        通过对375,000个企业和60,000个家庭的调查来收集数据。报告显示： 经济中
新增或减少的就业岗位，每小时平均工资和每周平均工作时间。 此报告是最重要的经
济报告之一，它不仅披露了最新的实时信息，而且同NFP(非农业就业人数变化报告)一
起反映了完整的国家经济状况。
报告还会分别介绍不同部门（生产、服务、建筑、采矿、公共事业）的情况。
就业情况报告(Employment Situation Report) [高]
美国劳动统计局会在每月的第一个星期五，美国东部时间早上8:30公布上月数据
        就业情况报告是一个月度指标,有两个主要组成部分。 第一部分是失业人数和
新增就业机会，报告会介绍失业率及失业率的变化情况。报告的第二部分介绍有关每周
平均工作时间和每小时平均工资等方面的信息，数据反映了劳动力供求关系而这也是通
货膨胀的决定因素之一。 劳动局的调查涵盖了全美的250余个地区，几乎包括了每个主
要产业。该指标作为金融市场上最受关注的指标之一，总会对市场构成影响。 因内容
时效性强，发布及时，就业报告深受投资者重视。它是反映当前经济健康状况的最佳指
标之一。
FOMC（联邦公开市场委员会）会议： 利率声明(Rate announcement)[高]
由美国联邦银行的代表们参加的会议，一年举行8次。 每次会议中（大约在东部时间14
:15）会公布有关基准利率的决定。
        美联储（美国联邦储备局）负责调控美国的货币政策，监管银行，为政府机构
和公民提供服务并保持国家的金融稳定。
        美国有12个联邦区域（每个区域由几个州组成）在联邦委员会中由地区专员代
表其所在区域。
        一个国家的利率实际上就是持有该货币的投资回报率。 货币利率越高，就会
有越多的人持有、买入该货币从而加强了货币价值。 它是一个非常重要的指标会影响
通货膨胀率和市场驱动力。
        FOMC关于利率的声明是极为重要的，会议的其他内容（通常在2周后发布）对
市场投资者同样重要。
GDP - 国内生产总值（Gross Domestic Product） [高]
BEA(美国商务部经济分析局) 会在每个季度的最后一天,美国东部时间早上8:30公布上
季度数据。
        美国商务部，分3阶段公布GDP: 预估值；初值；终值。
        GDP从总体上衡量市场活动。它反映了在一定时期内一经济体中所生产出的全
部最终产品和服务的货币价值。这包括消费、政府采购、投资和贸易收支。 GDP可能是
表现一个国家经济健康状况的最佳指标。它通常按年衡量，但也会发表季度统计数据。
        美国商务部在每季度的最后一天会发布一份“预估值报告"。该报告发布后的
一个月之内发布“初值报告",之后再过一个月发布“终值报告"。 越新的GDP数据对市
场而言相对越为重要。它反映了一个国家经济增长（或衰退）的速度。
ISM 美国供应管理协会(Institute for Supply Management) 制造业指数(
Manufacturing Index)[高]
美国供应管理协会在每月的第一个工作日,美国东部时间早10:00公布上月数据
        制造业ISM报告基于400余家工业企业采购经理每月对问题的回答编辑而成。它
反映了5个主要经济领域（新用户订单占30%，制造业占25%，就业占20%，供货订单占15
%，库存占10%）的复合平均值。 若此数据高于50，则说明经济活动扩张；若其低于50
，则经济紧缩。
MCSI - 密西根消费者信心指数(Michigan Consumer Confidence Index)[高]
密西根大学在每月的第一天公布上月数据
        由密西根大学调查得出的消费者信心指数, 对投资者来说变得越来越有用。它
提供了消费者是否愿意消费的信息。
NFP - 非农就业人数变化(Changes in non-farm payrolls) [高]
美国劳工部在每月的第一个星期五,美国东部时间早上8:30公布上月数据
        数据旨在反映美国任何行业带薪雇员的总人数变化情况,但不包括以下人员：
        - 一般政府雇员;
        - 私人家庭雇员;
        - 为个人提供帮助的非营利机构雇员;
        - 农业人口。
        在参与创造美国国内生产总值的雇员中，非农雇员占80%，它可帮助政府决策
制定者和经济学家把握当前经济状况和预测未来经济活动水平。由于预测值常常与实际
值有较大的偏差，该数字的公布往往会造成大幅的市场波动。
PMI - 采购经理人指数（Purchasing Managers Index） [高]
美国供应管理协会在每月的第一个工作日,美国东部时间早上10:00公布上月数据
        PMI 是基于五项主要指标的综合指数，包括： 新订单指标、库存指标、生产
指标、供货商交货指标和就业环境指标。每一指标的侧重不同，同时数据会根据季节因
素进行调整。 采购经理人协会将调查来自全国20个不同行业的300余名采购经理人。
若PMI指数超过50，则表明制造业正在扩张；若其低于50，则意味着产业萎缩。 由于
PMI报告是反映工厂生产的最佳指标，它对金融市场也就显得尤为重要。此指标通常被
用于监测通货膨胀压力和生产经营活动。 在监测通货膨胀方面PMI没有CPI敏感，但由
于PMI数据是在每月的第一个工作日公布，它的时效性较强。 若PMI中提到了预期之外
的变化,那么通常随着它的发表，市场很快就会作出回应。报告中特别重要的地方是新
订单的增加，它预测了未来几个月的生产活动。
零售销售数据（Retail Sales Data）;扣除汽车外的零售销售（Retail Sales less
Automotives）[高]
美国商务部统计局在每月12号左右,美国东部时间早上8:30公布上月数据
        零售销售是带动美国经济的主要动力之一，它跟踪公司在零售领域内的销售,
衡量了消费者在零售销售中（不含服务费用）的总花费。零售收入占据了美国经济的主
要部分（三分之二）。 普查局的调查涵盖了上百家提供零售业务的不同规模的公司和
企业。 每月发布的数据显示了上月变化的百分比。 负数表明着与前几个月相比销售下
降。这个指标是一股重要的市场驱动力，因为它被作为衡量消费者活动和信心的标准，
较高的销售数据代表着经济活动的增强。 上月的零售销售数据会在本月的前两周内公
布，它的时效性较强。
短观调查报告（Tankan Survey）[高]
BoJ (日本银行);每年4次分别在4月、7月、10月和12月中旬;格林威治时间晚上10:50发
布
        由日本中央银行发布的日本企业经济状况调查,可作为制定货币政策的参考。
参与调查的上千家企业都具有雄厚的资本，一些影响力很大的公司也被包含在内。 问
题包括对当前行业状况和趋势的看法,以及对下一季度和下一年商业活动的预期。它被
认为是日元货币对的主要市场驱动力。
长期债券交易TIC(美国财政部国际资本流动)数据 [高]
美国财政部在每月的第十二个工作日左右,美国东部时间早上9:00公布隔月数据
        TIC数据提供了有关美国处理当前帐户赤字采取的主要方法： 向外国卖出长期
债券，或者转移债务。重要的是，我们须知道解决赤字还有其他方法：从外国银行借款
或者吸引净FDI(海外直接投资)流入。 但由于FDI是负值,而银行流入的资金又较少，所
以美国主要通过向海外出售长期债券获取所需资金。TIC数据是衡量某一货币在国际投
资市场受信任程度的最佳工具。该数据可以导致大幅的市场波动。
贸易收支（Trade Balance）[高]
美国商务部在每月的第二周，美国东部时间早上8:30公布隔月数据
        贸易收支是一个国家国际收支的最大组成部分。它反映了一个国家出口的产品
和服务的总值与其进口的产品和服务的总值之间的差额。如果一个国家的进口大于出口
，会出现贸易逆差；反之，则会出现贸易顺差。该数据可以导致大幅的市场波动。

Using RS Scripter to backup/restore SSRS's report/subscription/roles...

f you are using SSRS then chances are high to come across a scenario where you want to deploy reports developed on development machine to the production server, there are various ways to do this and one of them is to use RS Scripter tool. Here are the steps to do this -

1. Creating Report Deployment Script

1. Download the Reporting Services Scripter - http://www.sqldbatips.com/showarticle.asp?ID=62
2. Update the servers.xml; change the reportservice value if your ReportServer name is different.

   
   
   

3. Run the RSScripter.exe.
4. Click on Options, and select the options selected in images below( for more information about Scripting Options check the readme file - http://www.sqldbatips.com/samples/code/RSScripter/readme.htm#_scripting_options) –
   
   
   
5. Change the SQL 2005 RS EXE Location in the Global tab as per the location of SQL server and Apply –
   
6. Select SQL 2005 from the Report Server dropdown and click on Get Catalog, all the reports, data source, Schedules, Roles etc present in report server will be displayed –
   
   
7. Select all the reports, data source, schedules and roles you want to transfer.
   
   

Change the output directory path (Should not be machine/user specific e.g. Desktop) e.g. C:\AClickReports and click on Script. The script for loading the specified items will be generated to the specified folder.

2. Deploying the Reports

1. Ensure that you have IIS and dot net framework 2.0 is installed on report server.
2. While installing SQL server 2005 select reporting services option.
3. Follow the report server configuration steps and make sure "Report Server Configuration Manager" is all green ticks?
   
4. Follow these steps to publish reports –
   1. Extract the AClickReports folder to the report server, C: Drive.
   2. Open the “RS Scripter Load All Items.cmd” file located in AClickReports folder for editing.
   3. Change the SCRIPTLOCATION Script variable value to the location where AClickReports folder is copied (Should be same to the output directory path selected in step 3.8)

        SET SCRIPTLOCATION=C:\ AClickReports\
      

      
      
   4. Change the RS Script variable value to the location where Microsoft SQL Server is installed (if it is different from the default C drive).

      SET RS="C:\Program Files\Microsoft SQL Server\90\Tools\Bin\RS.EXE"
      

   5. Run the RS Scripter Load All Items.cmd batch file, it will publish the reports to the report server.
5. Follow these steps to verify that reports are deployed correctly
   
   1. Go to http://localhost/Reports/Pages/Folder.aspx,
      
   2. Open the Data Sources you should see data sources selected in step 1.7
      
   3. Open the data source and update the data source details as per the configuration of report server(if they are different) and apply.
      
   4. Go back to Home page and click on the report folder selected in step 1.7 , select one of the reports and click on Edit button, then click on the Data Sources link ; Make sure that the data source is configured properly.
      
   5. Repeat the steps from 2.5.2 to 2.5.4 for other data sources and report folders.

统计数据库各个表所占空间的sql script

create table #t(name varchar(255), rows bigint, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20))
exec sp_MSforeachtable "insert into #t exec sp_spaceused '?'"
select * from #t
drop table #t

How to easily identify a scheduled SQL Server Reporting Services report

Problem
Our company has hundreds of SQL Server Reporting Services reports with daily, weekly, monthly, quarterly, yearly and adhoc schedules with various rendering formats like web archive, excel, pdf, csv, xml and tiff sent to multiple users, departments and groups. Sometimes we have to manually run a subscription to resend reports on an adhoc basis or re-send the reports when the actual subscriptions failed or when the data had to be corrected.
In this tip I will show you an easy way to identify the scheduled Reporting Services report, so you can run that scheduled job to reproduce the reports and delivery of the reports.
Solution
SQL Server Reporting services has come a long way from the first time it was released for SQL Server 2000 SP4. Unfortunately there are still some rough corners where it could be improved and one such place is subscriptions.
Manually initiating a subscription is a tedious task in SQL Server Reporting Services. Each subscription in Reporting Services is setup as a SQL Server Agent job, but the job names are NOT intuitive, rather named as a Unique Identifier as shown below.
Here is a screenshot of some of the subscriptions on a Reporting Services instance.

Fortunately, SQL Server Reporting Services comes with a rich set of meta-data that is stored in tables and for today's tip we will take advantage of these tables. There are two different methods to accomplish this task and both of them are outlined below.

---

Option 1:
This method looks at the dbo.ReportSchedule, dbo.Subscriptions, dbo.Catalog tables of the ReportServer database along with the dbo.sysjobs table of the msdb to figure out the SQL Agent job name. Additional information is also pulled to isolate the correct report when a similarly named report is in multiple folders/paths.

SELECT             b.name AS JobName             , e.name             , e.path             , d.description             , a.SubscriptionID             , laststatus             , eventtype             , LastRunTime             , date_created             , date_modified     FROM ReportServer.dbo.ReportSchedule a JOIN msdb.dbo.sysjobs b             ON a.ScheduleID = b.name             JOIN ReportServer.dbo.ReportSchedule c             ON b.name = c.ScheduleID             JOIN ReportServer.dbo.Subscriptions d             ON c.SubscriptionID = d.SubscriptionID             JOIN ReportServer.dbo.Catalog e             ON d.report_oid = e.itemid     WHERE e.name = 'Sales_Report'

From the above resultset, grab the uniqueidentifier for the JobName column and filter the job in SSMS as shown below. Once the correct job is identified, run the SQL Server Agent job to deliver the subscription.

---

Option 2:
This option is better than the first option and completely eliminates the manual step in Option 1 of having to find and run the SQL Agent job.
In this option, we will generate the T-SQL that is used inside the SQL Agent job directly from the tables and then just run the SQL. It's a pure SQL based solution.
After you run this query, get the column that has the EXEC command for the report you want to re-run and paste the EXEC command into a query window to execute the code to re-run the report.

SELECT     'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData='''  + CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand             , b.name AS JobName             , a.SubscriptionID             , e.name             , e.path             , d.description             , laststatus             , eventtype             , LastRunTime             , date_created             , date_modified     FROM ReportServer.dbo.ReportSchedule a JOIN msdb.dbo.sysjobs b             ON a.ScheduleID = b.name             JOIN ReportServer.dbo.ReportSchedule c             ON b.name = c.ScheduleID             JOIN ReportServer.dbo.Subscriptions d             ON c.SubscriptionID = d.SubscriptionID             JOIN ReportServer.dbo.Catalog e             ON d.report_oid = e.itemid     WHERE e.name = 'Sales_Report'

SQL Server script to rebuild all indexes for all tables and all databases

Problem

One of the main functions of a DBA is to maintain database indexes.  There have been several tips written about different commands to use for both index rebuilds and index defrags as well as the differences between index maintenance with SQL Server.  In addition, other tips have been written about using maintenance plans to maintain indexes on all databases.  One of the issues with maintenance plans is that they don't always seem to be as reliable as you would hope and you also sometimes get false feedback on whether the task actually was successful or not.  In this tip we look at a simple script that could be used to rebuild all indexes for all databases.

Solution

The one nice thing about maintenance plans is that it works across multiple databases and therefore you can push out one task to handle the same activity across all of your databases.  The problem that I have seen with maintenance plans is that sometimes they do not work as expected, therefore here is another approach.
The script below allows you to rebuild indexes for all databases and all tables within a database.  This could be further tweaked to handle only indexes that need maintenance as well as doing either index defrags or index rebuilds.
The script uses two cursors one for the databases and another for the tables within the database.  In addition, it uses the INFORMATION_SCHEMA.TABLES view to list all of the tables within a database. 
Because we need to change from database to database we also need to create dynamic SQL code for the queries.  For the DBCC DBREINDEX option we can just pass in the parameters, but for the ALTER INDEX statement we need to build the query dynamically.  Here is the script.

DECLARE @Database VARCHAR(255)   DECLARE @Table VARCHAR(255)  DECLARE @cmd NVARCHAR(500)  DECLARE @fillfactor INT

SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases   WHERE name NOT IN ('master','msdb','tempdb','model','distribution')   ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database  WHILE @@FETCH_STATUS = 0  BEGIN

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''  

   -- create table cursor
   EXEC (@cmd)
   OPEN TableCursor  

   FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
       BEGIN
           -- SQL 2005 or higher command
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
           EXEC (@cmd)
       END
       ELSE
       BEGIN
          -- SQL 2000 command
          DBCC DBREINDEX(@Table,' ',@fillfactor)
       END

       FETCH NEXT FROM TableCursor INTO @Table  
   END  

   CLOSE TableCursor  
   DEALLOCATE TableCursor

   FETCH NEXT FROM DatabaseCursor INTO @Database  END
CLOSE DatabaseCursor   DEALLOCATE DatabaseCursor

The script will work for both SQL 2000 and higher versions.  For SQL 2000 it uses DBREINDEX and for SQL Server 2005 and higher it uses ALTER INDEX.  Thanks go out to LittlePanthers for providing the code snippet to check the version of SQL Server.
Also, I have excluded the system databases, so you can include these or also add other databases to exclude from you index maintenance routines.

Next Steps

• This is a simple base script that could be modified into a stored procedure and also allow you to pass other parameters such as doing an index rebuild or an index defrag.
• Make the index rebuild statements more robust with other options.
• You could also modify this to read from a table that you create to identify which databases and which indexes you want to run this against.  You can look at index fragmentation and only rebuild the indexes that need to be rebuilt.
• This approach rebuilds all indexes, so be careful if you run this on very large indexes.

Index Fragmentation Report in SQL Server 2005 and 2008

ProblemWhile indexes can speed up execution of queries several fold as they can make the querying process faster, there is overhead associated with them. They consume additional disk space and require additional time to update themselves whenever data is updated, deleted or appended in a table. Also when you perform any data modification operations (INSERT, UPDATE, or DELETE statements) index fragmentation may occur and the information in the index can get scattered in the database. Fragmented index data can cause SQL Server to perform unnecessary data reads and switching across different pages, so query performance against a heavily fragmented table can be very poor. In this article I am going to write about fragmentation and different queries to determine the level of fragmentation.
SolutionWhen indexes are first built, little or no fragmentation should exist. Over time, as data is inserted, updated, and deleted, fragmentation levels on the underlying indexes may begin to rise. So let's see how it happens.
When a page of data fills to 100 percent and more data must be added to it, a page split occurs. To make room for the new incoming data, SQL Server moves half of the data from the full page to a new page. The new page that is created is created after all the pages in the database. Therefore, instead of going right from one page to the next when looking for data, SQL Server has to go from one page to another page somewhere else in the database looking for the next page it needs. This is called index fragmentation.
There are basically two types of fragmentation:

• External fragmentation - External, a.k.a logical,  fragmentation occurs when an index leaf page is not in logical order, in other words it occurs when the logical ordering of the index does not match the physical ordering of the index. This causes SQL Server to perform extra work to return ordered results. For the most part, external fragmentation isn’t too big of a deal for specific searches that return very few records or queries that return result sets that do not need to be ordered.
  
• Internal fragmentation - Internal fragmentation occurs when there is too much free space in the index pages. Typically, some free space is desirable, especially when the index is created or rebuilt. You can specify the Fill Factor setting when the index is created or rebuilt to indicate a percentage of how full the index pages are when created. If the index pages are too fragmented, it will cause queries to take longer (because of the extra reads required to find the dataset) and cause your indexes to grow larger than necessary. If no space is available in the index data pages, data changes (primarily inserts) will cause page splits as discussed above, which also require additional system resources to perform.

As we learned, heavily fragmented indexes can degrade query performance significantly and cause the application accessing it to respond slowly. So now the question is how to identify the fragmentation. For that purpose SQL Server 2005 and 2008 provide a dynamic management function (DMF) to determine index fragmentation level. This new DMF (sys.dm_db_index_physical_stats) function accepts parameters such as the database, database table, and index for which you want to find fragmentation. There are several options that allow you to specify the level of detail that you want to see in regards to index fragmentation, we will see some of these options in the examples below.
The sys.dm_db_index_physical_stats function returns tabular data regarding one particular table or index.

Input Parameter	Description
database_id	The default is 0 (NULL, 0, and DEFAULT are equivalent values in this context) which specify to return information for all databases in the instance of SQL Server else specify the databaseID from sys.databases if you want information about a specific database. If you specify NULL for database_id, you must also specify NULL for object_id, index_id, and partition_number.
object_id	The default is 0 (NULL, 0, and DEFAULT are equivalent values in this context) which specify to return information for all tables and views in the specified database or else you can specify object_id for a particular object. If you specify NULL for object_id, you must also specify NULL for index_id and partition_number.
index_id	The default is -1 (NULL, -1, and DEFAULT are equivalent values in this context) which specify to return information for all indexes for a base table or view. If you specify NULL for index_id, you must also specify NULL for partition_number.
partition_number	The default is 0 (NULL, 0, and DEFAULT are equivalent values in this context) which specify to return information for all partitions of the owning object. partition_number is 1-based. A nonpartitioned index or heap has partition_number set to 1.
mode	mode specifies the scan level that is used to obtain statistics. Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED. LIMITED - It is the fastest mode and scans the smallest number of pages. For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. In SQL Server 2008, only the associated PFS and IAM pages of a heap are examined; the data pages of the heap are not scanned. In SQL Server 2005, all pages of a heap are scanned in LIMITED mode. SAMPLED - It returns statistics based on a 1 percent sample of all the pages in the index or heap. If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED. DETAILED - It scans all pages and returns all statistics.

Note

• The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement. It requires only an Intent-Shared (IS) table lock in comparison to DBCC SHOWCONTIG which required a Shared Lock, also the algorithm for calculating fragmentation is more precise than DBCC SHOWCONTIG and hence it gives a more accurate result.
  
• For an index, one row is returned for each level of the B-tree in each partition (this is the reason, if you look at image below, for some indexes there are two or more than two records for a single index; you can refer to the Index_depth column which tells the number of index levels). For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition.

Example
Let’s see an example. The first script provided below gives the fragmentation level of a given database including all tables and views in the database and all indexes on these objects. The second script gives the fragmentation level of a particular object in the given database. The details about the columns and its meaning returned by the sys.dm_db_index_physical_stats are given in the below table.

Script : Index Fragmentation Report Script

--To Find out fragmentation level of a given database --This query will give DETAILED information --CAUTION : It may take very long time, depending on the number of tables in the DB USE AdventureWorks GO SELECT object_name(IPS.object_id) AS [TableName], SI.name AS [IndexName], IPS.Index_type_desc, IPS.avg_fragmentation_in_percent, IPS.avg_fragment_size_in_pages, IPS.avg_page_space_used_in_percent, IPS.record_count, IPS.ghost_record_count, IPS.fragment_count, IPS.avg_fragment_size_in_pages FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks'), NULL, NULL, NULL , 'DETAILED') IPS JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id WHERE ST.is_ms_shipped = 0 ORDER BY 1,5 GO --To Find out fragmentation level of a given database and table --This query will give DETAILED information DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'AdventureWorks'); SET @object_id = OBJECT_ID(N'Production.BillOfMaterials'); IF @object_id IS NULL BEGIN PRINT N'Invalid object'; END ELSE BEGIN SELECT IPS.Index_type_desc, IPS.avg_fragmentation_in_percent, IPS.avg_fragment_size_in_pages, IPS.avg_page_space_used_in_percent, IPS.record_count, IPS.ghost_record_count, IPS.fragment_count, IPS.avg_fragment_size_in_pages FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'DETAILED') AS IPS; END GO

Returned Column	Description
avg_fragmentation_in_percent	It indicates the amount of external fragmentation you have for the given objects. The lower the number the better - as this number approaches 100% the more pages you have in the given index that are not properly ordered. For heaps, this value is actually the percentage of extent fragmentation and not external fragmentation.
avg_page_space_used_in_percent	It indicates how dense the pages in your index are, i.e. on average how full each page in the index is (internal fragmentation). The higher the number the better speaking in terms of fragmentation and read-performance. To achieve optimal disk space use, this value should be close to 100% for an index that will not have many random inserts. However, an index that has many random inserts and has very full pages will have an increased number of page splits. This causes more fragmentation. Therefore, in order to reduce page splits, the value should be less than 100 percent.
fragment_count	A fragment is made up of physically consecutive leaf pages in the same file for an allocation unit. An index has at least one fragment. The maximum fragments an index can have are equal to the number of pages in the leaf level of the index. So the less fragments the more data is stored consecutively.
avg_fragment_size_in_pages	Larger fragments mean that less disk I/O is required to read the same number of pages. Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance.

---