Tuesday, December 24, 2013

How to Debug an SQL Stored Procedure

Have you come across this situation where you need to know in which statement the stored procedure executed in SQL gives you the slow performance?

If you have and you still can't find the answer, then you come to the right page.

This is how to debug SQL stored procedure:
1. Open SQL Server Profiler.
2. Click New Trace and enter the connection you want.
3. You will get "Trace Properties" window. In the second tab, "Event Selection", click "Show all events".
4. You will get the whole events. But no, you don't want all actually. So scroll down to the 'Stored Procedures" row. Tick "SP:StmtStarting" and "SP:StmtCompleted".
5. Click Run.

After activating the profiler, go back to the SQL Management Studio and run the SP you want to debug. The profiler will show you statement by statement executed by the SP.

Sunday, December 1, 2013

Help! My notebook needs paracetamol ... It's having fever! (translate: notebook gets overheat)

Couple months ago my office notebook Lenovo Y400 got overheat that it shut down itself without even asking for my permission. Not even a single word!
I reported it to our hardware support. Suspecting it could be because of failed internal fan I suggested him to buy an external fan for laptop where I can place it at the bottom of my notebook. 

It did help a lot. For some months the frequency of overheat was less than previously. Nevertheless, now and then it still happened for some reasons that I couldn't figure it out. Maybe it was tired. Maybe it hated me for working too hard. Ha!

And then, recently it's getting worse. Even with the external fan plugged in, the notebook got overheat and shutdown like crazy. The dosage was almost like a baby. Every 3 hours it cried.  

But hey, you're wrong, I'm not a lady who gives up easily. To me, it's a sign that I should prepare a cup of coffee and observe the notebook. Watching it eye to eye (it's the troubleshooter in me!).

So there I was. With a cup of coffee. Started to observe. First of all, I installed a software called Core Temp so that I could monitor the temperature before it shut down. I noticed that it got overheat after some minutes after I plugged in the AC adapter. Noted. First clue. 

At first, I suspect it's related with hardware. Could be the battery. Or AC adapter. But simulating by removing the battery and also simulating charging when it was off proved me wrong.

On further observation, from that software I noticed that it was the CPU load that triggered the temperature to reach the maximum temperature i.e. 85 C. Noted. Second clue. 

I tried to change the msconfig and restart the notebook in diagnostic mode. Even with only basic services the notebook gots overheat. Third clue. And I needed more coffee. 

I run task manager, sorted the processes by CPU load column to observe which services were on top five. The Idle service will always be number one. So pay attention to the number two. It turned out that it was the index service that most of the times was the runner-up. This service is for indexing the files so that when we do search the result is faster. Somehow in my notebook this service was on. I didn't really need it actually. So I stopped it. 

And voilaaa ... my fever-notebook was cured!

I should have known it. Overheat was not always a hardware issue. There was unexpected service run automatically like crazy only when the notebook was plugged in. The index service loaded CPU worse by time because my files were also grown by time. 

Well, I have saved company money bcos I almost asked company to replace it. I should have been given an extra bonus! :)