Apr

How to Configure a SFTP Folder in Sublime

When configuring a website, it’s extremely time-consuming/exhausting to have to make a file, upload it to your server and then run it on the server. Unfortunately, with site building, I have found that the process needs to be repeated over and over again, especially when attempting new styles, scripts, and formatting. And what happens when you decided you don’t really like your changes and want to revert to the old formatting? Well, the process needs to be restarted all over again.

Having a direct connection to your remote server folder can make this process extremely efficient and, fortunately, Sublime Text offers the option to set this up. To do so, you will need to have a couple things installed on your edition of Sublime Text:

If you haven’t already, you will need to install Package Control. The easiest way to do this is to copy the text corresponding to your edition of Sublime Text and to run it via the application’s console, which can be accessed via View → Show Console. You will need to restart Sublime Text in order for changes to take effect.
Once you have Package Control installed, you will need to install Sublime SFTP package. To do so, open up the Command Palette (Cmd + Shift + P on a Mac), find Package Control: Install Packages, and run it. It will open up a similar looking menu. Type in “SFTP” and download the first package that comes up.

Once you have the Sublime SFTP package installed, the real fun can begin.

Create a blank folder on your computer directory into which all your site files will be placed once you connect to your remote server.
Drag the blank folder into Sublime.
Right-click on the folder and select the option SFTP/FTP → Map to Remote.
A new file called “sftp-config.json” will pop up in your Sublime Text window. In here, configure, information about your server, including the server directory on which your site is hosted and information about your login and password. These are the main fields I usually change from the default (from top to bottom of the file):
- upload_on_save: Set to true. This will allow your saves to automatically sync with your remote.
- host: The name of my site.
- user: My username on the server hosting the site.
- password: My password onto that server.
- ftp_passive_mode: Set to false.
Once that’s all set, Save the file.
Now, when you right-click the folder in the left-hand directory in Sublime Text and navigate to the option SFTP/FTP, you have a host of options you can choose from. To start off, run the Sync Remote → Local command. This will collect all the files you currently have on your remote server and bring them into the blank directory you made.

(Note: before starting the download, Sublime SFTP will verify with your host that all the information is correct and then display to you a list of all the files that will be downloaded. Make sure to press “Yes” in order to begin the download.)

Now that you have all your files on your computer, you can work, save and review your efforts changes with greater efficiency!

Thank you to the writers of Sublime SFTP that have put together such a fantastic solution to this problem, my coworker Phillip (make sure to check out his site) who was the first to make me aware that a solution to this problem even existed, and to LevelUpTuts who put together a great tutorial about how to configure this package.

6 years ago Short URL 5 Comments

Sublime

connecting to your site through sublime/efficient programming/ftp to remote/quick build site/quick save for site/sftp to remote server/sublime text

Mar

Running Scripts from Sublime Directly in Your Virtual Environment

What’s better than having a virtual environment setup? Being able to run scripts directly from within Sublime on that virtual environment!

Here’s how you set this up:

Open up Sublime.
Navigate to Tools → Build System → New Build System.
A new tab will open up in your Sublime application. It’ll look like this:
Erase the contents of the tab and copy/paste the code below in its place:
```
{
	"cmd": ["/absolute-file-path-to-your-env/bin/python", "$file"],
	 "selector": "source.python"
}
```
Make sure to change the path (the first item in the “cmd” array) to the absolute path to your virtual environment version of Python.
Save the file. Make sure to name it as you would like to see it in the build directory.
Now select the virtual environment build from the Build System menu and get to programming.

Thank you to Shang Liang for originally posting on this.

6 years ago Short URL Comments

Python/Sublime

efficient programming/executing scripts in sublime/programming/python/running python in sublime/running virtual environment in sublime/sublime/sublime text

Mar

Setting Up Virtual Environments for Python

I’ve been greatly encouraged by colleagues and friends to try working on a MacBook instead of a Windows machine. Given that there is no cost to me, since the computer is provided by my company, I thought I’d give it a try. Thus far, I’ve been enjoying it greatly. And one of my favorite things that I’ve learned thus far is the ease with which one can build and manage virtual environments.

A virtual environment is different from a virtual machine. A virtual machine requires allocation of disk space and RAM. A virtual environment is simply an isolated working copy of Python. The two main benefits to setting up virtual environments are

that you can work in two completely different environments simultaneously and
you create accountability and tracking for changes made to the environment that can later be replicated by a team member if they decide to pick up on your project

Now, if you’re like me when I first heard about this, you’re probably feeling a bit intimidated. No worries! Let’s do a quick step-by-step on how to set all of this up so that you can start configuring and using your own virtual environments.

Start by installing the virtualenv module on your machine via Terminal. To do this, use the command
```
$ pip install virtualenv
```
If you don’t have pip already installed, I would encourage you to install it using the following command:
```
$ sudo easy_install pip
```
It’ll definitely come in great aid later when you’re installing packages in all your new environments.
Now that you have the virtualenv module installed on your machine, you can start setting up virtual environments. Yes, it’s that simple!To start off, it’s best practice that you setup your virtual machine within the folder of the project that you are working on. So, through the Terminal, navigate to the folder where your project is stored.
Once within the directory, create the virtual environment using the command
```
$ virtualenv project
```
Another best practice is to name your virtual environment the name of the project on which you are working. In my case, the name project is befitting, but make sure to select the right name for yourself.

Note that if you have multiple instances of Python installed on your machine, you can also specify which instance you would like to be used in this particular virtual environment. We do not specify that in the command above, so the version used in this particular virtual environment will just be the default version on the machine.
Now, before we start using our new environment, we need to configure it with modules specific to our project. To do so, activate the environment using the command
```
$ source project/bin/activate
```
Make sure to change the first word after “source” to the name of your own virtual environment. The name of your environment will be added in front of your command line once you’ve activated the environment.

When your virtual environment is active, configure it as you wish. In this example, I install module elasticsearch in my project virtual environment:
Once you complete configuring your environment, quit configuration by using the command
```
$ deactivate
```
To verify that your virtual environment is indeed different from your defaulted edition of Python, you can run a simple query testing for new modules in the defaulted Python version and then again in the virtual environment version. Note in my example that elasticsearch is present in my virtual environment, but not in my defaulted version of Python:
Now that you have your virtual environment setup, you can run scripts specific to your project within that virtual environment without having to modify your defaulted version or affecting other projects you may be working on. Additionally, you can share your virtual environment with collaborating colleagues. To share your environment, while in your project’s directory within the Terminal, use the following command to create a requirements file:
```
$  pip freeze > requirements.txt
```
This will create a file in your project folder with all the modules present in your virtual environment.

When collaborating with other people, send them this requirements files, have them place it in their project directory, then navigate to that directory via their Terminal, and run the following command:
```
$  pip install -r requirements.txt
```
This will set up a matching virtual environment on their machine so that they can run your files without any issues.

For those seeking to learn more about virtual environments and all the things you can do with them, I highly encourage you to check out the full documentation for the module, which can be found here. A big thank you to my colleague who took time out of her day today to educate me about this.

6 years ago Short URL 2 Comments

Python

macbook/modules/python/running python on mac/setting up virtual environments/terminal/virtual environments/virtualenv

Mar

Installing MySQL on Microsoft Windows: A Simple Guide

Having MySQL installed on your personal computer is helpful when wanting to work offline or when wanting to test table configuration and procedures without affecting others’ work. The process is fairly straightforward, but the official documentation on the MySQL website certainly doesn’t give that effect. It’s a labyrinth of hyperlinks, Google searches for missing components, and unanswered questions regarding what to click and when to click it.

Therefore, I thought I’d try to compile most of the necessary downloadable components along with step-by-step information in one place so that others may install it quickly and easily.

Go here and download “Windows (x86, 32-bit), MSI Installer” (mysql-installer-community-5.6.23.0.msi). You will need to log in or create an account with Oracle.
Once the file is fully downloaded, launch the installation wizard by double-clicking the downloaded file.
Once the wizard launches, click the “Add” button in the top right corner and accept the license agreement.
On the screen labeled “Select Products and Features” download the products and features that you might need. These are the ones I elected to download:
1. MySQL Server 5.6.23 – X86: MySql Servers > MySQL Server > MySQL Server 5.6
2. MySQL For Excel 1.3.3 – X86: Applications > MySQL For Excel > MySQL for Excel 1.3
3. Connector/Python (2.7) 2.0.3 – X86: MySQL Connectors > Connector/Python > Connector/Python 2.0 for Python 2.7
4. MySQL Documentation 5.6.23 – X86: Documentation > MySQL Documentation > MySQL Documentation 5.6
5. Samples and Examples 5.6.23 – X86: Documentation > Samples and Examples > Samples and Examples 5.6
Note: Feel free to download anything else you might feel is necessary to your operations or even pass on some of the bonuses I opted to download. MySQL Server is necessary — that’s the main component you want to download. Everything else is optional. I opted for Python connectors and Excel plugins since I definitely rely on Python connectivity for a lot of my projects and thought that Excel might be useful in the future. Documentation and Examples are recommended in case you might ever get into trouble, but are not required. Review the menus and options available and determine what bonuses you might prefer.
On the Installation screen, click “Execute”. This will start the installation of all components selected on the previous screen.
Once all components have finished downloading, click the Next button and begin the configuration of your MySQL server.
1. Most items on the Types and Networking page can be left as is. I didn’t touch any of the default settings.
2. Set up your root user password and configure any other users. When setting my user, since I’m just planning to work on my local server I selected the localhost server option.
3. The next page is for configuring MySQL as a Windows Service. These are pretty standard and what enables your computer to run as a MySQL server. You can access Windows services by opening services.msc via Run… Here’s a breakdown of what each of these settings mean (I kept all mine as is):
  1. Configure MySQL Server as a Windows Service: If you are not planning to run MySQL for your local server, then it may be worth it to skip this. This option is most likely required if you plan to use your computer as a localhost. I’m not sure if MySQL has an option to run the service interactively (which would be the alternative to a Windows service).
  2. Windows Service Name: The name of the service as it will be in services.msc.
  3. Start the MySQL server at System Startup: As the name suggests, this will configure the service to startup at boot. If you don’t plan to use MySQL regularly or have limited processing capacity, you may want to uncheck this and turn the service on manually when you want to utilize localhost capabilities.
  4. Run Windows Service as…: This is for permissions configuration. If you don’t want the Windows service to have full system accessibility, you may elect to set up a custom user with limited permissions.
4. Click “Execute” on the next screen to configure your application.
5. Click “Finish”.
If you elected to install Samples and Examples, you will be taken back to the Product Configuration screen. You will be required to configure this component, as well, prior to completing installation. Click “Next” to begin this configuration.
1. On the Connect to Server page, “Check” the root user in order to be able to click the “Next” button.
2. “Execute” configuration.
“Finish” your installation. Copy the installation log, if you so desire.

And that’s it! Use your preferred MySQL Management Tool (I like HeidiSQL) to connect to your local server and start working.

You might not be able to download certain components without downloading other related applications. Here are some of the common applications required prior to installing MySQL on my computer:

6 years ago Short URL Comments

MySQL

configuration/installation/localhost configuration/MySQL/mysql on windows/setting up/starting out

Feb

Downloading Files in Pentaho Kettle

For one of my transformations in Pentaho Kettle, I wanted to download data from an internet source. The source did not have an API, so I could not use a REST step to call on it. Instead, the source had a link that connected to a CSV that could then be downloaded in the browser. I wanted to download the data from this URL and automatically place it on my computer so that I could manipulate it as necessary. To do this, I used an HTTP step. This step is only available in Jobs (not in Transformations). The icon looks like this:

This is what the menu looks like when you double-click on the step:

The main fields to look at here are the “URL” field and the “Target file” field.

URL: This field is where you specify the URL to which you want Kettle to connect in order to access the file you would like to download.
Target file: This field is where you specify where you want Kettle to store the file once it’s been downloaded. It’s best to use variable locations in order to make sure that you can run this from any computer without running into issues with directory setup and names.

This is what my completed step looked like:

You can download the Job I set up here. Feel free to manipulate the setup in order for it to suit your needs.

You’ll noticed that Kettle also provides options for authentication (in case there is a login process involved in accessing the file) and also for uploading a file. I did not experiment with either of these options, but would be interested in hearing from those that have.

As you can imagine, having access to this step can be fairly useful. Now, what happens if we wanted to download multiple files at once? That’s where things get a bit more complicated. To see my solution to that issue, you can download this Job. The solution here is not as clear-cut as when we download one file from a single source. Here’s a step-by-step of how I set up the mutli-file download job:

START: Naturally, I start the job with a START step.
PATH: Next, I use the “Set Variables” step to set my PATH variable. This is the variable I will use to specify the directory in which I would like to save the files once I download them. (Note: I could have also used this same process of assigning a PATH variable in the last job, but instead elected to specify the path directly in the HTTP step. This step is not truly necessary, but is good habit. Use a static directory here, since path variables get a bit murky when getting passed from one job to another. The original setup I used – C:\temp – may not work on your machine, so make sure to change it as necessary before running my job.)
FILES: Here I used a “Transformation” step to call on a transformation named files.ktr that I set up in conjunction with this job. That transformation identifies what to name the file once it’s downloaded and the URL source of the file. Here’s that transformation step-by-step:
- Data Grid: I set up a data grid with the columns filename and url. Under the Data tab, I specify the data for these columns:
- Copy Rows to Results: Next I copy the created rows to the job using the “Copy Rows to Results” step. Note that between this step and the preceding step you can use JavaScript or some other combination of steps to add uniqueness to your filenames. I’ll leave that up to you to figure out.
DOWNLOAD: Next I reference a job using the step “Job”. This job is structured similar to our previous single-file download job, but instead of referencing constants, we now reference the variables we set up. This is where all the magic happens, so note the changes we made:
- While still configuring the job setup in the MAIN job, we specify under the “Advanced” tab to “Copy the results to parameters” and also to “Execute for every input row”.
- Still in the setup of the DOWNLOAD job in the MAIN job, we also specify that we are passing parameters:
- Now in the DOWNLOAD job, we go to Edit > Settings and specify under the Parameters tab for the job to anticipate and utilize the parameters FILENAME and URL.
- Lastly, in the HTTP step in the DOWNLOAD job, we use the parameter names instead of the constants we used in the single-file download.
SUCCESS: We connect to the Success step to mark the completion of the job.

And that’s it! A bit more complicated, but definitely handy for downloading batches of files.

Big thanks to Raffael Volger who initially posted on this.

6 years ago Short URL 1 Comment

Pentaho Kettle

File Download/HTTP/Job/Job Within a Job/Multiple File Download/Pentaho Kettle/Transformation

Feb

Running Scripts in Pentaho Kettle

WAIT! Before you read this, please know that I’ve published an update to this article here. I think the update is much more useful, so maybe read the update and then come back? Or don’t come back at all? Whatever you prefer!

Recently, I found myself needing to run a Python script from inside Pentaho Kettle. I wanted to convert a CSV file into JSON format and found that Kettle was running for an extremely long time just to complete this simple process. Since I already had a solid Python script in place to complete this task, I decided I could use that instead of relying on the traditional Kettle steps.

To run an external script in Kettle, you need to use the Shell step. This step is currently (as of Pentaho Kettle 5.2.0.0) only available in Jobs – not Transformations. The step icon looks like this:

Shell Command

When you double-click on the step, you will encounter a menu like this:

The main items you should be concerned with are the fields “Script file name” and “Working directory”.

Script file name: This is the name of the script you would like to run. Alternatively, if you would like to input custom code, you can use the Script tab to do so. In my case, I had a saved .py script that I could use. I specified that script in this field. Make sure to include the file location in the name.
Working directory: This is where the Python shell can be found. In my case, this is a folder on the C drive. You do not need to specify the exact executable – just the folder in which it’s present.

And that’s it! This is how my final configured step looked:

Since all my scripting was in the included Python file, I did not rely on the Script tab for anything. In the case that a file is specified, Kettle automatically fills the Script tab with the command “python [file name]”.

As can be seen from the screenshots, Kettle also provides options for logging. If your script outputs information about its progress, timing, or anything else, you can store that information in a particular file.

Kettle also offers specific options for iterating scripts over every row.

I did not experiment with either the logging option or the iteration option, but would be interested in hearing from anybody that might have. Happy scripting!

6 years ago Short URL 4 Comments

Pentaho Kettle

Jobs in Kettle/Pentaho Kettle/Python in Kettle/Running Scripts

Feb

Pentaho Kettle MySQL Connection Errors

It’s been a minute.

I’ve been working on a number of personal projects recently and have not taken the time to document my development. I’ve learned a ton about everything from JavaScript, to ETL software Pentaho Kettle, to MySQL and Python and feel like now might be a good time to recap some of the issues I ran into during my development and also the solutions I found/devised as workarounds. More than anything, I hope this helps others that run into similar issues (and possibly also help me if by accident I run into these issues again).

The first issue I would like to recount is trouble in connecting to my GoDaddy MySQL server via Pentaho Kettle. For the longest time, this was an absolute struggle for me with no seeming explanation in site. I had set up my MySQL server on my hosting service, GoDaddy, without any issue and had been able to connect successfully via HeidiSQL. However, when I was attempting to set up my MySQL connection in Pentaho Kettle, I kept getting this absolutely horrendous error message:


Error connecting to database [devdb2] : org.pentaho.di.core.exception.KettleDatabaseException: 
Error occured while trying to connect to the database

Exception while loading class
org.gjt.mm.mysql.Driver


org.pentaho.di.core.exception.KettleDatabaseException: 
Error occured while trying to connect to the database

Exception while loading class
org.gjt.mm.mysql.Driver


    at org.pentaho.di.core.database.Database.normalConnect(Database.java:368)
    at org.pentaho.di.core.database.Database.connect(Database.java:317)
    at org.pentaho.di.core.database.Database.connect(Database.java:279)
    at org.pentaho.di.core.database.Database.connect(Database.java:269)
    at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:86)
    at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2464)
    at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:533)
    at sun.reflect.GeneratedMethodAccessor55.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:329)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:139)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:123)
    at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:26)
    at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:119)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.eclipse.jface.window.Window.runEventLoop(Window.java:820)
    at org.eclipse.jface.window.Window.open(Window.java:796)
    at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:378)
    at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:304)
    at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:115)
    at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:62)
    at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.newConnection(SpoonDBDelegate.java:493)
    at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.newConnection(SpoonDBDelegate.java:478)
    at org.pentaho.di.ui.spoon.Spoon.newConnection(Spoon.java:7770)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:329)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:139)
    at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:123)
    at org.pentaho.ui.xul.swt.tags.SwtMenuitem.access$100(SwtMenuitem.java:27)
    at org.pentaho.ui.xul.swt.tags.SwtMenuitem$1.widgetSelected(SwtMenuitem.java:77)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1183)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:6966)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:567)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.pentaho.commons.launcher.Launcher.main(Launcher.java:134)
Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
Exception while loading class
org.gjt.mm.mysql.Driver

    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:423)
    at org.pentaho.di.core.database.Database.normalConnect(Database.java:352)
    ... 50 more
Caused by: java.lang.ClassNotFoundException: org.gjt.mm.mysql.Driver
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Unknown Source)
    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:414)
    ... 51 more

I still shudder looking at this.

Fortunately, the solution to this problem is not a complicated one. I found the answer on this post from StackOverflow. The reason the connection is failing is due to a missing MySQL Java connector library in Pentaho Kettle. This is how I solved the issue on my computer:

I downloaded the most recent version of the MySQL Java Connector. (I needed to create an Oracle MySQL account in order to download this driver. To get the .zip format, I selected the Platform Independent option from the drop-down menu.)
Extract the files from the downloaded .zip file and find the one labeled mysql-connector-java-5.1.34-bin.jar.
Place this file into your Pentaho Kettle lib folder. The address for that should be something like C:\Program Files\pentaho\kettle\data-integration\lib.
Restart Pentaho Kettle.

6 years ago Short URL Comments

Pentaho Kettle

Connection Issues/ETL/GoDaddy/Java/MySQL/Pentaho Kettle/Transformations/Troubleshooting

Apr

Troubleshooting SQL-Based Software

The company where I currently work produces a software solution that relies on SQL databases to store its information. A lot of the issues I encounter on a daily basis have to do with performance: how come a certain module is taking so long to open or why is a certain procedure running long?

Issues with performance are exciting. In a way, the process of finding optimal performance is a never-ending hunt. Performance can be improved in a multitude of ways: by enhancing the manner in which the software’s graphic user interface interacts with the underlying tables, changing the indexes used on certain tables, rewriting the queries used to access information, etc.

The possibilities are endless!

But I’m getting ahead of myself. With this post, I hope to focus on the method I use to determine the source of a performance issue. Thus, I’ll be walking you through some of the steps I take when troubleshooting such issues. I hope that these examples can be of use to you.

The Issue

Fictional e-mail:

Hi Moran,

I was trying to open a certain module in the application today and noticed that the process was running a bit longer than usual. Do you know what could be the cause?

Thanks,

Boss

So, as copied above, I received a fictional e-mail from my manager this morning. It seems that he’s having trouble opening a certain module in the application. The module is able to open, but it takes a long time to do so–longer than it used to take.

Given that I am familiar with the make-up of this application, I know several things and can begin my troubleshooting there. These are my assumptions:

The software is built on SQL Server databases.
Information within the software is accessed using queries that collect information from the SQL Server databases.
This is not an issue with the configuration of the software given that opening this module is a routine operation.

With this in mind, I formulate my plan of attack. I decide on the following:

Recreate the issue on my local machine.
While the issue is occurring, conduct a review of the SQL processes.
Dig deeper, as necessary.

Step 1: Recreating the Issue

Given that my manager and I are able to connect to the same servers and access the same user account, I am able to easily recreate the issue on my machine.

In other instances, this might be harder to do. You may need to screen share with the user and watch as they recreate the issue or, in the case that the issue is very unique, have them provide you with a series of detailed steps on how they ran into the issue.

For those short on writing, I recommend using Screencast-o-Matic for quick and easy recording of your desktop. No installation necessary!

Step 2: Initial Backend Review

Once the issue has been recreated, the first thing I like to do is run the following command in SQL Server Management Studio:

sp_who2 active

You can run this command on any database. The output provides you with a record of all active SPIDs on your server. When running this, I’m interested in finding which SPID and process is associated with the issue I’m witnessing and whether there are any blocks in the system (represented by SPID values in the “BlkBy” column). This is my output from running the command:

The first thing I notice is that there are no blocks occurring in my system.

Next, I try to discern which activities are of interest to me. I identify them by the information reported in the HostName, DBName, and LastBatch columns. I am looking for SPIDs initiated from my machine (MNACHUM), in the “AdventureWorks” database, and that were launched about five minutes ago. SPID 56 is the one I identify as corresponding to my issue. SPID 55 comes close, but is actually connected to the sp_who2 command I just launched in SSMS; I am able to discern this fact by noting that the last batch for that SPID was executed just seconds ago.

As reported in my query output, SPID 56 is SUSPENDED. This might be the reason why my manager’s application is taking a long time to open the module. I do some more digging by running the following diagnostic command:

DBCC INPUTBUFFER(56)

The value in the parentheses references the SPID for the activity of interest. The command, when run, returns information about the last command sent out by that particular activity. This is my output:

From this, I can discern that the issue is occurring with a particular stored procedure, dbo.sp_run_a_while. However, this might be a long procedure with many moving parts. To troubleshoot the exact cause of slow performance within the stored procedure, I’ll need to dig deeper.

Step 3: Digging Deeper

So, I’ve determined that the slowness witnessed by my manager is due to a particular stored procedure. But what part of it?

If I am able to open the stored procedure, I could review it and look for a command similar to the one associated with the activity SPID reported by my sp_who2 command (if you go back and review the screenshot from earlier, you’ll notice that the associated command for SPID 56 is WAITFOR).

However, I don’t consider this method particularly efficient, since it requires scouring through the code of the stored procedure. At times, these procedures can be thousands of lines and have many similar commands.

A better procedure to follow is to do one of two things:

Query the sys.dm_exec_requests table with a CROSS APPLY on the sys.dm_exec_sql_text table to determine the last command that was run by the stored procedure.
Set up and run a trace on the stored procedure to determine where it is getting hung up.

For the first option, you can use the following code I developed:

SELECT sql_handle,
statement_start_offset,
statement_end_offset,
plan_handle,
total_elapsed_time,
query_hash,
text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

The last column (text) of the output specifies the last command initiated by any of the active SPIDs on your server. Once again, be discerning in recognizing which of the rows actually corresponds to your particular SPID. In my case, it is the second row:

I can expand the column or copy and paste the text into the editor to review it further.

Alternatively, I can set up a trace to see where the procedure gets hung up. To do so,

From the top menu options, navigate to Tools –> SQL Server Profiler.
In the new window, connect to your server.
Open a new trace and configure it with the following Events under the Event Selection tab (make sure to check the “Show All Events” and “Show All Columns” options in the bottom right-hand corner to access these events):
1. Maintain the default settings
2. Under the Stored Procedure heading, select “SP: Completed”, “SP: Starting”, “SP: StmtCompleted”, “SP: StatementStarting”
3. Under the TSQL heading, select “SQL: StmtCompleted” and “SQL: StmtStarting”
Open the Column Filters window by selecting the option in the bottom right-hand corner and filter on the following items:
1. ApplicationName: should be like the one for your SPID (i.e. MNACHUM)
2. DatabaseName: should be like the one for your SPID (i.e. AdventureWorks)
3. Duration: helpful for issues with slow performance, set to “Greater Than 4000”
4. ObjectName: should be like your stored procedure (i.e. sp_run_a_while)
Start the trace.

In order to capture the long-running event, you will need to recreate the issue again while the trace is running.

When I did so in my environment, I found that the command within the stored procedure causing my manager slow performance was

WAITFOR DELAY '15:00';

Now that I know what’s causing the slowness I can communicate with the writers of the stored procedure and begin discussions as to whether the command is necessary and what can be done to improve its efficiency.

(Obviously, a WAITFOR command by nature is inefficient in this kind of setting. Maybe we’ll explore the troubleshooting of more complex queries in a different post.)

A big thank you to my colleague, Maxwell, who trained me and guided me on a lot of these troubleshooting techniques.

7 years ago Short URL Comments

SQL

active/AdventureWorks/Application/Cross Apply/DBCC/InputBuffer/queries/Software Review/SPID/sp_who2/SQL/SQL Server Management Studio/SQL-based/Troubleshooting

Apr

Moving Databases in SQL: Two Simple Methods

Recently, as part of a larger task, I was given the responsibility of moving databases from a permanent drive to a SAN drive. This needed to be done due to the possible increase in the size of the associated DATA and LOG files.

I was assisted in this task by a colleague from my own team and another from my company’s database support group. However, the process was fairly straight-forward and can probably be done by most anybody on their own. I’ve included in this post two methods for carrying out the process: via SQL Server Management Studio (SSMS) interface and via commands in the query window.

For this example, I’ll move the AdventureWorks database on my machine from directory “C:\Location1” to “C:\Location2”.

I’ll start with the method that utilizes the SQL interface and then discuss how to do the same procedure using the commands in the query window.

Database: AdventureWorks
Current Location: C:\Location1
Destination Location: C:\Location2

Moving Databases Using SSMS Interface

Open your Server Instance in SSMS, collapse the Databases option, and find the database that you would like to move in your directory.

Highlight the database, right-click it, and open up Properties. Navigate to the Files Tab. This page shows you information about where the current DATA and LOG files for the database are stored.

Use the Path information to find where the files are currently placed in your environment. Open up the directory location and verify that the files are accessible.

Close the Properties window in SSMS. Right-click the database (i.e. AdventureWorks) and navigate to Tasks → Detach…
In this window, select from the options available for database detachment. There are two: Drop Connections and Update Statistics. In order to properly move your database, you will need to drop connections. Updating statistics can be done at your own discretion.

Hit “OK” once you’ve made your selections. The database should disappear from your collapsed database options in SSMS.

In Windows Explorer, move your LOG and DATA files from their current directory to their new destination directory. This is done outside of SSMS.

Once the files have been moved, in SSMS right-click the Databases folder under the server instance name (i.e. MNACHUM\SQL2008R2) and select the Attach… option.
In the new window, select the Add… option under the “Databases to Attach” frame. A directory window will come up. Navigate to the directory where you placed your DATA file and add it. Information about the DATA and LOG file should be automatically populated in the bottom frame, “[DATABASE NAME] details”.Note: If you notice that the auto-populated information in the bottom frame is incorrect, you can use the ellipsis button next to the individual LOG and DATA file paths to change the files of reference.

Once you have all the information configured correctly, press OK. You’ve now re-attached the database (i.e. AdventureWorks) with the associated files in the new directory location. You’ve effectively moved your databases!

Moving Databases Using SSMS Query Window

Start by getting the information about the current database configuration, specifically the associated LOG and DATA file names, file locations, and current statuses.
```
USE master
GO
SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('AdventureWorks')
```
Note: It’s a good idea to keep the results from this query open in one window as a reference while you run the other commands in this guide in a different window.
Next, take the servers offline.
```
USE master
GO
ALTER DATABASE AdventureWorks SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
```
Note: The ROLLBACK IMMEDIATE portion of the command refers to any processes that may be running on the database at the moment of taking it offline. Instead of stopping the process abruptly, each individual process will be rolled back to its most recent checkpoint.

If you choose to simply take the database offline without the ROLLBACK IMMEDIATE option, SSMS will wait for all the current processes to get to a logical stopping point before taking the database offline. This could take a while if you have long-running processes. Alternatively, if you chose to go offline without the ROLLBACK IMMEDIATE option, you can kill the processes using the “KILL [SPID NUMBER]” option; however, work done by the process will be abruptly interrupted as opposed to rolled back.

Ultimately, ROLLBACK IMMEDIATE is your friend and your best option in this case.
In Windows Explorer, move your LOG and DATA files from their current directory to their new destination directory. This is done outside of SSMS.
In SSMS, update the LOG and DATA information associated with the database (i.e. AdventureWorks) using the following commands.
```
USE master
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
( NAME = AdventureWorks_Data,
FILENAME = 'C:\Location2\AdventureWorks_Data.mdf')

USE master
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
( NAME = AdventureWorks_Log,
FILENAME = 'C:\Location2\AdventureWorks_log.ldf') 
```
Note: The field to be filled for NAME is the information provided under the “name” column in our first query. In this case, “AdventureWorks_Data” and “AdventureWorks_Log”.

Put the database back online using the following command.

USE master
GO
ALTER DATABASE AdventureWorks SET ONLINE
GO

Lastly, using our initial SELECT query, verify that the database was properly transferred and that the physical_name column reports that the DATA and LOG files are now in the new location.
```
USE master
GO
SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('AdventureWorks')
```
You’ve now moved the database via the query window.

A big thanks to Suresh Raavi, who originally posted on this topic on SQLServerZest.com.

If you run into any issues or have any concerns regarding the methods suggested, please don’t hesitate to comment or contact me at morannachum@gmail.com.

7 years ago Short URL 1 Comment

SQL

AdventureWorks/Command Query/DATA/files/LOG/Moving Databases/OFFLINE/ONLINE/SQL/SQL Server Management Studio/SSMS