In the previous part we discussed about the basic concept of SQL, RDBMS and what are the different joins used to select the desired data, different clauses used for writing SQL queries.

Now in this part we will further discuss about some other important concepts.

Profiler:

Now technically defining what profiler means in SQL, the main function of profiler is to monitor how many queries are running at run time, what is the analysis of SQL server, how are stored procedures affecting the performance, etc.

So in short the profiler monitors every activity happening in the SQL server, also the activities in ADO.NET, activities of stored procedures and their performances. It is basically a tool which helps us to trace and capture number of SQL statements fired.

In SQL Management Studio we see where the profiler tool is present,

1

In the Tools we find SQL profiler. After selecting that

2

We see the different processes, their performances also the start & end of SQL Query, showing of plan, etc.  This is how the profiler looks when it is providing us with the necessary information.

Stored Procedure:

Store Procedures are very important parameter in SQL server. They are defined as pre compiled SQL execution plan. These Stored Procedures can also perform insert, update, and delete functions.

SQL Plan:

SQL plan is made every time some SQL statements are fired. The plan is made so that we know how SQL server internally works. This plan is stored in Cache, so that every time we run the process in SQL server it will go according to plan which is already store in cache. But the moment we do some changes in our process in SQL server a new plan is made and then stored in cache. So whenever we are running the same process again and again in SQL server it will not create the new plan, but it will take it from the cache.

This plan recompiles when there is a structure change or data change and therefore it creates new plan. If there are some minor changes done, no new plan will be created and it will follow the previous plan stored in cache.

Now we know that stored procedures are precompiled SQL execution plan, they are always better, because if there are some changes in parameters or some minor changes, it will always use from the cache. This will increase the performance of our SQL server. Where as in a normal SQL statement if the parameters are changed or some minor change is done, there will be a new plan created and stored in cache. And every time some minor changes are done to the SQL statements it will make a new plan rather than using the previous plan stored in cache.

So due to these differences between Stored Procedures & normal SQL statements we can say that Stored procedures are better and helps in increasing the performance of SQL server.

 

Advertisements