April 22, 2015 at 6:06 am
N.D (4/22/2015)
In sysdatabases the following are listedsnapshot_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
April 22, 2015 at 7:01 am
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 🙂
April 22, 2015 at 7:10 am
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
April 23, 2015 at 1:38 pm
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" 😉
April 24, 2015 at 3:16 am
Hi - straight selects, no temporary table / CTEs or anything like that
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply