SQL Server Performance

Win a $250 Amazon.Com Gift Certificate this March

Discussion in 'SQL-Server-Performance.Com Contest [Archived]' started by bradmcgehee, Mar 1, 2006.

  1. bradmcgehee New Member

    Tell Us Your Story and Win

    Are poorly performing procedures in SQL Server giving you headaches?

    Now they could help you win a $250 Amazon.com Gift Certificate!

    Simply send in the story of your absolute worst performing procedure — or send screenshots with descriptions. Also be sure to tell us:

    How it affected your system's performance.
    How you found it.
    How long it took.
    How you fixed it. (Hint: Try Idera SQL Diagnostic Manager. It will automatically pinpoint the worst performing procedures for you in seconds! You can download a trial copy if you like.)

    The absolute worst performing procedure story will win a $250 Amazon.com Gift Certificate!

    Seehttp://www.sql-server-performance.com/contest-mar-06.asp for more information and to enter.

    Brad M. McGehee, MVP
  2. bradmcgehee New Member

    The winner of the March contest is Leandro Santos from Brazil. He submitted the best story about dealing with a poorly performing query. He will receive a $250 Amazon.Com gift certificate, compliments of www.Idera.com .

    PS: Leandro, when you read this, please contact me: [email protected]

    Here is his winning entry:

    I am a SQL Server DBA. At about 9:00 AM, a developer came by my desk, telling me that a batch process (we ran for a client) that had started last night at 11:00 PM was still running, and that the job was interfering with production work on our SQL Server. The process involved a stored procedure that was responsible for modifying about 90,000 rows of data, among some other tasks. Obviously, the job was running much too long. The job finally ended after 11 hours of running.

    My first impression was that this problem should be easy to solve, especially since we had a development environment that simulated the bulk loading process.

    So, my first step was to take a look at the stored procedure that has been written by the developer. I used Query Analyzer and did a Query Plan on the various queries within the stored procedure. From this, I learned that all of the queries were against the primary key of the table and were all index seeks, so indexing was not the problem.

    I ended up spending the rest of the day looking at potential causes of the problem, doing a lot of testing, and even using Performance Monitor to look for potential issues. I did make some changes in the logic implemented inside the stored procedure. After some additional testing, I started a trace and noticed that all the SELECT and UPDATE operations, even the seeks for the PKs, had a duration of 200 miliseconds. I spent about 30 minutes looking at the trace windows trying to imagine what could lead this to happen, but nothing came to me.

    So, that evening, the developer installed the new stored procedure I had rewritten, and this time, it took 3 hours less to run to run, which was an improvement, but still much too long. At that time my boss was already in a conference call with the client trying to explain how a load like this was taking 8 hours to complete.

    So again, the next day, after the 8 hour run in the evening, I spent a lot of time looking at the procedure code and trace results, looking for the problem. What confused me the most was that I could not figure out how it could take 200 miliseconds for a SELECT or an UPDATE to run in a table with only about one million rows and two active connections.

    Now it was day three, and still no solution. As I was still reviewing the stored procedure code, I realized that all of the variables declared in the stored procedure were BIGINT variables. So for an experiment, I replaced all of the BIGINT variables with INT variables and ran the stored procedure as a query, while tracing what happened. Now, each SELECT and UPDATE now only took 16 milliseconds to run. EUREKA! I also then checked the table design, and I also noticed that all the columns were set to BIGINT also.

    I asked the developer if there was any reason that INT could not be used instead of BIGINT, and I was told no.

    So I made the necessary changes to the table and stored procedure, and now the same batch import not only takes eight minutes.

    As you might imagine, my manager asked my how could anyone write a stored procedure that took eleven hours when it should only take eight minutes. You can imagine my answer.

    Brad M. McGehee, MVP
  3. Luis Martin Moderator


    Luis Martin

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte

    All postings are provided “AS IS” with no warranties for accuracy.

  4. Madhivanan Moderator

    Congrats to Leandro Santos [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. FrankKalis Moderator

    Congrats! Nice story. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  6. mmarovic Active Member

    Congratulations! It is still strange that data changes on bigint columns are so much slower. It's worth to have in mind and also worth further investigation.
  7. chaloner New Member



    Everything happens for your own good.
  8. ramkumar.mu New Member

    Congrats to Leandro Santos.
    Any idea why bigin is a big problem???


    "It is easy to write code for a spec and walk in water, provided, both are freezed..."

Share This Page