February 17, 2009 at 11:32 am
I have a new database, copy of an old one, that is very slow.
"select count(*) from coverage" takes 30 secs to run (about 6 million records in coverage). I have rebuilt the indexes and updated the statistics (which improved the performance to 30 secs).
Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)
Feb 26 2008 18:15:01
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
database isPublished.
Any suggestions what to do next?
Thanks,
Jim
February 17, 2009 at 2:13 pm
so is this database on the same server and how quick is the original database?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 18, 2009 at 6:08 am
It's a new server. The old one returned the results in about 12 secs. I'm not sure yet about hardware or configuration differences yet, that question is in to the sys admin now.
Thanks,
Jim
February 18, 2009 at 7:03 am
I'd be focused pretty much on the hardware and configuration issues. I remember the time we were troubleshooting a production performance problem. I backed up the database, moved it to a new server and suddenly, it was running twice as fast. I couldn't figure out why until I finally looked at the machine. Come to find out the test box was much newer than the production system and had twice as fast a cpu...
You should probably check the SQL Server settings to be sure they're the same between the two servers. Also, see if you can see a difference in the execution plans.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 18, 2009 at 7:11 am
I'm with Grant here, look at hardware.
also, is this a new restore or restart? Could it be caching somewhere? If you run that query twice, is it the same speed the second time?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 18, 2009 at 8:43 am
I have checked the settings and the only diff I see is that the new db is set at
name minimum maximum config_value run_value
max text repl size (B) 0 2147483647 2147483647 2147483647
and the old is set at 65536. There are no text fields in this table, but there are mis-typed fields of text in the db.
The execution plans differ only in that on the new db there is a step for parallelism
Old server execution plan
<--
Select <-- Compute Scalar <-- Stream Aggregate (cost:1%) <-- Clustered index scan (cost : 99%)
New server execution plan
<--
Select <-- Compute Scalar <-- Stream Aggregate <-- Parallelism <-- Stream Aggregate <-- Clustered index scan (cost: 100%)
Jim
February 18, 2009 at 8:50 am
skt5000 (2/18/2009)
I have checked the settings and the only diff I see is that the new db is set atname minimum maximum config_value run_value
max text repl size (B) 0 2147483647 2147483647 2147483647
and the old is set at 65536. There are no text fields in this table, but there are mis-typed fields of text in the db.
The execution plans differ only in that on the new db there is a step for parallelism
Old server execution plan
<--
Select <-- Compute Scalar <-- Stream Aggregate (cost:1%) <-- Clustered index scan (cost : 99%)
New server execution plan
<--
Select <-- Compute Scalar <-- Stream Aggregate <-- Parallelism <-- Stream Aggregate <-- Clustered index scan (cost: 100%)
Jim
Ah, what are the max degree of parallelism and the paralellism thresholds between the two servers?
If the old server was a single processor machine, the new one might need to have the parallelism threshold set higher. I'd recommend a number as high as 20, maybe even 30, instead of the default 5.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 18, 2009 at 8:59 am
Grant,
The new database was copied from the old, but I'm not sure of the technique. I am the only one in this database now and will be until we get it up to par. The query takes the same amount of time no matter when run. I ran dbcc dropcleanbuffers and got the same response time. It looks like hardware is going to have a bigger impact than I thought. I heard somewhere that hardware usually doesn't have that much an effect in a database our size that really isn't very busy.
Jim
February 18, 2009 at 9:07 am
The parallelism settings are a SQL Server level setting, not on the database. You'll need to look there.
Hardware always matters. If your database is really tiny, memory might not matter that much because if the entire database fits into 2gb of memory, having 500gb isn't going to help too much. Otherwise, hardware will always make a difference.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 18, 2009 at 9:08 am
The max DOP is set at 0 for both, and the cost threshold is set at 5 for both. Should I change the threshold to 20?
Jim
February 18, 2009 at 9:11 am
I would. Is the old server a single CPU machine?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 18, 2009 at 10:28 am
I changed the threshold to 20, but no change. Here's what my sysadmin had to say about the hardware
"The hardware is better 71 & 245, 69 is a monster server - quad processor. We are not running raid on any of these servers except 69"
looks like 30 secs is as good as I get.
Jim
February 18, 2009 at 10:40 am
just as an experiment, try using a query hint on the query itself to set the max degree of parallelism to 1, just to see. Use: OPTION (MAXDOP 1)
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 18, 2009 at 11:10 am
Unfortunately, still at 30 secs
Jim
February 18, 2009 at 11:40 am
Did you update the stats "WITH FULLSCAN, ALL" ?
* Noel
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply