[Music]
hi I'm a shot a cloud support engineer
here at the AWS office in Bangalore
India
sometimes customers ask me how do I read
a service I trace an idea sequel server
Amazon ideas being a managed database
service you don't have access to the
underlying operating system the problem
with using a profiler is that it's a
client-side tool and if the network
connection stops your tray stops and
this is one reason why you should use a
server-side trace sequel server provides
you with an option of reading
server-side traces using queries and
this is exactly what I'm going to show
you in this video I am already connected
to the RDS sequel server instance using
the sequel server management studio and
as a first step we would need to list
down the traces one way you can find the
detailed information of running traces
is by using
fn:trace get an for function but my
personal preference is to use sistar
traces which gives you a detailed
information on the traces and their
status the cess traces catalog view
contains the currently running traces on
the system
as you can see from the output we have
two traces the trace ID one which is the
default trace and the trace ID two which
is the service I trace creating a
server-side trace as currently outside
the scope of the video however I have
included the link in the description
section or the article attached to the
video now as we can see from the output
of the query we also get the status
status of one indicates that the trace
is currently running and we also get the
path on which the trace file exists so
now that we know where the trace file
exists the next step would be to read
the trace file we can read the trace
files by using fn:trace get table
function I'm going to select and execute
the query as you can see I am passing
the file path of the trail server-side
trace as an argument to the function
as seen here we can clearly see that the
output here is similar to what you would
otherwise see on a profiler trace you
can save service I trace results to a
database table and use the database
table as the workload for the tuning
advisor again by using fn:trace care
table function as you can see in the
below
query I have created a database called
profiler trace TV and now I'm going to
create a table within the database
called RDS trace and use F and trace get
table to copy the contents of the trace
into this table
now if I query the IDS trace table
I can see the contents of the profiler
trace and this can be used to the
database tuning advisor as an input now
let's see what ideas has to offer when
it comes to retaining the trace and dump
files on the server trace and dump files
can accumulate and consume disk space by
default Amazon RDS purchase trace and
dump files that are older than seven
days to modify the retention period of
trace files we can use the RDS set
configuration procedure and set the
trace file retention in minutes again
back to the demo we can see that the
stored procedure that has been selected
sets the retention to 24 hours that is
1440 minutes
thanks for watching and happy cloud
computing from all of us here at
Database
[Music]