SQL Remove Old Backups: configuration

Showing posts with label configuration. Show all posts

Sunday, March 25, 2012

32bit to 64bit Server performance is very poor

Hi
You would really have to post something more specific, but...
Have you updated the statistics and indexes?
Make sure that your configuration options have not changed (sp_configure)
e.g. check that you are using the right amount of memory, CPU affinity etc..
Check database options (sp_dboptions) to make sure that setting are
consistent (e.g Autoshrink is not on!)
Check that you have not introduced an IO bottleneck (e.g logs and data on
same disc or disc sector size has not been set correctly).
Run SQL profiler to identify what is taking a prolong time and investigate
specific queries/procedures.
Use Performance Monitor to check for other issues.
HTH
John
"Jaison Jose" wrote:

> Dear Team,
> After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we ar
e
> facing performance related issues....Could any one help me plz?
> Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itani
um
> 2(IA64).
> Regards,
> JaisonDear Team,
After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we are
facing performance related issues....Could any one help me plz?
Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itanium
2(IA64).
Regards,
Jaison|||Hi
You would really have to post something more specific, but...
Have you updated the statistics and indexes?
Make sure that your configuration options have not changed (sp_configure)
e.g. check that you are using the right amount of memory, CPU affinity etc..
Check database options (sp_dboptions) to make sure that setting are
consistent (e.g Autoshrink is not on!)
Check that you have not introduced an IO bottleneck (e.g logs and data on
same disc or disc sector size has not been set correctly).
Run SQL profiler to identify what is taking a prolong time and investigate
specific queries/procedures.
Use Performance Monitor to check for other issues.
HTH
John
"Jaison Jose" wrote:

> Dear Team,
> After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we ar
e
> facing performance related issues....Could any one help me plz?
> Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itani
um
> 2(IA64).
> Regards,
> Jaison|||Hi John,
Statistics are updated. All the options are set as it was there in the
previous environment. The data and log file are on the same disc. But john,
this all settings are quite fine with Xeon Processors, the cpu utilization
was only ranging in between 10-15%. Application was working very well.But
When it comes to itanium , suddenly the environment has changed. The cpu
starts shooting upto 90%. I just want to know one thing , If i want to move
a
32 bit database to a 64 bit environment, what and all the wright steps that
i
need to take before proceeding with the migration?....So that i can come to
know where i went wrong....
Regards,
Jaison Jose
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You would really have to post something more specific, but...
> Have you updated the statistics and indexes?
> Make sure that your configuration options have not changed (sp_configure)
> e.g. check that you are using the right amount of memory, CPU affinity etc
.
> Check database options (sp_dboptions) to make sure that setting are
> consistent (e.g Autoshrink is not on!)
> Check that you have not introduced an IO bottleneck (e.g logs and data on
> same disc or disc sector size has not been set correctly).
> Run SQL profiler to identify what is taking a prolong time and investigate
> specific queries/procedures.
> Use Performance Monitor to check for other issues.
> HTH
> John
> "Jaison Jose" wrote:
>|||Hi John,
Statistics are updated. All the options are set as it was there in the
previous environment. The data and log file are on the same disc. But john,
this all settings are quite fine with Xeon Processors, the cpu utilization
was only ranging in between 10-15%. Application was working very well.But
When it comes to itanium , suddenly the environment has changed. The cpu
starts shooting upto 90%. I just want to know one thing , If i want to move
a
32 bit database to a 64 bit environment, what and all the wright steps that
i
need to take before proceeding with the migration?....So that i can come to
know where i went wrong....
Regards,
Jaison Jose
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You would really have to post something more specific, but...
> Have you updated the statistics and indexes?
> Make sure that your configuration options have not changed (sp_configure)
> e.g. check that you are using the right amount of memory, CPU affinity etc
.
> Check database options (sp_dboptions) to make sure that setting are
> consistent (e.g Autoshrink is not on!)
> Check that you have not introduced an IO bottleneck (e.g logs and data on
> same disc or disc sector size has not been set correctly).
> Run SQL profiler to identify what is taking a prolong time and investigate
> specific queries/procedures.
> Use Performance Monitor to check for other issues.
> HTH
> John
> "Jaison Jose" wrote:
>|||Hi Jaison
When you have
http://www.microsoft.com/sql/soluti...ingto64bit.mspx it sounds
easy, but as this is a migration and not a in-situ upgrade you need to
consider all the factors that a migration entails.
You may want to read
http://www.microsoft.com/technet/pr...on.ms
px which covers alot of the things you should check regardless of version.
You may also want to do some monitoring with performance monitor such as
http://www.sql-server-performance.c...nce_article.asp
Also if this is a new system then running SQLIOStress may be a good idea.
I assume that you have checked that the hardware is supported by the version
of SQL Server you are using
http://msdn.microsoft.com/library/d...>
g64_2ctv.asp and everything is service packed?
John
"Jaison Jose" wrote:
[vbcol=seagreen]
> Hi John,
> Statistics are updated. All the options are set as it was there in the
> previous environment. The data and log file are on the same disc. But john
,
> this all settings are quite fine with Xeon Processors, the cpu utilization
> was only ranging in between 10-15%. Application was working very well.But
> When it comes to itanium , suddenly the environment has changed. The cpu
> starts shooting upto 90%. I just want to know one thing , If i want to mov
e a
> 32 bit database to a 64 bit environment, what and all the wright steps tha
t i
> need to take before proceeding with the migration?....So that i can come
to
> know where i went wrong....
> Regards,
> Jaison Jose
>
> "John Bell" wrote:
>|||Hai John,
I had created a fresh database in the 64 bit new server. Then
I had generated a complete script(database->alltask->generate script-> ) from
the 32 bit sql server and made it run on 64 bit. And then done a DTS to move
the data to the newly created database. The reason why i had gone for this
way is nothing but while trying for an attach i was facing some error(Sorry,
i am not remembering exactly the error..).
Does this cause any issue?
If something had gone wrong with this, Will it be possible me to configure
it proper..?
"Jaison Jose" wrote:

> Dear Team,
> After migrating to sql server 2000 32 bit to sql server 2000 64 bit, we ar
e
> facing performance related issues....Could any one help me plz?
> Previous OS was win2003 EE(X86),Now we are using win2003 EE with SP1,Itani
um
> 2(IA64).
> Regards,
> Jaison|||Hi Jaison
Check that the indexes were scripted properly. If data was pumped into the
tables after creating the indexes you would need to rebuild the indexes. If
you have auto create stats on for you database then you will have lost these
.
Backup/restore should have worked fine when transferring the database as
well as detach/attach which should be significantly quicker than your method
so you may want to try it again.
John
"Jaison Jose" wrote:
[vbcol=seagreen]
> Hai John,
> I had created a fresh database in the 64 bit new server. Then
> I had generated a complete script(database->alltask->generate script-> ) fr
om
> the 32 bit sql server and made it run on 64 bit. And then done a DTS to mo
ve
> the data to the newly created database. The reason why i had gone for this
> way is nothing but while trying for an attach i was facing some error(Sorr
y,
> i am not remembering exactly the error..).
> Does this cause any issue?
> If something had gone wrong with this, Will it be possible me to configure
> it proper..?
>
> "Jaison Jose" wrote:
>|||Hi John,
USE Database_Name
GO
EXEC sp_MSforeachtable @.command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
Hope this will do. Appreciate ur comments as well.
I had already done an index defragmentation by using DBCC INDEXDEFRAG
statement , but no visible effects. In this case is there any difference
between
DBCC INDEXDEFRAG and DBCC DBREINDEX ?
Regards,
Jaison
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jaison
> Check that the indexes were scripted properly. If data was pumped into the
> tables after creating the indexes you would need to rebuild the indexes. I
f
> you have auto create stats on for you database then you will have lost the
se.
> Backup/restore should have worked fine when transferring the database as
> well as detach/attach which should be significantly quicker than your meth
od
> so you may want to try it again.
> John
> "Jaison Jose" wrote:
>|||Hi Jaison
When you have
http://www.microsoft.com/sql/soluti...ingto64bit.mspx it sounds
easy, but as this is a migration and not a in-situ upgrade you need to
consider all the factors that a migration entails.
You may want to read
http://www.microsoft.com/technet/pr...on.ms
px which covers alot of the things you should check regardless of version.
You may also want to do some monitoring with performance monitor such as
http://www.sql-server-performance.c...nce_article.asp
Also if this is a new system then running SQLIOStress may be a good idea.
I assume that you have checked that the hardware is supported by the version
of SQL Server you are using
http://msdn.microsoft.com/library/d...>
g64_2ctv.asp and everything is service packed?
John
"Jaison Jose" wrote:
[vbcol=seagreen]
> Hi John,
> Statistics are updated. All the options are set as it was there in the
> previous environment. The data and log file are on the same disc. But john
,
> this all settings are quite fine with Xeon Processors, the cpu utilization
> was only ranging in between 10-15%. Application was working very well.But
> When it comes to itanium , suddenly the environment has changed. The cpu
> starts shooting upto 90%. I just want to know one thing , If i want to mov
e a
> 32 bit database to a 64 bit environment, what and all the wright steps tha
t i
> need to take before proceeding with the migration?....So that i can come
to
> know where i went wrong....
> Regards,
> Jaison Jose
>
> "John Bell" wrote:
>

Friday, February 24, 2012

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))