SQL Performance: I think a SQL squish is in order....

Deathmage

Hey guys,

So I was wondering, shouldn't a SQL database be defragged sometimes in SQL with the databases offline to allow for SQL to function better.

Since I started working here in Feb, the performance of SQL was always slow but now on the VMware cluster the problem was masked by the insane speed of the SAN and the IOPS of the fabric but now it's catching up to us...

As the pictures show below, the primary partition where the SQL database are store is um just a tad fragmented, wouldn't you say?



Now the past few weeks vROPS has been running and this server is the only one that is yellow and it's sucking down my score to under 84%

So I've been looking over the numbers and these READS are super freaking high on the SQL VM's and specifically on the partitions for SQL database, transaction files, and logs as shown in the pictures below:




So with this being said and the SAN is a RAID 50, I kind of think the SQL databases need to be compacted just a tad since the fragmentation of the VMDK is sucking down performance of the whole array. I've found out the Syteline ERP has been used for 7 years and has NEVER been compacted and I kind of feel this is a huge performance crutch.

Would anyone else agree or have some insight I might be overlooking?

I plan on doing a SQL packing this weekend after doing backups.

eSenpai

Hmmm....I am really replying to this in hopes of gaining some additional knowledge since it is really not "my thing" currently but something which I have done in the past. That said, if one were to infer from your post then:

1. You are using VmWare of some flavor.
2. You are using SQL Server of some flavor (a quick Google revealed only SQL Server as an option for that flavor of the ERP)
3. It is an older version of SQL Server. I mean if it has not been explicitly maintained in 7 years then it probably also has not been upgraded.

(Any of those assumptions could be wrong so take these next statements with a grain of salt.)

Given those things then

1. The configuration could be unsupported from Microsoft(https://support.microsoft.com/en-us/kb/956893).Since it works, I guess that's not an issue in and of itself but more of an information point.
2. Even though many have done it(MS SQL 2008 R2 on Hyper V - Spiceworks), I personally would not have virtualized any high workload version of SQL Server prior to 2012 without absolutely understanding the workload patterns because SQL Server can be a performance monster in a VM. Given that I think SQL Server 2008 R2 was the first that was officially supported in a virtual environment then there is even more cause for concern. Again...it works! So just food for thought.
3. A high workload is a high workload. Whether that workload is a 100,000 reads/minute for a go hard or go home webserver with a PB backend or 100 reads/hour for a single disk DB, it is all relative to the hardware and setup. Could it simply be that the configuration is being taxed by an increased workload??? The number of read/writes you illustrate is simply not high enough to tax a well endowed system (Raid 50 certainly qualifies as that) but your hardware could have been masking a bad config where a slight increase in usage simply made the problem come to light.
4. Disk defragging is not the same as DB "defragging" and generally you need to use the tools provided by the RDBMS in order to see actual "fragmentation" issues within the DB. After 7 years, you could(should) indeed need to manually rebuild the indexes but if that RAID 50 is being used in a non-dedicated setup then fragmentation at the disk layers doesn't mean much any way since ALL of your read/writes are in essence random (Stop Worrying About SQL Server Index Fragmentation by Brent Ozar Unlimited®). Conversely a fragmentation issue on a highly populated drive could cause subsequent DB issues but this problem is not usually seen as an issue caused the DB files themselves but everything else on the array (Should I Defragment My SQL Server Data Files? | Database Performance Tuning content from SQL Server Pro).

Again these are just my musings for an area, I no longer over see. Any enlightenment is welcome.

Deathmage

Hey guys,

So I've been doing some server optimization the past few months and I finally got all of the servers to the point where even under load, minus the SQL server, servers never get above 2 GB's of memory and 500 mhz on the CPU's. I've employed successfully about 9 registry tweaks that reduce the system kernel memory requirements between 12 to 24% depending upon the server and it's background services, the registry tweaks are linked in my blog in my signature.

The past two weeks I've been moving off scripts and other just ill-placed files on the partitions on the SQL server to a more permanent cold storage platform and I've managed to increase the performance of SQL substantially with the IOPS being super low (as shown in the picture below), during peak they can spike to 900. I'm curious if anyone knows any further things and/or tweaks I can apply to make the SQL VM perform better? ...Right now all my other VM's never get above 300 IOPS even during peak, normally their under 50 per server except at 8 am, lol!

So far this is the basic spec:

1. Database(s) are on one partition.
2. Logs are on another partition.
3. Swap is on it's own partition.
4. SAN array is in RAID 50 for the LUN.
5. SQL has 8 vCPU's, 24 GB's of RAM.

alias454

Are the datastores where the db and logs sit an RDM or are you using a VMDK? You can try thick provisioning the LUN on the storage side and/or the VMDK if it's thin. Another thing is when you setup the mount and formatted it, what block size did you use? Did you adjust the block size to 64k or leave it default at 4k?

Deathmage

alias454 wrote: »

Are the datastores where the db and logs sit an RDM or are you using a VMDK? You can try thick provisioning the LUN on the storage side and/or the VMDK if it's thin. Another thing is when you setup the mount and formatted it, what block size did you use? Did you adjust the block size to 64k or leave it default at 4k?

it was thick Lazy Zero after the P2V but was inflated to Eager Zero and the block size is 64k.

Deathmage

Well this afternoon, I whipped out my rusty programming skills (COBOL, C++, SQL) and made two SQL queries; one that tells me the percent of a fragmentation on a SQL database and then one to re-index all of the SQL indexes for that database to a minimum of 20%.

Perhaps these will be helpful to others.

Will be putting these on my blog in a little bit. Took me about 5 hours to write these, and boy was it fun.


Database fragmentation Percentage Query:

USE [Insert Database Name here]
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName
,ind.name AS IndexName
,indexstats.index_type_desc AS IndexType
,indexstats.avg_fragmentation_in_percent
,page_count AS page_count
FROM sys.dm_db_index_physical_stats(DB_ID() ,NULL ,NULL ,NULL ,NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
AND page_count > 50
AND indexstats.index_type_desc <> 'Heap'
ORDER BY indexstats.avg_fragmentation_in_percent DESC

Database re-indexing query:

--FIND DB ID
--SELECT DB_ID()

IF OBJECT_ID('tempdb..#work_to_do') IS NOT NULL
DROP TABLE tempdb..#work_to_do

BEGIN TRY
--BEGIN TRAN

use [Insert Database Name]

-- Ensure a USE statement has been executed first.

SET NOCOUNT ON;

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 @pagecount INT;
DECLARE @command NVARCHAR(4000);


DECLARE @page_count_minimum SMALLINT
SET @page_count_minimum = 20

DECLARE @fragmentation_minimum FLOAT
SET @fragmentation_minimum = 5.0

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.

SELECT object_id AS objectid ,
index_id AS indexid ,
partition_number AS partitionnum ,
avg_fragmentation_in_percent AS frag ,
page_count AS page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,'LIMITED')
--sys.dm_db_index_physical_stats(37, NULL, NULL, NULL,'LIMITED')
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
AND index_id > 0
AND page_count > @page_count_minimum;

IF CURSOR_STATUS('global', 'partitions') >= -1
BEGIN
PRINT 'partitions CURSOR DELETED' ;
CLOSE partitions
DEALLOCATE partitions
END
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR LOCAL
FOR
SELECT *
FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE ( 1 = 1 )
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag, @pagecount;

@FETCH_STATUS < 0
BREAK;

SELECT @objectname = QUOTENAME(o.name) ,
@schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid
AND index_id = @indexid;

SELECT @partitioncount = COUNT(*)
FROM sys.partitions
WHERE object_id = @objectid
AND index_id = @indexid;

SET @command = N'ALTER INDEX ' + @indexname + N' ON '
+ @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1
SET @command = @command + N' PARTITION='
+ CAST(@partitionnum AS NVARCHAR(10));

EXEC (@command);
--print (@command); //uncomment for testing

PRINT N'Rebuilding index ' + @indexname + ' on table '
+ @objectname;
PRINT N' Fragmentation: ' + CAST(@frag AS VARCHAR(15));
PRINT N' Page Count: ' + CAST(@pagecount AS VARCHAR(15));
PRINT N' ';
END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
--COMMIT TRAN

END TRY
BEGIN CATCH
--ROLLBACK TRAN
PRINT 'ERROR ENCOUNTERED:' + ERROR_MESSAGE()
END CATCH