Page contention - what's the page?

Question

Page contention - what's the page?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

N.D (4/22/2015)
In sysdatabases the following are listed
snapshot_isolation_state_desc = ON
is_read_committed_snapshot_on = 1
This setting was recommended by the vendor

Ah, that would explain why you have a silly combination of settings. 🙂

Do you ever use SET TRANSACTION ISOLATION LEVEL SNAPSHOT? If not, then you're incurring overhead with no associated benefit

I also have some further news about the page contention - we have a ridiculous number of cores on this server (80), so had a go at adding some more tempdb files (from 8 to 16)

Why? You said multiple times that the page latch waits were not in TempDB, so why are you adding files to TempDB?

Edit: Page latch waits, not patch waits. 🙁

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

N.D Ten Centuries Points: 1132 More actions · Answer 2

Why? You said multiple times that the patch waits were not in TempDB, so why are you adding files to TempDB?

Reasons beginning with the letter P ... :rolleyes:

I'll go away and have a look at the isolation settings, thanks for your help with this so far - learned some new things 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

N.D (4/22/2015)
Why? You said multiple times that the patch waits were not in TempDB, so why are you adding files to TempDB?
Reasons beginning with the letter P ... :rolleyes:

?? Sorry, not following.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 233948 More actions · Answer 4

N.D (4/22/2015)
I have some more information -
The queries that are suffering from contention are selects. The db ID in sysprocesses definitely shows as the user database and we're now using a script that identifies them as GAM pages
We do have snapshot isolation enabled and read committed snapshot on

Is it a straight select or does it create a table from the select

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

N.D Ten Centuries Points: 1132 More actions · Answer 5

Hi - straight selects, no temporary table / CTEs or anything like that