October 9, 2017 at 8:11 am
Hi,
We intend to write a query that will act like wireshark i.e. note down which process or program was executing at particular point of time OR which query had to wait for resources OR blocked another query OR what was the query plan etc. This query would run for 16 hours during the day and will read more than 50 million messages and would be stored in a table on day to day basis.
This is required to track if any issues was encountered or analysis for performance tuning like. for e.g. a query timeout was encountered in live environment however we could not trace the real reason.
Kindly guide for the same. Please note the query should be light weight and should not block the main transactions as time is critical factor here.
Regards,
Saumik
October 10, 2017 at 8:52 am
I'm not sure what you mean here. What is the query going to look at? Are you thinking some query can look backward at activity in SQL Server? Are you thinking to write a query that runs constantly to examine what processes are running?
Your goal isn't clear here.
October 11, 2017 at 12:35 am
Yes the query will constantly run to examine what processes are running, why at particular time a particular stp was running slow OR if there was any blocking session then which query was blocking OR wait types which can be avoided OR as i had mentioned why query timeout occurred when executing one stp, which usually it works properly especially when that stp has only INSERT statement.
Just like how wireshark tracks all the messages on the wire through which we can know where is the contention or if packet is getting dropped, on similar basis we want something that can track the processes
Hope i was able to elaborate and clear what we require
Regards,
Saumik Vora
October 11, 2017 at 7:43 am
You need extended events to do this. This isn't tracked by SQL Server, so there's no query to run now. A session has to be set up in advance.
We have a series that will help you get started: http://www.sqlservercentral.com/stairway/134867/
October 13, 2017 at 9:20 pm
saum70 - Monday, October 9, 2017 8:11 AMHi,
We intend to write a query that will act like wireshark i.e. note down which process or program was executing at particular point of time OR which query had to wait for resources OR blocked another query OR what was the query plan etc. This query would run for 16 hours during the day and will read more than 50 million messages and would be stored in a table on day to day basis.
This is required to track if any issues was encountered or analysis for performance tuning like. for e.g. a query timeout was encountered in live environment however we could not trace the real reason.Kindly guide for the same. Please note the query should be light weight and should not block the main transactions as time is critical factor here.
Regards,
Saumik
Let me ask... what would one of these 50 million messages actually have in it by column to actually make is useful in posthumously determining what a fault at any given time of the day was? Considering the possible number of messages, 50 million messages in a 16 hour period is way too low but, for the sake of argument, 50 million messages in 16 hours equates to 868 or so messages PER SECOND. What makes you think that you'll know which of those 868 messages was actually causing a problem?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply