Prev: Inline view with a left join
Next: Stored Procedures
From: SR on 20 May 2010 13:09
Using SQL 2008:
When stress testing the query shown below, I find it runs faster when CPU &
I/O affinity is set to 1 CPU socket (vs all CPU's) on a 8 way box.
(Testing is done with SQL load generator and with 50 parallel identical
queries).

Is there a way to rewrite the query to scale up with all CPU's (this is on a
NUMA 8-socket 6 core DL785 box)? Waitstats show most time spent in and
PAGELATCH_EX AND PAGELATCH_SH.

Thanks
Suresh


SELECT top 10 * into #t
FROM dbname.dbo.abc
WHERE [1] > 53 and [102] = 0
drop table #t

From: Tom Moreau on 20 May 2010 15:53
If you have 8 CPU's, then you should have 8 *data* files for tempdb. (Some
folks recommend only 4, but we won't argue that here.) Once you've done
that, try running with CPU affinity off and see if that helps. Also, try
turning parallelism off for the instance or for the query. For the latter,
that would be:

SELECT top 10 *
into #t
FROM dbname.dbo.abc
WHERE [1] > 53 and [102] = 0
(OPTION MAXDOP 1)


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"SR" <SR(a)discussions.microsoft.com> wrote in message
news:9F469381-4478-4770-A560-12F931AA2D0A(a)microsoft.com...
Using SQL 2008:
When stress testing the query shown below, I find it runs faster when CPU &
I/O affinity is set to 1 CPU socket (vs all CPU's) on a 8 way box.
(Testing is done with SQL load generator and with 50 parallel identical
queries).

Is there a way to rewrite the query to scale up with all CPU's (this is on a
NUMA 8-socket 6 core DL785 box)? Waitstats show most time spent in and
PAGELATCH_EX AND PAGELATCH_SH.

Thanks
Suresh


SELECT top 10 * into #t
FROM dbname.dbo.abc
WHERE [1] > 53 and [102] = 0
drop table #t

 | 
Pages: 1
Prev: Inline view with a left join
Next: Stored Procedures