Understanding SQL Server Profiler Tool

Welcome. This is a module on SQL Server Profiler from the University of Washington. I am Greg Hay. So, high level, what is SQL Profiler? Well, it's a monitoring tool and what it allows us to do is see what activity is happening across the entire server. Now, we can do some filtering. But unfiltered, it's going to show all activity. These could include stored procedures being called, functions, even mouse clicks on going through a menu system. Any activity happening on the server within the SQL Server database management system is going to show up on Profiler. This helps us. It helps us in many ways. When we want to learn about the environment from the very beginning, we can do a trace. We can see from a window what activity is occurring. Also, we can store the historical data to either a table or a file. And that allows us to write queries that we can ask questions later about which is the most frequent. Store procedure being called, for example, what the average duration is, who's actually calling it, and from there we can learn about our environment. Also, it can help us troubleshoot. We can do measurements from a high level that we want to see how the system is performing. We want to see what's going on. Then we can write additional filters that will allow us to do targeted. investigations such as how frequent is this one particular store procedure being called? How about what activities are hitting this database? If we're seeing slow performance for a particular table or such, we can really narrow our search in our filter and capture only the data that's really relevant to a very specific question. So when we start a file, of capturing activity, it's called a trace. So a trace is just going to be a duration of measuring what activity is going on. We can add filters to it and, again, make it very specific. So a trace is a subsystem of SQL Server Profiler, leverages an external application programming interface, better known as an API. And, again, this is very flexible. It is... allowing us to answer very, very specific questions or start from a very high level just to see what's going on. A couple, and we will take a look at SQL Server Profiler in just a minute, I want to have just a high level set of hints and recommendations when we are working with SQL Server Profiler is first off, we need to have a plan. If all we do is turn it on and start capturing... all of the activity happening on the server, it's a lot of data, it's a lot of information. It could be millions and millions of rows very quickly. And if we don't have a plan, it's entirely possible that we're going to add additional burden onto a server that is not performing well already. So we may be investigating a slowly performing server, and then if we're not thinking clearly, we may start a trace. on that server without any filters and it may be just enough additional overhead that the system stops working completely. I have actually done that. Not proud about it. But when we say we want to have a plan, have an idea of what data we really want to trace for. Know how long we're going to have the trace run. Do we need to have it run for... an hour or can we get away with running it for only five minutes? Obviously the shorter the better for burden on the server but the questions we may be seeking to answer may actually require the trace to run longer. We may want to start with a predefined template. Profiler has 20, 21 different templates we can choose from, each being a collection of events and data columns that may be better fit or a better align for exactly what we're looking for. I'm going to bring up the database engine to find SQL Server Profiler we go up to the menu bar and we select tools underneath tools we have SQL Server Profiler. I'm going to run this on my laptop. I'm going to authenticate and then from here we have a general trace properties. We can give this a clever name. Greg trace one. Again, this is going to be on my laptop. We have a choice of templates. We may be looking just for SQL code being called. We may be looking for locks. We may be trying to answer very specific questions. We may be trying to capture more activity to do some more elaborate tuning. Let's not choose. anything other than the standard default right now. When we are doing large amount of data capture, let's say we're going to have this trace run for three hours. That would be a very large file. We want to be careful. We'd want to save this to a file as opposed to saving it as a table. We can save it as a table. Ultimately, we want to get the file data into a database so we can write queries of the trace results. For right now we're just going to go to a file and we can cap the size of the file maybe 15 megabytes would be an initial size and then we can continue to create additional files automatically. We can have a start and an end time on our trace. Preferably we want to have an end time. I've seen people forget that they started start a trace and all of a sudden it's like taking up space on disk. Ok so trace has been started. I'm going to expand this so it takes up more room. I just want people to be generally aware that this is what it looks like. That's one. What is available We'll go back and take a look at what I filtered on in just a minute. Text data is going to be what the code is that's going to be run. This is just whatever is being executed on my laptop is going to show up right here. We're going to see evidence of whatever is being executed. Text data is going to be the actual code. If I go out to SQL Server and let's say I run one of these. If we look back here, we should see evidence of that. There it is. So the activity that occurs on the server is going to show up in which category it is. Is it a store procedure that's been called? Is it a login, logout? Is it a batch? of SQL statements. We have for each row there's going to be measurements. The measurements will be what was the amount of CPU that that call took up, what were the reads, what were the writes, how long did it run, when was the start time, when was the end time. All of this is relevant when we're trying to troubleshoot or learn more about what the activity is that occurs on a particular server. If we're brand new to an environment, there may not be documentation. We have to go create our own documentation, do our own learning, and Profiler is fantastic for that. I wanted to show how we can create a filter, so let's go ahead and stop this trace. I will close this, and we'll start a new one. I say new trace, log into the server again. Now, I'll give it another clever name, GregTrace2. This is where I'm going to, instead of taking the default, I'm going to go in and select some more events. I will click show all events and show all columns. If I wanted to see only the stored procedure activity, I can come down. And I will look specifically for store procedures. RPC stands for Remote Procedure Call. I may want to capture the occurrence, the measurement of a remote procedure call starting, and maybe Store procedures completed, store procedures recompiling, starting and completed, and maybe some dynamic SQL, we got that. So I'm going to see store procedures as well as the code. I can filter, let's say I want to put it only on a database ID equals. I think 6 is the database I'm looking for. Let's find out. I can get the database IDs for the databases by querying sys.databases. If you see right here, dbid, 6 is ubalr. Sure, that works. Although, let's go with customer build number 10. We're going to filter on DBID 6 and 10. Okay. We're going to fire that off. Now, in the background, I'm going to go execute our customer build database store procedure. Oops, my apologies, I'm not in the right database. Change the context, this will work. I'll add 500 rows. It is working. I want to go out here and then see the activity. There's my call. EXEC US Populate Cust Avenues 500, right there. And there should be activity going on. We're going to come back and take a look at this. It completed very quickly. So, as you can see, there's 500 occurrences here. Did I put 500? So, there was a, it showed up very quickly. You can see that there's a certain number of reads per occurrence. It all relates back to the same system process ID. And then I have a start time and end time. Once we get this saved. So the trace is still running. I can stop the trace. I can save it as a file. I can save it as a table. If I save it as a table, I want to make sure that I'm not going to impact performance of production. If there's data coming in and all of a sudden I'm going to throw 10 million rows of a trace file into a table. That's going to compete for resources. We just want to make sure that we're doing it, if we have a large amount of data, that we're going to do it at a time of day where it's not impacting. Okay, so I'll save it as a trace table. Again, I need to log in, authenticate. It's going to say, where do you want to save it? Well, we can put it in one of my databases, sample UW, give it some clever name, Greg Trace, Trace 2. and this will save. It's only 547 rows. If we go back out to SQL Server, I put that in Customer Build, refresh, and refresh. That was funny, I put it actually in sample UW. I was looking at the wrong table. Here's my trace file. And from here, I can select star from drag and drop, GregTrace2. Take the DBO out. I'm going to get in a hurry. I want sample URL. ALR, my bad. Here we go. Here is the data that has been captured. Now this aligns exactly to how our trace was running. Notice across the top, event class, text data, application name and so on. Same exact columns in a table. 548 rows. From here we can write queries where we want to, since text data is going to hold the actual code, customer build. Actually let's go with the store procedure. So I'm searching on the execution of a stored procedure. Now, the event class 10 and 11, SQL statement starting, SQL statement stopping. And what I was trying to show is that we can see and learn from the data that was captured. In a normal environment, there would be... hundreds, perhaps, or more connections, each calling one of dozens, if not hundreds, of stored procedures, and we would see a wide range of stored procedures. So if I did a search on execution of a stored procedure, I'd see all of the stored procedures. Then we can do a group by and see how many per. store procedure were called and we can get an idea of what the highest frequency is of store procedures versus functions what's the busiest store procedure how do we know this well we do a trace capture 10 minutes 15 minutes worth of activity at different points of the day and then check and see during which window which store procedure which objects are being accessed the most This helps us understand our environment. This is where we can build a list of our most important or highly frequent store procedures, which transactions are being thrown against the database. And again, the better we understand our environment, the better able we are to be in position to solve problems. Okay, I want to just remind people have a plan, know what our scope is. have our filters in place. We don't just want to run a loose trace that's going to capture everything that, again, could have millions and millions of rows. We may want to leverage a predefined template that may help us out. When we execute a trace, there's a difference between having it server-side and client-side. Server-side, we are going to have a set of... All the activity is going to go to the server, go to the table that we have created in the server. This is going to add additional burden to the server. If we have a server or an application or a database that is not performing well, and then we add additional burden to it in forcing it to capture trace activity, that may not be... desirable. It may put additional burden on the server, again to the point where the database no longer is going to be able to take traffic. We want to not send it via GUI. We can send it straight to disk in a file and it can actually handle a large amount of activity without increasing the overhead. Where it becomes burdensome is when we try and present it in a graph. presented in a GUI across the network. We can save it either as a file or as a table, and then we can query the data once we have, so if it's a large amount of data, save it as a file. Then when we are done, ready to write queries, the trace has been stopped, we will then write it to a table. It, from a table, allows us to write queries. Group the data, get an idea of which objects are being called the most and which are. having dependencies or impacts and again allows us to learn very clearly what is happening on our particular server. I put here avoid if possible writing to a table initially because again this is going to be logged activity, inserts, updates and we want to be cognizant that if we're in production that it's going to impact production, the performance. We may want to define a trace as narrow as we can. We want to apply filters, so we're not grabbing every piece of activity, only those that affect the database, the login, the store procedure, the objects that we are filtering on. If we're just starting our troubleshooting, we may just capture a certain type. of object like a store procedure and just let the trace run for maybe five minutes. And from that we should be able to get an idea and then narrow further such as filtering on a database name, filtering on a particular host or login. We should be comfortable starting, stopping, pausing a trace. We want to be able to do this with a script. Sometimes the best use of SQL Server Profiler is in response to an error, and it's called automatically as the alert is raised. So the raising of an error sends a notice to the error log. It also may notify a technician, but at the same time, if we set this up correctly, we may want to capture immediately who's connected to the server, what processes are running, and we might be able to better isolate, especially if it's a resource consumption issue, and we just want to say, fine, what was going on on the server at the time? We can capture that immediately versus having to wake someone up, have them log in, and then 10 minutes later we're trying to recreate something that is no longer on the server. Again, when we are having a trace that's going to run in the background, a trace log, we may want to have... measurements taken every two minutes. We may want to have measurements taken every 15 minutes. We may just try to get an idea of what activity is occurring. So we may want to run this in the background. We can create it via the GUI and then script. Right click and then script the file that we just created. And then we can call it from SQL Server or SQL Server agent. We can also create a trace if we want to replay it. This works really well sometimes if we're testing applications. We may want to capture production data, restore a production database in a test environment, and then rerun all of the commands that were issued against production in a window that reflected, oh, perhaps, what a normal act. activity, normal activity for an hour or so. We capture that in production, we take a backup of the database, we go to a dev environment, and then we make whatever changes we want to attempt, such as new indexes, such as partitioning. We may want to try new ways of optimizing the structures. We don't want to practice in production per se, so then we take a copy of everything and then place it in a safe environment, in a dev environment. But there is a capability of capturing the activity and replaying it, and Profiler is fantastic for that. We can also synchronize with a database backup. I gave the brief intro here. There's, again, a lot of flexibility with this tool. This is going to tell us the what is happening. It might even tell us who, if we want to know where it came from. This is going to tell us what the login was and what the application was that was using it, and then what the category was, and then the actual code. So you can see, it can get as detailed as what's going to run. Okay, that is... SQL Profiler. I'm going to include the screenshot on the slides so people that want to review can do so. So this is just going to walk through creating a new trace, logging in and creating a location to save that output, establishing filters, and then issuing the start. Again, there's 21 templates. These are aligned with capturing the right columns, the right events that we want to have data returned on. Some are geared towards just knowing what code is running. Others might be what other activity. Store procedures at a granular level. It could be SQL code. It could be everything. And we may be trying to capture for a replay. We may be capturing for doing some tuning. There's different templates that are going to fit different needs. Have an idea of what your purpose is. It could be just to take two minutes and review the output with a bunch of queries. Very powerful tool. Very flexible. Allows... Capturing of everything that's, again, going on on the server. This is going to tell us what activities events are happening. I think it's the best tool for measuring a list of database objects, what's going on on the server, logins, store procedures, triggers, functions, and so on. Best for measuring the impact of a single object. What happens when we call this store procedure? Does it call any other store procedure? If we filter on what activity, we are going to see a ripple effect. It can be correlated with Perfmon if we want to see the impact from a system resource perspective. Here's the code. Now, what happened with system resources? How much memory? How much CPU? Quite powerful. Brief intro. SQL Server Profiler. There will be a lab that will include some opportunities with SQL Server Profiler. If you have questions, please feel comfortable sending me an email. gthay at uw.edu. I can also be reached via phone, 206-355-2887. I appreciate everyone's time.

Welcome. This is a module on SQL Server Profiler from the University of Washington. I am Greg Hay.

So, high level, what is SQL Profiler? Well, it's a monitoring tool and what it allows us to do is see what activity is happening across the entire server. Now, we can do some filtering. But unfiltered, it's going to show all activity.

These could include stored procedures being called, functions, even mouse clicks on going through a menu system. Any activity happening on the server within the SQL Server database management system is going to show up on Profiler. This helps us. It helps us in many ways. When we want to learn about the environment from the very beginning, we can do a trace.

We can see from a window what activity is occurring. Also, we can store the historical data to either a table or a file. And that allows us to write queries that we can ask questions later about which is the most frequent. Store procedure being called, for example, what the average duration is, who's actually calling it, and from there we can learn about our environment. Also, it can help us troubleshoot.

We can do measurements from a high level that we want to see how the system is performing. We want to see what's going on. Then we can write additional filters that will allow us to do targeted.

investigations such as how frequent is this one particular store procedure being called? How about what activities are hitting this database? If we're seeing slow performance for a particular table or such, we can really narrow our search in our filter and capture only the data that's really relevant to a very specific question. So when we start a file, of capturing activity, it's called a trace.

So a trace is just going to be a duration of measuring what activity is going on. We can add filters to it and, again, make it very specific. So a trace is a subsystem of SQL Server Profiler, leverages an external application programming interface, better known as an API.

And, again, this is very flexible. It is... allowing us to answer very, very specific questions or start from a very high level just to see what's going on.

A couple, and we will take a look at SQL Server Profiler in just a minute, I want to have just a high level set of hints and recommendations when we are working with SQL Server Profiler is first off, we need to have a plan. If all we do is turn it on and start capturing... all of the activity happening on the server, it's a lot of data, it's a lot of information.

It could be millions and millions of rows very quickly. And if we don't have a plan, it's entirely possible that we're going to add additional burden onto a server that is not performing well already. So we may be investigating a slowly performing server, and then if we're not thinking clearly, we may start a trace. on that server without any filters and it may be just enough additional overhead that the system stops working completely. I have actually done that.

Not proud about it. But when we say we want to have a plan, have an idea of what data we really want to trace for. Know how long we're going to have the trace run. Do we need to have it run for... an hour or can we get away with running it for only five minutes?

Obviously the shorter the better for burden on the server but the questions we may be seeking to answer may actually require the trace to run longer. We may want to start with a predefined template. Profiler has 20, 21 different templates we can choose from, each being a collection of events and data columns that may be better fit or a better align for exactly what we're looking for. I'm going to bring up the database engine to find SQL Server Profiler we go up to the menu bar and we select tools underneath tools we have SQL Server Profiler. I'm going to run this on my laptop.

I'm going to authenticate and then from here we have a general trace properties. We can give this a clever name. Greg trace one.

Again, this is going to be on my laptop. We have a choice of templates. We may be looking just for SQL code being called. We may be looking for locks.

We may be trying to answer very specific questions. We may be trying to capture more activity to do some more elaborate tuning. Let's not choose. anything other than the standard default right now.

When we are doing large amount of data capture, let's say we're going to have this trace run for three hours. That would be a very large file. We want to be careful.

We'd want to save this to a file as opposed to saving it as a table. We can save it as a table. Ultimately, we want to get the file data into a database so we can write queries of the trace results. For right now we're just going to go to a file and we can cap the size of the file maybe 15 megabytes would be an initial size and then we can continue to create additional files automatically.

We can have a start and an end time on our trace. Preferably we want to have an end time. I've seen people forget that they started start a trace and all of a sudden it's like taking up space on disk.

Ok so trace has been started. I'm going to expand this so it takes up more room. I just want people to be generally aware that this is what it looks like. That's one.

What is available We'll go back and take a look at what I filtered on in just a minute. Text data is going to be what the code is that's going to be run. This is just whatever is being executed on my laptop is going to show up right here. We're going to see evidence of whatever is being executed. Text data is going to be the actual code.

If I go out to SQL Server and let's say I run one of these. If we look back here, we should see evidence of that. There it is. So the activity that occurs on the server is going to show up in which category it is.

Is it a store procedure that's been called? Is it a login, logout? Is it a batch? of SQL statements.

We have for each row there's going to be measurements. The measurements will be what was the amount of CPU that that call took up, what were the reads, what were the writes, how long did it run, when was the start time, when was the end time. All of this is relevant when we're trying to troubleshoot or learn more about what the activity is that occurs on a particular server.

If we're brand new to an environment, there may not be documentation. We have to go create our own documentation, do our own learning, and Profiler is fantastic for that. I wanted to show how we can create a filter, so let's go ahead and stop this trace.

I will close this, and we'll start a new one. I say new trace, log into the server again. Now, I'll give it another clever name, GregTrace2. This is where I'm going to, instead of taking the default, I'm going to go in and select some more events. I will click show all events and show all columns.

If I wanted to see only the stored procedure activity, I can come down. And I will look specifically for store procedures. RPC stands for Remote Procedure Call. I may want to capture the occurrence, the measurement of a remote procedure call starting, and maybe Store procedures completed, store procedures recompiling, starting and completed, and maybe some dynamic SQL, we got that. So I'm going to see store procedures as well as the code.

I can filter, let's say I want to put it only on a database ID equals. I think 6 is the database I'm looking for. Let's find out.

I can get the database IDs for the databases by querying sys.databases. If you see right here, dbid, 6 is ubalr. Sure, that works.

Although, let's go with customer build number 10. We're going to filter on DBID 6 and 10. Okay. We're going to fire that off. Now, in the background, I'm going to go execute our customer build database store procedure.

Oops, my apologies, I'm not in the right database. Change the context, this will work. I'll add 500 rows.

It is working. I want to go out here and then see the activity. There's my call.

EXEC US Populate Cust Avenues 500, right there. And there should be activity going on. We're going to come back and take a look at this.

It completed very quickly. So, as you can see, there's 500 occurrences here. Did I put 500? So, there was a, it showed up very quickly.

You can see that there's a certain number of reads per occurrence. It all relates back to the same system process ID. And then I have a start time and end time.

Once we get this saved. So the trace is still running. I can stop the trace.

I can save it as a file. I can save it as a table. If I save it as a table, I want to make sure that I'm not going to impact performance of production.

If there's data coming in and all of a sudden I'm going to throw 10 million rows of a trace file into a table. That's going to compete for resources. We just want to make sure that we're doing it, if we have a large amount of data, that we're going to do it at a time of day where it's not impacting. Okay, so I'll save it as a trace table.

Again, I need to log in, authenticate. It's going to say, where do you want to save it? Well, we can put it in one of my databases, sample UW, give it some clever name, Greg Trace, Trace 2. and this will save.

It's only 547 rows. If we go back out to SQL Server, I put that in Customer Build, refresh, and refresh. That was funny, I put it actually in sample UW.

I was looking at the wrong table. Here's my trace file. And from here, I can select star from drag and drop, GregTrace2. Take the DBO out. I'm going to get in a hurry.

I want sample URL. ALR, my bad. Here we go. Here is the data that has been captured. Now this aligns exactly to how our trace was running.

Notice across the top, event class, text data, application name and so on. Same exact columns in a table. 548 rows. From here we can write queries where we want to, since text data is going to hold the actual code, customer build. Actually let's go with the store procedure.

So I'm searching on the execution of a stored procedure. Now, the event class 10 and 11, SQL statement starting, SQL statement stopping. And what I was trying to show is that we can see and learn from the data that was captured. In a normal environment, there would be...

hundreds, perhaps, or more connections, each calling one of dozens, if not hundreds, of stored procedures, and we would see a wide range of stored procedures. So if I did a search on execution of a stored procedure, I'd see all of the stored procedures. Then we can do a group by and see how many per. store procedure were called and we can get an idea of what the highest frequency is of store procedures versus functions what's the busiest store procedure how do we know this well we do a trace capture 10 minutes 15 minutes worth of activity at different points of the day and then check and see during which window which store procedure which objects are being accessed the most This helps us understand our environment.

This is where we can build a list of our most important or highly frequent store procedures, which transactions are being thrown against the database. And again, the better we understand our environment, the better able we are to be in position to solve problems. Okay, I want to just remind people have a plan, know what our scope is. have our filters in place. We don't just want to run a loose trace that's going to capture everything that, again, could have millions and millions of rows.

We may want to leverage a predefined template that may help us out. When we execute a trace, there's a difference between having it server-side and client-side. Server-side, we are going to have a set of... All the activity is going to go to the server, go to the table that we have created in the server. This is going to add additional burden to the server.

If we have a server or an application or a database that is not performing well, and then we add additional burden to it in forcing it to capture trace activity, that may not be... desirable. It may put additional burden on the server, again to the point where the database no longer is going to be able to take traffic. We want to not send it via GUI.

We can send it straight to disk in a file and it can actually handle a large amount of activity without increasing the overhead. Where it becomes burdensome is when we try and present it in a graph. presented in a GUI across the network. We can save it either as a file or as a table, and then we can query the data once we have, so if it's a large amount of data, save it as a file. Then when we are done, ready to write queries, the trace has been stopped, we will then write it to a table.

It, from a table, allows us to write queries. Group the data, get an idea of which objects are being called the most and which are. having dependencies or impacts and again allows us to learn very clearly what is happening on our particular server. I put here avoid if possible writing to a table initially because again this is going to be logged activity, inserts, updates and we want to be cognizant that if we're in production that it's going to impact production, the performance.

We may want to define a trace as narrow as we can. We want to apply filters, so we're not grabbing every piece of activity, only those that affect the database, the login, the store procedure, the objects that we are filtering on. If we're just starting our troubleshooting, we may just capture a certain type.

of object like a store procedure and just let the trace run for maybe five minutes. And from that we should be able to get an idea and then narrow further such as filtering on a database name, filtering on a particular host or login. We should be comfortable starting, stopping, pausing a trace. We want to be able to do this with a script. Sometimes the best use of SQL Server Profiler is in response to an error, and it's called automatically as the alert is raised.

So the raising of an error sends a notice to the error log. It also may notify a technician, but at the same time, if we set this up correctly, we may want to capture immediately who's connected to the server, what processes are running, and we might be able to better isolate, especially if it's a resource consumption issue, and we just want to say, fine, what was going on on the server at the time? We can capture that immediately versus having to wake someone up, have them log in, and then 10 minutes later we're trying to recreate something that is no longer on the server. Again, when we are having a trace that's going to run in the background, a trace log, we may want to have...

measurements taken every two minutes. We may want to have measurements taken every 15 minutes. We may just try to get an idea of what activity is occurring.

So we may want to run this in the background. We can create it via the GUI and then script. Right click and then script the file that we just created.

And then we can call it from SQL Server or SQL Server agent. We can also create a trace if we want to replay it. This works really well sometimes if we're testing applications.

We may want to capture production data, restore a production database in a test environment, and then rerun all of the commands that were issued against production in a window that reflected, oh, perhaps, what a normal act. activity, normal activity for an hour or so. We capture that in production, we take a backup of the database, we go to a dev environment, and then we make whatever changes we want to attempt, such as new indexes, such as partitioning.

We may want to try new ways of optimizing the structures. We don't want to practice in production per se, so then we take a copy of everything and then place it in a safe environment, in a dev environment. But there is a capability of capturing the activity and replaying it, and Profiler is fantastic for that.

We can also synchronize with a database backup. I gave the brief intro here. There's, again, a lot of flexibility with this tool. This is going to tell us the what is happening. It might even tell us who, if we want to know where it came from.

This is going to tell us what the login was and what the application was that was using it, and then what the category was, and then the actual code. So you can see, it can get as detailed as what's going to run. Okay, that is...

SQL Profiler. I'm going to include the screenshot on the slides so people that want to review can do so. So this is just going to walk through creating a new trace, logging in and creating a location to save that output, establishing filters, and then issuing the start. Again, there's 21 templates.

These are aligned with capturing the right columns, the right events that we want to have data returned on. Some are geared towards just knowing what code is running. Others might be what other activity. Store procedures at a granular level. It could be SQL code.

It could be everything. And we may be trying to capture for a replay. We may be capturing for doing some tuning. There's different templates that are going to fit different needs.

Have an idea of what your purpose is. It could be just to take two minutes and review the output with a bunch of queries. Very powerful tool. Very flexible. Allows...

Capturing of everything that's, again, going on on the server. This is going to tell us what activities events are happening. I think it's the best tool for measuring a list of database objects, what's going on on the server, logins, store procedures, triggers, functions, and so on.

Best for measuring the impact of a single object. What happens when we call this store procedure? Does it call any other store procedure?

If we filter on what activity, we are going to see a ripple effect. It can be correlated with Perfmon if we want to see the impact from a system resource perspective. Here's the code.

Now, what happened with system resources? How much memory? How much CPU?

Quite powerful. Brief intro. SQL Server Profiler.

There will be a lab that will include some opportunities with SQL Server Profiler. If you have questions, please feel comfortable sending me an email. gthay at uw.edu. I can also be reached via phone, 206-355-2887.

I appreciate everyone's time.

Transcript for:Understanding SQL Server Profiler Tool

Transcript for:
Understanding SQL Server Profiler Tool