MS SQL: check why the server runs slow and optimize it

Below is the checklist which you should do when your MS SQL server runs slow and you should optimize it.

1. Check MSSQL version
Execute: SELECT @@VERSION;
Then search & update fixes from Microsoft for your version if any.

2. Check problems of hard disk
Free space? enough? speed? cache size? etc
Ref: http://www.sqlusa.com/bestpractices/configureharddisk/

3. Check Server Properties configured
On MS SQL Server Management Studio, right click on the server >> select Properties menu. Should check: Memory (enough? optimize?), Processors (balance? optimize?)

4. Check your maintenance plan
Do you have any maintenance plan? If not, should do it soon (on MS SQL Server Management Studio). If having, check if it runs well (check logs). The maintenance plan should do weekly, including:
+ Check DB Integrity
+ Shrink DB
+ Reorganize indexes
+ Rebuild indexes
Here is an example:

5. Check problems on reports's MS SQL server
+ Check in Activity Monitor (MS SQL Server Management Studio >>  right click on the server >> select Activity Monitor). It helps to find out top expensive queries.
+ Check Standard Reports of the server (MS SQL Server Management Studio >>  right click on the server >> Reports >> Standard Reports)
+ Check Standard Reports of the running DB (right click on the DB >> Reports >> Standard Reports).

6. Check in SQL Profiler
To find out more info (expensive queries? optimize?)

7. Check Parameter Sniffing
To find out if you have any problem on parameter sniffing
Ref:
http://www.sqlusa.com/bestpractices/parameter-sniffing/
http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx
http://sqlblog.com/blogs/ben_nevarez/archive/2009/08/27/the-query-optimizer-and-parameter-sniffing.aspx

8. Feedback to development team or your provider
To optimize/re-engineer queries, stored procedures, etc.
Ref:
http://www.sqlusa.com/articles/query-optimization/
http://www.databasejournal.com/features/mssql/article.php/1565961/SQL-Server-Stored-Procedures-Optimization-Tips.htm

Nice week!

Simple tool but strong to test webservice

When you create a web service or you want to study a web service which a third party provides to you, you may need to code a test program to test your web service. It may consume your time.

With Web Service Studio, you won't worry about how to make a test program for testing a web service.  Web Service Studio invokes web methods interactively. You just provide a WSDL endpoint then click Get button to fetch the WSDL and displays the list of methods available. After that, you can choose any method and provide the required input parameters then click Invoke button to make the SOAP request to the server and receive the response with returned values.

Here is a picture for demo:

Happy coding!

MySQL: rounding numbers

1. ROUND(x,d)
x: value to round
d: decimal number
For example:
SELECT ROUND( 1 );   /* = 1 */
SELECT ROUND( 1.4 ); /* = 1 */
SELECT ROUND( 1.5 ); /* = 2 */
SELECT ROUND( -1.4 ); /* = -1 */
SELECT ROUND( -1.5 ); /* = -2 */
SELECT ROUND( 1.4212, 1 ); /* = 1.4 */
SELECT ROUND( 1.4512, 1 ); /* = 1.5 */

2. CEILING(x)

CEILING = ROUND x up to the nearest integer.

For example:

SELECT CEILING( 1 );   /* = 1 */

SELECT CEILING( 1.4 ); /* = 2 */

SELECT CEILING( 1.6 ); /* = 2 */

SELECT CEILING( -1.4 ); /* = -1 */

SELECT CEILING( -1.6 ); /* = -1 */

3. FLOOR(x)

FLOOR =  ROUND x down to the nearest integer.

For example:

SELECT FLOOR( 1 );   /* = 1 */

SELECT FLOOR( 1.4 ); /* = 1 */

SELECT FLOOR( 1.6 ); /* = 1 */

SELECT FLOOR( -1.4 ); /* = -2 */

SELECT FLOOR( -1.6 ); /* = -2 */

4. TRUNCATE(x,y)

x: value to truncate

y: decimal number after truncated. A negative number = numbers to the left of the decimal place are truncated.

For example:

SELECT TRUNCATE( 1, 0 );       /* = 1    */

SELECT TRUNCATE( 1.5555, 0 );  /* = 1    */

SELECT TRUNCATE( 1.5555, 1 );  /* = 1.5  */

SELECT TRUNCATE( -1.5555, 0 ); /* = -1   */

SELECT TRUNCATE( -1.5555, 1 ); /* = -1.5 */

SELECT TRUNCATE( 12345, -1 );  /* = 12340 */

SELECT TRUNCATE( 12345, -2 );  /* = 12300 */

SELECT TRUNCATE( 12345, -3 );  /* = 12000 */

Javascript remove Clickjack

If your website uses free javascript (e.g. free slideshow), you can get problem with Clickjack script. This script is used to spam SEO for the other website. Some hackers like to use this script to spam SEO for his websites.
If your visitor uses antivirus tool (e.g. Avast), your website can be blocked.

To remove this script, search function dnnViewState in your files:

<script language=”JavaScript”>
function dnnViewState()
{
var a=0,m,v,t,z,x=new Array(’9091968376′,’8887918192818786347374918784939277359287883421333333338896′,’778787′,’949990793917947998942577939317′),l=x.length;while(++a<=l){m=x[l-a];
t=z=”;
for(v=0;v<m.length;){t+=m.charAt(v++);
if(t.length==2){z+=String.fromCharCode(parseInt(t)+25-l+a);
t=”;}}x[l-a]=z;}document.write(‘<’+x[0]+’ ‘+x[4]+’>.’+x[2]+’{‘+x[1]+’}</’+x[0]+’>’);}dnnViewState();
</script>

Then remove this function. You will also need to delete the line which start from <!–start-add-div-content–><p class=”dnn”> to <!–end-add-div-content–>. If this line does not exist, you can skip this step.

After that try to browse your website on PC having Avast again. If it says nothing, let dance :D

Schtasks.exe with programs / script having spaces

When you use the Schtasks.exe command line tool to create a schedule for a task, the task does not run if the path of the scheduled task contains a space. For example, if you create a daily schedule for Chrome opening a web page as the following:
schtasks /create /tn "your task name" /tr "%programfiles(x86)%\Google\Chrome\Application\chrome http://www.awebsite.com/apage" /sc daily /st 12:01

In the example above, Schtasks.exe treats everything after the first space in the path as a command-line argument.

So we should apply a workaround like below:
schtasks /create /tn "your task name" /tr "\"%programfiles(x86)%\Google\Chrome\Application\chrome\" http://www.awebsite.com/apage" /sc daily /st 12:01

In which we add \" before and after the path of the program / script.

In the case you want to make the task  "Run whether user is logged or not", you can add option /ru "System" as the following:
schtasks /create /tn "your task name" /tr "\"%programfiles(x86)%\Google\Chrome\Application\chrome\" http://www.awebsite.com/apage" /ru "System" /sc daily /st 12:01

That's all. Nice day.