Installing the new xSQL Profiler 2.1

We just released the newest version of our xSQL Profiler and besides the increased range of supported SQL Server versions and events that can be traced, as well as some other bug fixes and improvements there were some changes in the setup and installation. Let's go through the new installation shall we?


A new feature in the xSQL Profiler installation is the ability to choose a "Standard Installation" and have all the configurations done behind the scenes as opposed to the "Advanced Installation" where you have to manually enter each configuration option.

There are a few things to keep in mind with the Standard Installation though.

• First of all, you need to have a SQL Server 2008 and up installed as a default instance. By default xSQL Profiler uses "(local)" as the server name which means that it will search for the default SQL Server instance on the machine. One thing that might confuse you a little bit is that just because you have SQL Server installed on your machine, it's not necessarily the default instance, even if there is only one SQL Server installed. You need to manually tell the SQL Server installation to register the instance as a default instance. If you haven't done this during installation, and are reading all those forums that say it cannot be done after the instance is installed, there is no need to panic because they are simply not correct. Here is an article on how to do that.
• The next thing to note is that the xSQL Profiler service will run under the "Local System" account. This is necessary because it is the only built in service account that is sure to have access to SQL Server (neither "Local Service" or "Network Service" can access SQL Server by default). However, if you have SQL Server 2012 and up, even the Local System account doesn't have access, so you will need to manually configure this account to be able to access the SQL Server. Keep in mind that this is a very high privileged account so you need to be very careful because you can expose your system to some security risks. You can always change the account on which the service is run on after it is installed and manually provide the necessary permissions.

Like all previous versions of xSQL Profiler this version supports the advanced installation if you like to manually specify all the configurations for xSQL Profiler. The first thing you do is specify the account that xSQL Profiler's Service will run on.

Your options here are one of the three built-in service accounts (for more information on those check out this MSDN page), or a separate windows account. If you do choose one of these accounts, like mentioned above, for versions up to SQL Server 2008 R2, by default, only Local System can connect with the server instance and for versions later than that, none of the service accounts can connect with the instance. So if you choose one of these you need to manually configure the account to be able to connect with the server. Keeping with the principle of least privilege, the best thing to do is create a new user specifically for the profiler's service and give it the necessary permissions. The minimum set of permissions it needs are "Log on as service" and permissions to connect with the SQL server instances.

The next thing you need to do is to choose the SQL server where the xSQL Repository will be created and provide the authentication details for that server.

This is fairly straightforward, much like providing the authentication details when you use SQL Server Management Studio. You provide the server name and authentication details, as well as the new database's name. The data you provide here will only be used by the setup to create the database and then discarded. You can choose a SQL server on the local machine or on another machine as the server that will hold the repository for xSQL Profiler. If you choose the Windows Authentication option, the current logged in user will be used to connect with the database, so make sure that user is registered as a login on SQL Server and has permissions to create a database.

The last thing you need to do is to provide the authentication details that will be used  by the xSQL Profiler's service to connect with the repository.

This is where you should be a little careful. You can choose either Windows or SQL Server Authentication and each of these options has benefits and drawbacks. From a security standpoint, Windows Authentication is better because the SQL Server Authentication option will save your login details on the service's XML configuration file. However, if you do choose windows authentication keep in mind that:

• The service's user needs to be registered as a login on the repository's SQL server
• The service's user needs to have the necessary permissions to access the database.

The security risk of SQL Server authentication can be mitigated by choosing a SQL Server user with the minimum set of required permissions but this will, again, require some work to be done. You can do this after xSQL Profiler has been installed because the authentication details in this step are not used during the setup. They are used when you run the application.

So there you have it. All you have to do now is click "Next", review the installation details and if you have provided the correct information, the setup should complete without errors.

Read More

xSQL Profiler 2.1 now available

We just published a new version (2.1) of xSQL Profiler with built-in support for tracing low level SQL Server events. Now, if you want to create a trace for a specific SQL Server event, you don't have to create a new event definition for that event. All low level SQL Server events are included. This is especially useful when you are using xSQL Profiler with SQL Server Express.

A few minor UI improvements such as better context menu and tooltips, updated grid etc. are also included in the new version.

xSQL Profiler is FREE for one SQL Server Express instance. You can download the new version from our website: http://www.xsql.com/download/sql_server_profiler/

Read More

SQL Server: Functions vs. Stored Procedures to return result sets

A while back, I was building the database schema for a web application which had some reporting functionality and among other things, I had do implement logic in the database to prepare the data for the application's reports. The queries I constructed were relatively complex which meant that I needed to construct objects in the database to encapsulate these queries. So, it came down to a choice between table-valued functions and stored procedures. If you do a little research you'll notice that there is no clear cut suggestion regarding the choice between functions or stored procedures for cases when you need to retrieve a result set from the database. So, here is a comparison of the two, which, in the end will be concluded with a suggestion for those of you out there who can't make up your minds.

T-SQL statements

With regards to the types T-SQL statements that each of the objects can contain, stored procedures are much more versatile because almost all T-SQL statements can be included in a stored procedure. The only exceptions are the following:

• USE <database>
• CREATE AGGREGATE, RULE, DEFAULT, CREATE, FUNCTION, TRIGGER, PROCEDURE, or VIEW

However, when it comes to table-valued functions, there is an entirely different story. Based on the T-SQL statements that can be used in them, they are quite limited. Namely, they cannot:

• Apply schema or data changes in the database
• Change the state of the database or a SQL Server instance
• Create or access temporary tables
• Call stored procedures
• Execute dynamic SQL
• Produce side effects such as relying on the information from a previous invocation.

So, basically, only SELECT statements are allowed in table-valued functions. The only exception is on multistatement-table valued functions, which must contain an INSERT ... SELECT statement that populates the table variable which will be returned by the multi-statement table-valued function. 

Parameters and return types

Both, stored procedures and table-valued functions accept parameters of all data types however, there are a few differences.

The first, and most important is that unlike stored procedures, table-valued functions do not accept output parameters. In fact, table-value functions return data to the client in only one way: through the RETURN statement. Stored procedures on the other hand, do accept output parameters and they have three ways to return data to the client: through output parameters, by executing a select statement in the procedure's body or by using a RETURN statement.

Another, more subtle difference is on how parameters with default values are handled. While both stored procedures and table valued functions support default values for parameters, these type of parameters are optional only on stored procedures. Weird as it is, if you want the default value for a parameter when using the function, you have to write the DEFAULT keyword in place of the value for that parameter. With stored procedures you can simply omit the value and SQL Server will supply the default value. 

The last difference is that when you call a stored procedure, you can specify the parameter values by association, meaning that you can use a syntax like this: <parameter_name> = <value> to supply the parameter values, which greatly improves the code's readability. You can't do this with functions. This might become an issue if the function has a lot of parameters, because you would constantly need to review the documentation just to find out the order of the parameters in the definition.

So, if you need to return multiple result sets or if you are worried about the readability of your code, stored procedures might be a better option

Performance

If you think about it, table-valued functions, especially inline-table valued functions, are a lot like another database object. Yeap, you guessed it, VIEWS. Even the SQL Server optimizer treats inline table valued functions the same as it does views. This is why one can think of table valued functions like parameterized views.

Performance wise, functions and stored procedures are identical. They both make use of execution plan caching, which means that they are not recompiled every time they are executed. To prove they are identical, you can create a function and a procedure with the same SELECT statement, execute each one a few times, and then check the sys.dm_exec_query_stats DMO. You will notice that the last_elapsed_times differ very little.

Usage

This is where I think, table-valued functions have the greatest advantage. Because they resemble views, they can be placed anywhere a table can be placed in a query. This means that you can filter the result set of the function, use them in join statements, etc. You cannot do the same with stored procedures. Of course, if you have enough knowledge and experience with T-SQL you could probably find some workaround, but generally, manipulating the result set returned by a stored procedure is not as straightforward as doing the same for a table-valued function. So, if for some reason, you need to apply some additional manipulation to the data returned by a function you can do that very easily. If that same data comes from a stored procedure, in most cases you may need to alter the procedure's code, which will require having the necessary permissions and what not. 

If you think that the result set of the stored procedure of function may need to be further manipulated, use table valued functions.

One last thing

One thing that I really like about table-valued functions, is that you can use the SCHEMABINDING option on them to prevent any changes on the underlying objects that can break the function. The same option is not always available on stored procedures. You can use it only on natively compiled stored procedures which are available only on SQL Server 2014 and up, and Azure SQL database. So, if you are using regular stored procedures to retrieve data, keep in mind that they can break if you change the structure of the referenced objects.

To conclude, as a general rule of thumb, I tend to use table-valued functions whenever I need to retrieve a result set from the database, and stored procedures when I need to perform some work on the database.

Read More