SQL Remove Old Backups: virtual

Showing posts with label virtual. Show all posts

Friday, February 24, 2012

2005 slower than 2000?

Admittedly I'm evaluating 2005 in a virtual machine but I'm comparing it to
an older machine:
2005 thinks it has 1Gb and 1 cpu on a host which has 2GB, 2 2.4Ghz Amd64's,
raid0. it runs in boosted priority when active.
2000 is on an older single Amd64 2Ghz, 1Gb, single sata drive.
A very ugly query built by selections on a web page is parsed and
parameterized.
on 2000 it runs in a couple seconds against a few thousand rows joining lots
of tables, varying by the selections made.
on 2005 it takes twice as long for the identical query. all other software
variables are identical. the db is a copy from 2000. stats updated. tried
with both compatibility levels.
I'm thinking I'll wait for 9.1 or 9.2 before I make the move.br (br@.discussions.microsoft.com) writes:
> Admittedly I'm evaluating 2005 in a virtual machine but I'm comparing it
> to an older machine:
> 2005 thinks it has 1Gb and 1 cpu on a host which has 2GB, 2 2.4Ghz
> Amd64's, raid0. it runs in boosted priority when active. 2000 is on an
> older single Amd64 2Ghz, 1Gb, single sata drive.
> A very ugly query built by selections on a web page is parsed and
> parameterized. on 2000 it runs in a couple seconds against a few
> thousand rows joining lots of tables, varying by the selections made.
> on 2005 it takes twice as long for the identical query. all other
> software variables are identical. the db is a copy from 2000. stats
> updated. tried with both compatibility levels.
> I'm thinking I'll wait for 9.1 or 9.2 before I make the move.
Before you do that... The database on SQL 2005, is that a copy of the
database from SQL 2000? In such case, did you run UPDATE STATISTICS
WITH FULLSCAN on all table (or just sp_updatestats if you prefer)? When
you restore a database from SQL 2000, all statistics are invalidated,
this can be an important reason for difference in performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Did you install SQL Server Standard/Enterprise Edition or Express Edition?
Express edition is limited by design to 1 CPU, 1 GB RAM, and 4 GB database
size.
There are server and database configuration settings that can impact
performance:
http://www.sql-server-performance.c...ance_audit5.asp
Analyze the execution plan and performance monitor logs to determine what
exactly is going on.
http://msdn.microsoft.com/library/d... />
1_5pde.asp
http://www.informit.com/guides/cont...&seqNum=28&rl=1
BTY, what is "9.1 or 9.2" ?
"br" <br@.discussions.microsoft.com> wrote in message
news:2C96E892-1713-4486-8DE6-B0E56AEF29C3@.microsoft.com...
> Admittedly I'm evaluating 2005 in a virtual machine but I'm comparing it
> to
> an older machine:
> 2005 thinks it has 1Gb and 1 cpu on a host which has 2GB, 2 2.4Ghz
> Amd64's,
> raid0. it runs in boosted priority when active.
> 2000 is on an older single Amd64 2Ghz, 1Gb, single sata drive.
> A very ugly query built by selections on a web page is parsed and
> parameterized.
> on 2000 it runs in a couple seconds against a few thousand rows joining
> lots
> of tables, varying by the selections made.
> on 2005 it takes twice as long for the identical query. all other software
> variables are identical. the db is a copy from 2000. stats updated. tried
> with both compatibility levels.
> I'm thinking I'll wait for 9.1 or 9.2 before I make the move.
>|||Thanks guys,
It's the developer version. Roughly equivalent to standard.
The stats are updated.
I'm figuring I'll have to re-optimize for 2005, so I don't mind waiting for
a fix rev or two.
But thanks for the links. I'll check them out.
"JT" wrote:

> Did you install SQL Server Standard/Enterprise Edition or Express Edition?
> Express edition is limited by design to 1 CPU, 1 GB RAM, and 4 GB database
> size.
> There are server and database configuration settings that can impact
> performance:
> http://www.sql-server-performance.c...ance_audit5.asp
> Analyze the execution plan and performance monitor logs to determine what
> exactly is going on.
> http://msdn.microsoft.com/library/d...>
n_1_5pde.asp
> http://www.informit.com/guides/cont...&seqNum=28&rl=1
> BTY, what is "9.1 or 9.2" ?
> "br" <br@.discussions.microsoft.com> wrote in message
> news:2C96E892-1713-4486-8DE6-B0E56AEF29C3@.microsoft.com...
>
>|||Sorry, missed that. Means one or two revisions to Sql2005, which I'm
considering to be Sql Server 9.
"JT" wrote:
> BTY, what is "9.1 or 9.2" ?|||Try testing again in an environment other than a virtual machine (MS Virtual
PC ?). There may be VM session settings limiting RAM and CPU usage. Also,
SQL Server is optimized to work directly with the OS and hardware, and a VM
session is an abstraction layer.
"br" <br@.discussions.microsoft.com> wrote in message
news:9C34254A-E671-4E1D-84BF-E586D5A74830@.microsoft.com...
> Thanks guys,
> It's the developer version. Roughly equivalent to standard.
> The stats are updated.
> I'm figuring I'll have to re-optimize for 2005, so I don't mind waiting
> for
> a fix rev or two.
> But thanks for the links. I'll check them out.
> "JT" wrote:
>|||There are a lot of people using 2005 today with no broad based complaints
about performance. You are perhaps thinking that a future service pack will
include performance optimizations for a vitual machine environment? ;-)
"br" <br@.discussions.microsoft.com> wrote in message
news:4A3878EB-4E4B-491F-9C3B-5A81F2FC8510@.microsoft.com...
> Sorry, missed that. Means one or two revisions to Sql2005, which I'm
> considering to be Sql Server 9.
> "JT" wrote:
>|||True... ok, some w

end when I have a free morning I'll put it on a real
machine.
"JT" wrote:

> ...a VM session is an abstraction layer.
>

2005 Reporting Services Configuration Issues

Hi All -

I am trying to configure reporting services on virtual machine but unable to succeed. I never touched this topic before SQL Server 2005. The left hand side options in Reporting Services Configuration Manager such as Server Status, Virtual Directories, Web Identity works fine but it fails in Database Setup section

Here are the details:

AppServer: SQL Server 2005 Reporting Services & Client components build# 2153 (with SP1 & SP1 hotfix)

going to apply SP2 and Add-on component after the configuration

DBServer: SQL Server 2005 Database Services & Client components build# 2153 (with SP1 & SP1 hotfix)

OS: Windows 2003 Server with SP1

IIS version on AppServer: 6

I have SQL 2005 Reporting services and 2005 Database Engine on 2 different machines.

Here is the error message I am receiving when I try to apply the script in "Database Setup" section of Reporting Services Configuration Manager

It did not log any errors in SQL Server however it shows a warning message in Event Log

Event Log message - "The RSReportServer.config file has been modified."

Configuration Manager error message - It shows me "Create a Grants Rights Script" task failed

"System.UnauthorizedAccessException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(Object o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementObjectSearcher.Initialize()
at System.Management.ManagementObjectSearcher.Get()
at Microsoft.ReportingServices.Diagnostics.WebUtil.GetIPAddresses(String machine)
at ReportServicesConfigUI.Panels.DatabaseSetupPanel.IsLocalDbServer(String dbServer)
at ReportServicesConfigUI.Panels.DatabaseSetupPanel.SetDatabaseConnectionTask(String connectionString, String server, String database, ConfigurationCredentialsType credsType, String account, String password, Boolean upgrade, String dbVersion)"

I have captured the screenshot, let me know if I can send this.

One of my friends suggested the following and I have tried the first option but could not work - Option I)

Our account that is used for database connection should have

1) public and RSExecRole roles for the ReportServerDatabase.

2) RSExecRole roles for the master, msdb , and ReportServerTempDB databases.

Else - Option II)

we can run the Reporting Service Windows Service under the local System Account and then the credentials type for the database set up would be Service credentials. This way we would have more permission.

Did anyone face this problem? how should I resolve this?

Anyone have steps to follow as I feel of missing something in the configuration steps?

Any information/ suggestions/ pointer on this are higly appreciated.

Thanks in advance !!

-Satya

We were getting this error and found that the dbo.role was not the owner of the dbo.schema. When we made that change we were able to update the database. When we originally installed and had problems with the remote database we had to make sure that database had windows authentication only.|||

TWe also escalated this issue to Microsoft Product Support Service and they suggested the following:

The account that is used for database connection should have

1) public and RSExecRole roles for the ReportServerDatabase.

2) RSExecRole roles for the master, msdb , and ReportServerTempDB databases.

Else we can run the Reporting Service Windows Service under the local System Account and then the credentials type for the database set up would be Service credentials. This way we would have more permission.

but before we had a work around however you will still find the script showing error in Reporting Services configuration manager.

We have generated the login script and executed thru' Management Studio. Verified the account permission as mentioned above.

Just ignore the error message that you see in Configuration Manager -

Configuration Manager error message - "System.UnauthorizedAccessException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))