MAJOR PROBLEM: New SQL2005 installation performance problem.

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

MAJOR PROBLEM: New SQL2005 installation performance problem.

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

MAJOR PROBLEM: New SQL2005 installation performance problem.

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

MAJOR PROBLEM: New SQL2005 installation performance problem.

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

MAJOR PROBLEM: New SQL2005 installation performance problem.

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

MAJOR PROBLEM: New SQL2005 installation performance problem.

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

Maitnenance Plans start, but don't do anything

Hi,
This is an SQL2005 issue.
We are having a strange problem where maintenance plans will start,
but not do anything and just stay in the "Executing" stage forever. I
have created a simple plan that just does a tran log backup for a test
database which should be done in half a second, but starts and thats
it. Also, no tran log is created so it isn;t doing anything. I have
created a job that runs sql script and it runs with no problem, it
just seems to be with the maintence plans that has the issue.
there are no errors being generated and the logs all look fine.
Anyone have any ideas what is going wrong or what I can try to do to
figure this out'
Thanks!Ed,
You don't mention the version and build of SQL2005.
Chris
"Ed" <ede@.nait.ab.ca> wrote in message
news:1193673452.504643.227330@.z24g2000prh.googlegroups.com...
> Hi,
> This is an SQL2005 issue.
> We are having a strange problem where maintenance plans will start,
> but not do anything and just stay in the "Executing" stage forever. I
> have created a simple plan that just does a tran log backup for a test
> database which should be done in half a second, but starts and thats
> it. Also, no tran log is created so it isn;t doing anything. I have
> created a job that runs sql script and it runs with no problem, it
> just seems to be with the maintence plans that has the issue.
> there are no errors being generated and the logs all look fine.
> Anyone have any ideas what is going wrong or what I can try to do to
> figure this out'
> Thanks!
>|||Try right-clicking on the job and select 'refresh job', or 'refresh' on
SQLagent.
It's not the job that's going on forever, it's the fact that MS (like EM
before it) has to be manually refreshed.
PITA, but what is, is.
Good luck,
Jay
"Ed" <ede@.nait.ab.ca> wrote in message
news:1193673452.504643.227330@.z24g2000prh.googlegroups.com...
> Hi,
> This is an SQL2005 issue.
> We are having a strange problem where maintenance plans will start,
> but not do anything and just stay in the "Executing" stage forever. I
> have created a simple plan that just does a tran log backup for a test
> database which should be done in half a second, but starts and thats
> it. Also, no tran log is created so it isn;t doing anything. I have
> created a job that runs sql script and it runs with no problem, it
> just seems to be with the maintence plans that has the issue.
> there are no errors being generated and the logs all look fine.
> Anyone have any ideas what is going wrong or what I can try to do to
> figure this out'
> Thanks!
>

Maintenance Plan won't run, delete, or modify

Had to replace leased server hardware running Server2003, Sql2005. Had
machine Server B, set up sql2005, set up maintenance plan to do full backup
on all user dbs every night to Server C network share. Worked great for over
a week. Came time to do the swamp, brought down old server, renamed Server B
to Server A, ran:
EXEC sp_dropserver '<old_name>'
GO
EXEC sp_addserver '<new_name>', 'local'
GO
All other functions to the database seem to be working fine. Was using a
network administrator account - same one that worked before the name change
so the SID is the same. Unfortunately, I decided to try and delete and start
over in case it was just a gliche. Now, the history seems to be gone, but I
can't actually delete the job or the maintenance plan. Get an error "Does
not allow remote connections." even when I'm physically sitting at the
machine.
Now, maintenance plans won't run - can't delete them, can't modify them.
Other dts packages that execute a query or bring in data from other
datasources work just fine.
Help!?Have a litte more information. I successfully recreated the two maintenance
plans and with one minor problem, they work fine. I'll deal with the new
ones later.
But, since I've pretty much got new plans for system and user databases, I'd
like to delete the originals, but can't. For the systemMtnc, when I try to
delete, I get this error when I'm physically sitting at the machine:
"Exception has been thrown by the target of an invocation(mscorlib). An
error has occurred while establishing a connection to the server. When
connecting to sql server 2005, this failure may be cause by the fact that
under the default settings, sql server does not allow remote connecitons.
(provider: tcp provider, error: 0 no such host is know) error 11001
I've disabled both jobs, but would prefer to delete the plan/jobs. But, it
won't let me. Any ideas?
"Janet" wrote:
> Had to replace leased server hardware running Server2003, Sql2005. Had
> machine Server B, set up sql2005, set up maintenance plan to do full backup
> on all user dbs every night to Server C network share. Worked great for over
> a week. Came time to do the swamp, brought down old server, renamed Server B
> to Server A, ran:
> EXEC sp_dropserver '<old_name>'
> GO
> EXEC sp_addserver '<new_name>', 'local'
> GO
> All other functions to the database seem to be working fine. Was using a
> network administrator account - same one that worked before the name change
> so the SID is the same. Unfortunately, I decided to try and delete and start
> over in case it was just a gliche. Now, the history seems to be gone, but I
> can't actually delete the job or the maintenance plan. Get an error "Does
> not allow remote connections." even when I'm physically sitting at the
> machine.
> Now, maintenance plans won't run - can't delete them, can't modify them.
> Other dts packages that execute a query or bring in data from other
> datasources work just fine.
> Help!?|||Found this and it worked like a charm - hope it helps someone else.
1. Select the ID with the select statement
select * from sysmaintplan_plans
2. Replace with the selected ID and run the delete statements
delete from sysmaintplan_log where plan_id = ''
delete from sysmaintplan_subplans where plan_id = ''
delete from sysmaintplan_plans where id = ''
3. Delete the SQL Server Jobs with the Management Studio
"Janet" wrote:
> Have a litte more information. I successfully recreated the two maintenance
> plans and with one minor problem, they work fine. I'll deal with the new
> ones later.
> But, since I've pretty much got new plans for system and user databases, I'd
> like to delete the originals, but can't. For the systemMtnc, when I try to
> delete, I get this error when I'm physically sitting at the machine:
> "Exception has been thrown by the target of an invocation(mscorlib). An
> error has occurred while establishing a connection to the server. When
> connecting to sql server 2005, this failure may be cause by the fact that
> under the default settings, sql server does not allow remote connecitons.
> (provider: tcp provider, error: 0 no such host is know) error 11001
> I've disabled both jobs, but would prefer to delete the plan/jobs. But, it
> won't let me. Any ideas?
>
> "Janet" wrote:
> > Had to replace leased server hardware running Server2003, Sql2005. Had
> > machine Server B, set up sql2005, set up maintenance plan to do full backup
> > on all user dbs every night to Server C network share. Worked great for over
> > a week. Came time to do the swamp, brought down old server, renamed Server B
> > to Server A, ran:
> > EXEC sp_dropserver '<old_name>'
> > GO
> > EXEC sp_addserver '<new_name>', 'local'
> > GO
> >
> > All other functions to the database seem to be working fine. Was using a
> > network administrator account - same one that worked before the name change
> > so the SID is the same. Unfortunately, I decided to try and delete and start
> > over in case it was just a gliche. Now, the history seems to be gone, but I
> > can't actually delete the job or the maintenance plan. Get an error "Does
> > not allow remote connections." even when I'm physically sitting at the
> > machine.
> >
> > Now, maintenance plans won't run - can't delete them, can't modify them.
> > Other dts packages that execute a query or bring in data from other
> > datasources work just fine.
> >
> > Help!?

Maintenance Plan won't run, delete, or modify

Had to replace leased server hardware running Server2003, Sql2005. Had
machine Server B, set up sql2005, set up maintenance plan to do full backup
on all user dbs every night to Server C network share. Worked great for over
a week. Came time to do the swamp, brought down old server, renamed Server B
to Server A, ran:
EXEC sp_dropserver '<old_name>'
GO
EXEC sp_addserver '<new_name>', 'local'
GO
All other functions to the database seem to be working fine. Was using a
network administrator account - same one that worked before the name change
so the SID is the same. Unfortunately, I decided to try and delete and start
over in case it was just a gliche. Now, the history seems to be gone, but I
can't actually delete the job or the maintenance plan. Get an error "Does
not allow remote connections." even when I'm physically sitting at the
machine.
Now, maintenance plans won't run - can't delete them, can't modify them.
Other dts packages that execute a query or bring in data from other
datasources work just fine.
Help!?
Have a litte more information. I successfully recreated the two maintenance
plans and with one minor problem, they work fine. I'll deal with the new
ones later.
But, since I've pretty much got new plans for system and user databases, I'd
like to delete the originals, but can't. For the systemMtnc, when I try to
delete, I get this error when I'm physically sitting at the machine:
"Exception has been thrown by the target of an invocation(mscorlib). An
error has occurred while establishing a connection to the server. When
connecting to sql server 2005, this failure may be cause by the fact that
under the default settings, sql server does not allow remote connecitons.
(provider: tcp provider, error: 0 no such host is know) error 11001
I've disabled both jobs, but would prefer to delete the plan/jobs. But, it
won't let me. Any ideas?
"Janet" wrote:

> Had to replace leased server hardware running Server2003, Sql2005. Had
> machine Server B, set up sql2005, set up maintenance plan to do full backup
> on all user dbs every night to Server C network share. Worked great for over
> a week. Came time to do the swamp, brought down old server, renamed Server B
> to Server A, ran:
> EXEC sp_dropserver '<old_name>'
> GO
> EXEC sp_addserver '<new_name>', 'local'
> GO
> All other functions to the database seem to be working fine. Was using a
> network administrator account - same one that worked before the name change
> so the SID is the same. Unfortunately, I decided to try and delete and start
> over in case it was just a gliche. Now, the history seems to be gone, but I
> can't actually delete the job or the maintenance plan. Get an error "Does
> not allow remote connections." even when I'm physically sitting at the
> machine.
> Now, maintenance plans won't run - can't delete them, can't modify them.
> Other dts packages that execute a query or bring in data from other
> datasources work just fine.
> Help!?
|||Found this and it worked like a charm - hope it helps someone else.
1. Select the ID with the select statement
select * from sysmaintplan_plans
2. Replace with the selected ID and run the delete statements
delete from sysmaintplan_log where plan_id = ''
delete from sysmaintplan_subplans where plan_id = ''
delete from sysmaintplan_plans where id = ''
3. Delete the SQL Server Jobs with the Management Studio
"Janet" wrote:
[vbcol=seagreen]
> Have a litte more information. I successfully recreated the two maintenance
> plans and with one minor problem, they work fine. I'll deal with the new
> ones later.
> But, since I've pretty much got new plans for system and user databases, I'd
> like to delete the originals, but can't. For the systemMtnc, when I try to
> delete, I get this error when I'm physically sitting at the machine:
> "Exception has been thrown by the target of an invocation(mscorlib). An
> error has occurred while establishing a connection to the server. When
> connecting to sql server 2005, this failure may be cause by the fact that
> under the default settings, sql server does not allow remote connecitons.
> (provider: tcp provider, error: 0 no such host is know) error 11001
> I've disabled both jobs, but would prefer to delete the plan/jobs. But, it
> won't let me. Any ideas?
>
> "Janet" wrote: