Avatar of Jim Youmans
Jim YoumansFlag for United States of America

asked on 

SQL Server 2016 SP2 on Windows Server 2016

I have a client that is using temporal tables for data auditing and session contexts to secure data from different systems from one another.

To access any data you first have to set the context variable like so.

exec sp_set_session_context @key = 'SystemID', @value='A65T' 

Open in new window

13 of the most used tables have temporal versioning so they can audit data.

The server has 2 cores with 8 GB memory.

My issue is that the server is not used very heavily but the CPU and IO are always high.  My thought is that it could be from the things I mentioned above but can't find articles to confirm or deny this.

Has anyone used these features and if so, what kind of overhead did it cause?

Thank you!
Microsoft SQL ServerWindows OSWindows 10AzureWindows Server 2016

Avatar of undefined
Last Comment
Scott Pletcher
Avatar of lcohan
Flag of Canada image

Yes there is for sure a performance penalty for using Row-Level Security in SQL 2016 and that is described in details at link below.

Aside that and without knowing the specs for your database(s) tables/sizes - it looks to me that the hardware used that "has 2 cores with 8 GB memory"...is very low end for running a SQL Server. I mean my old Dell 790 desktop has more hardware resources than your SQL Server so I would through this into consideration as well.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo