1

How can I monitor which data is being accessed and which frequency?

I'm in need to migrate several (very) small SQL Server instances, each which several small databases. Current configuration is based in a lot of also small servers with local storage. New configuration is based in a single server with a single NAS.

So far, the SQL Server memory and CPU sizing is OK. Also DB sizes and total IOPS. But there's no existing documentation of what data set is actually being accessed. So, basically, I don't have a clue about what are the real storage requirements since the total amount of IOPS may be for only a couple of tables (so it would work like a charm with just a couple of SSD) or if the whole set of databases are being scanned all the time and I'll need several dozens of disks.

So, back to the question: How can I "profile" and get statistics of what data is being accessed? Either at SQL or Windows level?

2

If the crux of the question is, "How do I get correct data to size my storage subsystem?" and you want to it fairly repeatedly across many servers, use windows performance counters.

Here is the list I would minimally have:

Logical Disk(*)\Current Disk Queue Length
Logical Disk(*)\Disk Read Bytes/Sec
Logical Disk(*)\Disk Reads/Sec
Logical Disk(*)\Disk Write Bytes/Sec
Logical Disk(*)\Disk Writes/Sec
Logical Disk(*)\Disk Transfers/Sec
Logical Disk(*)\Avg. Disk Sec/Read
Logical Disk(*)\Avg. Disk Sec/Write
Logical Disk(*)\Avg. Disk Sec/Transfer

The Avg. Disk Sec/XXX is going to give you the current milliseconds per operations (read/write). This is important to know so when you move to new storage you meet or exceed the current setup. It's also important as we don't want extremely high numbers here as that will manifest itself in other ways, making it "feel" like SQL Server is slow.

The Disk XXX/Sec is going to give you the number of operations (IOPS) with the Transfers being total IOPS (or Reads + Writes). This is also going to give you an IO profile of your server, knowing whether you're heavy read or write so that any caches could be tuned appropriately or more cache bought per storage unit.

The Disk XXX Bytes/Sec is going to give you an understanding of the size of the IO happening for reads/writes/total. This, in association with the Disk XXX/Sec should give you a better IO profile. Are you doing many small IOs, many large, a mixture, etc. It'll help you decide how to carve up LUNs, shares, etc. It'll also help you understanding what you'll need to do to any caches or cache sizes that may be in the mix.


I know I'm using 30k IOPS. And the storage solution can provide that. BUT the proposed storage solution can achieve that using Fast VP / Fast Cache. That will actually mean that it's able to deliver 30k for some data (the hot one). But not for everything. I need to know what set of data requires those 30k IOPS. Because it's random data all the day long, I know the storage won't be able to cache or move that data to the fastest drives in order to obtain 30k. And I will end up with issues.

You have no control over what users end up wanting, and in some cases (insert) the data doesn't even exist yet. If you're having issues with storage at that level you'll want to go back and ask your storage admins to not be tiered. If you want to know every page that is touched per database, period, you'll want to create an extended events session and ask for a few TBs worth of storage to handle the crazy large amount of data you'll be doing. IMHO you're aiming at an unknown moving target that isn't feasible at all. Talk to your storage team.

| improve this answer | |
1

You can run a query similar to the following that will show the number of reads and writes for files since SQL Server started. Change the number in line 6 to get stats for different databases. 1 = master, 2 = tempdb, 3 = model, etc. 5 and up will be for your user databases.

SELECT files.physical_name, files.name, 
  stats.num_of_writes, 
  CAST(ROUND((1.0 * stats.io_stall_write_ms / stats.num_of_writes),2) AS numeric(36,2)) AS avg_write_stall_ms,
  stats.num_of_reads, 
  CAST(ROUND((1.0 * stats.io_stall_read_ms / stats.num_of_reads),2) AS numeric(36,2)) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files 
  ON stats.database_id = files.database_id
  AND stats.file_id = files.file_id
ORDER BY name
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.