Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
johngekas
Starting Member
1 Post |
 Posted - 2008-12-12 : 05:07:14
|
| Hi all,I would greatly appreciate some help with a performance issue we are experiencing. The problem is that a stored procedure used to generate a report takes up too much time (ie. around 10 mins) to execute. The procedure's database resides on a 64-bit SQL Server Enterprise, with SP1.The same procedure runs well enough (ie. < 30 secs) when running off an identical database residing on a different SQL Server 2005 (Standard edition, on a totally different machine). The two databases are completely identical in terms of schema, data, indexes, procedures and so on (basically, the one on the SQL 2005 Standard has been restored from the on on the SQL Enterprise).I have been trying to figure out why this might be happening, but I am totally perplexed. To make things worse, I do not have access to the Enterprise server in order to take a closer look - I only have access to the Standard edition server (on a different physical location).In my effort to duplicate the poor performance conditions, I have restored the same databse on other servers as well (which I have access to, mostly local to my location). When the database was restored on a SQL Express server, the stored procedure takes about as long to execute (~10 mins), as on the Enterprise server. I am aware that SQL Express has different memory restrictions to the other versions, but would be very interested to know whether there might be a relation between the two cases.Using the SQL Profiler on both the Standard and Express servers, I noticed that the procedure occupies many times the CPU resources and memory (under the 'CPU' and 'Reads' columns the Profiler generates) when run on the Express server, compared to when run on the Standard server. Given that both databases are identical both in schema and data, isn't this a bit weird?In addition, is there any chance that the poor performance on the Enterprise server might be related to settings such as AWE memory allocation, query plans, or the "lock pages in memory" setting? Would there be any benefit tampering with the above settings (or any other, for that matter)?Any advice on this issue would be greatly appreciated. Thank you very much for your time.John G.ps. Apologies in case I posted this under the wrong forum category. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-12 : 09:36:19
|
| First of all, You need to install latest Service packs for SQL 2005.You should enable 'Lock pages in Memory' for Enterprise Edition. The stored procedure could run slow due to various factors:parameter sniffing,procedure cache consuming lots of memory,Indexes are fragmented,stats are out of date.Can you post output of DBCC MEmorystatus on Ent server? Also what SP is doing? |
 |
|
|
sqlserverdba
Yak Posting Veteran
53 Posts |
Posted - 2008-12-12 : 09:49:39
|
| Hi,How to check out of date stats ? Does enabling 'Lock pages in Memory' for Enterprise Edition 2000 also help to get better performanace of procedure?Thanks |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-12 : 10:02:10
|
| When you restore the database the indexes are build from scratch on the new server with no fragmentation.As sodeep points out, the original indexes may be fragmented giving them poorer performance.If not done already: set up reindexing / defrag of index, either as a seperate scheduled job or as part of backup maintenance. This can be done either online or at quite times. If you do not have online indexing available:Run dbcc showcontig against tables and save results to seperate table. Then query results and run DBCC DBREINDEX against tables with LogicalFragmentation > x OR ScanDensity < x |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-12 : 10:28:15
|
quote:
Originally posted by sqlserverdba
Hi,How to check out of date stats ?DBCC Showstatistics Does enabling 'Lock pages in Memory' for Enterprise Edition 2000 also help to get better performanace of procedure?It is recommended in 64-bit Enterprise OS when you are running SQL 2005 Enterprise Edition.SQL 2000 Ent Edition can't give Max server Memory.Thanks
|
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-12 : 10:29:19
|
| Read this carefully:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114445 |
|
|
|
|
|
|
|
|
Poor performance issue on SQL 2005 Enterprise