Same Query Same Database Different Servers
-
Okay, we just upgraded our PeopleSoft database servers from SQL Server 2000 EE to SQL Server 2005 EE. We had significant issues on the Finance Production server, and did a lot to try and fix it: drop and recreate views/indexes, create new indexes, add memory to the server. Seemed to help (what helped, don't know), but we have some queries that are running longer than under 2000. I have one query that I am running in SSMS, and it is significantly slower on our production server than on our development server AND our production server is more robust (dual proc hyper thread 8 GB RAM (only using 3 GB at this time) with database on FC Switch SAN vs dual proc (non hyper thread) 3 GB RAM, local (SCSI) drives -- also dev's processors are slower).
One thing I have found so far is the execution plan on dev is more efficient than the plan on production. Indexes on the tables involved in the query are the same. What else should I be looking at?
-
Update statistics.
-
We did so many things over the weekend, I'm not sure if we did that or not. I'll give it a try and see what happens.
Just in case it doesn't help, still looking for other ideas.
Thanks!
-
Check that you have memory correctly configured;
In case when lock pages in memory is not enabled it is possible that OS will page out SQL Server. This situation might result in inappropriate SQL Server performance. When OS hits memory pressure, depending on the state of the machine and applications, it could be really slow - attempting to free up the resources. This means that SQL Server might not get enough CPU cycles to respond to memory pressure quickly enough.
Such conditions can be avoided by setting max memory setting to appropriate value. This recommended value is calculated by deriving it based on amount of RAM available on the machine and amount of available memory needed to keep OS and other applications running.
Best practices for Memory Settings:
RAM Installed
Available Memory Recommended
< 4GB
512MB - 1GB
4-32GB
1GB - 2GB
32GB - 128GB
2GB-4GB
It does not hurt to rebuild all of your indexes (use ALTER INDEX command). This can be done online or with sort in tempdb as you use Ent edition; watch the space available on the disk for index rebuild (1.2 times space occupied by the db files)
-
We update statistics nightly. Didn't help. Going to look more closely at the indexes. Also asked sys admin to add lock pages in memory priv to the service account login. System has 8 GB RAM installed and visible to the OS and we are only allowing SQL to use up to 3 GB (/3GB switch in boot.ini, and AWE is not enabled).
Still open to other suggestions as well.
Thanks!
-
Hi Lynn - are you running an update statistics with fullscan? sp_updatestats only performs a sampling....You can confirm by executing a DBCC SHOWCONTIG('tablename').
Tommy
Follow @sqlscribe -
Is the dev box still on 2000? Is this a new box you put 2005 on or same box as you had before? Are the databases in 2000 mode still?
-
Hi Lynn - meant to say - confirm by issuing a DBCC SHOW_STATISTICS ('tablename','indexname'). The rows should match the rows sampled - if not update statistics with a fullscan (UPDATE STATISTICS tablename WITH FULLSCAN,INDEX) and try the query again.
Tommy
Follow @sqlscribe -
1st, dev boxes were upgraded to SQL 2005 first. We have no PeopleSoft Database Servers currently running SQL Server 2000.
2nd, same boxes as before. Uninstalled SQL Server 2000, installed SQL Server 2005, reattached user databases, changed to 9.0 compatibility, ran updateusage. Production boxes are more robust (faster/hyperthreaded cpus, FC switched connection to SAN, 15,000 RPM disks in SAN).
Will check stats and get back to you all.
Thanks
-
K - other things you might also want to check, memory configuration (awe), max degree of parallelism, etc.
Tommy
Follow @sqlscribe -
AWE is NOT enabled, only using the /3GB switch. max degree of parallelism is currently set to 2 (the number of physical processors in the server).
-
I'm assuming your running W2K with the /3GB switch? If your running W2K3 you can remove this from the boot.ini file altoghether and it will use PAE. In either case I would recommend using PAE. MSSQL 2005 should automatically enable AWE as soon as the memory is available to the OS. How did the DBCC SHOW_STATISTICS look?
Tommy
Follow @sqlscribe -
Actually, we are running Windows Server 2003 Enterprise Edition. Sys Admin had to add the PAE when he added the additional 4 GB RAM to the server on Friday so the OS could see the full 8 GB RAM.
We added another LUN on the SAN to the server (which blue screened it by the way) to move tempdb to its own set of drives. Sys Admin also had to do some work as a result of the blue screen as the OS stopped seeing the floppy drive, cd drive and something else I can't remember. While doing this extra work this evening, he also found that there was a driver issue with the HBA's -- don't know if this may have been the actual cause of our performance issues but who knows.
Looking at just a few of the stats, yes, some are sampled but some aren't. I am going to put together a script to update stats doing a full scan, but may not get it run until this weekend.
I will keep everyone posted on how things go.
I appreciate the ideas and assistance as at this time, people aren't too happy with the upgrade.
Thanks!
-
we had similar issues
we had 2 queries that under sql 2005 spiked the CPU to 100% and didn't do it on sql 2000. solution is to change the query.
also had a few where the execution plan was different, but we checked on a sql 2005 subscriber and it had a good execution plan. problem was the statistics. dropped and rebuilt the indexes and now we run update statistics with full scan every weekend.
select * from sys.dm_exec_requests order by cpu_time desc
select * from sys.dm_exec_sql_text(0x0200000011EEE20382B1C563CDA789BEC348BF37A8D03B14)
you can use these system views to catch a CPU hog query
we also drop and rebuild some indexes via script rather than alter index. seems it works better in some cases for some reason
-
Unfortunately, some of these queries are written using the PeopleSoft Query Tool (visually build your query and let Oracle decide how best to run it!).
We have come to the conclusion that some of the indexes I created over the weekend helped with some of the performance issues, but which ones og the 139, still don't know.
I am working on updating stats and rebuilding indexes. Want to do it using scripts where I can have some control over when and how things are done.
I am also going to run DTA on a trace I just completed.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply