SQL Server索引统计信息未及时更新,导致排序混乱
今天,遇到了这样的一个例子:
SQL Server索引统计信息未及时更新,导致排序混乱
我们知道,在sql
server上创建索引后,同时会对该索引上的值进行排序,但对于新增加的值,如果未能及时更新统计信息,将有可能导致排序的混乱,也就是没有排序。
这是产生问题的语句:
Select * from V_L_IcStockProInEntry Where FInterID= '329482'
V_L_IcStockProInEntry是一个视图,其语句如下:
SELECT t2.FInterID, t2.FSeq,
t2.FItemID, t2.FMustQty, t2.FQty
from dbo.IcStockProIn AS t1 INNER JOIN
dbo.
IcStockProInEntry
AS t2 ON t1.FInterID = t2.FInterID LEFT OUTER JOIN
dbo.t_IcItem AS t3 ON t2.FItemID = t3.FItemID LEFT OUTER JOIN
dbo.IcProductTrace AS d ON t2.FTraceInterid = d.FInterID LEFT OUTER
JOIN
dbo.t_IcItemPrimary AS e ON d.FMainItemID = e.FItemID LEFT OUTER JOIN
dbo.t_EventType AS t10 ON t2.FQulityID = t10.FID LEFT OUTER JOIN
dbo.t_Customer AS h ON t2.FCustID = h.FID LEFT OUTER JOIN
dbo.V_WorkItem AS i ON t2.FStWorkItemID = i.FitemID LEFT OUTER JOIN
dbo.IcShopMo
AS G ON
G.FinterID = t2.FSourceInterID
查询的结果是finterid对应的多个fseq值未能正常排序。
finterid和fseq
属于icstockproinentry表的列,在这两列上有个聚集索引,所以会自动的排序。但由于数据更新较快,新插入的值未能及时有效排序,所以导致
了排序的混乱。
首先来看下统计信息:
dbcc show_statistics('IcStockProInEntry',PK_IcStockProInEntry)
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS
AVG_RANGE_ROWS
3 0 1 0 1
...
....
282987 101155 6 27942 3.620178
288803 18440 1 5253 3.510375
296886 24587 6 &nb
相关文档:
一个简单的例子:
先建一个C#类:
引用System.Data.Linq.dll程序集,
using System.Data.Linq.Mapping和
using System.Data.Linq 两个空间。
[Table]
public class Inventory
{
[Column]
public string Make;
[Column]
public string Color;
&nbs ......
1 避免无计划的全表扫描
如下情况进行全表扫描:
- 该表无索引
- 对返回的行无人和限制条件(无Where子句)
- 对于索引主列(索引的第一 ......
注释:只适合单表单列数据,
create database test
go
use test
go
create table users
(
:id int identity(1,1) primary key not null,
:name nvarchar(20)
)
go
create proc sp_Inserts
@Names nvarchar(4000)
as
declare @Name nvarchar(20),@ErrorSum int
:set @ErrorSum = 0
:begin tra ......
Use DatabaseName
--DB shrink
--获取database 空余空间, 决定是否作shrinkDB
exec [DBNAME].dbo.sp_spaceused
DBCC ShrinkDB(DBNAME)
--Log file shrink
Use DatabaseName
GO
Alter Database DatabaseName Set Recovery Simple
GO
Alter Database DatabaseName Set Recovery Full
GO
DBCC SHRINKFILE ('Log ......