Performance Problem

Question

Post reply

Performance Problem

Justin Woody

Grasshopper

Points: 14
More actions
April 28, 2005 at 8:30 am

#166595

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,
Justin

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

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

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

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
whoisactive

Justin Woody Grasshopper Points: 14 More actions · Answer 2

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.

Justin

Adam Machanic SSCoach Points: 15370 More actions · Answer 3

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

--
Adam Machanic
whoisactive

Justin Woody Grasshopper Points: 14 More actions · Answer 4

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

Justin

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

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?

Justin Woody Grasshopper Points: 14 More actions · Answer 6

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.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 7

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 http://sirsql.net

Matteo-204379 SSC Veteran Points: 255 More actions · Answer 8

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.

Cesar-134342 Ten Centuries Points: 1319 More actions · Answer 9

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.

Justin Woody Grasshopper Points: 14 More actions · Answer 10

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.

Justin

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 11

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

Justin Woody Grasshopper Points: 14 More actions · Answer 12

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.