It happened last month when I helped my relative to connect an iPad to a computer. Though they had tried every way, I found the solution was quite simple.
iTunes may prevent a computer from recognizing an Apple device if the iTunes' Apple ID is different than the device's Apple ID. So just log out that account in iTunes.
Monday, November 28, 2016
Thursday, November 24, 2016
Checking for Updates Stuck in Windows 8.1
Also, in "Control Panel\All Control Panel Items\Troubleshooting\Fix problems with Windows Update", received this after scanning:
"windows update error 0x80070490" ...... fixed (actually it's not fixed)
After searching for solutions online for a while, there is a simply fix for this problem. Use the link below, download and install KB3172614:
http://www.catalog.update.microsoft.com/Search.aspx?q=3172614
By the way, these solutions are useless to me:
KB3172614 may get stuck just like the Windows Update does.
I tried several more computers with this problem. Though I solved all of them, it seems I have to use these solutions as a combination. Here are the solutions, and no more than these :)
"windows update error 0x80070490" ...... fixed (actually it's not fixed)
After searching for solutions online for a while, there is a simply fix for this problem. Use the link below, download and install KB3172614:
http://www.catalog.update.microsoft.com/Search.aspx?q=3172614
By the way, these solutions are useless to me:
- WSUS Offline
- WindowsUpdateDiagnostic.diagcab
- sfc /scannow
- Delete "SoftwareDistribution"
- ....
KB3172614 may get stuck just like the Windows Update does.
I tried several more computers with this problem. Though I solved all of them, it seems I have to use these solutions as a combination. Here are the solutions, and no more than these :)
- use "Fix problems with Windows Update" in Control Panel
- download and run "WindowsUpdateDiagnostic.diagcab"
- delete everything in "SoftwareDistribution" folder in C:\Windows\
- use "Disk Cleanup" for C:\ and then, click "Clean up system files" (this may take long)
- install KB3172614
Sunday, December 21, 2014
%2BUSB%2B3.0%2BFlash%2BDrive.png)
%2BSeagate%2B500GB%2B2.5in%2BHDD.png)
Friday, November 21, 2014
MS Access 2013 自动切换输入法的问题 (IME Changes Automatically When Editing)
现象 / Symptom:
用Access 2013编辑表格内容时，输入法总是自动切换到中文。
IME automatically changes to "Chinese" all the time when editing table cells in Access 2013.
环境 / Environment:
英文版 Windows 8.1 和 MS Office 2013；语言偏好中第二项为默认中文输入法
English version of Windows 8.1 and MS Office 2013; Default IME "Chinese (Simplified, China)" as second Language Preference.
解决方法 / Solution:
1. Open Access Options (Tools \ Options).
2. Check "Datasheet IME control" checkbox.
用Access 2013编辑表格内容时，输入法总是自动切换到中文。
IME automatically changes to "Chinese" all the time when editing table cells in Access 2013.
环境 / Environment:
英文版 Windows 8.1 和 MS Office 2013；语言偏好中第二项为默认中文输入法
English version of Windows 8.1 and MS Office 2013; Default IME "Chinese (Simplified, China)" as second Language Preference.
解决方法 / Solution:
1. Open Access Options (Tools \ Options).
2. Check "Datasheet IME control" checkbox.
Tuesday, November 11, 2014
[转] 【SQL Server高可用性】数据库复制
一篇关于SQL SERVER 数据库复制技术（Database Replication）的文章，还有测试截图，非常详尽，故存之。
原文地址： http://blog.csdn.net/sqlserverdiscovery/article/details/17059073
因为太大的数据库，要再备份，估计需要花很长的时间，所以通过数据库复制，也保证了数据的安全。
然后，到虚拟机的wc数据库中，查询B表的数据：
原文地址： http://blog.csdn.net/sqlserverdiscovery/article/details/17059073
经常在论坛中看到有人问数据同步的技术，如果只是同步少量的表，那么可以考虑使用链接服务器+触发器，来实现数据同步，但当要同步的数据表比较多，那么可以考虑用数据库复制技术，来实现数据的同步。
一、使用场景
数据发布和订阅，就是sql server的数据库复制技术，用于同步数据。我觉得使用情况主要有2种：
1、适合于数据分散在不同的地区的情况。
比如，总公司，还有分公司，可能在北京，上海，广州，等地区，那么每个地区都有地区数据库，都会有销售数据，那么通过对每个分公司的数据库进行发布，然后总公司进行订阅，那么数据就从分公司复制到总公司，也就是发布端复制到了订阅端。
2、如果数据库很大，压力很大，读写很频繁，那么为了分担单个数据库服务器的压力，可以通过数据库复制技术，来实现读写分离。
可以从一个数据库，把数据复制到多台服务器上，主数据库用于写，而其他几台服务器用于读取数据，减轻主数据库的压力。
1、适合于数据分散在不同的地区的情况。
比如，总公司，还有分公司，可能在北京，上海，广州，等地区，那么每个地区都有地区数据库，都会有销售数据，那么通过对每个分公司的数据库进行发布，然后总公司进行订阅，那么数据就从分公司复制到总公司，也就是发布端复制到了订阅端。
2、如果数据库很大，压力很大，读写很频繁，那么为了分担单个数据库服务器的压力，可以通过数据库复制技术，来实现读写分离。
可以从一个数据库，把数据复制到多台服务器上，主数据库用于写，而其他几台服务器用于读取数据，减轻主数据库的压力。
3、总结一下：
一个是用于分布式数据的情况。
一个是数据库的高可用性，不仅能通过读写分离，提高性能，减轻压力，同时还能起到数据库备份的作用。
一个是用于分布式数据的情况。
一个是数据库的高可用性，不仅能通过读写分离，提高性能，减轻压力，同时还能起到数据库备份的作用。
因为太大的数据库，要再备份，估计需要花很长的时间，所以通过数据库复制，也保证了数据的安全。
二、发布类型
不同的发布类型，会有不同的效果，适用于不同的场景，特别是下面的2种发布类型，需要特别注意：
快照发布:
发布服务器按预定的时间间隔向订阅服务器发送已发布数据的快照。也就是间隔时间一到，2000把数据的快照，也就是所有数据，都发生到订阅端，一般适合同步间隔时间比较长的，比如1周才同步一次。
事务发布:
在订阅服务器收到已发布数据的初始快照后，发布服务器将事务流式传输到订阅服务器。
也就是，设置好后，第一次会进行初始化，然后以后每次同步的时候，只是把修改的数据，比如insert、update、delete，同步到订阅端，一般适合对同步时间要求比较严格的情况，间隔时间可能就10秒，或者几分钟。
发布服务器按预定的时间间隔向订阅服务器发送已发布数据的快照。也就是间隔时间一到，2000把数据的快照，也就是所有数据，都发生到订阅端，一般适合同步间隔时间比较长的，比如1周才同步一次。
事务发布:
在订阅服务器收到已发布数据的初始快照后，发布服务器将事务流式传输到订阅服务器。
也就是，设置好后，第一次会进行初始化，然后以后每次同步的时候，只是把修改的数据，比如insert、update、delete，同步到订阅端，一般适合对同步时间要求比较严格的情况，间隔时间可能就10秒，或者几分钟。
总结一下：
快照复制，每次都是复制所有的数据，所以如果数据量大，那么往往复制的时间会很长。
而事务复制，每次只复制变化的部分，所以速度较快。
而事务复制，每次只复制变化的部分，所以速度较快。
三、实验环境
一台是笔记本，机器名：ggg-pc，实例名：MSSQLSERVER
另一台是安装的虚拟机，机器名：DTSCIOW1G2GJOED，实例名：MSSQLSERVER。
那么，如果不是虚拟机，能否配置成功呢？
我也尝试过在两台笔记本之间，也做了事务复制，配置与上面的类似，也同步成功了。
需要注意的是在配置完成订阅端成功后，发现在复制目录下面本地订阅目录下，没有出现订阅，这个多刷新几次就好了。
还有就是数据库引擎服务和代理服务，都是通过Local System帐号来启动的，不一定需要Administrator帐号。
需要特别说明的是这个数据库复制，并不是实时的，最短复制间隔为10秒，所以不能完全满足高安全性的要求，也就是当出现故障时，会有少量数据没有复制到订阅端。
四、首先，分别在两个实例中分别运行下面的代码，来创建数据库：
- --在笔记本上
- create database wc
- go
- use wc
- go
- create table A
- (
- id int primary key,
- v varchar(100)
- )
- --在虚拟机上
- create database wc
- go
- use wc
- go
- create table B
- (
- id int primary key,
- v varchar(100),
- vv varchar(100) --注意：这里比A表多了一列
- )
- go
五、然后，接下来就是要配置同步与订阅，来实现把A表的2列，同步到B表的2列中：
首先是设置发布：
1、展开“复制”目录，选择“本地发布”，右键，选择“新建发布”
2、弹出新建发布向导，单击下一步
3、选择：把ggg-pc作为自己的分发服务器，单击下一步
4、选择：是，将SQL Server的代理服务器配置为自动启动
5、选择：快照文件夹，默认是
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData
6、选择：要发布的数据库，这里选wc
7、选择：发布类型，这里选择“事务发布”
8、选择要发布的表A，以及要发布的列：id和v
然后，点一下A表，点击右边的“项目属性”按钮，在弹出的菜单中选择“设置突出显示的 表的项目属性”，弹出“项目属性”对话框：
然后，往下拉，找“目标对象”，由于需要同步到的不是A表，而是已存在的B表，所以把“目标对象名称”改为“B”，同时把“名称已被使用时的操作”改为“现有对象保持不变”：
9、在筛选表行中，你可以设置只同步满足条件的记录，而不是所有的记录：
10、设置快照代理，在两个复选框上都打上勾，也就是立即启动快照、计划快照代理：
然后，单击右边的”更改“按钮，设置同步的时间间隔为 10秒：
11、代理安全性：
然后，单击 安全设置 ，弹出 快照代理安全性 对话框，选择 在SQL Server代理账户下运行，在“连接到订阅服务器”中，选择“使用以下SQL Server登录名”，输入：sa和密码。
12、如果你希望生成脚本，也可以勾选”生成包含创建发布的脚本文件“
13、输入发布名称：wc_publisher，然后下一步，点击完成，就发布成功了。
接下来，是设置订阅，这个是在虚拟机上设置的：
1、展开“复制”，右键“本地订阅”，选择“新建订阅”:
2、弹出新建订阅对话框，单击下一步：
3、在发布服务器下拉框中，选择“查找SQL Server发布服务器”：
然后，选择ggg-pc服务器，然后输入连接到ggg-pc的用户名和密码：
4、分发代理位置， 选择“在分发服务器ggg-pc上运行所有代理”：
5、选择：订阅服务器和订阅数据库：
6、设置分发代理安全性
点击按钮，在弹出的对话框中设置：“在SQL Server代理账户下运行”，在连接到订阅服务器上，选择“通过以下SQL Server登录名”，并输入连接到订阅服务器的：用户名和密码
7、同步计划，点击下一步
8、初始化订阅，单击下一步，最后点击完成
9、创建订阅成功
六、最后，验证一下数据是否同步
我们在ggg-pc的wc数据库的A表中插入数据：
- --插入数据
- insert into a
- values(1,'aaa')
然后，到虚拟机的wc数据库中，查询B表的数据：
如果不需要再进行数据库的复制，那么只需要直接在发布端进行删除就可以，主要就是删除复制目录-》本地发布-》发布的项目删除，然后，右键“复制”，选择“禁用本地发布”，就可以删除分发数据库distribution，会自动删除订阅端，不需要在订阅端进行删除。
Thursday, November 6, 2014
SETTING UP DATABASE MIRRORING (No Witness Server)
SETTING UP DATABASE MIRRORING (No Witness Server)
--* Recovery model must be Full.--* Set "Auto_Close" database property to False.
--* Configure MSSQLSERVER service accounts on Principal and Mirroring server. (Don't use NETWORKSERVICE...)
--* Firewall or Port(5022, 5023) is down to allow connection.
--* No need for SQL SERVER AGENT.
--1. Full backup the database
backup database testdb1
to disk = 'c:\data\testdb1.bak'
with init;
backup log testdb1
to disk = 'c:\data\testdb1_log.bak'
with init;
--2. restore backup on Mirroring server
restore database testdb1
from disk = 'c:\data\testdb1.bak'
with norecovery
,move 'testdb1_data' to 'c:\data\testdb1_data.mdf'
,move 'testdb1_log' to 'c:\data\testdb1_log.ldf'
go
restore log testdb1
from disk = 'c:\data\testdb1_log.bak'
with norecovery
go
--3. Start Mirroring on Principal Database
Database Properties \ Mirroring Page \ Configure Security ...
Choose Principal Server and Mirror Server, Endpoint names, Ports, Service Accounts. (Without Witness Server)
** Service Accounts for both servers should have enough permission.
** If Endpoint is not started by default, manually start it by SQL.
--4. When succeeded, you will see...
On principal database: testdb1 (Principal, Synchronized)
On morror database: testdb1 (Mirror, Synchronized / Restoring...)
FAILOVER
--Important SQL:
ALTER DATABASE <database_name> SET PARTNER FAILOVER ----切换主备
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS ---强制启用镜像数据库
*. To manually fail over database mirroring 数据库镜像会话期间的角色切换
--------------------------------------------------------------------------
Principal & Mirroring - Role switching. (When both are online.) 联机时的主备切换
A. <SSMS>
Right-click the PRINCIPAL database: Tasks -> Mirror,
Database Properties dialog box: Mirroring page -> [Failover]
B. <T-SQL> (on Principal server)
ALTER DATABASE <database_name> SET PARTNER FAILOVER
----Takes a few seconds. Very quick.
*. Force Service in a Database Mirroring Session 在数据库镜像会话中强制服务
----------------------------------------------------------------------------
Take mirror database online after principal server is gone. (Potential data loss may happen.)
Using following SQL on mirroring server.
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
--To test it,
1) Stop the MSSQLSERVER service on Principal server.
2) The Mirroring database statues: (Mirror, Disconnected / In Recovery).
Run T-SQL on Mirroring server:
ALTER DATABASE [testdb1] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
3) The Mirroring database statues: (In Recovery).
Run T-SQL:
RESTORE DATABASE [testdb1] WITH RECOVERY
4) Use SSMS to remove the Mirroring. (in Database properties) --Done.
PAUSE / RESUME Mirroring
-- Run either on Principal server or Mirror serverALTER DATABASE <database_name> SET PARTNER SUSPEND --暂停镜像服务
ALTER DATABASE <database_name> SET PARTNER RESUME --恢复镜像服务
--== REFERENCE ==--
--(English, US)
http://msdn.microsoft.com/en-CA/library/ms189850(v=sql.110).aspx#ManualFailover
--(简体中文)
http://msdn.microsoft.com/zh-cn/library/ms189850(v=sql.110).aspx#ForcedService
http://jimshu.blog.51cto.com/3171847/586956
Wednesday, November 5, 2014
DBCC SHOWCONTIG
DBCC SHOWCONTIG
--Run following code for example--
USE [AdventureWorks2012]
GO
DBCC SHOWCONTIG('Purchasing.ProductVendor')
-------------------------------------------------
DBCC SHOWCONTIG scanning 'ProductVendor' table...
Table: 'ProductVendor' (722101613); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 5
总页数
- Extents Scanned..............................: 3
总区数
- Extent Switches..............................: 2
区读取跳转次数
- Avg. Pages per Extent........................: 1.7
平均页数/区
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
扫描密度【最少区读取次数:实际区读取次数】(官方解释为区切换/跳转次数)
- Logical Scan Fragmentation ..................: 40.00%
乱序页
- Extent Scan Fragmentation ...................: 66.67%
乱序区
- Avg. Bytes Free per Page.....................: 1472.0
每页空余空间字节数
- Avg. Page Density (full).....................: 81.81%
每页已用空间量(%)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
TABLE level scan performed.
- Pages Scanned................................: 5
- Extent Switches..............................: 2
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 40.00%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
/********* Read The Result ************/
Data space: 0.039 MB
Page = 页 (8 KB)
Extent = 区 (8 Pages)
- Pages Scanned................................: 5
0.039 MB ==> 40 KB ==> "5" Pages is enough.
- Extents Scanned..............................: 3
5 pages in "3" extents.
- Extent Switches..............................: 2
Jump "2" times across 3 extents.
- Avg. Pages per Extent........................: 1.7
5 pages / 3 extents = "1.7"
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
***Important
Best Count: The minimal Extent Number that can hold all pages. There is only 5 pages. "1" extent is enough. Therefore, best read count is "1".
(最少区跳转次数。或者说，能装下所有Page的最少的Extent数，即最少的区读取次数。)
Actual Count: Extent Switches + 1 = "3". It is possible that "Actual Count > Extents Sanned". (means at least one extent has been read more than once.)
(实际区跳转次数。如果Page有乱序，则会导致区之间的跳转次数增加。有的区可能会被读取多次。)
- Logical Scan Fragmentation ..................: 40.00%
***(Important) The ratio of Pages with a different physical order from the logical order.
(有40%的Page是“乱放”的，即逻辑上的下一页数据，物理上没有放在隔壁页的位置，而放到别处去了。)
(打比方说，全家住旅馆时，你家人没住你隔壁房间，而被安排到远处的另一个房间去了。)
- Extent Scan Fragmentation ...................: 66.67%
***(Important) The ratio of Extents with a different physical order from the logical order.
(同理，有66.67%，即3个中有2个Extent是“乱放”的。)
- Avg. Bytes Free per Page.....................: 1472.0
The free/empty/unused/available/redundant/usable/spare/unspent/wasted space in a page.
- Avg. Page Density (full).....................: 81.81%
Here, [ (8192 - 1472) / 8192 ] * 100% ==> 81.81%
/********** Defragment ************/
EXEC sp_helpindex 'table_name' ---- check index name
ALTER INDEX index_name ON table_name REBUILD
http://msdn.microsoft.com/en-us/library/ms175008(v=sql.110).aspx
/**************************************************************/
------ Test --------
IF OBJECT_ID('ProInv', 'U') IS NOT NULL
DROP TABLE [ProInv];
GO
select ISNULL(ROW_NUMBER() OVER(ORDER BY [ProductID],[LocationID]), -1 )as RowNum, *
into ProInv
from [AdventureWorks2012].[Production].[ProductInventory]
----(1069 row(s) affected)
dbcc showcontig('ProInv')
--------------------------------
DBCC SHOWCONTIG scanning 'ProInv' table...
Table: 'ProInv' (1973582069); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 8
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 2.0
- Scan Density [Best Count:Actual Count].......: 25.00% [1:4]
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 468.0
- Avg. Page Density (full).....................: 94.22%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
alter table ProInv add constraint PK_rn Primary key (RowNum);
dbcc showcontig('ProInv')
--------------------------------
DBCC SHOWCONTIG scanning 'ProInv' table...
Table: 'ProInv' (1973582069); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 9
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 2.3
- Scan Density [Best Count:Actual Count].......: 50.00% [2:4]
- Logical Scan Fragmentation ..................: 44.44%
- Extent Scan Fragmentation ...................: 75.00%
- Avg. Bytes Free per Page.....................: 1315.6
- Avg. Page Density (full).....................: 83.75%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
alter index PK_rn on ProInv REBUILD;
dbcc showcontig('ProInv')
--------------------------------
DBCC SHOWCONTIG scanning 'ProInv' table...
Table: 'ProInv' (1973582069); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 9
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 1.8
- Scan Density [Best Count:Actual Count].......: 40.00% [2:5]
- Logical Scan Fragmentation ..................: 66.67%
- Extent Scan Fragmentation ...................: 60.00%
- Avg. Bytes Free per Page.....................: 1315.6
- Avg. Page Density (full).....................: 83.75%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
/*
囧.....
I found this...
https://connect.microsoft.com/SQLServer/feedback/details/244214/index-rebuild-doesnt-affect-fragmentation
Description
We've got a few small tables (~1000) rows that have a high degree of fragmentation. When we run ALTER INDEX indexname ON schema.tablename REBUILD, no change happens in the fragmentation.
Posted by Microsoft on 12/8/2006 at 4:03 PM
This is by-design.
For small tables, usually performance impact on fragmentation is undectable. The first 8 page allocation would be from mixed extents and mixed extents could be anywhere in database files. Rebuilding indexes would not change this nature.
If you have a small table, those mixed pages weight a lot during fragmentation calculation; therefore, rebuilding index may not reduce fragmentation. (As matter of fact, I could easily construct a case that fragmentation increases after rebuild.) Those fragmentation would not be a pain for your query performance; so basically you can ingore them.
When page counts of an index reaches to certain big size (for example, 1000 pages), then fragmentation may start to impact performance. Rebuilding index should reduce fragmentation.
Another thing to consider is that how high the fragmentation is. If it is < 10%, it is hard for rebuilding to reduce more (we never could 100% guarantee that you can reach 0% fragmentation; with mixed page allocation, it is a hard goal to achieve).
If you don't care about space utilization, you can use undocumented trace flag 1118 to disable mixed page allocation, but I am not recommend it (this trace flag usually is used in tempdb for reducing SGAM contention).
*/
--Run following code for example--
USE [AdventureWorks2012]
GO
DBCC SHOWCONTIG('Purchasing.ProductVendor')
-------------------------------------------------
DBCC SHOWCONTIG scanning 'ProductVendor' table...
Table: 'ProductVendor' (722101613); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 5
总页数
- Extents Scanned..............................: 3
总区数
- Extent Switches..............................: 2
区读取跳转次数
- Avg. Pages per Extent........................: 1.7
平均页数/区
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
扫描密度【最少区读取次数:实际区读取次数】(官方解释为区切换/跳转次数)
- Logical Scan Fragmentation ..................: 40.00%
乱序页
- Extent Scan Fragmentation ...................: 66.67%
乱序区
- Avg. Bytes Free per Page.....................: 1472.0
每页空余空间字节数
- Avg. Page Density (full).....................: 81.81%
每页已用空间量(%)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
-- Messages (WITH FAST)
-------------------------------------------------
DBCC SHOWCONTIG scanning 'ProductVendor' table...
Table: 'ProductVendor' (722101613); index ID: 1, database ID: 5TABLE level scan performed.
- Pages Scanned................................: 5
- Extent Switches..............................: 2
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 40.00%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
/********* Read The Result ************/
Data space: 0.039 MB
Page = 页 (8 KB)
Extent = 区 (8 Pages)
- Pages Scanned................................: 5
0.039 MB ==> 40 KB ==> "5" Pages is enough.
- Extents Scanned..............................: 3
5 pages in "3" extents.
- Extent Switches..............................: 2
Jump "2" times across 3 extents.
- Avg. Pages per Extent........................: 1.7
5 pages / 3 extents = "1.7"
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
***Important
Best Count: The minimal Extent Number that can hold all pages. There is only 5 pages. "1" extent is enough. Therefore, best read count is "1".
(最少区跳转次数。或者说，能装下所有Page的最少的Extent数，即最少的区读取次数。)
Actual Count: Extent Switches + 1 = "3". It is possible that "Actual Count > Extents Sanned". (means at least one extent has been read more than once.)
(实际区跳转次数。如果Page有乱序，则会导致区之间的跳转次数增加。有的区可能会被读取多次。)
- Logical Scan Fragmentation ..................: 40.00%
***(Important) The ratio of Pages with a different physical order from the logical order.
(有40%的Page是“乱放”的，即逻辑上的下一页数据，物理上没有放在隔壁页的位置，而放到别处去了。)
(打比方说，全家住旅馆时，你家人没住你隔壁房间，而被安排到远处的另一个房间去了。)
- Extent Scan Fragmentation ...................: 66.67%
***(Important) The ratio of Extents with a different physical order from the logical order.
(同理，有66.67%，即3个中有2个Extent是“乱放”的。)
- Avg. Bytes Free per Page.....................: 1472.0
The free/empty/unused/available/redundant/usable/spare/unspent/wasted space in a page.
- Avg. Page Density (full).....................: 81.81%
Here, [ (8192 - 1472) / 8192 ] * 100% ==> 81.81%
/********** Defragment ************/
EXEC sp_helpindex 'table_name' ---- check index name
ALTER INDEX index_name ON table_name REBUILD
http://msdn.microsoft.com/en-us/library/ms175008(v=sql.110).aspx
/**************************************************************/
------ Test --------
IF OBJECT_ID('ProInv', 'U') IS NOT NULL
DROP TABLE [ProInv];
GO
select ISNULL(ROW_NUMBER() OVER(ORDER BY [ProductID],[LocationID]), -1 )as RowNum, *
into ProInv
from [AdventureWorks2012].[Production].[ProductInventory]
----(1069 row(s) affected)
dbcc showcontig('ProInv')
--------------------------------
DBCC SHOWCONTIG scanning 'ProInv' table...
Table: 'ProInv' (1973582069); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 8
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 2.0
- Scan Density [Best Count:Actual Count].......: 25.00% [1:4]
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 468.0
- Avg. Page Density (full).....................: 94.22%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
alter table ProInv add constraint PK_rn Primary key (RowNum);
dbcc showcontig('ProInv')
--------------------------------
DBCC SHOWCONTIG scanning 'ProInv' table...
Table: 'ProInv' (1973582069); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 9
- Extents Scanned..............................: 4
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 2.3
- Scan Density [Best Count:Actual Count].......: 50.00% [2:4]
- Logical Scan Fragmentation ..................: 44.44%
- Extent Scan Fragmentation ...................: 75.00%
- Avg. Bytes Free per Page.....................: 1315.6
- Avg. Page Density (full).....................: 83.75%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
alter index PK_rn on ProInv REBUILD;
dbcc showcontig('ProInv')
--------------------------------
DBCC SHOWCONTIG scanning 'ProInv' table...
Table: 'ProInv' (1973582069); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 9
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 1.8
- Scan Density [Best Count:Actual Count].......: 40.00% [2:5]
- Logical Scan Fragmentation ..................: 66.67%
- Extent Scan Fragmentation ...................: 60.00%
- Avg. Bytes Free per Page.....................: 1315.6
- Avg. Page Density (full).....................: 83.75%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
/*
囧.....
I found this...
https://connect.microsoft.com/SQLServer/feedback/details/244214/index-rebuild-doesnt-affect-fragmentation
Description
We've got a few small tables (~1000) rows that have a high degree of fragmentation. When we run ALTER INDEX indexname ON schema.tablename REBUILD, no change happens in the fragmentation.
Posted by Microsoft on 12/8/2006 at 4:03 PM
This is by-design.
For small tables, usually performance impact on fragmentation is undectable. The first 8 page allocation would be from mixed extents and mixed extents could be anywhere in database files. Rebuilding indexes would not change this nature.
If you have a small table, those mixed pages weight a lot during fragmentation calculation; therefore, rebuilding index may not reduce fragmentation. (As matter of fact, I could easily construct a case that fragmentation increases after rebuild.) Those fragmentation would not be a pain for your query performance; so basically you can ingore them.
When page counts of an index reaches to certain big size (for example, 1000 pages), then fragmentation may start to impact performance. Rebuilding index should reduce fragmentation.
Another thing to consider is that how high the fragmentation is. If it is < 10%, it is hard for rebuilding to reduce more (we never could 100% guarantee that you can reach 0% fragmentation; with mixed page allocation, it is a hard goal to achieve).
If you don't care about space utilization, you can use undocumented trace flag 1118 to disable mixed page allocation, but I am not recommend it (this trace flag usually is used in tempdb for reducing SGAM contention).
*/
Subscribe to:
Posts (Atom)