"Auto update statistics" option on tempdb

"Auto update statistics" option is enabled by default on tempdb, if it is disabled, you may get trouble in some case.Today when I tested script, I found the index created on the temp table didn't work because of "Auto update statistics" disabled.

here is the script.

USE [master]
GO
ALTER DATABASE [tempdb] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT
GO
USE [tempdb]
GO
dbcc freeproccache
GO
Create table #mytemp1(a int, b int)
GO
create  index temp_inx1 on #mytemp1(a)
GO
declare @int int
set @int=1
while @int <10000
begin
insert into #mytemp1 values(@int, @int+1)
--insert into #mytemp2 values(@int, @int+1)
set @int+=1
end

GO
set statistics profile on
GO
select b from #mytemp1 where a=50
GO
set statistics profile off
GO

and you can see the script use table scan instead of index seek, because we created an index on a, table scan is not we wanted.

let's enable the "Auto update statistics", and do the test again

USE [master]

GO

ALTER DATABASE [tempdb] SET AUTO_UPDATE_STATISTICS on WITH NO_WAIT

GO

USE [tempdb]

GO

dbcc freeproccache

GO

Create table #mytemp1(a int, b int)

GO

create  index temp_inx1 on #mytemp1(a)

GO

declare @int int

set @int=1

while @int <10000

begin

insert into #mytemp1 values(@int, @int+1)

--insert into #mytemp2 values(@int, @int+1)

set @int+=1

end

GO

set statistics profile on

GO

select b from #mytemp1 where a=50

GO

set statistics profile off

GO

drop table #mytemp1

this time we got index seek, this is because:

when "Auto update statistics" is enabled, sql server will check if statistics is stall before generation query plan. since we inserted 1000 rows after index created, the statistics is stall. then sql server will update the statistics, so sql server can get correct statistics and select index seek instead of table scan as query plan.

if you are working with big temp table object, be careful for the "Auto update statistics" on tempdb. 

Got "Cannot generate SSPI context" error message after changing sql service account

Today I changed a sql server service startup account during testing, then when I tried to connect sql server with powershell, I got error message below:

The target principal name is incorrect.  Cannot generate SSPI context."


Here is the troubleshooting step I used
1. Rollback service startup account to old one, then the powershell script work. so it should be the issue with the new account.

2. Start sql server service again with new account. check sql server error log, then found error message in it


2013-01-20 00:53:36.49 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

the error indicted sql server service can not register SPN during startup.

3. search the error code 0x2098, found link below

http://msdn.microsoft.com/en-us/library/ms681390(VS.85).aspx

ERROR_DS_INSUFF_ACCESS_RIGHTS

8344 (0x2098)

Insufficient access rights to perform the operation.

4. grant "Read servicePrincipalName" and "Write servicePrincipalName" permission to new account.

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

for window 2012 AD, it is a little bit different

1. Click Start, click Run, type Adsiedit.msc, and then click OK.
2. In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName, and then click Properties. 
3. In the CN= AccountName Properties dialog box, click the Security tab.
4. On the Security tab, click Advanced.
5. In the Advanced Security Settings dialog box, select one of "SELF"'s row just like the pic below

     6. Click Edit, Open Permission Entry dialog box.

     7. Make sure Pricipal is "SELF", Type is "Allow" and "Applied to" is "This Object Only", in Properties section, select the properties below:

Read servicePrincipalName

Write servicePrincipalName

     8. Click OK three times, and then exit the ADSI Edit snap-in.

after restart the sql server service, make sure the spn resgister correctly:

2013-01-20 03:08:27.20 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/machinename.domainname.com ] for the SQL Server service. 

Row of nonclustered index page

Below is the formula of calculating the row size of non-clustered index page

so unique non-clustered index created on unique clustered index use smallest space, while non-unique non-clustered index created on non-unique clustered index use largest space.

Heap might take more space than clustered Index when inserting row

Sometimes when you insert row on heap, even if the page has enough free space, the new row can not be inserted into that page, and a new page will be created for the new row. Here is interesting example:

1. Create testing DB

use master
go
CREATE DATABASE [test] ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB)
GO

2. Create table

CREATE TABLE myHeap
(
a char(500) NOT NULL,
b char(100) NOT NULL,
c char(153) NOT NULL,
)  ON [PRIMARY]
GO

so the row length will be 753+7=760 bytes, in theory, the every page can hold 10 rows(8060/760=10)

3, insert 10 rows

Declare @int int

set @int=1

while (@int <= 10)

begin

insert into myHeap(a,b,c)

values(right('00000'+CONVERT(varchar(5),@int),5),'bbbbb','ccccc')

set @int=@int+1

end

4. check the table page status

SELECT %%physloc%% rowid, sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID],DATALENGTH(a) a_length, DATALENGTH(b) b_length,DATALENGTH(c) d_length, * 

FROM test.dbo.myHeap 

rowid	Physical RID	a_length	b_length	d_length
0x9900000001000000	(1:153:0)	500	100	153
0x9900000001000100	(1:153:1)	500	100	153
0x9900000001000200	(1:153:2)	500	100	153
0x9900000001000300	(1:153:3)	500	100	153
0x9900000001000400	(1:153:4)	500	100	153
0x9900000001000500	(1:153:5)	500	100	153
0x9900000001000600	(1:153:6)	500	100	153
0x9900000001000700	(1:153:7)	500	100	153
0x9900000001000800	(1:153:8)	500	100	153
0x9B00000001000000	(1:155:0)	500	100	153

in fact, the first 9 rows are in page 153, however, the 10th row are in the other page 155.

4. let's check how much free space is in page 153

DBCC TRACEON(3604)

GO

dbcc page(test,1,153,3) 

GO

m_pageId = (1:153)                   m_headerVersion = 1                  m_type = 1

m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000

m_objId (AllocUnitId.idObj) = 27     m_indexId (AllocUnitId.idInd) = 256  

Metadata: AllocUnitId = 72057594039697408                                 

Metadata: PartitionId = 72057594038779904                                 Metadata: IndexId = 0

Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)

pminlen = 757                        m_slotCnt = 9                        m_freeCnt = 1238

m_freeData = 6936                    m_reservedCnt = 0                    m_lsn = (32:86:2)

m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0

m_tornBits = 0                       

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               

PFS (1:1) = 0x63 MIXED_EXT ALLOCATED  95_PCT_FULL                         DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED    

Slot 0 Offset 0x60 Length 760

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 760

well, there is 1238 bytes free space, the row size is 760 byte, so there is enough free space to hold the 10th rows. why sql server create a new page to keep 10th row??? here we found a interesting info in the page head : PFS indicts the page is 95% full, so there is only 8060*5%=403 bytes free space.

let's review how sql server insert rows on Heap, because there is no clustered index, a new row is always inserted wherever room is available in the heap table. sql server will use IAMs and the PFS pages to keep track of which extents in ta file already belong to a table and which of the pages in those extens have space available. 

5. let's check the PFS page:

DBCC TRACEON(3604)

GO

dbcc page(test,1,1,3) 

GO

(1:153)      -              =     ALLOCATED  95_PCT_FULL                     Mixed Ext

The PFS page contains 1 byte for each page in a 8088-age range of a file, and the last three bits are used to indicate the age fullness. 

The bits in each byte has some meaning as illustrated below

• bits 0-2: how much free space is on the page
  • 000 is empty
  • 001 is 1 to 50% full
  • 010 is 51 to 80% full
  • 011 is 81 to 95% full
  • 100 is 96 to 100% full
• bit 3 : 1 if page has ghost records else 0
• bit 4 : 1 if an IAM page else 0
• bit 5 : 1 if mixed extent, 0 if uniform
• bit 6 : 1 if page allocated, 0 if unallocated

we have 9 rows in page 153, 9*760/8060 =84.9% full, so PFS indicts it is 95% full. when sql server check page 153, it think the free space on page 153 is 8060*5%=403 bytes < row size 760bytes, so a new page 156 was created for 10th row.

6. In order to save space and keep the 10 rows in the same page, we can create a clustered index, 

ALTER TABLE dbo.myheap ADD CONSTRAINT

PK_mytest PRIMARY KEY CLUSTERED 

(

a

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

then we can find all 10 rows are in the same page

SELECT %%physloc%% rowid, sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID],DATALENGTH(a) a_length, DATALENGTH(b) b_length,DATALENGTH(c) d_length, * 

FROM test.dbo.myHeap 

rowid	Physical RID	a_length	b_length	d_length
0x9C00000001000000	(1:156:0)	500	100	153
0x9C00000001000100	(1:156:1)	500	100	153
0x9C00000001000200	(1:156:2)	500	100	153
0x9C00000001000300	(1:156:3)	500	100	153
0x9C00000001000400	(1:156:4)	500	100	153
0x9C00000001000500	(1:156:5)	500	100	153
0x9C00000001000600	(1:156:6)	500	100	153
0x9C00000001000700	(1:156:7)	500	100	153
0x9C00000001000800	(1:156:8)	500	100	153
0x9C00000001000900	(1:156:9)	500	100	153

DBCC TRACEON(3604)

GO

dbcc page(test,1,156,3) 

GO

m_pageId = (1:156)                   m_headerVersion = 1                  m_type = 1

m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x4

m_objId (AllocUnitId.idObj) = 28     m_indexId (AllocUnitId.idInd) = 256  

Metadata: AllocUnitId = 72057594039762944                                 

Metadata: PartitionId = 72057594038845440                                 Metadata: IndexId = 1

Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (0:0)

pminlen = 757                        m_slotCnt = 10                       m_freeCnt = 476

m_freeData = 7696                    m_reservedCnt = 0                    m_lsn = (32:93:21)

m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0

m_tornBits = 0                       

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED

this example is one of the reasons why clustered index is recommended on big table

reference

http://sqlsimplified.blogspot.com/2012/01/page-free-space.html