Tools Tips and Tricks #3: Custom Rowsets using SQL Nexus

This post is part three of the Tools Tips and Tricks series that I started last week. In my T-SQL Tuesday post for this month, I already explained how I always have the inclination of importing data collected into a SQL Server database. I shall touch upon this yet again but this time through the use of SQL Nexus. I am going to use a small script to collect data for the user requests which are executing queries against a particular SQL Server instance. The script that I used to collect data is shown below:


set nocount on
go

while (1=1)
begin

print '-- RequestsRowset'

select GETDATE() as runtime,a.session_id as session_id,
a.start_time as start_time,
a.[status] as [status],
a.command as command,
a.database_id as database_id,
b.objectid
from sys.dm_exec_requests a
cross apply sys.dm_exec_sql_text(sql_handle) b
where session_id <> @@spid

raiserror ('', 0, 1) with nowait

waitfor delay '00:00:05'
end

I have stored the output captured by the above query in a file called CustomRowset.OUT. Using the Edit Custom Rowset option in the SQL Nexus UI (available in the third expandable tab named Data, on the left hand side), I can pull up a UI where I can specify the table name into which the data needs to be imported into (tbl_RequestsExample in this case) and the identifier for the data which needs to be imported (— RequestsRowset in this case). I was executing a WAITFOR DELAY command from another session while the above script was capturing data. I then import the data into a SQL Nexus database using the Importoption. Once this is done, I can then query the database and look into the results which were imported into the database. (see screenshot below). You can extend this functionality to any degree you want and even combine multiple T-SQL commands to capture different result sets in the same loop. You just need to make sure that the rowset identifier for each query result set is unique.

Additionally, if you attempt to capture outputs which have columns with data type length greater than 8000, then the import will fail with the following error in the SQL Nexus log file:

SQLNexus Information: 0 : RowsetImportEngine Error: An unexpected error has occurred:

System.Data.SqlClient.SqlException: The size (8192) given to the column ‘query_text’ exceeds the maximum allowed for any data type (8000).

SQL Nexus 3.0 doesn’t give you the option to add your own column data types using the UI.  Using the form (shown on the left) will treat all columns as varchar. If you want to change this behavior, then modify C:\Users\<user name>\AppData\Roaming\sqlnexus\TextRowsetsCustom.xml directly to add or modify the data types that you want.

Where is this helpful?
Let’s say I decided to collect the output of customized T-SQL script for an extended period of time. Instead of scouring the .txt or .out file manually using a text editor, I can import the data into a table and then run queries on them to save yourself a hair-raising experience and valuable time!

How do I make sure that the data is imported correctly?
1. If you have data which is larger than varchar(8000) in the result sets collected, then make sure to modify the TextRowsetsCustom.xml before you import the data.
2. Give each rowset that you collect an unique identifier. You don’t want the importer to mix-n-match the data you are importing.
3. Add a runtime column using GETDATE() or a variable for scripts capturing data in a loop to ensure that you can track the trend easily without having to second-guess.
4. All columns that are collected in the result set(s) have to be named.
5. Avoid CR/LFs in the result set i.e. don’t use CHAR(13) in your T-SQL script while capturing the data as this seriously confuses the importer as it treats CR/LFs as end-of-row indicator.
6. Either capture the data directly into a file by running the data collection script from Management Studio or use sqlcmd -W parameter if you are capturing the data using sqlcmd. -W ensures that trailing spaces from the result sets are removed.

That is all I have for today. Happy customizing and importing!

Tools Tips and Tricks #2: SQL Express RANU instances

Recently I had worked on an issue with Run As User Instances a.k.a. RANU. The user instance, also known as a child or client instance, is an instance of SQL Server that is generated by the parent instance (the primary instance running as a service, such as SQL Express edition) on behalf of a user. The user instance runs as a user process under the security context of that user. The user instance is isolated from the parent instance and any other user instances running on the machine. The user instance feature is also referred to as “Run As Normal User” (RANU). This is not a feature of SQL Server that is common knowledge to most people in the SQL realm. Note the following mention in BOL for the topic “User Instances for Non-Administrators”:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Why did I decide to talk about a SQL Server feature in a series that I am running on my blog related to tools. Well, this post is about connecting to a RANU instance and the tools available to do this. But this is a lesser known beast, I had to set some context first. Smile

User instances are created when the “User Instance option” is set in the SQL Client connection string. Following is a section of a Visual Basic configuration file that shows the User Instance option:

<connectionStrings>
<add name=”TestVB1.Settings.Database1ConnectionString” connectionString=”Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Database1.mdf; Integrated Security=True; User Instance=True” providerName=”System.Data.SqlClient” />
</connectionStrings>

How can I find out if there are RANU/User Instances running on the machine?

On the SQLEXPRESS instance, the configuration option “user instances enabled” in should be enabled. Then execute the query shown in the screenshot below against the SQLEXPRESS instance:

image

The heart_beat column value will tell you is the instance is online or not. For an online instance, the heart_beat will have a value set to alive. RANU instances allow only local Named Pipe connections. So there are three ways how you can connect to a RANU instance:

1. Using Management Studio

image

a. Launch a Management Studio on the machine where the SQL Express instance is running.
b. In the Connect to Server dialog box, you will have to specify the instance_pipe_name value obtained from the above output as the Server Name.

2. Using SQLCMD

Using the sqlcmd utility, you can also connect using the pipe name in the following manner:

sqlcmd -Snp:\\.\pipe\BA78E627-AD14-4F\tsql\query -E

3. Using SQL Server Express Utility

This is a utility available on the Microsoft Downloads site but is not supported by CSS. One of the tasks that can be performed by this utility is to connect to User Instances. The SSEUtil.exe –childlist command will list all the RANU instances on the machine. The output is the same as the one shown above which was retrieved using sys.dm_os_child_instances. Refer screenshot below.

image

The ReadMe.htm available with the installation of the utility has information on what can be done with this utility other than connecting to a user instance.

You can then connect to the instance using the following command:

SSEUtil.exe -c –child <domain>\<user name>

How do I shutdown a RANU/User Instance?

A RANU instance has a defined timeout value of 60 minutes; after 60 minutes without activity, RANU shuts itself down. The RANU timeout is configurable at the parent instances through the ‘User Instance Timeout’ setting. If you want to shutdown a RANU instance before the timeout is reached, then you can use one of the methods listed above to connect to the User Instance and issue the following command:

SHUTDOWN WITH NOWAIT

References:
Understanding SQL Express behavior: Idle time resource usage, AUTO_CLOSE and User Instances
Connecting to SQL Express User Instances in Management Studio

Now it’s time to shutdown this post… Adieu till the next post.

Tools Tips and Tricks #1: Process Monitor

I recently wrote about importing a Process Monitor trace into SQL Server database table and crunch up the data to extract the events and call stacks. This prompted me to think about capturing data with Process Monitor and some things I learnt along way while using this tool working at CSS.

imageThe first tip is to disable any activity that you don’t want to capture or are not required for the issue that you are troubleshooting. The capture tracks three classes of operations: File System, Registry and Process. In the toolbar show on the left in the screenshot, you can enable/disable the following captures:

a. Registry activity
b. File System activity
c. Network activity
d. Process and Thread activity
e. Profiling events

More information about the above is available in the Process Monitor help file. image

The command line options specified are immensely helpful if you are scripting the capture of a trace using a batch file or if you are generating an automation routine to load the captured data into another data source. I had used /OpenLog and /SaveAs1 option to generate the XML file from the saved .PML file.

imageOne of the most useful options that I suggest using when capturing a Process Monitor trace is to use the backing file option (/BackingFile command line parameter or CTRL+B when using the GUI). This prevents using the page file as the backing store for trace capture and avoid running in unresponsive server issues while you are still capturing your trace and the paging file fills up. I normally point the backing file to a local drive on the machine which has sufficient amount of disk space.image

Process Monitor can use symbol information, if available, to show functions referenced on event stacks. You can point to the symbol path (local symbol cache or Microsoft Symbol Server: http://msdl.microsoft.com/download/symbols) using Options –> Configure Symbols. Additionally, you can specify the path to the source files for the application in the same dialog. This will help you resolve the function calls using the symbol path and if a source path is present, open a text viewer dialog with the source line highlighted which is being referenced. The symbol path is needed when /SaveAs2 option is used for converting the .PML file to .XML format. Note that this option considerably increases the export time due to symbol resolution time involved.

I am starting a series tagged with “Tools Tips and Tricks” which will document the various tweaks that I use for data collection for the various data collection/analysis tools that I use on a day-to-day basic.