The Hot Side of the Dark Side of the Force

Stored procedure takes longer run SQL2005 than 2000

Here there is something interesting that I would like to share with you guys.

Even thou Microsoft SQL Server 2005 is out for quite some time, it is still common to see people working in projects using Microsoft SQL Server 2000 and often in mixed environments.

That's the case I want to talk about: The mixed environment, and I am working in a project where some applications have that hybrid configuration.

So someone told me that my report developed in .NET 2.0 was running slower than the similar one done in the old fashioned ASP. Of course I denied, just to see later the proof I was wrong.

Yes, the same stored procedure executed from the same page from, in the same machine was running faster in the old environment while it was slower in the new (and supposedly improved) environment. How's that possible? I traced the execution, used the SQL profiler but nothing gave me a good clue. Than I found this in the Microsoft website.

In SQL Server 2000, the execution plan for the query uses an Index Seek operator. In SQL Server 2005, the execution plan for the query uses an Index Scan operator. The optimizer produces an index spool for the Index Scan operation. When you use the FORWARD_ONLY cursor, SQL Server scans the index for every FETCH statement. Each fetch takes a long time. Therefore, the query takes a long time to execute.

See that example below:

50 declare @p1 int

51 set @p1=0

52 declare @p3 int

53 set @p3=16388

54 declare @p4 int

55 set @p4=8194

56 declare @p5 int

57 set @p5=0

58 exec sp_cursoropen @p1 output, <Transact-SQL statement> ,@p3 output,@p4 output,@p5 output

This code will run faster if you are NOT using the .NET 2005 SQL Connectors or running in a SQL Server 2000. Here we are using the sp_cursoropen to open a cursor, then specifying the forward-only option in the parameter list.

This is a bug you can only experience if you are using a lot of cursor-based stored procedures from a SQL 2000 to a SQL 2005 environment, and here we have a VERY HIGH cursor usage. (not that I like them neither I defend its usage, it is just a fact from the environment here)

How to fix this?

If you do not want to download and apply the patch and want to fix this in the code itself use "OPTION (FAST 1)" in the stored procedure call. That will make it run faster in the SQL 2005 machine. Otherwise download here and here the patches.

See ya later

By Edge Pereira

TeamSystem and TFS add-on to Count Lines of Code and Predict Errors

Here another cool thing. From the Microsoft download website you can find this:

"Microsoft IT partnered with Microsoft Research to create a VSTS 2005 extension that counts lines of code and predicts system defects. In the software development environment, insight into the volume of code being produced, and the changes applied to that code, provide measurements of productivity and quality. The Line of Code (LOC) counter provides a flexible and extensible framework for automating the LOC counting process."

Isn't that amazing ? Finally a cool software metric add-on for Team System, and if you wanted another reason to stop using SourceSafe and start using Team Foundation System guess what: It also works with TFS.

If you use Team System or TFS, get it here.


See you later.

By Edge Pereira

How to Make Productive Project Meetings

Project Meetings can be very productives but also can be a real waste of time and money.

Recently while working on a client where I was responsible to have a project development meeting as meeting coordinator. The group of participants were an heterogeneous group and despite the fact that I did not know some of the atendees, the meeting was a big success.

During a conversation on our coffee break I was asked about meetings strategies and how to conduct them.

So I am going to share with you guys here what I told them, and what I effectivly did during that particular meeting:

• Every meeting MUST have 3 elements: purpose, agenda and maximum duration. If any of these items is missing, the meeting is meaningless and should not happen.


• Make sure you are able to define a purpose for the meeting in a maximum of 2 sentences, for instance:"This meeting is to plan the new developments for the project X". This way, everyone will know why they are there, what needs to be done and how to proceed in order to well-succeed.


• Define a clear agenda in advance. Make a list of all the items to be discussed, revised, analysed, displayed etc. When I conduct meetings, my personal strategy is to allocate a time limit for each item in the agenda and to assign the responsability to lead the discussion to someone in the group. Works as a charm.


• Define a duration for the meeting, how many minutes/hours it should last. From the start make crystal clear to everyone what time the meeting will start and, sometimes more importantly, when it will end. It is amazing the number of managers who have absolutely no control of their meetings and do not know how to enforce the finishing rule. If you think you have this habit...CHANGE THIS !!!


• Do not wait for the delayed people. Meetings must start on the agreeded time. Do not wait about late arrivals. Do not wait for those who need to be called for the meeting. You just make sure everyone gets notified, then when someone arrives after the meeting have started, DO NOT STOP TO REVIEW WHAT WAS SAID. Do this as a proof of respect to those who arrived on time.


• If the meeting's organizer is late, Consider the meeting cancelled, and get back to work. How long is considered late? Depends on the company, but I would not wait more than 5 minutes.


• Document your meeting. What I do is to put someone in charge of writing down the notes. What to put in the meeting notes? Basically the name of the attendants, the discussed subject, the agreed points, the next developments and/or actions with dates and their respective responsibles.


• When the meeting is over - do not wait more than 24 hours - the meeting notes must be sent to: All the participants, to those who could not make it to the meeting and to those who might be influenced by upcoming decisions.


• Keep the focus. Every meeting must have a regulator to notify the others when someone is discussing any subject outside the scope of the current topic. Ask one of the presents to volunteer for this task when the meeting is about to start. His/her task is to interrupt the meeting at any given time when the focus is lost and bring back the main subject. This new outside topic can maybe then be noted and even can be discussed in future meetings. In case of doubt regarding a specific topic being in or outside the scope, the meeting organizer has the final word.

I hope these notes can be of any help in your next meetings. If you have any comments or other meetings ideas, please feel free to leave them here and share as well.

See ya later.

By Edge Pereira