January 8, 2020 at 7:00 pm
I'm working for a client that is extremely concerned about read/write performance to a table that contains around 600 million records with an estimated growth of 20-30 million records per year. I've been assigned to capture and analyze performance metrics specific to this table, including replication latency and disk queue length. Is this even possible?
January 9, 2020 at 1:20 pm
Sure. Just not straight forward. On SQL Server 2014, you have really two good options. One will give you a general understanding of the behavior. The other will give you all the details and more.
One approach to do this would be to simply query the cache. Sys.dm_exec_query_stats will give you an overview of the writes, reads, etc., for any query that references this table. You'll have to do wild card searches on the data (unless it's only accessed through known procedures, cross your fingers that's the case, also you'd switch to sys.dm_exec_procedure_stats then). It doesn't give you replication latency. You'd have to measure that on the replication side (and it won't be by table), but it'll give you most of the rest.
Conversely, extended events is the way to go. Again, filtering is either a wild card search for the table name (much less preferable) or using the object ids if it's procedures, or a combination of both. There you can get the performance metrics, toss in the waits, and you'll have almost everything you could possibly want. Now, it's going to gather a ton of data, so you have to be prepared for that. You'll also want to learn how to deal with the XML data returned, either through the SSMS gui, or by writing XQuery. However, there are tons of examples for this online.
If you were on 2016 or better, I'd suggest looking to Query Store to help out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply