Performance Problem

  • Hi All,

    Having a performance problem with two queries during a warehouse loading process. It works extremely fast on our test server. Just wondering if SQL Server has something similar to Oracle's Top Sessions to help diagnose the problem. If there is a native or 3rd party product I'd appreciate the info.

    Thanks in advance,


  • Try:

    EXEC sp_who2 'active'

    But if you already know what the two queries are, then why do you need to see the worst sessions?

    Adam Machanic

  • Hi Adam,

    Trying to see if we are getting row locking or some other contention between the loading queries and other system queries.

    Used sp_who2 already, but it doesn't seem to show the entire body of the query. I only see SELECT or BACKUP etc.


  • To get the query, try DBCC INPUTBUFFER(spid), where spid is the spid from sp_who2.

    Adam Machanic

  • OK, cannot find any contention issues. The process basically takes denormalized data from a source table, parses and does some calculations and then inserts the normalized record into a fact table. The approximate number of records that are processed at any one time is ~25000. Out of those 25000 records we only actually insert or update 1-2 thousand records in the fact table.

    This process takes less than a minute on my development server, which has less processing power, less ram, etc., and is currently taking over 6 hours on my production server. Indexes are the same, there is 6GB of free space on the production server, which is more than enough, and other loading processes are not affected.

    Any suggestions on what I can look for to help diagnose this?

    Thanks again


  • Can you post your hardware information and SQL Server configuration information on both dev and prod servers?

    Since you have already know which quesies do the loading, Can you compare the query execution plans on both servers too?

    The interest thing is that it is not only slow on loading, but also does the indexing. How do you rebuild the indexes?

  • Production Server:

    3ghz / 3GB ram / SCSI RAID 5

    Dev Server:

    1 Ghz/ 512MB ram / SCSI no raid

    I had already compared the execution plans and they were almost the same. The production server had an added Parallelism/Gather Streams piece in the plan, which I know can often play a big part in performance issues. However, when I run that query on the dev and production servers, the query comes back in about the same amount of time. I'm thinking it has to do with the insert/update of the fact table. That table has a clustered index on the pk columns.

    Not sure how indexes are rebuilt. I didn't orignally set this database up, I just inherited it...

    Thanks for all of the help.

  • Just wondered if you are seeing an IO bottleneck. RAID 5 is hella slow, can you check out a perfmon, see if you are getting disk queuing?

    Shamless self promotion - read my blog

  • Chek the use of indexes, if the are not the same try to Hint them:


    FROM Table1 (INDEX = idx_index_name)



    Sometimes if you use calculation in the joins it does not use the right index.

  • Try this: compare the setting on both server on:

    a) Transaction log mode. Maybe the Test is in Simple (non logged) mode, and prod may be full, more robust, but slower.

    b) Autogrow size for the log file. If it has to increase the log file in small chunks and you're loading large quantities of data, it may take longer.

    c) The table and indexes fillfactors. Depending on the distribution of the data you're loading, these two settings may be the cause of the slower loading.

    Hope this help.


  • I narrowed it down to the problem query. It wasn't using the clustered index within the stored procedure. Whenever I ran the exact same query in query analyzer, it always used the index. Any idea why that would happen? Always uses the index on the production server.

    Dropped the process from 6+ hours to <1 minute

    Thanks again for all of the help.


  • Try to recreate the SP with recompile option to force new execution plan to be generated each time it is called.

  • From a performance perspective, isn't that worse than the hint?

    I would think that the query with the additional hint added wouldn't have to be recompiled every time.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply