November 24, 2015 at 9:20 am
We enabled TDE on a heavy transaction server and started noticing cpu over utilization.
so we bumped the core count and memory on the server and now it's running around 20% most of the time.
My boss is asking me to look into turning off TDE.
Can you do that w/o rebooting or having a high impact on users?
my understanding is i would run ALTER DATABASE "TDE enabled database" SET ENCRYPTION OFF
Which then spawns a thread and decrypts the database.
Do you have to restart services or anything else?
November 24, 2015 at 9:23 am
You run the ALTER and you wait until the background thread finishes, and that should be it.
Why is a CPU usage of 20% a concern?
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
November 24, 2015 at 10:20 am
it's not i think the TDE caused table/index fragmentation.
which is causing the issue.
can you run a table by table reindex rebuild while in production? as in not off-hours?
November 24, 2015 at 10:46 am
I'm not sure that enabling TDE causes fragmentation, but I could be wrong.
If you have Enterprise Edition - and obviously you have, since you have TDE - you can use the ONLINE option when you rebuild an index to prevent the table from being locked. You will still need to short Sch-M locks when starting and completing the rebuild. And for a large table, the rebuild will take resources.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 24, 2015 at 10:56 am
how wise is that and how intensive are we talking about?
November 24, 2015 at 11:00 am
/* Conditional indexing script. */
-- Ensure a USE <databasename> statement has been executed first.
USE iDocs
GO
SET NOCOUNT ON
-- =======================================================
-- || Configuration variables:
-- || - 10 is an arbitrary decision point at which to
-- || reorganize indexes.
-- || - 30 is an arbitrary decision point at which to
-- || switch from reorganizing, to rebuilding.
-- || - 0 is the default fill factor. Set this to a
-- || a value from 1 to 99, if needed.
-- =======================================================
DECLARE @reorg_frag_thresh float SET @reorg_frag_thresh = 10.0
DECLARE @rebuild_frag_thresh float SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor tinyint SET @fill_factor = 85
DECLARE @report_only bit SET @report_only = 1
-- Variables required for processing.
DECLARE @objectid int
DECLARE @indexid int
DECLARE @partitioncount bigint
DECLARE @schemaname nvarchar(130)
DECLARE @objectname nvarchar(130)
DECLARE @indexname nvarchar(130)
DECLARE @partitionnum bigint
DECLARE @partitions bigint
DECLARE @frag float
DECLARE @command nvarchar(4000)
DECLARE @intentions nvarchar(4000)
DECLARE @table_var TABLE(
objectid int,
indexid int,
partitionnum int,
frag float
)
-- Conditionally select tables and indexes from the
-- sys.dm_db_index_physical_stats function and
-- convert object and index IDs to names.
INSERT INTO
@table_var
SELECT
[object_id] AS objectid,
[index_id] AS indexid,
[partition_number] AS partitionnum,
[avg_fragmentation_in_percent] AS frag
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE
[avg_fragmentation_in_percent] > @reorg_frag_thresh AND
index_id > 0
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT * FROM @table_var
-- Open the cursor.
OPEN partitions
-- Loop through the partitions.
WHILE (1=1) BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag
IF @@FETCH_STATUS < 0 BREAK
SELECT
@objectname = QUOTENAME(o.[name]),
@schemaname = QUOTENAME(s.[name])
FROM
sys.objects AS o WITH (NOLOCK)
JOIN sys.schemas as s WITH (NOLOCK)
ON s.[schema_id] = o.[schema_id]
WHERE
o.[object_id] = @objectid
SELECT
@indexname = QUOTENAME([name])
FROM
sys.indexes WITH (NOLOCK)
WHERE
[object_id] = @objectid AND
[index_id] = @indexid
SELECT
@partitioncount = count (*)
FROM
sys.partitions WITH (NOLOCK)
WHERE
[object_id] = @objectid AND
[index_id] = @indexid
-- Build the required statement dynamically based on options and index stats.
SET @intentions =
@schemaname + N'.' +
@objectname + N'.' +
@indexname + N':' + CHAR(13) + CHAR(10)
SET @intentions =
REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
@intentions
SET @intentions = @intentions +
N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10)
IF @frag < @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
SET @command =
N'ALTER INDEX ' + @indexname +
N' ON ' + @schemaname + N'.' + @objectname +
N' REORGANIZE'
END
IF @frag >= @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)
SET @command =
N'ALTER INDEX ' + @indexname +
N' ON ' + @schemaname + N'.' + @objectname +
N' REBUILD'
END
IF @partitioncount > 1 BEGIN
SET @intentions = @intentions +
N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
SET @command = @command +
N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
END
IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
SET @intentions = @intentions +
N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
SET @command = @command +
N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
END
-- Execute determined operation, or report intentions
IF @report_only = 0 BEGIN
SET @intentions = @intentions + N' EXECUTING: ' + @command
PRINT @intentions
EXEC (@command)
END ELSE BEGIN
PRINT @intentions
END
END
-- Close and deallocate the cursor.
CLOSE partitions
DEALLOCATE partitions
GO
with something like that?
November 24, 2015 at 11:10 am
yakko_Warner (11/24/2015)
it's not i think the TDE caused table/index fragmentation.
TDE won't cause index fragmentation. It's applied as the pages are written to disk, doesn't change where the pages are (fragmentation)
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
November 24, 2015 at 11:18 am
So does one ever run index rebuilds during live hours.
We have users getting actions suspended and so on.
Also when we bumped the Memory as I watch it. It doesn't seem to be going above 41GB but the database uncompressed backup is 80GB
November 24, 2015 at 1:54 pm
If you have a 24/7 system, you have no choice but running index rebuild on the system while it is live.
But there is very much which is unclear in your posts. You enabled TDE, and for some reason you seem to think the system took a performance hit because of this. And then you start to talk about index rebuilds, although we have told you that TDE does not cause fragmentation.
Without knowing much about your system I can only offer wild guesses. Since you talked about restrarting SQL Server when disabling TDE, should I infer that you restarted SQL Server after enabling TDE? Since this flushed the plan cache, maybe you got some bad executions plans because statistics were out of date?
What does "SELECT @@version" return?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 24, 2015 at 4:32 pm
So, the long and short of it is that TDE did indeed cause increased overhead and the server was not sized correctly to handle the I/O load.
We also had a concurrent issue with the application that was connecting to the database failing on it's own do to not having the FQDN listed in it's connection options (major source of pain with the system but not apparent at the time).
I was grasping at straws to try to figure out why the users where getting random disconnects related to the SQL when it seems that it was the App server that was causing a lot of issues and bombing out the application.
So now after adding the FQDN to the connection to the app server cluster things seem to be stabalizing (crosses fingers)
Thanks for the advice.
Sorry the previous posts were not as clear as they could be.
November 25, 2015 at 1:52 am
yakko_Warner (11/24/2015)
So, the long and short of it is that TDE did indeed cause increased overhead and the server was not sized correctly to handle the I/O load.
TDE won't increase IO load. It adds a small CPU overhead (the encryption/decryption of the pages).
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
November 25, 2015 at 8:22 am
That may be true I'm still pretty new to SQL and we migrated from a physical server to a virtual server which I'm sure is impacting performance do to storage not be the same setup as the physical box.
We also moved the server from a location that has less the a millisecond latency to 30ms round trip. They also didn't match the cores of the physical server in the virtual server.
But from what I have read the increase in CPU overhead b/c of TDE being enabled, which is what I was trying to say goes up depending on the workload of the server. The workload this server is seeing is having around 500 users connecting to it all day during business hours getting data ect.
I am still very appreciative to all the thoughtful responses and I am learning all the time.
Thanks again!
Have a Happy Thanksgiving!
November 25, 2015 at 9:55 am
yakko_Warner (11/25/2015)
which I'm sure is impacting performance do to storage not be the same setup as the physical box.
storage is generally the weak link, you wont get like for like performance between a physical disk and a virtual disk
yakko_Warner (11/25/2015)
They also didn't match the cores of the physical server in the virtual server.
there's no 1 to 1 relationship here either, if a server has 8 cores and you virtualise it you wouldn't generally give it 8 vCPUs, it doesn't work that way. The number of vCPUs just indicates a portion of the host CPU time.
yakko_Warner (11/25/2015)
But from what I have read the increase in CPU overhead b/c of TDE being enabled, which is what I was trying to say goes up depending on the workload of the server. The workload this server is seeing is having around 500 users connecting to it all day during business hours getting data ect.I am still very appreciative to all the thoughtful responses and I am learning all the time.
Thanks again!
Have a Happy Thanksgiving!
The increased CPU hit on a VM could well be causing issues, the restricted virtual disk performance will also be a factor
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply