DB migration

[michael]

I just moved a SQL 2000 database to a new server (backup/restore).
This server is much beefier with respect to hardware.

The problem is that the system doesn't seem to be very fast at all. It
seems to be having issues with thread count. For example, I saw some
errors like this in my client apps:

"The query processor could not start the necessary thread resources
for parallel query execution."

Also, connections from SSMS occasionally timeout and running this
"query": "PRINT GETUTCDATE()" takes as long as 2 minutes to return the
current date.

What should I be looking for? What things should I check?

Is it possible the db is just taking IO hits like crazy as it spins up
(it's been up for about an hour now)?

Thanks!

[michael]

On Jun 17, 12:22*pm, michael <[e-mail]:

I just moved a SQL 2000 database to a new server (backup/restore).
This server is much beefier with respect to hardware.

The problem is that the system doesn't seem to be very fast at all. It
seems to be having issues with thread count. For example, I saw some
errors like this in my client apps:

"The query processor could not start the necessary thread resources
for parallel query execution."

Also, connections from SSMS occasionally timeout and running this
"query": "PRINT GETUTCDATE()" takes as long as 2 minutes to return the
current date.

What should I be looking for? What things should I check?

Is it possible the db is just taking IO hits like crazy as it spins up
(it's been up for about an hour now)?

Thanks!

It turns out that this was caused by the max degree of parallelism
threshold being set to 0. I guess the optimizer was parallelizing
everything, even the simplest of queries. I changed it back to 5 (the
default) and it's running much, much better now.

[Erland Sommarskog]

michael (mh****@gmail.com) writes:

It turns out that this was caused by the max degree of parallelism
threshold being set to 0. I guess the optimizer was parallelizing
everything, even the simplest of queries. I changed it back to 5 (the
default) and it's running much, much better now.

The default is actually 0, but it's good to hear that your issue was
resolved.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Dan Guzman]

To add to Erland's response, consider examining the execution plans of the
parallel queries to see if index/query tuning is warranted. SQL Server will
sometimes throw parallelism at a query to compensate for lack of useful
indexes. I have seen cases where tuning eliminates parallelism and improves
performance.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"michael" <mh****@gmail.comwrote in message
news:94**********************************@i76g2000 hsf.googlegroups.com...

>I just moved a SQL 2000 database to a new server (backup/restore).
This server is much beefier with respect to hardware.

The problem is that the system doesn't seem to be very fast at all. It
seems to be having issues with thread count. For example, I saw some
errors like this in my client apps:

"The query processor could not start the necessary thread resources
for parallel query execution."

Also, connections from SSMS occasionally timeout and running this
"query": "PRINT GETUTCDATE()" takes as long as 2 minutes to return the
current date.

What should I be looking for? What things should I check?

Is it possible the db is just taking IO hits like crazy as it spins up
(it's been up for about an hour now)?

Thanks!