One of the benefits of stored procedures in enterprise applications is encapsulation and abstraction. If an application is using dynamic SQL SQL text that is being generated on the fly , the application is typically more challenging to tune. Tuning dynamic SQL requires recompiling the application. Stored procedures, on the other hand, encapsulate their functionality and can be modified without affecting the public signature.
Just as methods in your APIs are used for encapsulating functionality, stored procedures are in many ways the same capability, but in the database. Of course there are also some good cases for using dynamic SQL. If dynamic SQL was used in any of the asp. However, there is none. I would also recommend installing SQL Server tools as part of any standard developer environment.
Once you open Profiler you will need to first connect to a database before you can begin a profiling session. To connect to the database, select File New Trace. Note that you will need to be a member of the SQL Server sysadmin group to run profiler.
Next, after the credentials have been verified you will need to set some properties for the trace in the Trace Properties dialog. By default, Profiler will capture events defined in the Standard template. This is an important subset of events rather than all of them. While capturing everything can be useful, most servers support more than one database and a trace that captures all database traffic isn't necessary or useful.
By clicking on the Filters tab you can identify some filters to constrain what is captured. For example, let's say you wanted to run a trace of all database traffic for Community Server. Furthermore, you only wanted to look at communications that were taking over ms to execute usually items that take greater than ms will be noticeable to the end user.
In order to do this I set a filter with a specified login name, like csforums, and a duration greater than or equal to Figure 2 shows the result of this trace. These numbers aren't terrible, but they aren't perfect either. Using the information that Profiler provides, you can at least begin to understand what your database is doing and where it is spending its time.
In this trace session, I filtered for procedures that were taking a long time to run and focused on the forums database. There might also be cases in which there are too many queries running and slowing down the system, but in those cases it's best to alleviate some of the database load by adding an additional server or getting a local copy of the database for testing to remove the false positives.
On a heavily loaded system, rather than using the Profiler UI to collect your SQL traces, you should consider using T-SQL to send the results to a server-side file you can still use the UI to create the trace definition, to script it, and to execute it. Sending results to server-side trace files ensures that no events are dropped, while sending trace events to a rowset which is what SQL Profiler uses does not guarantee this.
Now that I've armed you with the basic ability to examine and profile your database you are probably wondering what happens next. Well, the next step is to take the queries and examine them in Query Analyzer, another wonderful tool that is useful for any developer working with SQL Server. As Profiler is useful for taking the 50, foot view of the system, Query Analyzer is the microscope used to analyze the details. Using Query Analyzer, you can execute queries and stored procedures and get a visual display of the SQL Server execution plan.
Once connected, you can type the following to switch to your database:. Next, click the play button on the menu bar or highlight the text and press F5. Either option executes the highlighted SQL. The SQL trace from Profiler can be copied into Query Analyzer and you can then see exactly what that particular query is doing. To do this, you select Tools, Options to launch the Options dialog. The default page has a section named Environmental Layout.
This much-anticipated tool was slated for SQL Server , but it was pulled before making it to the marketplace. Fortunately, it made it to SQL Server , and it was worth the wait. This is especially true for those developers who have been working with Visual Studio or other Microsoft development tools that have this feature.
IntelliSense is a handy tool that helps you complete queries as you are typing them in the query editor window. Start typing and you will see. The drop-down, in this case, contains the databases and tables from which you can select data. If you type in a stored procedure name to execute, a drop-down shows you the parameters that the stored procedure accepts.
Type SYS. This includes catalog views and the related columns that these views contain. If you type in a query that is incorrect, IntelliSense places a red squiggly line under the part of the query that is syntactically incorrect. The value of this tool will become more apparent as you use it. It can be confusing at times, but it will ultimately speed up your development time. It can also reduce the number of times you need to go to Books Online or some other help source and will make your development life easier.
If you start typing a query against a database from a prior version, the handy IntelliSense drop-downs do not appear. You are not limited to database queries based on SQL. You see these new query options when you create a new query. The SSMS toolbar has icons that correspond to each type of query that can be created.
Each query type has a code pane that works much the same way across all the different types of queries. The code pane, which is the topmost window, color-codes the syntax that is entered, and it has sophisticated search capabilities and other advanced editing features that make it easy to use. SQL Server is able to use the code editor without a database connection. When creating a new query, you can choose to connect to a database or select Cancel to leave the code pane disconnected.
To connect to the database later, you can right-click in the code pane window and select the Connect option. You can also disconnect the Query Editor at any time or choose the Change Connection option to disconnect and connect to another database all at once.
Along with disconnected editing are some changes to the Windows behavior that are worth noting. The biggest changes relate to the behavior of query windows currently open at the time a file is opened for editing. With SQL Server Query Analyzer, the currently selected window would be populated with the contents of the file you were opening.
Prior to this replacement, a prompt would be displayed asking whether you wanted to save your results. If the query window was empty, the contents would be replaced without the prompt for saving. SQL Query Analyzer also has an "Object Browser" that you can use to browse and edit tables, views, and stored procedures.
To open the Object Browser, press F8. Alternatively, you can click the Object Browser icon on the toolbar.
The Object Browser displays to the left of your workspace. You are now ready to write SQL queries against your database. You can use this interface to create database objects such as databases, tables, views etc , insert data into a database table, select data, update data, delete data.
0コメント