I wish I could talk you out of this. Unless you have some tried and true method that I don't know about, I don't believe this is going to be all that you think it will. A much better approach would be to download a copy of Brent Ozar's first responder kit, learn how to use sp_Blitz and sp_BlitzCache from that, and analyze that data, instead. Here's the link... instructions for each script included are at the bottom of the page of that same linked page.
1 Like
In an indirect way, yes.
A file is incoming... some external job processes the file with DB calls and then another process, later, may use the data from the DB possibly from the result of the previous process OR from another external component updating/inserting data into the same DB. 
1 Like
Thanks Jeff! Ill definitely look into this. Basically im open for anything as long as i can get a before/after snapshot.
If you have SQL2016 or above you might also want to activate query store for the database. This will retain the queries and while it does not tie them up to a particular process it is often possible to make an educated guess.
ps Query Store might also have been included in later SPs of SQL2014.
Do you actually have any scripts/product in-hand that will examine the two copies and tell you everything that you say you're looking for? I could be wrong but I would think not.
If you DO have something that will do as you describe, please post where you got the script or product because I know of no such thing except for simple data comparisons that take a million years to execute, row counts (which are easier to get elsewhere), and something that will let you compare the objects that changed in each. It's not going to tell you anything else. Nothing about memory used, cpu, how often things where used, what kind of load the system was under, or even what ran just at the proc level.
Again... I'm hoping to be able to talk you out of this "compare of snapshots" thing. There is little to be derived from any comparisons and, what can be compared, can more easily be determined using more focused and easier to do methods.
Comparing two "snapshots" of the full database(s) isn't going to help you with that at all.
OK, well what is? How can I compare the data from before and after jobs run and see the delta?
Comparing the data only isn't going to tell you what processes made those changes. All that will tell you is the data was changed. If you already have a way of determining who updated the row in that table, then you don't need a snapshot before or after. You can look at that information and back-track to the calling process.
If you need to find the processes, then you need to capture what is running when it is running - and for that you need either profiler or extended events. But - to capture everything you are looking for will be quite intrusive, so you should filter it down to a specific set of tables/objects.
As I've said and JeffW8713 have said, comparing the Before'n'After of data isn't going to do a bloody thing for you because there's no lead back to what caused the changes. I provided you with links to help determine the health of the system and to find the top worst queries and a whole lot more. It's a total waste of time to compare the data between two snapshots.
Thanks Jeff. I understand. It's just a tough one without giving too much (but as necessary information as I can).
The SQL Server gets hammered by different applications and we have like maybe 10% of those applications that are the ones I am focusing on, the rest are from other teams not related to us but still consume the same databases/datasets. So I am trying to isolate as much as possible.
Totally understood. That's why I'm saying that comparing the data between two snapshots is a futile effort.
One of the things you can do to get an idea of the "flavor" of what I'm talking about is to go to the Object Explorer in SSMS. Right click on the instance name and then follow the selections indicated in the following graphic. The CPU report is one and the one just below that for IO is another one. They will show you your worst queries and some info as to what objects they belong to. Adam Machanic's sp_WhoIsActive will do similar but will return more information and also allow you to Copy'n'Paste things (why MS reports don't allow that is totally beyond me).
That will give you a really good idea of what's happening in your server. Understand all of that is based on what is currently in cache. If you have a high turnover rate there, it could change every minute but most systems don't experience such rapid cache flushes.
Depending on the "mode" you setup with Adam's code, you can see "what's running now" or you can do the aggregate thing I previous described above. As "Peter's 2 Law" states, "When give a choice... take BOTH". 
You could even set it up to run as a regularly scheduled task for a few days and save the output in a table for additional analysis. MUCH more good info than just comparing data in a couple of snapshots.
You could also check on things like rows and space used by index/heap, etc, and track that over time to see where the growth is occurring. Here's some sample code for that.
SELECT ObjectType = t.type_desc
,SchemaName = SCHEMA_NAME(t.schema_id)
,TableName = t.name
,IndexName = i.name
,FillFactorPct = i.fill_factor
,IndexType = i.type_desc
,SchemaID = t.schema_id
,ObjectID = t.object_id
,IndexID = i.index_id
,PartitionNum = p.partition_number
,RowCnt = p.rows
,CompType = p.data_compression_desc
,AllocType = a.type_desc
,TotalPages = a.total_pages
,UsedPages = a.used_pages
,DataPages = a.data_pages
,TotalSizeMB = a.total_pages/128.0
,UsedSizeMB = a.used_pages/128.0
,DataSizeMB = a.data_pages/128.0
,UnUsedSizeMB = (a.total_pages-a.data_pages)/128.0
FROM sys.tables t
JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
and a.type_desc = 'IN_ROW_DATA'
ORDER BY TotalSizeMB
;
Last but most certainly not least, DO use Brent Ozar's sp_Blitz and sp_Blitz_Cache. The first will give you a huge amount of information about the health of the machine and the databases and what to do to fix it complete with links for each item in the output, etc, etc.
1 Like
Wow. Thank you Jeff!