SQLServer tips

This page would be a growing list for things related to SQLServer. Mostly kept for the rainy season :)

1. How to find most expensive queries (for cache query plans only) 

(ref: http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/)

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.total_logical_writes, qs.last_logical_writes,

qs.total_worker_time,

qs.last_worker_time,

qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_logical_reads DESC -- logical reads

-- ORDER BY qs.total_logical_writes DESC -- logical writes

-- ORDER BY qs.total_worker_time DESC -- CPU time

2. Queries taking longest elapsed time

SELECT TOP 10

qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,

qs.total_elapsed_time / 1000000.0 AS total_seconds,

qs.execution_count,

SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,

o.name AS object_name,

DB_NAME(qt.dbid) AS database_name

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id

where qt.dbid = DB_ID()

ORDER BY average_seconds DESC;

3. Queries doing most I/O

SELECT TOP 10

(total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,

(total_logical_reads + total_logical_writes) AS total_IO,

qs.execution_count AS execution_count,

SUBSTRING (qt.text,qs.statement_start_offset/2,

(CASE WHEN qs.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) AS individual_query,

o.name AS object_name,

DB_NAME(qt.dbid) AS database_name

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id

where qt.dbid = DB_ID()

ORDER BY average_IO DESC;

4. Find blocking and blocked queries (sessions)

Use sp_who2

Create swap file for your VPS

Just got a VPS for your own use? RAM only 512MB? or maybe you are accustomed to out of memory error and need a way to increase your memory.
Then you got to have a swap file for your VPS, the size of which will depend on your usage.

One thing to remember, swap file is created on your storage. So don't expect it to be as fast as your RAM.

Here are the steps you can follow (works on Ubuntu 12.04):
1. Check for existing swap file, if you already have one then all are good.
    swapon -s

2. Create your file for the swap file
sudo dd if=/dev/zero of=/swapfile bs=1024 count=256k

3. Set it as swap file
sudo mkswap /swapfile

4. Register the swap file
sudo swapon /swapfile

5. Make it permanent on the system
sudo nano /etc/fstab

And add the following line at the end:
/swapfile       none    swap    sw      0       0 

6. Set the swappiness
echo 10 | sudo tee /proc/sys/vm/swappiness
echo vm.swappiness = 10 | sudo tee -a /etc/sysctl.conf

7. Protect the file

sudo chown root:root /swapfile 

sudo chmod 0600 /swapfile

That's all, enjoy your VPS.

Reference:

https://www.digitalocean.com/community/tutorials/how-to-add-swap-on-ubuntu-12-04

Configure reverse ssh tunneling

When you have a need to access your local PC from the internet, you might be interested in this blog.

Requirement:
1. Root access to a public server (or any kind of access as long as you can do things below :).
2. SSH client


Now, let's begin with the server:
1. Edit sshd_config located in /etc/ssh/ and add the line below
GatewayPorts clientspecified

2. Restart sshd
sudo service ssh restart

That's all for the server.


To the local PC we go:
1. Create reverse ssh tunneling using ssh client.
Open a terminal/console to run the ssh client. In windows you might use Putty.
ssh user@server -R server:serverport:localdestination:localport

2. Test the connection by using telnet
telnet server serverport
Expect no connection refused :)


The steps above is very simple but requires some configuration changes in the public server.

There's a more secure way but requires more effort on the client side which means only geek will be able to access it :) I'll discuss it later on a separate blog.

Setting up vnc for ubuntu desktop

If your installation is ubuntu desktop and you need to remote it using vnc, the steps below might help you to do it:
1. Update your package list
sudo apt-get update

2. Install Gnome desktop environment essential component and Gnome session manager
sudo apt-get install gnome-core gnome-session-fallback

3. Install vnc server
sudo apt-get install vnc4server

4. Start the vnc server
vncserver 
Note: find out the console port being assigned to you. There is a way to start up the connection with a pre defined port number (e.g. vncserver :1)

Example: the assigned port is 1 (5901)

New 'server:1 (server)' desktop is server:1

Starting applications specified in /home/user/.vnc/xstartup

Log file is /home/user/.vnc/user:1.log

5. Edit the xstartup file located /home/user/.vnc/ as below.

At first, when you open the vnc connection there will be only a terminal. You will need to start the gnome-session for the vnc connection.

#!/bin/sh

# Uncomment the following two lines for normal desktop:

 unset SESSION_MANAGER

#exec /etc/X11/xinit/xinitrc

[ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup

[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources

xsetroot -solid grey

vncconfig -iconic &

#x-terminal-emulator -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" &

#x-window-manager &

gnome-session --session=gnome-classic &

6. Restart the vncserver by killing it first then starting it up again

vncserver -kill :1

To access vnc from the client, you need to install vnc viewer app. 

The sample url for vnc: server_url:5901

the port number is assigned when you start the vnc connection.

Up to now, for ubuntu server I use Xfce desktop environment instead of gnome. It is lightweight and somehow I still can't setup the gnome env under vnc :)

Reference link:
http://coddswallop.wordpress.com/2012/05/09/ubuntu-12-04-precise-pangolin-complete-vnc-server-setup/

Configuring php for handling large file upload

Cut it short, what I need is to configure my nginx-php to be able to handle around 200M file upload.
So here are the configuration changes I need to make:
1. nginx.conf
    usually the file is placed in /etc/nginx/nginx.conf.
    The parameters to add inside http are:
    - client_max_body_size: specifies the maximum accepted body size of a client request
    - client_body_timeout: sets the read timeout for the request body from client
    http {

        ##
        # Basic Settings
        ##

        client_max_body_size 200m;
        client_body_timeout 600s;
       
        ....
   }

2. php.ini
    Since I use php5-fpm, this file is placed in /etc/php5/fpm/php.ini
    The parameters to change is:
    - post_max_size: Sets max size of post data allowed
    - upload_max_filesize: The maximum size of an uploaded file
    - max_execution_time:This sets the maximum time in seconds a script is allowed to run before it is terminated by the parser.
    - max_input_time:  This sets the maximum time in seconds a script is allowed to parse input data, like POST and GET.


After changing those variables, restart both nginx and php5-fpm and you're done!

Reference links:
http://wiki.processmaker.com/index.php/Nginx_and_PHP-FPM_Installation
https://rtcamp.com/tutorials/php/increase-file-upload-size-limit/
http://www.radinks.com/upload/config.php
http://wiki.nginx.org/HttpCoreModule#client_body_timeout