SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unexplained Performance Observation


Unexplained Performance Observation

Author
Message
BrerSQL
BrerSQL
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 578
This is occurring on SQL 2012 but I do not think it is specific to 2012. I have also observed this on our 2005 servers prior to our upgrade to 2012 upgrade in June.

I have a highly active session database that manages application session.
I know it is not a good practice to manage application sessions in SQL Server but for now lets ignore that.

Basically there is one extremely active table, here is the definition...

CREATE TABLE [dbo].[Session](
[SessionID] [varchar](100) NOT NULL,
[CreateDate] [datetime] NOT NULL,
[ExpireDate] [datetime] NOT NULL,
[LockDate] [datetime] NULL,
[LockID] [int] NOT NULL,
[Timeout] [int] NULL,
[Locked] [tinyint] NULL,
[SessionItems] [varchar](max) NULL,
[Flags] [int] NULL,
[MachineName] [varchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[SessionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



There is one non-clustered index on ExpireDate
I have two procedures that perform massive amounts of updates to this table (each over 6 million times per day).
One update simply locks a session rows before returning information back to the caller (UpdateA):
UPDATE Session 
SET Locked = 1,
LockDate = @Now
WHERE SessionID = @SessionID
AND Locked = 0



The other physically updates session info etc (UpdateB)...
UPDATE Session 
SET ExpireDate = DATEADD(mi, @TimeoutMinutes, @Now),
SessionItems = @SessionItems,
Locked = 0,
MachineName = @MachineName
WHERE SessionID = @SessionID
AND LockID = @LockID



Both updates are executed about the same number of times per day.
Judging by the nature of the updates, I would predict with relative confidence, that UpdateB would be the more expensive update given that it is performing an EXPENSIVE update by updating a varchar MAX, a Varchar(300) and updating a non-clustered index column.
UpdateA is only updating two fields which should result in an in-place update.

We have Confio Ignite installed on our server and we have been observing the exact opposite.
The execution plans do not look bad yet we are seeing a DRASTIC difference in WriteLog waits where updateA is much worse than updateB.

Can anyone provide a logical explanation for this?
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31408 Visits: 8670
1) Sorry, but I cannot ignore having session state stored in SQL Server. w00t

2) You have a varchar(100) as your clustered index. Does it look like a GUID by any chance? If not, does it have new values throughout the range of characters? If so, you are likely getting page splits all over the place during inserts. You are also carrying that fat field as the pointer on the NC index too, bloating it

3) You are very likely NOT getting in-place updates on your first update. Both fields are NULLABLE and as such when you give them a value for that update they become LARGER. Page split potential again. And for that you are moving about 4K of data to a new page.

4) All of the above is exacerbated by having a defaulted fill factor. Bad news there.

5) Have you done any analysis to determine if the second update actually increases field sizes much if at all? Review log file records to see what is really getting written there. ApexSQL has some nice capabilities for that, and there is the semi-documented fn_dblog command you can use as well.

6) I would analyze page splits and fragmentation and adjust fill factor(s) as needed.

7) Consider removing all NULLs and using appropriate DEFAULTs if you can.


Above all else I would move my session storage to an appropriate system. :-D

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5062 Visits: 875
TheSQLGuru (8/22/2013)
3) You are very likely NOT getting in-place updates on your first update. Both fields are NULLABLE and as such when you give them a value for that update they become LARGER.


Yes, if the table is compressed that would be true. But else not. Fixed-length data types like tinyint and datetime take up the same amount of space, no matter they are NULL or not.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
BrerSQL
BrerSQL
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 578
TheSQLGuru (8/22/2013)
1) Sorry, but I cannot ignore having session state stored in SQL Server. w00t

2) You have a varchar(100) as your clustered index. Does it look like a GUID by any chance? If not, does it have new values throughout the range of characters? If so, you are likely getting page splits all over the place during inserts. You are also carrying that fat field as the pointer on the NC index too, bloating it

3) You are very likely NOT getting in-place updates on your first update. Both fields are NULLABLE and as such when you give them a value for that update they become LARGER. Page split potential again. And for that you are moving about 4K of data to a new page.

4) All of the above is exacerbated by having a defaulted fill factor. Bad news there.

5) Have you done any analysis to determine if the second update actually increases field sizes much if at all? Review log file records to see what is really getting written there. ApexSQL has some nice capabilities for that, and there is the semi-documented fn_dblog command you can use as well.

6) I would analyze page splits and fragmentation and adjust fill factor(s) as needed.

7) Consider removing all NULLs and using appropriate DEFAULTs if you can.


Above all else I would move my session storage to an appropriate system. :-D




1 - yes...unfortunately thats the way it is for now. On the brighter side, there is a project in motion to move it.

2 - yes it is a GUID and yes we are definitely getting page spits. This table is extremely un-healthy, at it mature state it basically works at a little better than one row per page. Yes you are correct inserts are page splitting as I would expect. But, my question deals with the updates...why updateA would perform worse than updateB?

3 - Yes agreed, but these rows will be updated thousands of times before either timing out or being logged out. The null values should only be present until the first update. Every update after that should be in-place. Unless I am misunderstanding something.

4 - I'm not sure there is a good fill factor for a table of this nature. There is definitely session bloat present. 7400 bytes average per session with a max of 50,000 bytes.

5 - yes, the SessionItems is a volatile column that will fluctuate with size so my assumption is that the second update is truly an EXPENSIVE update.

6 - What would you suggest for fill factor on a table like this? 100%?

7 - This would be a good start.

Thank You for the response
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5062 Visits: 875
[quote]Eric1/2aB (8/26/2013)
6 - What would you suggest for fill factor on a table like this? 100%?{/quote]

For indexes on GUIDs, an idea is to have a low fill factor, say 50%. New guids will fill in the holes, and reduce the amount of page split. As the pages start to get full, page splits will occur, so you should rebuild in due time.

It will mean a waste of empty space in the buffer cache, though.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31408 Visits: 8670
Erland Sommarskog (8/26/2013)
[quote]Eric1/2aB (8/26/2013)
6 - What would you suggest for fill factor on a table like this? 100%?{/quote]

For indexes on GUIDs, an idea is to have a low fill factor, say 50%. New guids will fill in the holes, and reduce the amount of page split. As the pages start to get full, page splits will occur, so you should rebuild in due time.

It will mean a waste of empty space in the buffer cache, though.


I concur with Erland. I usually use somewhere between 50 and 70% fill factor for indexes at clients that lead off with a non-sequential GUID, depending on the index defrag interval and how fragmented stuff gets between intervals. I like to see you just hitting 5 or maybe 10% frag when you hit your index mx run. That seems to give a balance between having pages start out (and spend too much time) too empty and splitting pages/extents all over the place (with the corresponding tlog activities and other negative frag effects).

Speaking of sequential GUIDs, some systems can be configured to create them on the generating server. I HIGHLY recommend seeing if your systems can do that. HUGE win on many fronts if you can avoid full-range GUID values. Having 3 or 10 places where ranges split is way better than the alternative I think.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
BrerSQL
BrerSQL
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 578
Erland and Kevin...this is great information.
I will definitely look into the fill factor.
Sequential GUIDs definitely would make sense.
I am in the process of pushing Session off of SQL Server so hopefully I do not have to resort to a full change of the their (application developers) session application.
Thank you guys for the input
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)

Group: General Forum Members
Points: 209607 Visits: 41973
BrerSQL (8/26/2013)

Sequential GUIDs definitely would make sense.

I'm not so sure that's true. If the computer is rebooted, the start of a new sequence might not be > the largest guid already in the table. I think you could end up with massive page splits anyway.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31408 Visits: 8670
Jeff Moden (8/27/2013)
BrerSQL (8/26/2013)

Sequential GUIDs definitely would make sense.

I'm not so sure that's true. If the computer is rebooted, the start of a new sequence might not be > the largest guid already in the table. I think you could end up with massive page splits anyway.


Yes, but still at just a single point in the tree for the newly rebooted server. Still much better than the alternative in my experience.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
BrerSQL
BrerSQL
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 578
Jeff Moden (8/27/2013)
BrerSQL (8/26/2013)

Sequential GUIDs definitely would make sense.

I'm not so sure that's true. If the computer is re-booted, the start of a new sequence might not be > the largest guid already in the table. I think you could end up with massive page splits anyway.


Keep in mind this is an Application Session DB.
If the server is re-booted and we experience a planned or unplanned outage as a result, then all rows in this table are useless as this data maintains current application session state. We could truncate the table on startup to off set this issue.
This particular server should rarely go down.

Actually on second thought, FILL FACTOR may not work very well for a table like this unless we perform frequent INDEX REBUILDS.

Note: We have found a VLF issue for this database which could be causing our WRITELOG latency issue. The VLF issue was prety severe. I am in the process of rebuilding this DB with an optimal number/Size of VLF's for this database. I am hoping that we may see an improvement, at least until I can move session off of the server.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search