February 21, 2006 at 8:47 pm
We have an interesting problem.
We have a database that's around 2gig in size, runs great on our development server and other client sites, but on this one particular site, it runs very, very slow...to the point of users getting timeouts.
We've been doing some basic comparions using one stored procedure that takes 1 sec to run on our development server, and about 9 seconds on the proposed production server which is double the spec in terms of hardware performance.
It's got me beat. Other clients on lesser servers with bigger versions of this database don't have these sort of issues.
The proposed production server is SQL2000 spk3, with the default installation parameters. Not been messed around with in any way, and is the same setup as we have at the office, except they have better hardware than us.
The database we use between the two is the same database, indexes checked, database checked, etc. We've run out of places to look and things to check.
Any suggestions greatly appreciated.
February 21, 2006 at 10:16 pm
Have you tried using profiler to get some idea of what CPU, memory and disk usage are for your new server? This will give the user group a bit more info for troubleshooting purposes.
February 21, 2006 at 10:58 pm
Yes, we've looked at Processor time, Avg. Disk Read Queue Length, Page life Expectancy and User Connections. Everything we've looked at seems normal.
The production server currently hosts about half a dozen other databases, and we've noticed about 140 connections to the server overall. Other databases/systems are running fine without any performance issues, which points the finger at our database. But running the same database on our development server and even an average desktop MSDE, returns much faster. In addition, other clients have larger databases with 40+ users without any problems runing on lower spec database servers.
February 22, 2006 at 12:54 pm
How many processors are on the 2 servers. prod and test. If you run sp_configure what is the cost threshold for parallelism? min memory per query (KB)? Are the indexes the same? Are they fragmented? Are the execution plans the same? Amount of data the same.
A couple places to look.
Let us know.
Tom
February 22, 2006 at 1:06 pm
Also check max memory setting:
exec sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'max server memory'
February 22, 2006 at 3:44 pm
Development server has two Xeon processors, and the production server has 4 Xeons.
Database on the two is identical, we re-indexed just to be sure, and did a checkdb which came back without any errors.
I will check out sp_configure... and report back.
Cheers.
H
February 22, 2006 at 3:50 pm
>>we re-indexed just to be sure,
Speaking of indexes, does every table have a clustered index ? Rebuilding non-clustered indexes doesn't solve fragmentation in the underlying table data pages if the table is a heap without a clustered index.
February 22, 2006 at 3:54 pm
Don't think so, but I'll check anyway. But if it was indexing or db related, wouldn't we see an even worse performance on the development server (which is lower spec than production)?
February 22, 2006 at 10:27 pm
Results of the SP_CONFIGURE:
| Production | Development | |||||||||
| Name | Min | Max | Config Value | Run Value | Name | Min | Max | Config Value | Run Value | |
| affinity mask | -2147483648 | 2147483647 | 0 | 0 | affinity mask | 0 | 2147483647 | 0 | 0 | |
| allow updates | 0 | 1 | 0 | 0 | allow updates | 0 | 1 | 0 | 0 | |
| awe enabled | 0 | 1 | 0 | 0 | awe enabled | 0 | 1 | 0 | 0 | |
| c2 audit mode | 0 | 1 | 0 | 0 | c2 audit mode | 0 | 1 | 0 | 0 | |
| cost threshold for parallelism | 0 | 32767 | 5 | 5 | cost threshold for parallelism | 0 | 32767 | 5 | 5 | |
| Cross DB Ownership Chaining | 0 | 1 | 0 | 0 | ||||||
| cursor threshold | -1 | 2147483647 | -1 | -1 | cursor threshold | -1 | 2147483647 | -1 | -1 | |
| default full-text language | 0 | 2147483647 | 1033 | 1033 | default full-text language | 0 | 2147483647 | 1033 | 1033 | |
| default language | 0 | 9999 | 0 | 0 | default language | 0 | 9999 | 0 | 0 | |
| fill factor (%) | 0 | 100 | 0 | 0 | fill factor (%) | 0 | 100 | 0 | 0 | |
| index create memory (KB) | 704 | 2147483647 | 0 | 0 | index create memory (KB) | 704 | 2147483647 | 0 | 0 | |
| lightweight pooling | 0 | 1 | 0 | 0 | lightweight pooling | 0 | 1 | 0 | 0 | |
| locks | 5000 | 2147483647 | 0 | 0 | locks | 5000 | 2147483647 | 0 | 0 | |
| max degree of parallelism | 0 | 32 | 0 | 0 | max degree of parallelism | 0 | 32 | 0 | 0 | |
| max server memory (MB) | 4 | 2147483647 | 2147483647 | 2147483647 | max server memory (MB) | 4 | 2147483647 | 2147483647 | 2147483647 | |
| max text repl size (B) | 0 | 2147483647 | 65536 | 65536 | max text repl size (B) | 0 | 2147483647 | 65536 | 65536 | |
| max worker threads | 32 | 32767 | 255 | 255 | max worker threads | 32 | 32767 | 255 | 255 | |
| media retention | 0 | 365 | 0 | 0 | media retention | 0 | 365 | 0 | 0 | |
| min memory per query (KB) | 512 | 2147483647 | 1024 | 1024 | min memory per query (KB) | 512 | 2147483647 | 1024 | 1024 | |
| min server memory (MB) | 0 | 2147483647 | 0 | 0 | min server memory (MB) | 0 | 2147483647 | 0 | 0 | |
| nested triggers | 0 | 1 | 1 | 1 | nested triggers | 0 | 1 | 1 | 1 | |
| network packet size (B) | 512 | 65536 | 4096 | 4096 | network packet size (B) | 512 | 32767 | 4096 | 4096 | |
| open objects | 0 | 2147483647 | 0 | 0 | open objects | 0 | 2147483647 | 0 | 0 | |
| priority boost | 0 | 1 | 0 | 0 | priority boost | 0 | 1 | 1 | 0 | |
| query governor cost limit | 0 | 2147483647 | 0 | 0 | query governor cost limit | 0 | 2147483647 | 0 | 0 | |
| query wait (s) | -1 | 2147483647 | -1 | -1 | query wait (s) | -1 | 2147483647 | -1 | -1 | |
| recovery interval (min) | 0 | 32767 | 0 | 0 | recovery interval (min) | 0 | 32767 | 0 | 0 | |
| remote access | 0 | 1 | 1 | 1 | remote access | 0 | 1 | 1 | 1 | |
| remote login timeout (s) | 0 | 2147483647 | 20 | 20 | remote login timeout (s) | 0 | 2147483647 | 20 | 20 | |
| remote proc trans | 0 | 1 | 0 | 0 | remote proc trans | 0 | 1 | 0 | 0 | |
| remote query timeout (s) | 0 | 2147483647 | 600 | 600 | remote query timeout (s) | 0 | 2147483647 | 600 | 600 | |
| scan for startup procs | 0 | 1 | 1 | 1 | scan for startup procs | 0 | 1 | 0 | 0 | |
| set working set size | 0 | 1 | 0 | 0 | set working set size | 0 | 1 | 0 | 0 | |
| show advanced options | 0 | 1 | 1 | 1 | show advanced options | 0 | 1 | 1 | 1 | |
| two digit year cutoff | 1753 | 9999 | 2049 | 2049 | two digit year cutoff | 1753 | 9999 | 2049 | 2049 | |
| user connections | 0 | 32767 | 0 | 0 | user connections | 0 | 32767 | 0 | 0 | |
| user options | 0 | 32767 | 0 | 0 | user options | 0 | 32767 | 0 | 0 |
The real standout difference is they have Cross DB Ownership Chaining enabled, but I can't see how that would affect performance when our system uses the single database. (That's the only reason I have highlighted this entry)
February 23, 2006 at 2:09 am
Hi,
Just another possible problem: I have noticed performance issues on production servers having more CPU's than test machines. It seems that the degree of parallelism / hyperthreading can have a negative influence on performance. Check the processor tab on Enterprise manager properties. I sometimes just check 'use 1 processor for parallel execution'.
Hth,
JP de Jong
February 23, 2006 at 3:18 am
Tom mentioned it but I didn't see a response, have you checked the execution plans on both servers to see if there are any differences between the servers?
February 23, 2006 at 4:28 am
There are lots of factors that can affect performance other than procs. I'd run a trace for wait stats, I'd also check out disk i/o completion time and other disk stats to make sure that the disk subsystem isn't your bottleneck. Compare the RPC completion vs sp completion times in case you have a connectivity issue - poor performance from a middle tier server can impact sql server.
Run some tests from QA on the actual server, either on console or terminal session.
I think there are too many other variables at play here.
I'd recommend SQL Server Performance Tuning by ms press ISBN 0-7356-1270-6 as a good reference point and basis for starting to understand performance issues.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 23, 2006 at 8:22 am
Without seeing the execution plans my guess right now would be parallelism. To test on production, I would set cost threshold for parallelism to 12 - 15. That is normally what I run at on my multi proc production servers. This is easy enough to change back and forth.
Tom
February 23, 2006 at 11:29 am
I would assume that Auto close and Auto Shrink is off in Production
February 23, 2006 at 12:43 pm
Before looking at the OS and configuration I'd start with:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply