MSSQLWIKI

Karthick P.K on SQL Server

Archive for the ‘Space management’ Category

Tempdb latch contention

Posted by Karthick P.K on September 17, 2013

You might see Page latch contention in tempdb when you repeatedly drop and create TempDb objects (Temp tables, table variables etc.).

When you notice PAGELATCH_* contention on tempdb (Wait resource in sysprocesses starts with 2: ) check if the latch wait is on PFS,GAM or SGAM page. When there is latch contention on tempdb you will see lot of sessions waiting on Pagelatch_* similar to one below.

In the below output session is waiting on resource 2:15:121320 . If we decode the wait resource it is 2: database id of tempdb ,  15: file number , 121320 is page number. 121320 is in multiple of 8088 so it is a PFS page, similarly identify if the page we are waiting is GAM or SGAM page if it is not PFS page.

Wait type                            Wait resource

PAGELATCH_UP               2:15:121320

PAGELATCH_UP               2:15:121320

How to identify if page is PFS,GAM or IAM?

PFS Page: A PFS page occurs once in 8088 pages. SQL Server will attempt to place a PFS page on the first page of every PFS interval(8088Pages). The only time a PFS page is not the first page in its interval is in the first interval for a file. File header page is first, and the PFS page is second. (Page ID starts from 0 so the first PFS page is at Page ID 1). If (page number)/8088 is round value then the page is PFS page.

GAM Page: GAM page is page 2 in the data file, next GAM page is placed at 511230 Page after first GAM page (GAM interval). If (page number-1)/511230 is round value then the page is GAM page.

SGAM Page: SGAM page is page 3 in data file , next SGAM page is placed at 511230 Page after first SGAM page. If (page number-2)/511230 is round value then the page is GAM page.

clip_image002

How to resolve?

1. Increase the number of TEMPDB data files files and size them equally. As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues further increase the number of data files by multiples of 4 (You may not see improvement once you reach 32 files). 

2. Enable server side trace flag 1118.

3. If you further see latch contention on PFS  page after following above two steps then the only option is to modify your application to limit the tempdb usage.

4. If you see contention on 2:1:103 (Page 103 is for system table sys.sysmultiobjrefs. This table manages the relationship between created objects in every database). The only way to reduce contention on this page is reduce the relation. Example creating lot of temp tables with primary key can cause this contention because the relation between the table and PK constraint has to be updated in sys.sysmultiobjrefs.

What’s the best practice ?

1. Create multiple tempdb data files instead of creating 1 large file and size them equally in all your SQL Server instances.

2. Make TF1118 (Uniform allocation) as default. (Extra space required by this trace flag shouldn’t really matter as amount additional space required is minimal and storage cost is not that high these days).  

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights

Posted in Performance, Space management, SQL General, SQL Server Engine | Tagged: , , , , | 1 Comment »

Transaction log for the database is growing and system SPID is holding open transaction

Posted by Karthick P.K on May 18, 2012

Transaction log for the database is growing and system SPID is holding open transaction

Select log_reuse_wait_desc From Sys.databases where name=’%’ will return

‘ACTIVE_TRANSACTION’

DBCC Opentran says there is a Opentran held by system thread (Similar to SPID (server process ID): 7s and 6s in below example)

Oldest active transaction:

SPID (server process ID): 7s

UID (user ID) : -1

Name : user_transaction

LSN : (543263:28204:1)

Start time : Dec 8 2011 11:02:19:483PM

SID : 0x01

Replicated Transaction Information:

Oldest distributed LSN : (544101:227459:27)

Oldest non-distributed LSN : (543263:28204:1)

Oldest active transaction:

SPID (server process ID): 6s

UID (user ID) : -1

Name : tran_sp_MScreate_peer_tables

LSN : (958510:111529:1)

Start time : May 6 2012 5:33:37:240AM

SID : 0x01

Replicated Transaction Information:

Oldest distributed LSN : (962272:93878:5)

Oldest non-distributed LSN : (0:0:0)

If we see Name: tran_sp_MScreate_peer_tables in DBCC opentran

Follow http://support.microsoft.com/kb/2509302 ie. Restart SQL-Server and wait for Script upgrade to complete and then start the SQL Server Agent.

If you see Name : user_transaction then script upgrade for database has failed leaving an open transaction. Verify the SQL Server error log to identify why the script upgrade has failed.

We can enable Trace flag -T3601 which causes the first 512 characters of each batch being executed to be printed to the error log while doing script upgrade . Identify the batch which is failing and troubleshoot the batch.

For more information Follow : https://mssqlwiki.com/2010/11/17/sqlserver2008-script-level-upgrade-for-database-master-failed-because-upgrade-step-sqlagent100_msdb_upgrade-sql-encountered-error-574-state-0-severity-16/

Thanks

Karthick P.K

 

Posted in Space management, SQL Server Engine, Startup failures | Tagged: , , , , , | 6 Comments »

How to move the LOB data from one file group to other?

Posted by Karthick P.K on January 17, 2012

 

We do not have a direct way to move the LOB data from one file group to other. Using ALTER TABLE and/or CREATE INDEX to support moving LOB data is unavailable till current version of  SQL (SQL Server 2008).

 

Only way to move the LOB data is to

1. create new table in new file group

2. Move the data from existing table to new table.

3. Drop the existing table.

4. Change the name of new table to Old table.

Management studio has easy way to create script for all the above task.

1. In management studio Right click the table –>Design –>change the file group in properties windows (Click View—> properties window if you do not see properties window)

2. Generate Change Script.

clip_image002

3. Script similar to following script is generated.

4. Copy the script and run in Query window.

 

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_BLOB_TABLE

                (

                BLOBName varchar(100) NULL,

                BLOBData varbinary(MAX) NULL

                )  ON [PRIMARY]

                TEXTIMAGE_ON Lob2

GO

ALTER TABLE dbo.Tmp_BLOB_TABLE SET (LOCK_ESCALATION = TABLE)

GO

IF EXISTS(SELECT * FROM dbo.BLOB_TABLE)

                EXEC(‘INSERT INTO dbo.Tmp_BLOB_TABLE (BLOBName, BLOBData)

                                SELECT BLOBName, BLOBData FROM dbo.BLOB_TABLE WITH (HOLDLOCK TABLOCKX)’)

GO

DROP TABLE dbo.BLOB_TABLE

GO

EXECUTE sp_rename N’dbo.Tmp_BLOB_TABLE’, N’BLOB_TABLE’, ‘OBJECT’

GO

COMMIT

 

Thanks

Karthick P.K

Posted in Space management, SQL General, SQL Server Tools | Tagged: | 34 Comments »