Tuesday, May 31, 2011

Dealing With High CPU% of SQL Server 2005

Sometimes, we experience performance problem with high CPU% on DB Server. How can we detect which process take most "contribution" to this pheromone step by step?

Step 1. Check if SQL Server Process has problem or not?
 DECLARE @ts_now bigint;  
   SELECT @ts_now = cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info   
   SELECT TOP(10) SQLProcessUtilization AS [SQL Server Process CPU Utilization],  
           SystemIdle AS [System Idle Process],  
           100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],  
           DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]  
   FROM (  
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,  
         record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')  
         AS [SystemIdle],  
         record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')  
         AS [SQLProcessUtilization], [timestamp]  
      FROM (  
         SELECT [timestamp], CONVERT(xml, record) AS [record]  
         FROM sys.dm_os_ring_buffers  
         WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'  
         AND record LIKE '%<SystemHealth>%') AS x  
      ) AS y  
 ORDER BY record_id DESC;  

Then you will get result like this:

The picture above shows 70% of CPU% is taking by SQL Server 2005 , not by other processes on DB server.

Step2. Drill down to particular Loginuser and SPID which is taking most of CPU%:
 select  loginame,  *    
 from  master.dbo.sysprocesses  
 where  spid> 50  
 order by cpu desc   

PS: Why "spid > 50" here? Notice that SPIDs 1 to 50 are reserved for internal SQL Server processes, while SPIDs 51 and above are external connections. So most of time we assume that the suspects for High CPU% are from external connections.

Step3: Query the most expensive CPU process by spid (taking spid = 102 for example):

 dbcc inputbuffer(102)   

Then you can start to take further actions for tuning expensive Query or SPs... or just kill the abnormal ones for free :)

Monday, May 30, 2011

To enable Performance Profiling log with Guice AOP and Logback

I always want my methods to be profiled, so that I can learn about how fast/slow it is from a high level perspective at first. Then break it down to make it even faster.

I am using WebDriver to do automation testing, so i want to trace certain end user experience from performance perspective as well.

For example, if I want to measure Login action's response time, my intention is to enable my profiling log by just add @Profiled annotation in front of corresponding "Login" method:
 
@Profiled
public void clickLoginBtn() throws InterruptedException{  
           driver.findElement(By.id("login")).click();  
           if(!myWaiter.waitForMe(By.cssSelector("div.starcite-hyperlink"), 25, timeout)) return ;  
      }  

To make @Profiled work, i chose Guice as it is so called light weight and easy of use:

Step1: Create an annotation called Profiled:
 import java.lang.annotation.ElementType;  
 import java.lang.annotation.Retention;  
 import java.lang.annotation.RetentionPolicy;  
 import java.lang.annotation.Target;  
 @Retention(RetentionPolicy.RUNTIME)  
 @Target(ElementType.METHOD)  
 public @interface Profiled {  
 }  

Step2:Create matchers for the classes and methods to be intercepted:

 import com.google.inject.AbstractModule;  
 import com.google.inject.matcher.Matchers;  
 public class ProfiledModule extends AbstractModule {  
      @Override  
      public void configure() {  
           // TODO Auto-generated method stub  
           PerfTracing perftracing = new PerfTracing();  
           requestInjection(perftracing);  
           bindInterceptor(  
                     Matchers.any(),  
            Matchers.annotatedWith(Profiled.class),  
            perftracing);  
        }  
      }  

Step3: Write your own MethodInterceptor to do profiling using Logback
 import java.util.Arrays;  
 import org.aopalliance.intercept.MethodInterceptor;  
 import org.aopalliance.intercept.MethodInvocation;  
 import org.slf4j.Logger;  
 import org.slf4j.LoggerFactory;  
 public class PerfTracing implements MethodInterceptor{  
      private Logger logger = LoggerFactory.getLogger("PerfLog");  
      @Override  
      public Object invoke( MethodInvocation invocation) throws Throwable {  
           // TODO Auto-generated method stub  
           long start = System.nanoTime();  
     try {  
       return invocation.proceed();  
     }   
     finally {  
          if(logger.isDebugEnabled()){  
               Object[] paramArray = {     invocation.getMethod().getName(),   
                                             Arrays.toString(invocation.getArguments()),   
                                             (System.nanoTime() - start) / 1000000};  
            logger.debug("Invocation of method: {} with parameters: {} took: {} ms." , paramArray);  
          }  
     }  
      }  
 }  

Why I choose Logback for logging? please refer to this post :)

For Logback configuration, here is my sample:
 <?xml version="1.0" encoding="UTF-8"?>  
 <!-- Reference Manual http://logback.qos.ch/manual/index.html -->  
 <configuration>  
   <appender name="stdout" class="ch.qos.logback.core.ConsoleAppender">   
     <encoder charset="UTF-8">  
       <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger - %msg%n</pattern>  
     </encoder>  
   </appender>   
   <appender name="PerfLog" class="ch.qos.logback.core.rolling.RollingFileAppender">  
     <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">  
       <fileNamePattern>PerfLog-%d{yyyy-MM-dd}.log</fileNamePattern>  
       <maxHistory>30</maxHistory>  
     </rollingPolicy>   
     <encoder>  
       <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger - %msg%n</pattern>  
     </encoder>  
   </appender>  
   <logger name="PerfLog" additivity="false">  
        <level value="DEBUG"/>  
        <appender-ref ref="PerfLog" />  
   </logger>  
   <root level="ERROR">  
     <appender-ref ref="stdout" />  
   </root>  
 </configuration>  

The output in your log file should be like this:
 15:02:49.351 [main] DEBUG PerfLog - Invocation of method: clickLoginBtn with parameters: [] took: 3027 ms.  

Please notice Guice AOP's Limitations:
* Classes must be public or package-private.
* Classes must be non-final
* Methods must be public, package-private or protected
* Methods must be non-final
* Instances must be created by Guice by an @Inject-annotated or no-argument constructor

So You may have to change your source code to make Guice AOP work properly.

Saturday, May 21, 2011

My Current “Waiter” Class used by WebDriver Tests

My Current “Waiter” Class used by WebDriver automation tests, so keep away from your hard coded Thread.sleep() :

 import java.util.List;  
 import org.openqa.selenium.By;  
 import org.openqa.selenium.WebDriver;  
 import org.openqa.selenium.WebElement;  
 import org.openqa.selenium.support.ui.WebDriverWait;  
 import com.google.common.base.Function;  
 /*  
  * Two usage examples in your test code:  
  * MyWaiter myWaiter = new MyWaiter(driver);  
   WebElement search = myWaiter.waitForMe(By.name("btnG"), 10);  
   or  
   if(!myWaiter.waitForMe(By.name("btnG"), 1, 10)) return;  
   or if (!myWaiter.waitForMeDisappear(By.name("btnG"), 10)) return;  
  */  
 public class MyWaiter {  
      private WebDriver driver;  
      public MyWaiter(WebDriver driver){  
           this.driver = driver;  
      }  
      public WebElement waitForMe(By locatorname, int timeout){  
           WebDriverWait wait = new WebDriverWait(driver, timeout);  
           return wait.until(MyWaiter.presenceOfElementLocated(locatorname));  
      }  
      //Given certain number of web element to see if it is found within timeout  
      public Boolean waitForMe(By locatorname, int count, int timeout) throws InterruptedException{  
           long ctime = System.currentTimeMillis();  
           while ((timeout*1000 > System.currentTimeMillis()- ctime)){  
                List<WebElement> elementList = driver.findElements(locatorname);  
                if ((elementList.size()< count)){  
                     Thread.sleep(300);  
                }  
                //element is found within timeout   
                else  
                     return true;  
           }  
           // otherwise element is not found within timeout  
           return false;  
      }  
      //Given certain number of web element to see if it is disappear within timeout  
      public Boolean waitForMeDisappear(By locatorname, int timeout) throws InterruptedException{  
           long ctime = System.currentTimeMillis();  
           while ((timeout*1000 > System.currentTimeMillis()- ctime)){  
                List<WebElement> elementList = driver.findElements(locatorname);  
                if ((elementList.size()!= 0)){  
                     Thread.sleep(300);  
                }  
                //element is Disappear within timeout   
                else  
                     return true;  
           }  
           // otherwise element is still show up within timeout  
           return false;  
      }  
      public static Function<WebDriver, WebElement> presenceOfElementLocated(final By locator) {  
           // TODO Auto-generated method stub  
           return new Function<WebDriver, WebElement>() {  
                @Override  
                public WebElement apply(WebDriver driver) {  
                     if (driver.findElement(locator)!= null){  
                          return driver.findElement(locator);  
                     }  
                     else return null;  
                }  
           };  
      }  
 }  

Another useful method on implicitwait(), I also wrote a post related to it, FYI : http://joychester.blogspot.com/2010/09/webdriver-wait-is-easier-after-using.html

Wednesday, May 18, 2011

6 years since we met -- 2011-01-26

Cheng Shawn

PS: for how to draw a heart, please take a look at this link :)




Tuesday, May 17, 2011

Compressing Image with Image Opertimazer

Compressing plain text is easy by Zipping the files, however, images are said to be compressed by default, so often we are ignoring optimizing it.

I just found one awesome tool to optimize the website images without sacrificing much quality:Image Opertimazer

Here is the default Image, original size is 52KB:

After optimization, the size is 33KB, about 20% saving:

And you may heard of WebP created by Google, it is said that "WebP images were 39.8% smaller than jpeg images of similar quality", give it a try! Update: another awesome service to do Image compression, called JPEGmini : http://www.jpegmini.com/main/home