Performance hit when running concurrent queries

Question

Post reply

Performance hit when running concurrent queries

Ian Jones-147914

Valued Member

Points: 54
More actions
February 8, 2005 at 10:38 am

#163730

Please can anyone help !
I am running an optimised query that takes approximately 1 second to run through Query Analyser, however if I run two instances of the same query through different windows in Query Analyser then the time taken to run the query increases to two seconds. For every instance of the query running the results are returned with an additional 1 second overhead in the execution time.

I am confident that the database design and that the query is as optimised as possible and am starting to think that it is a configuration problem with either SQL Server (Version 7 Service Pack 4) or the server (Windows 2000, on a dual 2.4Ghz Xeon processor with 1Gb memory 80Gb Hard drive)

I have run several similar queries both on our live system and test system (SQL Server 2000 version 3) but see the same performance lag on both.

The issue is not a Query Analyser related problem as I see the same performance degradation when I run the queries through a VB application.
The heart of the problem is that SQL server does not appear to be running the queries in parallel, but in series, hence if 20 people hit the database asking it to return a query what should take 1 second it actually takes 20 seconds.

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Could you post the query that you are running so then we can provide a better solution.

My first idea would be to do something like this.

Select MyFields from dbo.MyTable WITH (NOLOCK) where ...

This allows sql server to do a dirty read of the table bypassing any locks on the table. (correct me if I'm wrong here).

The downside of this method is that you could fetch uncommited data from the table.

Ian Jones-147914 Valued Member Points: 54 More actions · Answer 2

Hi

This is the query I am running (as a stored procedure)

SELECT T_Contract_Header.Code,

T_Contract_Header.Description,

T_Contract_Details.HotelID,

T_Contract_Details.RecordNo,

T_Contract_Details.ValidFrom,

T_Contract_Details.ValidTo,

T_Contract_Details.RoomType,

T_Contract_Details.BoardBasis,

T_Contract_Details.RateType,

T_Contract_Details.AllocationType,

T_Contract_Details.Quantity,

T_Contract_Details.Information,

T_Contract_Rates.MidweekAdultNett

FROM T_Contract_Header

LEFT JOIN T_Contract_Details ON T_Contract_Details.Code = T_Contract_Header.Code

LEFT JOIN T_Contract_Rates ON T_Contract_Rates.Code = T_Contract_Details.Code

LEFT JOIN T_Hotels ON T_Hotels.Code = T_Contract_Header.Hotel

WHERE T_Hotels.Area='PRG'

Although any I can see the same problem occuring if I run any query that takes 1 or more second to run.

Thanks for the reply

Ian

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

1 - Do you really need to have all left joins for this query? This can cause great performance degredation on larger tables.

2- Try running sp_who to see if you have any locking problems that could hold the queries back.

3 - Have you tried (or can you use it) the nolock hint?

Sougata Ghosh SSC Journeyman Points: 97 More actions · Answer 4

^{Can you specify what are the number of rows being returned by each query?}

^{Casue I had the same problem and noted that the time lag was directly proportional to the number of records being returned.}

^{Meanwhile also try and check out the CPU activity at the server, if its giving a flat curve or just spikes.Better to test this out with more query windows so that you can have a substantial time lag to measure the activity.}

^{Aslo got to look at the resource blocking issue for all the sp_ids.}

^{Hope this helps, in case there is a trick solution pls post it here.}

^Regards

^SG

Tony Hinkley SSC-Addicted Points: 435 More actions · Answer 5

If this were purely a locking issue, then surely the queries would return in sequence, based on the locking order. You don't say whether all the queries are taking the same time to return, or if they have different (escalating?) execution times?

Tony

Vik Nat Hall of Fame Points: 3930 More actions · Answer 6

I would try a dirty read. That is use a NO_LOCK hint in your select statement and see if that helps. If it is better then look for a locking issue.

Ian Jones-147914 Valued Member Points: 54 More actions · Answer 7

Hi

Thanks for the replies

In answer to previous posts:

The NO LOCK hint makes no difference to the speed.

The queries take the same time to return - if 1 query is run then it takes 1 second to run - if 2 queries are run then each query takes 2 seconds to run, if I run 3 queries simultaneously then each query takes 3 seconds to run etc......This is the same for any query that I am running not just 1 specific query.

The processor runs at 100% whilst running the query and then drops back to usual usage (about 25%)

Ian

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 8

I can think of any number of reasons you may have problems.

I suggest you monitor to see if the reads are physical or logical. Technically a repeated query should read from cache, are you sending the identical query or does the parameter "PRG" change for each query?

I assume all your tables have a clustered index and all join columns are indexed?
If this is not a stored proc then I might expect to see differences - try it as a stored procedure
If you are making physical reads and you only have one disk then I might expect delays.
Are you running your multiple queries from multiple workstations?
You say the queries lengthen, so all queries take longer or each successive query lengthens in time?
Run profiler and monitor for the execution time of each query on the server, it may be the query runs ok on the server but the communication is poor.

Hope this helps.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Ian Jones-147914 Valued Member Points: 54 More actions · Answer 9

Hi

In answer to your suggestions:

For the purposes of this post, I am running the identical query - the query is a stored procedure. I agree that a repeated query should read from the cache, but this does not seem to be the case on our server - is there any way to determine whether the cache is being used ?

The tables and join columns are all indexed

I am running multiple queries from 1 workstation - this is to emulate what is happening in a web server environment

All queries take longer

I have run the profiler and the execution time for 4 queries running concurrently ranges from 3250 to 3686 each whereas 1 query running takes 1156 to run.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Try this setup :

set statistics io on

--set statistics time on

go

--begin tran

EXEC dbo.MyProc

--rollback tran

set statistics io off

--set statistics time off

check out the message sections of QA to get that info about cache reads (4 counter)

parmstrong1107 SSC Veteran Points: 225 More actions · Answer 11

You mention that "The processor runs at 100% whilst running the query and then drops back to usual usage". If both processors are running at 100% even when you are running only one instance of the query, then the processor is the bottleneck. Naturally, once the processor is maxed out additional instances of the query will have to wait for CPU time.

It would seem that the query is running from cache. In any event, that is easily verified by looking at "statistics io" as suggested by Remi above. Disk-bound queries don't usually max out the CPU.

Are you sure the query is fully optimized? Again, the statistics io is a good place to start looking for improvement possibilities.

Tony Hinkley SSC-Addicted Points: 435 More actions · Answer 12

Might also be worth taking a look at the actual execution plan to see if you can identify any bottlenecks there.

Tony

noeld SSC Guru Points: 96590 More actions · Answer 13

And Even though you have indexes are those defragmented ? -- Try a rebuild

* Noel