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

Comments

• Options

  eSenpai Member Posts: 65 ■■□□□□□□□□

  June 2015

  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.

  Working On:

  2018 - ITIL(SO, SS, SD, ST, CSI), Linux

  2019 - ITIL MALC, AWS Architect, CCSP, LPI-2, TOGAF
  

  0 · Share on FacebookShare on Twitter
• Options

  Deathmage Banned Posts: 2,496

  July 2015

  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.
  
  
  

  SQL performance, what more can I do..jpg 94K

  0 · Share on FacebookShare on Twitter
• Options

  alias454 Member Posts: 648 ■■■■□□□□□□

  July 2015

  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?

  “I do not seek answers, but rather to understand the question.”

  0 · Share on FacebookShare on Twitter
• Options

  Deathmage Banned Posts: 2,496

  July 2015

  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.

  0 · Share on FacebookShare on Twitter
• Options

  Deathmage Banned Posts: 2,496

  July 2015

  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

  0 · Share on FacebookShare on Twitter