September 13, 2011 at 9:31 am
Any Suggestions are greatly appreciated!
We support a 3rd party application that is storing session state variables in the database. Specifically this table;
/****** Object: Table [dbo].[SessionState_Variables] Script Date: 09/13/2011 10:42:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SessionState_Variables](
[lID] [bigint] NOT NULL,
[SessionName] [nvarchar](200) NULL,
[SessionValue] [ntext] NULL,
[dLastAccessed] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[SessionState_Variables] WITH CHECK ADD CONSTRAINT [CK_SessionState_MinID] CHECK (([lid]>(0)))
ALTER TABLE [dbo].[SessionState_Variables] CHECK CONSTRAINT [CK_SessionState_MinID]
Plus there are all the necessary indexes.
The issue we're having is that the updates to this table make up approximately 30% of all the wait time during business hours. Granted there are a lot of update executions (4 million or so daily) and the average execution time is still a brisk 2.5 Milliseconds.
Any recommendations on improving the performance of the update statement? The update statement looks like this;
BEGIN
UPDATE SessionState_Variables
SET SessionValue = @Value,
dLastAccessed = @LastAccessed
WHERE lID = @ID
AND SessionName = @Variable
END
Is there a way to improve performance by controlling the transaction with a begin transaction and a single commit statement? The SP the does the update passes a string with more than 1 Session name and updates each session variable - so there is likely more than 1 update executing.
Here is the full SP text;
/****** Object: StoredProcedure [dbo].[NIC_Main_SessionDB_SaveSession] Script Date: 09/13/2011 12:15:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[NIC_Main_SessionDB_SaveSession]
-- Add the parameters for the stored procedure here
@ID BIGINT,
@String varchar(max),
@LastAccessed DATETIME
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @Delimiter CHAR(1)
DECLARE @idx INT
DECLARE @slice VARCHAR(8000)
SET @Delimiter=','
SELECT @idx = 1
IF LEN(@String) < 1
OR @String IS NULL
RETURN
WHILE @idx != 0
BEGIN
SET @idx = CHARINDEX(@Delimiter, @String)
IF @idx != 0
SET @slice = LEFT(@String, @idx - 1)
ELSE
SET @slice = @String
IF ( LEN(@slice) > 0 )
BEGIN
DECLARE @Variable VARCHAR(150)
DECLARE @Value VARCHAR(200)
SET @Variable = SUBSTRING(@slice, 1,
NULLIF(CHARINDEX(';', @slice)
- 1, -1))
SET @Value = SUBSTRING(@slice,
CHARINDEX(';', @slice) + 1,
LEN(@slice))
DECLARE @count INT
SELECT @count = COUNT(*)
FROM Sessionstate_Variables
WHERE SessionName = @Variable
AND lId = @ID
IF @count > 0
BEGIN
UPDATE SessionState_Variables
SET SessionValue = @Value,
dLastAccessed = @LastAccessed
WHERE lID = @ID
AND SessionName = @Variable
END
ELSE
INSERT INTO SessionState_Variables
(
lid,
SessionName,
SessionValue,
dLastAccessed
)
VALUES (
@ID,
@Variable,
@Value,
@LastAccessed
)
END
SET @String = RIGHT(@String, LEN(@String) - @idx)
IF LEN(@String) = 0
BREAK
END
END
SAMPLE EXECUTION;
exec NIC_Main_SessionDB_SaveSession @ID=N'11542747',@String=N'authorizeduser;Yes,dlastrequest;8/11/2010 12:13:27 PM,',@LastAccessed='2010-08-11 12:13:28.2400000'
I also think the Count(*) has to go....
September 13, 2011 at 9:44 am
Interesting.
How many rows in the table?
Can you post the an actual execution plan?
Can you post the indexes as well?
Any indexes on that table could be removed?
September 13, 2011 at 9:49 am
This is an ugly an inefficient way to maintain session state anyway, but the TSQL implementation is very poor also.
To make a long story short, the table is made up of bloated data types and you don't mention what sort of indexes are on the table so I can't comment on that.
The stored procedure is also written very poorly in that it uses "select count(*) ..." to see if a session/variable exists to determine whether it should insert or update. It would be better to just always do an update, then check @@rowcount ; if it is 0 you do an insert because you know the session variable doesn't exist.
I don't know what your peak traffic is but using SQL server to maintain sessions state is not the best solution and you will need a HOG of a server to keep up with a high capacity website. At some point you will want to find something more efficient. You'll know when that time is approaching.
Edit: you did mention you get about 4M updates a day, average of 45 transactions/sec but your peak periods could be a lot more than that which can result in resource waits on this one table depending upon what sort of indexing is going on. Scalability is an issue with this sort of implementation.
The probability of survival is inversely proportional to the angle of arrival.
September 13, 2011 at 9:59 am
Thanks for the replies. I think the overall design is terrible but likely the best I could do is try to improve the execution of the SP. Remember this is a 3rd party application.
Here are the indexes;
/****** Object: Index [CL_SessionState_Variables] Script Date: 09/13/2011 12:55:19 ******/
CREATE CLUSTERED INDEX [CL_SessionState_Variables] ON [dbo].[SessionState_Variables]
(
[lID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
GO
/****** Object: Index [IX_SessionState_Variables_dLastAccessed] Script Date: 09/13/2011 12:55:33 ******/
CREATE NONCLUSTERED INDEX [IX_SessionState_Variables_dLastAccessed] ON [dbo].[SessionState_Variables]
(
[dLastAccessed] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [SYSHIST01 ]
GO
/****** Object: Index [IX_SessionState_Variables_SessionName] Script Date: 09/13/2011 12:55:50 ******/
CREATE NONCLUSTERED INDEX [IX_SessionState_Variables_SessionName] ON [dbo].[SessionState_Variables]
(
[SessionName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [SYSHIST01 ]
GO
September 13, 2011 at 10:05 am
We still need to see the plans generated by all the queries that hit that table (at least the update). That way we can make better tuning.
Going under 2.5 ms without touching the code might be hard to do.
You might be able to do another version of the code and submit it to the vendor so that you get a new version (or permission to use that code).
This might be a case where throwing more hardware is not the worst solution.
September 13, 2011 at 10:17 am
I'll try and get the execution plan.
How does this look as a replacement;
CREATE PROCEDURE [dbo].[NIC_Main_SessionDB_SaveSession]
-- Add the parameters for the stored procedure here
@ID BIGINT,
@String varchar(max),
@LastAccessed DATETIME
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @Delimiter CHAR(1)
DECLARE @idx INT
DECLARE @slice VARCHAR(8000)
SET @Delimiter=','
SELECT @idx = 1
IF LEN(@String) < 1
OR @String IS NULL
RETURN
WHILE @idx != 0
BEGIN
SET @idx = CHARINDEX(@Delimiter, @String)
IF @idx != 0
SET @slice = LEFT(@String, @idx - 1)
ELSE
SET @slice = @String
IF ( LEN(@slice) > 0 )
BEGIN
DECLARE @Variable VARCHAR(150)
DECLARE @Value VARCHAR(200)
SET @Variable = SUBSTRING(@slice, 1,
NULLIF(CHARINDEX(';', @slice)
- 1, -1))
SET @Value = SUBSTRING(@slice,
CHARINDEX(';', @slice) + 1,
LEN(@slice))
BEGIN
UPDATE SessionState_Variables
SET SessionValue = @Value,
dLastAccessed = @LastAccessed
WHERE lID = @ID
AND SessionName = @Variable
END
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO SessionState_Variables
(
lid,
SessionName,
SessionValue,
dLastAccessed
)
VALUES (
@ID,
@Variable,
@Value,
@LastAccessed
)
END
END
SET @String = RIGHT(@String, LEN(@String) - @idx)
IF LEN(@String) = 0
BREAK
END
END
GO
September 13, 2011 at 10:19 am
If possible, change the clustered index to include both IID and SessionName and drop the non-clustered index on SessionName. Alsop make it a unique clustered index, based on what you are doing that *should* be possible, but it depends upon what mechanism is issuing the Session ID values (IID).
Check the largest size of data in the SessionName column (select max(datalength(SessionName)) from SessionState_Variables)
I would strongly consider changing the SessionName datatype to a fixed length char dataype and ditch the nvarchar(). The sessionvalue should be a varchar(MAX) no text, but that's less of an issue. All of your indexes were created with 70% fill factor so lots of page splits are expected. I'm sure this table gets aged off (delete sessions older than an hour or so) if not is should be.
The probability of survival is inversely proportional to the angle of arrival.
September 13, 2011 at 10:25 am
Attached is the execution plan;
Another issue is that no the old session variables are not aged off. The table has 10,000,000 + rows.
September 13, 2011 at 10:28 am
FF 70 : I saw it too, but I assumed other figures had been tested. With DML anywhere in the table, I don't want to have FF 100% in there!
September 13, 2011 at 10:28 am
jackimo (9/13/2011)
Attached is the execution plan;Another issue is that no the old session variables are not aged off. The table has 10,000,000 + rows.
Well there's your problem.
September 13, 2011 at 10:31 am
This could also be a good candidate for table value parameter.
September 13, 2011 at 10:35 am
jackimo (9/13/2011)
Attached is the execution plan;Another issue is that no the old session variables are not aged off. The table has 10,000,000 + rows.
Lordy. You should age the old ones off at intervals, or may have to wait till low activity to age them off. Doit it in batches so you don't lock up the table.
The probability of survival is inversely proportional to the angle of arrival.
September 13, 2011 at 10:46 am
If you need to use SQL server as a platform for your session state management see if you can capitalize on certain of its performance characteristics.
As an example, a page is 8K so see if any of your session state variables exceed this. If not, you build a session state table with a fixed size column for the variable and avoid page splits and the 70% fill factor. It would avoid lock escalation you are experiencing under heavy activity.
Another option is to partition the table based on the Session ID. That way you would have less contention on one table. All of these ideas assume you have a dedicated SQL server with a lot of memory on it, and it should be a 64 bit server so you can better take advantage of the memory.
You can see that session state management is a specialized database functionality better implemented via a custom designed system that can scale out to very high volumes without adding unacceptable latencies to sessions.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply