July 26, 2018 at 3:40 am
Hi All,
I have a question related to extended event ring buffer target.
In SQL Server, how many ring buffer(s) will be there?
For example, in below examples, I am creating 2 extended events whose target is a ring buffer. Is it two ring buffers will be created with 500 MB size or else only one 500MB buffer will be created and will be reused for both the extended event traces??
CREATE EVENT SESSION azure_monitor
ON DATABASE
ADD EVENT sqlserver.error_reported(
ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.session_id, sqlserver.sql_text,
sqlserver.username))
ADD TARGET package0.ring_buffer
(SET max_memory = 500, max_events_limit = 10 )
;
GO
CREATE EVENT SESSION MonitorExpensiveQuery ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION
(
sqlserver.database_id,sqlserver.session_id,sqlserver.username,sqlserver.client_hostname,sqlserver.sql_text,
)
--Specify predicates to filter out your events
WHERE sqlserver.sql_statement_completed.cpu > 1000
OR sqlserver.sql_statement_completed.duration > 10000
)
--Specify the target where event data will be written with ADD TARGET clause
ADD TARGET package0.ring_buffer
(SET max_memory = 500, max_events_limit = 10 )
;
GO
Thanks,
Sam
July 26, 2018 at 7:30 am
Strong recommendation.
Don't use the ring buffer target. It's very attractive, but it leads to problems. Read this post by Jonathon Kehayias for details.
"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
July 26, 2018 at 7:51 am
vsamantha35 - Thursday, July 26, 2018 3:40 AMHi All,I have a question related to extended event ring buffer target.
In SQL Server, how many ring buffer(s) will be there?
For example, in below examples, I am creating 2 extended events whose target is a ring buffer. Is it two ring buffers will be created with 500 MB size or else only one 500MB buffer will be created and will be reused for both the extended event traces??CREATE EVENT SESSION azure_monitor
ON DATABASE
ADD EVENT sqlserver.error_reported(
ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.session_id, sqlserver.sql_text,
sqlserver.username))
ADD TARGET package0.ring_buffer
(SET max_memory = 500, max_events_limit = 10 )
;
GOCREATE EVENT SESSION MonitorExpensiveQuery ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION
(
sqlserver.database_id,sqlserver.session_id,sqlserver.username,sqlserver.client_hostname,sqlserver.sql_text,
)
--Specify predicates to filter out your events
WHERE sqlserver.sql_statement_completed.cpu > 1000
OR sqlserver.sql_statement_completed.duration > 10000
)
--Specify the target where event data will be written with ADD TARGET clause
ADD TARGET package0.ring_buffer
(SET max_memory = 500, max_events_limit = 10 )
;
GOThanks,
Sam
I could be mistaken but it appears that you're not actually finding the "most expensive" query, according to the criteria you've used. You're only finding the longest running query, which may not actually need any form of improvement at all.
I've found that the "most expensive" queries typically run in less than a second but they run thousands of times per hour. Through a "Continuous Improvement" program we have at work, we've been able to mostly eliminate nearly 200 Terabytes (NOT a misprint) of reads and several "CPU Days" of resource usage from the DAILY (10 hour work day) resource usage we were experiencing.
That did NOT include what we found when we started looking at compile times. We had one query that would change slightly every time it got used and so compiled every time it was used. Now, the query "only" (in quotes because we fixed that too) took 100ms to execute but the damned thing would take 2 to 22 SECONDS to compile and it was used thousands of times per day.
All of this was the proverbial "Death by a Thousand Cuts" and it took some time to fix most of them but it has been well worth it. Finding and fixing the "longest running" or "uses the most CPU" runs aren't typically (there, of course, will be exceptions) the best way to spend human resources on performance improvements.
Heh... to repeat a play on words, "Yes! DO SWEAT THE SMALL STUFF... and most of it IS small stuff." 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2018 at 10:05 am
I think your unit of measure might be wrong in the EVENT as well. You're checking duration > 10000, which I believe is 0.01 seconds, and could be a very large number of queries. Were you really trying to capture queries that run this quickly? 1 second would be duration = 1000000
July 26, 2018 at 1:10 pm
Thanks everyone for the replied. Sorry, looks like everyone is misunderstood my question. My question is,
"whether it creates two ring buffers with each buffer 500 MB size or else only one 500MB buffer will be created and will be reused for both the extended event traces??"
July 30, 2018 at 12:07 am
Anyone ?
July 30, 2018 at 1:27 am
vsamantha35 - Monday, July 30, 2018 12:07 AMAnyone ?
You don't create any ring buffers, they're created at startup - https://msdn.microsoft.com/en-us/library/dn135320(v=sql.110).aspx
July 30, 2018 at 6:43 am
BrainDonor - Monday, July 30, 2018 1:27 AMvsamantha35 - Monday, July 30, 2018 12:07 AMAnyone ?You don't create any ring buffers, they're created at startup - https://msdn.microsoft.com/en-us/library/dn135320(v=sql.110).aspx
And, you shouldn't be using them for the most part any way, see the article I linked above.
"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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply