February 2008 - Posts
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.
See ya later
That's a very funny video, made by Microsoft-Italy, that I saw once and I would like to share with you guys :-)
Hi guys,
Here another interesting problem. Crazy logins loosing their tokens. I had a chat with a friend that happens to work in an online shop website. He knew that I've developed once the authentication/authorisation mechanism for a similar company then he asked me that:
Edge, my ASP.NET website was running fine using SQLExpress 2005. My development and test environments are fine and my tests look OK, but since I moved my website to a better host using SQL Server 2005 I keep getting complaints from my customers; they say all of sudden while navigating the website they get logged out and because of that they are forced to login again. Just like that, out of nowhere. They say they can even add items to the shopping cart but when they start navigating the catalog somehow they realize they are not logged in anymore. That's causing me a lot of stress because ultimately I am loosing revenue. So I am suspecting that it could be some page that is forcing them to logout or doing some crazy redirect. I don't know.
But wait, there is more. Some customers say that after they are logged out, they wait a few minutes and then they refresh the page. They say this will bring their login back (???) Tha's crazy. How come they are logged in my website, then they get kicked out, then a few minutes later they are automatically logged back in? The only new variable is the SQL Server 2005. Is this a MS SQL Server 2005 bug?
My dear friends, I completely understand this case and I know the pain to debug and trace such a environment. Sometimes these problems can drives us crazy during long weekend nights.
So here a little diagram for us to start our case study:
So his environment is a ASP.NET website setup on a web farm, running IIS and having as backend a single instance of SQL Server 2005. The users were able to login to the website but then after a page
refresh, that could not be determined for sure when or where, they get logged out. According to him, the only new variable was the setup of a new SQL
Server, since before he was running using SQL Express; Interesting to note here:
you might be surprised to see how many people actually can use SQLExpress as
their backend. Based on that, I had a feeling that it was not going to be a code
issue, but most likely a setup or environment issue.
I noted that he heavily uses ASP.NET Sessions tables for his product. So, this gave me a good clue for us to look at the problem.
For those who don't believe me, ASP.NET session state really
works flawlessly on web farms. If you
have one and are not using it, I would say : give it a go. And I would go even further, use a
database to store the session state. It does not have to be SQL Server database,
it can be a MySQL, for example. I highly recommend MS database anyway. Why? Because
if you use SQL Server you can replicate
the database and it is going to integrate beautifully with you ASP.NET application.
Try to do this using any other database
and you are in big trouble. (Or not, if you don't like weekends with the family anyway). Now add to this the amazing
caching capabilities provided by SQL Server 2005. My friend, you are set for a
very stable environment.
His host provider uses ASP.NET session in a web farm and a load balancer. Now we
saw a likely breaking point. Did he setup correctly the web.config file for the new environment? Did he make sure all the servers were running the required service for state server? Ultimately, of course, we should rely in the hosting company to do this for us, but double-check just in case before doing any release like this one.
So, in my head I pictured this: The
user was going back and forth, loosing and getting back his login token. Like
if every time he did a refresh, his request would be transferred to another
server which did not have the state server running, therefore no information
about his login.
Then he refreshed again at some
point, his request was redirected to a machine with the state server running,
and the login was back again.
We tried this. Double-checked if
the web.config was correct for state servers and we made sure that every server
in the farm had the service on. The infra-guy fixed those and voilla…back in
business.
Hopefully this was the solution. So far, so good.
See ya later.
hi guys,
sorry for this post. I kinda give up, and I just can't make technorati see my blog <a href="http://technorati.com/claim/e4ung2snm" rel="me">Technorati Profile</a>
did you have the same problem? Do you have any ideas?
Please leave them in the comments
Hi guys, let's talk about open communication, and let me start sharing this point of view. I reckon the expression 'if you do not work, I fire you' is becoming history now more and more.
Today, the business gurus consider that an efficient organization must rely under motivation. The question is: how to make motivation work? How to find out what motivates people?
At the beginning of a new job, I ensure that almost everyone is highly motivated. However, according to a survey conducted by institutions like the Harvard Business School, around 85% of the cases, the moral suffer a huge drop after the upcoming 6 months and it will continue to drop, in a slower pace, for the next years. Big part of that, is due to the bad relationship between the managers and the managed ones.
The good news: mostly the times just suffice to correct some small attitudes in order to bring the employee's moral up, to make the work environment a better place and the organization to become more efficient. After all happier people produces better and in less time.
And come on, who does not like to be recognized?
Truth must be told, in my career I have heard some times some managers say: why do I have to thank my employee for a well-done job? He is paid for that anyway.
Well, that's a huge mistake some will agree with me. As a matter of fact, I can guarantee, sometimes a simple compliment or a touch in his shoulders are powerful motivational agents. Contact. Human beings by nature enjoy contact and interation.
So, if you recognize yourself in this scenario, IMHO, a little piece of advice: do not be a dictator.
Do you want to demotivate the employees in no time? Easy, just enforce a tough and authoritarian leadership. You can't go wrong here, if this is your goal.
Do you want motivate them? Understand that a good leader is not the one who barks louder the orders, but the one who consider that his mission is to lead the people working with him.
Now go there and talk to them.
I have seen countless times managers (and I've experienced this with some managers that I've had), not asking or seeking advice from their employed for a stupid reason called Fear.
Fear that he might be somehow seen with other eyes, as if he was a semi-god.
The employee, in other hand, maybe have some brilliant ideas but do not dare to communicate them.
That's why real leaders left their pride aside and constantly look for suggestions out of his colleagues.
I have an interesting theory and methodology that I use. Sometimes when I lead projects, I find myself in a position that I know exactly what to do and how to do it, but for the good sake of keeping a democratic communication channel I still go asking my managed ones for their suggestions, behind the scenes that's just to enforce the idea that they can approach me freely with ideas.
Honestly, many times they can come up with very good ideas and new paths, even better than my own ideas at times. Other times I just guide them to reach the idea that I have in mind, so in a sense I give them a feeling of accomplishment. That always works like a charm.
A practice often seen is the information distribution that follow a specific standard or criteria like what you only need to know. I find very disappointing when an employee is not aware about how his work can add value to the company as a whole.
My dear friends, it is easy and I'll be straight with this one: if an employee does not know the reason of his daily work, he will never wear the company's jersey.
This all sounds great, hun? Now you could be thinking, can any worker be motivated? Honestly, the answer is no. As in life, in any company we always find a small group of people who are in a sense, let's say, anti-work. They are there just for the money, to kill time, to justify their days to the society, for the free coffee etc. Not only this, but they also avoid those who want to do things right. For these, the answer might be: discipline. A clear talk with them about their attitude and if they do not perform, well then unfortunately the company can not afford. Even so, again we return to the starting point of this article: communicate.
See you later.
More Posts