MS SQL Server - Thread priority problem

Author

Thread priority problem

Zdenìk ©ebl

2005-04-26, 11:23 am

Hello,
we have following problem on SQL Server 2000 SP3a.

When we execute some query which consumes processor and does minimal IO
operations (for example query with multiple LIKE '%xxx%' condition),
performance of all other queries is dramaticaly affected.

For example nearly no CPU consuming simple query last normally about
0.0005s. But while executing in parallel with first query, it last about
20s.

There is probably some thread scheduling related problem.

Does anybody know how to solve it?

Thanks,
Zdenek

Wei Xiao [MSFT]

2005-04-26, 8:23 pm

SQL Server does fair scheduling among all the query threads. You might want
to see if the big query is parallel. If so, then you can try to see if
setting "max degree of parallelism" to 1 with sp_configure helps.

--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix

This posting is provided "AS IS" with no warranties, and confers no rights.

"Zdenìk ©ebl" <Zdenek.Sebl@matfyz.cz> wrote in message
news:Op4oL3nSFHA.3460@TK2MSFTNGP10.phx.gbl...
> Hello,
> we have following problem on SQL Server 2000 SP3a.
>
> When we execute some query which consumes processor and does minimal IO
> operations (for example query with multiple LIKE '%xxx%' condition),
> performance of all other queries is dramaticaly affected.
>
> For example nearly no CPU consuming simple query last normally about
> 0.0005s. But while executing in parallel with first query, it last about
> 20s.
>
> There is probably some thread scheduling related problem.
>
> Does anybody know how to solve it?
>
> Thanks,
> Zdenek

Zdenìk ©ebl

2005-04-27, 3:23 am

Thanks for reply.

The big query is not parallel and "max degree of parallelism" is already
set to 1.

Problematic query is
SELECT COUNT(*)
FROM problem
WHERE (uqid LIKE '%14240E%') OR
(uqid LIKE '%6F1CCE%') OR
(uqid LIKE '%B447E9%') OR
(uqid LIKE '%AB0982%') OR
(uqid LIKE '%4EBD1F%') OR
(uqid LIKE '%16BC97%') OR
(uqid LIKE '%D7A479%') OR
(uqid LIKE '%51D819%') OR
(uqid LIKE '%D65BA3%') OR
(uqid LIKE '%C03088%')

The table [problem] was created following way:

CREATE TABLE [dbo].[problem] (
[uqid] [varchar] (50) COLLATE Czech_CI_AS NOT NULL
) ON [PRIMARY]
GO

DECLARE @count int
SELECT @count = 800000

DELETE problem
WHILE @count > 0
BEGIN
INSERT INTO problem VALUES(NEWID())
SELECT @count = @count - 1
END

I tested it on two servers with different hardware configuration (dual
XEON 2,8GHz & dual P3 800MHz).

Thanks,
Zdenek

Wei Xiao [MSFT] wrote:
> SQL Server does fair scheduling among all the query threads. You might want
> to see if the big query is parallel. If so, then you can try to see if
> setting "max degree of parallelism" to 1 with sp_configure helps.
>

Wei Xiao [MSFT]

2005-04-28, 1:23 pm

Perhaps you can try rewriting the query in such a form, to see if it makes
any difference?

declare @total int
declare @subtotal int

> SELECT @subtotal = COUNT(*)
> FROM problem
> WHERE (uqid LIKE '%14240E%')
set @total = @subtotal
> SELECT @subtotal = COUNT(*)
> FROM problem
> WHERE (uqid LIKE '%6F1CCE%')
set @total = @subtotal+@total

If this still does not help, then you can use a cursor to count N rows at a
time.

I know this is ugly because we are essentially slowing down this connection
so your other connections gets a better chance to run.

--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix

This posting is provided "AS IS" with no warranties, and confers no rights.

"Zdenìk ©ebl" <Zdenek.Sebl@matfyz.cz> wrote in message
news:%23S2QG3uSFHA.3344@TK2MSFTNGP12.phx.gbl...[color=darkred]
> Thanks for reply.
>
> The big query is not parallel and "max degree of parallelism" is already
> set to 1.
>
> Problematic query is
> SELECT COUNT(*)
> FROM problem
> WHERE (uqid LIKE '%14240E%') OR
> (uqid LIKE '%6F1CCE%') OR
> (uqid LIKE '%B447E9%') OR
> (uqid LIKE '%AB0982%') OR
> (uqid LIKE '%4EBD1F%') OR
> (uqid LIKE '%16BC97%') OR
> (uqid LIKE '%D7A479%') OR
> (uqid LIKE '%51D819%') OR
> (uqid LIKE '%D65BA3%') OR
> (uqid LIKE '%C03088%')
>
> The table [problem] was created following way:
>
> CREATE TABLE [dbo].[problem] (
> [uqid] [varchar] (50) COLLATE Czech_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> DECLARE @count int
> SELECT @count = 800000
>
> DELETE problem
> WHILE @count > 0
> BEGIN
> INSERT INTO problem VALUES(NEWID())
> SELECT @count = @count - 1
> END
>
> I tested it on two servers with different hardware configuration (dual
> XEON 2,8GHz & dual P3 800MHz).
>
> Thanks,
> Zdenek
>
> Wei Xiao [MSFT] wrote:

Sponsored Links