December 8, 2015 at 8:35 am
I've recently come across a great little script that shows me where my I/O pressure is coming from and while I thought it was my primary software database, it wasn't...go figure.
It looks like nearly 60% of my I/O is taken up with tempDB on my primary SQLServer. It's running SQL Server 2012 and it's up to date with SP2. Additionally this SQL Server is a VM running Server 2k12 R2 Datacenter with 128GB of vRAM, 96GB to this SQL instance. The host is running ESX 5.5 and has 256GB of physical RAM and the datastores for each of the drives is tied to a EQX SSD hybrid array. Since it's running with 4 vCPUs I followed recommendations to have 4 tempDBs and sized them each at 10GB. My guess is either there are some other issues or they're undersized.
I was hoping to get some suggestions as to next steps to debug this issue. I've focused so much on tuning my production/proprietary database that I neglected to see if there was an underlying issue. Thanks in advance for any thoughts, suggestions, etc.
December 9, 2015 at 2:25 am
If you are using SAN storage, work with the SAN administrators to understand the hardware. How many disk controllers are there? Too many TempDb's can be just as bad as not enough. Make sure that the Temp DB's are on different spindles to the main data files and log files to reduce disk contention.
Do lots of reading around this area to work out the best set up for your hardware, don't base it just on processors.
December 9, 2015 at 2:34 am
Just a few more thoughts. Look at the size of the TempDB files when the server has been up for a period of time. Make sure the initial size of the files are set to at least this size. Also make sure auto-growth is set to fairly large chunks of disk space, that way physical file fragmentation and waiting for the file to grow will not hamper performance.
If you amend these values schedule a sever restart as soon as possible afterwards.
December 9, 2015 at 6:53 am
I can also speak to the storage since I manage that as well. The backend is tied to an Dell EQL 6110 which is comprised of 24 "spindles"... 7x 400GB SSDs, 17x 600GB 10k 2.5" HDDs including one hot spare. I'm relatively new to the EQL lineup and while it has dual controllers, it appears that only one is active at a time. Since it's a hybrid SAN I don't see how I can manage how many disks are assigned and to what (like the days of old :-P) but it appears with it's FastCache it moves what it seems it should to the SSDs. Additionally all of the LUNs are formatted RAID6 accelerated, there is no changing that.
Recently I went through and upgraded all of the drives and EQL firmware and this virtual SQL Server is tied to about a half a dozen datastores/LUNs on this SAN, the one for tempDB happens to be 250GB on the SAN, but the server only uses 100GB. Attached is a screenshot of the databases and the templog. We also have a solid SQL backup plan through RedGate's software which does FULL, DIFF and TRN backups frequently.
December 9, 2015 at 9:29 am
Check out this link, page 9 and 10 where Dell set up separate LUNS for Data, Logs, Tempdb etc. It might give you some clues.
http://www.principledtechnologies.com/Dell/M420_database_0912.pdf
It might not be the exact same hardware but the same principals apply.
December 11, 2015 at 10:09 am
I checked into my SAN storage and I did look over pages 9/10, but many of those things I'm already doing or are irrelevant due to having a SQL VM. I did find out some more about vNIC adapters and it looks like I may see an improvement using the VMXNET 3 adapter vs. the e1000 one that is on the VM now. I'm going to be planning the maintenance window and attempting that here shortly and will monitor the throughput. My VM is Server 2012 and is a VMware v10 machine so it should take full advantage of it. Anyone else share these same thoughts?
http://longwhiteclouds.com/2014/08/01/vmware-vsphere-5-5-virtual-network-adapter-performance/
December 11, 2015 at 12:42 pm
Could you share the script you're using to get those numbers?
I/O on tempdb can be expected to be many MB as it's the working area for the whole of sql server.
Cheers
December 11, 2015 at 1:17 pm
It's below, hope it helps some others out.
-- STEP 2 - IO by Database
--
-- Calculating the proportional rank of I/O for each database
-- Original author - Glenn Berry, @glennalanberry
--
WITH
Agg_IO_Stats AS
(SELECT DB_NAME(database_id) AS database_name,
CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576. AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS DM_IO_Stats
GROUP BY database_id)
SELECT ROW_NUMBER() OVER (ORDER BY io_in_mb DESC) AS [rank],
database_name, io_in_mb, CAST(io_in_mb / SUM(io_in_mb) OVER () * 100 AS DECIMAL(5, 2)) AS pct
FROM Agg_IO_Stats
ORDER BY [rank];
December 11, 2015 at 1:58 pm
Cheers, thought it'd be from sys.dm_io_virtual_file_stats.
Not really seeing a problem yet to be honest. Temp tables/table variables, ORDER BY, many maintenance tasks, and any number of other processes all use tempdb.
Only thing it might indicate is overuse of some of the above, particularly temp tables and order by clauses, or perhaps overkill/inefficiency on some maintenance jobs.
io_stall_read_ms / (1.0 * num_of_reads) and io_stall_write_ms / (1.0 * num_of_writes) from the same DMV are much more useful for finding underperforming I/O.
Gives you average read and write times - rule of thumb is below 50ms for reads and below 20ms for writes & you're ok.
You can check these in real time in perfmon too - Avg. Disk sec/Read and Avg. Disk sec/Write, although that's for anything on the disk, not just the SQL files.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply