SQL Server Profiler using tutorials, easy to understand is the kingly

SQL Server Profiler using tutorials, easy to understand is the kingly

Do development, usually inevitably and database dealing with, especially write stored procedures, for those of us who do not often write SQL is a very painful thing, every write run is always wrong, if the use of the local database is OK, you can debug SQL in this machine, if the database on the server, debugging is disabled, It's tragic.

Recently, due to job requirements, write a stored procedure, there are dozens of parameters, the database on the server, finished, test the query is not a problem, but the project inside the query error, it is irritating! Think of the use of SQL Profiler this tool, open the selection of default settings, start tracking, halo, massive data flooding, see the dizziness, there is no trace to track the statement. Later, after Baidu Search and careful study, it is learned to use SQL Profiler. Let's share it with you.

I. Introduction to SQL Server Profiler

SQL Profiler is a graphical interface and a set of system stored procedures that function as follows:

• Graphical monitoring of SQL Server queries;
• Collect query information in the background;
• Analyze performance;
• Diagnosing a problem like a deadlock;
• Debug T-SQL statements;
• Simulated replay of SQL Server activity;

You can also use SQL Profiler to capture activities that are performed on an instance of SQL Server. Such an activity is called Profiler tracking.

ii. Simple configuration and use of SQL Profiler

Not much to say nonsense, about SQL Profiler introduction on the Internet is, we find it yourself. Here is the practical, start the operation! (Note: The local database is needless to say, you can debug the run, mainly the database on the server above the situation.) )

First of all, the SQL Server Express version is no SQL Profiler tool, Enterprise Edition has this tool, the other version is not used. Let's take a look at where SQL Profiler is, first open SQL Server Management sutdio, click menu, Tools > SQL Server Profiler,

After the click, the login screen will appear first, enter the user name password, you will see the following interface:

Line four has a use the template, choose Standard, if it is a local database, access to the database only you, click Run on it. If there are a lot of people accessing the data on the server, you have to go through some of the results. Then, click on the tab of Events Selection,

You can see the default options for standard, this is not a tube, check the box show all columns, otherwise the hostname cannot be found . Click on the column Filters ... button to open the Filter Options page:

Drop-down to find the hostname entry, add your own computer name to the like so that only the SQL sent by our computer is displayed.

Then click OK, click Run to start tracking, and then the database access you have on your computer will be recorded.

Note: It is on your computer, you access the server above the website, the database access is done on the server, not on your computer. Only when you run the debugging, you can access the server from your computer to be tracked.

trace results are as follows:

When a record is selected, the executed SQL is displayed, and as for the stored procedure, we can clearly see the parameters passed in and can be copied out and debugged.

If you have too many records, you can choose Edit > Find ... Below the menu to search.

See here, is not suddenly feel very simple ah, hehe, read so many tutorials, talk about a lot of things, do not find what they want, the more the whole more annoying!

third, the establishment of their own template, convenient next use

If you change the computer name every time, it is inconvenient, we can make a template of their own, the next time you use it will not be configured. Here's a look at how to build your own template:

, click the menu to open the template page.:

Enter your own template name, such as Myprofiler, and then tick base new template on existing one: based on an existing template on the line modification, we still choose standard, there is a use as default The template for selected server type, which is set as the default template, is checked, we will select the template we added each time we create a new trace, the recommended tick. Then click on the Events Selection tab and set up hostname. Save it when you're done.

Then, click File > New trace ... New Trace, you will see our new template, and is selected by default, we can click Run directly to continue our work.

SQL Server Profiler using tutorials, easy to understand is the kingly