We are experiencing a major issue since upgrading from SQL2000 to SQL2005 over the weekend. Starting today, it appears that the performance of SQLServer reaches a limit every 15 minutes.
Our configuration is as follows:
Window Server 2K3 x64 Enterprise
SQLServer 2005 x64 Enterprise
HP DL585 with 4 dual core Opterons
32 GB of RAM
2 TB EMC SAN
At first, I thought there was a memory pressure problem, since I had the default max memory set. After changing the max memory to only 25 GB (out of 32 available), the issue went away temporarily. However, after 15-20 minutes, the number of batches/sec dropped in half, and remained after half until I changed the max memory setting again. Over the course of the day, I was able to fix the issue each time by just changing the max memory by 1MB. (From 30,000 to 29,999 and back from 29,999 to 30,000). Each time, the batches/sec counter immediately doubles and remains there for about 15-20 minutes. None of the SQL statements have changed since upgrading.
I have found this post, which talks about a similar issue at the end of the thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=746299&SiteID=1
Any ideas?...this is frustrating to no end.
Thanks!
Ryan
Have you enabled the "Lock Pages in Memory" option for the SQL Server Service Account? I have more details in my blog at http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!115.entry
If you don't do this, you will see a lot more paging, which is under control of the host OS, rather than under the control of the SQLOS.
Did you change the database compatibility level to 90 and then run sp_updatestats on the database after the upgrade?
|||
The symptoms don't sound like they are related to paging, changing max server memory wouldn't automatically bring memory back in from page file. What it might do is remove a poor plan from cache.
Start by analyzing the physical bottlenecks on the system. Use perfmon to gather Processor, Memory and Disk statistics.
The main counters you'll be interested in are
Processor object:
%Processor Time\*
PhysicalDisk Object:
Avg. Disk Queue Length\*
Avg. Disk Sec/Read\*
Avg. Disk Sec/Write\*
Memory Object:
Available MB
Depending on the data you find there you'll need to then look at what queries are consuming a particular resource.
Usually there are no "easy answers" when it comes to determining the cause of a performance issue.
This whitepaper details many methods of finding bottlenecks and resolving issues. http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Have a look at that, then post with which resource seems to be the bottleneck.
|||
I have experienced the same anomaly.
In order to "fix" this, run
DBCC FreeSystemCache ('ALL')
every 5 minutes or so. I setup a batch file that uses osql and run it with the task scheduler every 5 min. For some reason, the memory cache continues to grow until it maxes out your available memory. By running the above command, it seems to reset it again and keeps it under control, allowing consistently high batch throughput.
|||There is something else you can check. SQL Server 2005 now caches zero cost plans where it didn't with SQL Server 2000. I have seen a few high volume OLTP environments fill the cache quickly with ad hoc plans. SQL Server SP2 fixes this and other memory related issues, check out SP2.|||We are definitely seeing symptoms of this ourselves (with 32GB of RAM on x64), where adhoc plans are using 13GB of RAM, causing internal memory pressure in SQL Server 2005. We are eagerly awaiting SP2.|||Hi!
I am experiencing some of the same problems, but with a much smaller system: I'm running SQL Server Express 2005 on a Dual Xenon system with 4GB of RAM and 3 SCSI disks in RAID 5. I am aware that Express only use one CPU and max 1GB for data.
My DB serves a Web Site that during peak time gets about 15 page loads per second, with lots of DB queries related to that (all web pages are dynamic). Currently the DB size is 2GB.
The SQL Server process itself maxes out at about 1.5GB before it reaches it's max 1GB for data limit.
This is the problem (similar to one described above): Every 10 minutes or so the Disk Queue Length suddenly goes crazy and reaches values of 100 - 500. This is a total killer for my site. Normal Disk Queue lenght before this happens is 1 - 3.
My "solution": I've made two scripts, which I schedule to run every 10 minutes. One is scheduled to run 5 minutes before the other, so that the two scripts are alternating every 5 minutes. The scripts are as follows:
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
USE master
EXEC sp_configure 'max server memory (MB)', 4096
RECONFIGURE WITH OVERRIDE
and
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
USE master
EXEC sp_configure 'max server memory (MB)', 1280
RECONFIGURE WITH OVERRIDE
This is a dirty hack indeed (and I don't like it), but it works! The Disk Queue Length remains at 1 - 3, and the site runs smoothly.
I've just now installed the SQL Server Express 2005 Service pack 2 CTP (version 9.0.3033), but this doesn't solve anything. I still have to run with my two sp_configure scripts.
I've also tried the DBCC FreeSystemCache ('ALL') as suggested above, but this doesn't help in my case...
|||Hi again!
This is just an update from me.
I was wrong before about DBCC FREESYSTEMCACHE ( 'ALL' ). It seems to do the trick anyway, and now I run a script with that statement every minute(!).
This makes my server run smooth, but if I don't do this everything halts.
I'm running Service pack 2 now with no help.
After doing some research I think it's got something to do with memory pressure. My SQL server doesn't allocate more than about 1.5 gig of my total of 4 (it's SQL Server Express), but I am running my web server (IIS6) in the same box and with my huge number of pageloads it's stressing the system. I have a lot of photos being cached... and this is what is causing the memory pressure, I think.
Can someone from Microsoft say something about this problem?
|||You said you did an upgrade from SQL Server 2000 to 2005. How did you do the upgrade? The query execution behavior in 2005 is different from 2000. Which means you will have to rewrite some (or worse all) of your stored procedures and queries in order to fully utilize the power of SQL Server 2005 in terms of query execution performance
No comments:
Post a Comment