xSQL Profiler Use case: Custom event definitions

SQL Traces can be very effective for gathering information about the operation of a SQL Server provided you have a good utility to manage them, and a tool like xSQL Profiler can prove to be essential for this purpose. Among a large number of problems, xSQL Profiler provides solutions for, it can help drastically reduce the amount of expert manpower needed to monitor SQL Servers. How does it achieve this? 3 words: Custom event definitions.

One might think that the task of choosing what events and what columns to include is relatively easy. However, if you look at the MSDN documentation for traces you'll see that there are hundreds of different SQL events and even more event columns to choose from. Keeping in mind that a trace needs to be as efficient as possible, you can see how this task can become very complicated, very quickly. Which is why any person handling the monitoring of SQL Servers needs to have extensive knowledge of SQL Traces. If there are only a few SQL Servers to monitor one person should be enough to handle this task but when you have a large server farm things can escalate quickly and the associated costs can get out of control.

Custom event definitions make it possible for a single individual to handle practically unlimited number of servers. If you have all the right knowledge and experience with SQL Traces, creating custom event definitions in xSQL Profiler if very easy.

You can either click on Trace > Add event definition or right click on the list of event definitions and click 'Add Event Definition'. The following dialog shows up.

First, you choose the list of SQL events that will be included in the event definition and then click 'Get Columns' to get the all the columns that are supported by these SQL Events. Next, you select only the columns that you want to be included in this trace to make it as efficient as possible while getting all the information needed. After the event definition is created, anyone can create a trace with this event definition and have all of its columns selected without having to worry which columns need to be selected and which do not.

Furthermore, xSQL Profiler allows you to complete automate the process by collecting trace data from target servers at pre-determined time intervals, enabling the DBAs to focus on analyzing the trace data instead of managing the traces. 

Download xSQL Profiler now an try it. xSQL Profiler supports all editions of SQL Server, including MSDE and SQL Server Express and it is completely free for a single SQL Express instance. 

Read More

Performance auditing with xSQL Profiler: Identifying slow queries

Most large scale systems, at some point in time, are bound to display a database related performance issue. As a DBA, if you haven't already, you will undoubtedly get a phone call or an email from a developer telling you that, somewhere in the midst of all the database's T-SQL code, you need to find those queries that are disrupting the system's performance. When this happens, usually you will have two choices: spend hours and hours running and testing queries to see which one could potentially be blocking the system, or employ a tool like xSQL Profiler that will collect information while the database is at work, filter that information, and present you only the execution related information needed to identify slow running queries. Needless to say why, the second option is much better.

xSQL Profiler offers an abstraction on top of SQL Server's native tracing mechanisms, SQL Traces. Let's go over the process of creating a traces on xSQL Profiler to identify slow running queries.

Event Definitions

SQL Traces register information provided by SQL Events when they are triggered during SQL Server's operation. xSQL Profiler supports all the native SQL Server event definitions and, in addition to this, it comes with extra built in event definitions which group together SQL Events that kind of "go together" and provide the full picture behind an action in SQL Server. To identify slow running queries you need to create traces that collect T-SQL execution related information such as the duration, I/O operations, records affected, estimated execution plans, etc.

I am going to use 2 xSQL Profiler event definitions from the "Performance" section of the Event Selection: Statement performance, and Analyze performance issues, and one event from the native SQL Server events: Performance - Showplan XML.

All together these events register data related to:

• Query compilation and execution plans. This information is very useful in finding out why a query is running slow. For example, if the estimated number of records is different from the actual number of records in an execution plan, that means that SQL Server statistics are not up to date, which is one of the reasons queries might not perform well. These data are collected by the 'Performance - Showplan XML' xSQL Profiler event.
• Query execution. This is the most useful information in identifying problematic queries. These data will show you when a piece of T-SQL code started executing, how long it took, how many I/O operations were performed, how much CPU resources were used, who executed the query, etc. Information regarding the query execution is mostly recorded by the 'Statement performance' and 'Analyze performance issues' events. 

Event Columns

Another very important aspect in building traces to identify slow queries is specifying the correct event columns. This affects both the effectiveness and efficiency of the traces. Choosing the right columns is substantial to gathering the information you need, and besides that, you should keep in mind that with every column you select there is an extra amount of data that needs to be collected which presents extra overhead for the server. So knowing exactly which columns to choose will help you avoid any unnecessary overhead on the server. Let's go over the necessary columns for each event definition:

Statement Performance

• Duration - this right here is a no brainer. If you want to identify slow running queries you will most definitely need to know how long a query took to execute. This column displays the duration of the event in microseconds.
• ObjectName -  this column shows the SQL object that executed the query. If it is a stored procedure or a function, it will show the function name. If it is a T-SQL statement that is not in a stored procedure, it will have the value 'Dynamic SQL'.
• TextData - the value of this column depends on the SQL event that is being captured. for this event, it will display the T-SQL code for each of the statements that are being executed, inside and out of stored procedures.
• CPU - This column shows the amount of CPU time (in milliseconds) used by the query.
• Reads - This column shows the number of logical reads performed by the query. The smaller this number is, the less I/O operations are performed.
• Writes - This column displays the number of physical writes a query performs. Again, the smaller this number, the less I/O operations are performed.
• IntegerData - for this event definition, it will show the actual number of rows that are returned by the query. If the query is being executed by an application and it returns a high number of rows, you might want to think about making the WHERE clause a bit more restrictive or applying paging to get a smaller number of records because chances are, the client application does not need that many records.
• ApplicationName, LoginName - this column are self descriptive, and while they serve no purpose on indicating whether the query is slow or not, they are very useful filtering columns in case your database is being queried by more than one application or user. If more than one application communicates with the database, but you know that only one of those applications has performance issues, you could place a filter on the ApplicationName or LoginName columns to record events that are raised only by that specific application or user.
• Event sequence - The value on this column serves to show the sequence in which the events were raised. It will be used to link each individual query with it's execution plan.

Analyze performance issues

The columns for this event are the same as for the previous because the SQL events this event definition gathers data for, complement the events from 'Statement performance'.

Performance - Showplan XML

• TextData - The value in this column is the XML text of the query's estimated execution plan. To view the execution plan simply save this value in a .sqlplan file and open it SQL Server Management Studio.
• IntegerData - for this event, the value in this column displays the estimated number of returned rows.
• EventSequence - the value of this column will always be 1 unit smaller than the value of the same column for excecution related events for the same queries. For example, if the value of EventSequence for SP:StmtCompleted SQL Event is 1001, the execution plan for that query is in XML format in the row with event sequence 1000.  So if you order the data by EventSequence, the data from Showplan XML will always appear above the rest of the data for one query, enabling you to understand which execution plan belongs to which query.

Filters

The most important filter here is placed on the 'Duration Column'. Here you specify the threshold for a query to be considered "slow". There is no real formula for this value because it depends entirely on what a DBA considers a slow query but a good starting point is specifying the Duration to be greater than or equal to 5,000,000 microseconds (5 sec). You can then lower or raise this threshold depending on the data that the trace returns. If running the trace again is not an option you can always use xSQL Profiler's reporting capabilities to filter the data however you see fit.

Download the xSQL Profiler today and try for yourself. 

Read More

xSQL Profiler v2.2 is now available - 50% OFF!

New and improved version 2.2 of the xSQL Profiler is now available.

Download the new version today and take advantage of a 50% discount if you decide to purchase a license before the end of this week, July 1, 2017 (US Eastern Time) - use code PROFILER2 on checkout.

Any suggestions or comments on how we can make this tool more helpful for you would be greatly appreciated! Please complete the simple feedback form on our site at: http://www.xsql.com/comments/ and we will consider including your suggestions on the next build.

Read More

Using xSQL Profiler with a limited access SQL user

The process of tracing events on a SQL Server and collecting those event data, is not among the usual tasks that a user performs on SQL Server. Because of this, the T-SQL commands that are executed to perform the start / stop trace and data collection operations, demand a very specific set of permissions from database users that are not system administrators. I've struggled a bit with this in the past which is why I'm writing this article to provide a step by step guide to making tracing with xSQL Profiler work with non-sysadmin users. To demonstrate these examples I've created a user called 'xsql_profiler_user'. Of course you can specify whatever name you like instead. Let's go over those requirements and how to fulfill them:

• First of all, the SQL user needs to have access to the master database and all the databases that you want to trace. Giving the new user access to these databases is quite easy. on SSMS simply go to the [SQL Server name] > Security > Logins and double click on the name of your login. In this case, 'xsql_profiler_user'. Then go to the User Mapping section and select all the databases you want.
• To be able to start / stop traces in a database, the xsql_profiler_user needs to have ALTER TRACE permissions on the server. To give these permissions to the user you again go to [SQL Server name] > Security > Logins > [name of the login], double click it, go to the 'Securables' section and grant Alter trace permissions from the 'Explicit' tab.

xSQL Profiler configures the traces to write their data into trace files which are then read, their data transferred to the repository and deleted. As you might have imagined, the proper permissions need to be in place to perform all these operations. 

• xSQL Profiler uses SQL Server's xp_cmdshell to delete the files after it uses them, which means that the user you provide for the database connection (in this case xsql_profiler_user) needs to have EXECUTE permission on xp_cmdshell. To do this, simply run:
  

  GRANT EXECUTE ON xp_cmdshell TO xsql_profiler_user
  

• Even though we granted EXECUTE permissions on xp_cmdshell we're still not done. Because the user is not system admin, SQL Server will not allow it to execute xp_cmdshell with the SQL Server's windows account, so we need to create a new windows account to use as a proxy for the xp_cmdshell. Go to Computer Management > Local Users and Groups > Users and create a new user. I've named mine 'xp_cmdshellproxy'.
• After this, you need to give this user the 'Log on as batch job' right. This is because SQL Server will try to use this user to run batch commands without actually logging on with it, and windows will not allow it unless it has the Log on as batch jog right. To do this, search for 'Local Security Policy', go to Local Policies > User rights assignment, double-click on 'Log on as batch job' and add the user created in the previous step.
• Next, assign full rights for the user in the Load temp trace data folder.
• The last thing we need to do is configure SQL Server to use the windows account we just created as a proxy account for xp_cmdshell. To do that, run this query:
  

  EXEC sp_xp_cmdshell_proxy_account 'domain\acountname','password'
  

After you have completed all the above steps, you should be good to go.

Read More