Database statistics

I need to know the statistics of use of the databases in my sql server 2k to
make a report... How ca I do that?
When you say use I assume you mean items like:
How many users have logged on per day.
How many commands have been processed per database.
Average duration of a command
etc...
Well I hate to say it but sql server does not provide you this
automatically. Although you can do server side tracing to save trace
information to a sql server table. Then using the information saved from
your trace you can then produce some of these statistics. Here is an
article I wrote that might help:
http://www.dbazine.com/larsen7.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ricardo" <nomail@.terra.com.br> wrote in message
news:OSezecwhEHA.3264@.tk2msftngp13.phx.gbl...
> I need to know the statistics of use of the databases in my sql server 2k
to
> make a report... How ca I do that?
>
sql

Database statistics

I need to know the statistics of use of the databases in my sql server 2k to
make a report... How ca I do that?When you say use I assume you mean items like:
How many users have logged on per day.
How many commands have been processed per database.
Average duration of a command
etc...
Well I hate to say it but sql server does not provide you this
automatically. Although you can do server side tracing to save trace
information to a sql server table. Then using the information saved from
your trace you can then produce some of these statistics. Here is an
article I wrote that might help:
http://www.dbazine.com/larsen7.shtml
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ricardo" <nomail@.terra.com.br> wrote in message
news:OSezecwhEHA.3264@.tk2msftngp13.phx.gbl...
> I need to know the statistics of use of the databases in my sql server 2k
to
> make a report... How ca I do that?
>

Database statistics

I need to know the statistics of use of the databases in my sql server 2k to
make a report... How ca I do that?When you say use I assume you mean items like:
How many users have logged on per day.
How many commands have been processed per database.
Average duration of a command
etc...
Well I hate to say it but sql server does not provide you this
automatically. Although you can do server side tracing to save trace
information to a sql server table. Then using the information saved from
your trace you can then produce some of these statistics. Here is an
article I wrote that might help:
http://www.dbazine.com/larsen7.shtml
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ricardo" <nomail@.terra.com.br> wrote in message
news:OSezecwhEHA.3264@.tk2msftngp13.phx.gbl...
> I need to know the statistics of use of the databases in my sql server 2k
to
> make a report... How ca I do that?
>

Database Space Used

I'm trying to report the amount of space allocated and used for each
database. I use sysfiles to report the total space allocated to a database,
but can't find information regarding how much of that space is has been used.
I want to store that information in a table each week/month to chart growth.
Is there a system table that stores how much space of each datafile/database
is being used?
Thanks. Any help would be appreciated.
Ron
You can get that information from the stored procedure sp_spaceused. That
just sums the space used and reserved for the tables and indexes in the
database from sysindexes. You can study the code of sp_spaceused (it's in
the master database), but what you want is basically:
SELECT SUM(reserved)
FROM sysindexes
WHERE indid in (0, 1, 255)
Jacco Schalkwijk
SQL Server MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
> database,
> but can't find information regarding how much of that space is has been
> used.
> I want to store that information in a table each week/month to chart
> growth.
> Is there a system table that stores how much space of each
> datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>
|||There is an undocumented DBCC command 'DBCC SHOWFILESTATS' that returns
information on allocations per file. You can write a simple wrapper that
aggregates per filegroup or database (or both).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
database,
> but can't find information regarding how much of that space is has been
used.
> I want to store that information in a table each week/month to chart
growth.
> Is there a system table that stores how much space of each
datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>

Database Space Used

I'm trying to report the amount of space allocated and used for each
database. I use sysfiles to report the total space allocated to a database,
but can't find information regarding how much of that space is has been used.
I want to store that information in a table each week/month to chart growth.
Is there a system table that stores how much space of each datafile/database
is being used?
Thanks. Any help would be appreciated.
RonYou can get that information from the stored procedure sp_spaceused. That
just sums the space used and reserved for the tables and indexes in the
database from sysindexes. You can study the code of sp_spaceused (it's in
the master database), but what you want is basically:
SELECT SUM(reserved)
FROM sysindexes
WHERE indid in (0, 1, 255)
--
Jacco Schalkwijk
SQL Server MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
> database,
> but can't find information regarding how much of that space is has been
> used.
> I want to store that information in a table each week/month to chart
> growth.
> Is there a system table that stores how much space of each
> datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>|||There is an undocumented DBCC command 'DBCC SHOWFILESTATS' that returns
information on allocations per file. You can write a simple wrapper that
aggregates per filegroup or database (or both).
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
database,
> but can't find information regarding how much of that space is has been
used.
> I want to store that information in a table each week/month to chart
growth.
> Is there a system table that stores how much space of each
datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>

Database Snapshots & Reporting

We are looking at mirroring some of our databases to a remote location
and snapshotting those databases in order to report off them.
One of Microsofts recommendations is to add a time suffix to the
snapshotname in order to identify the age of the snapshot.
Any reporting system is going to use a DSN to connect to the snapshot,
we intend to snapshot frequently in order to keep the data as fresh as
possible. Does this not mean that the DSN is going to need to change to
point to the latest snapshot database?
The only alternative I can think of is to sp_renamedb the existing
snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
the original name.
This is a superb feature and a great selling point for 2005.
Has anyone implemented this ? and if so how did you overcome this
problem.
Kind Regards & Thanks.
An update for anyone else who has this problem, we have found a
possible solution.
If your application uses DSNs
You can create a number of of file DSNs relevant to the snapshot name
e.g.
appdb_1200.dsn
appdb_1800.dsn
appdb_0000.dsn
appdb_0600.dsn
Have your application use a DSN named appdb.dsn, and after creating the
snapshot database, do an xp_cmdshell to copy the relevant file over the
top of the appdb.dsn on the application server.
This allows your application to use a consistent DSN Name, but you are
cycling the DSNs with regards to the snapshot.
You can do the same thing if you use system DSNs as these are stored in
the registry.
Check out the following page
http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov04/hey1110.mspx
If you use a connection string hardcoded into the app, I guess you
could use the system views to determine the latest snapshot, or
populate a 'latest snapshot' table and build your connection string
dynamically based on that.
Hope this helps someone out there.
sqldood@.googlemail.com wrote:
> We are looking at mirroring some of our databases to a remote location
> and snapshotting those databases in order to report off them.
> One of Microsofts recommendations is to add a time suffix to the
> snapshotname in order to identify the age of the snapshot.
> Any reporting system is going to use a DSN to connect to the snapshot,
> we intend to snapshot frequently in order to keep the data as fresh as
> possible. Does this not mean that the DSN is going to need to change to
> point to the latest snapshot database?
> The only alternative I can think of is to sp_renamedb the existing
> snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
> the original name.
> This is a superb feature and a great selling point for 2005.
> Has anyone implemented this ? and if so how did you overcome this
> problem.
> Kind Regards & Thanks.

Database Snapshots & Reporting

We are looking at mirroring some of our databases to a remote location
and snapshotting those databases in order to report off them.
One of Microsofts recommendations is to add a time suffix to the
snapshotname in order to identify the age of the snapshot.
Any reporting system is going to use a DSN to connect to the snapshot,
we intend to snapshot frequently in order to keep the data as fresh as
possible. Does this not mean that the DSN is going to need to change to
point to the latest snapshot database?
The only alternative I can think of is to sp_renamedb the existing
snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
the original name.
This is a superb feature and a great selling point for 2005.
Has anyone implemented this ? and if so how did you overcome this
problem.
Kind Regards & Thanks.An update for anyone else who has this problem, we have found a
possible solution.
If your application uses DSNs
You can create a number of of file DSNs relevant to the snapshot name
e.g.
appdb_1200.dsn
appdb_1800.dsn
appdb_0000.dsn
appdb_0600.dsn
Have your application use a DSN named appdb.dsn, and after creating the
snapshot database, do an xp_cmdshell to copy the relevant file over the
top of the appdb.dsn on the application server.
This allows your application to use a consistent DSN Name, but you are
cycling the DSNs with regards to the snapshot.
You can do the same thing if you use system DSNs as these are stored in
the registry.
Check out the following page
http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov04/hey1110.mspx
If you use a connection string hardcoded into the app, I guess you
could use the system views to determine the latest snapshot, or
populate a 'latest snapshot' table and build your connection string
dynamically based on that.
Hope this helps someone out there.
sqldood@.googlemail.com wrote:
> We are looking at mirroring some of our databases to a remote location
> and snapshotting those databases in order to report off them.
> One of Microsofts recommendations is to add a time suffix to the
> snapshotname in order to identify the age of the snapshot.
> Any reporting system is going to use a DSN to connect to the snapshot,
> we intend to snapshot frequently in order to keep the data as fresh as
> possible. Does this not mean that the DSN is going to need to change to
> point to the latest snapshot database?
> The only alternative I can think of is to sp_renamedb the existing
> snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
> the original name.
> This is a superb feature and a great selling point for 2005.
> Has anyone implemented this ? and if so how did you overcome this
> problem.
> Kind Regards & Thanks.

Database Snapshots & Reporting

We are looking at mirroring some of our databases to a remote location
and snapshotting those databases in order to report off them.
One of Microsofts recommendations is to add a time suffix to the
snapshotname in order to identify the age of the snapshot.
Any reporting system is going to use a DSN to connect to the snapshot,
we intend to snapshot frequently in order to keep the data as fresh as
possible. Does this not mean that the DSN is going to need to change to
point to the latest snapshot database?
The only alternative I can think of is to sp_renamedb the existing
snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
the original name.
This is a superb feature and a great selling point for 2005.
Has anyone implemented this ? and if so how did you overcome this
problem.
Kind Regards & Thanks.An update for anyone else who has this problem, we have found a
possible solution.
If your application uses DSNs
You can create a number of of file DSNs relevant to the snapshot name
e.g.
appdb_1200.dsn
appdb_1800.dsn
appdb_0000.dsn
appdb_0600.dsn
Have your application use a DSN named appdb.dsn, and after creating the
snapshot database, do an xp_cmdshell to copy the relevant file over the
top of the appdb.dsn on the application server.
This allows your application to use a consistent DSN Name, but you are
cycling the DSNs with regards to the snapshot.
You can do the same thing if you use system DSNs as these are stored in
the registry.
Check out the following page
[url]http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov04/hey1110.mspx[/
url]
If you use a connection string hardcoded into the app, I guess you
could use the system views to determine the latest snapshot, or
populate a 'latest snapshot' table and build your connection string
dynamically based on that.
Hope this helps someone out there.
sqldood@.googlemail.com wrote:
> We are looking at mirroring some of our databases to a remote location
> and snapshotting those databases in order to report off them.
> One of Microsofts recommendations is to add a time suffix to the
> snapshotname in order to identify the age of the snapshot.
> Any reporting system is going to use a DSN to connect to the snapshot,
> we intend to snapshot frequently in order to keep the data as fresh as
> possible. Does this not mean that the DSN is going to need to change to
> point to the latest snapshot database?
> The only alternative I can think of is to sp_renamedb the existing
> snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
> the original name.
> This is a superb feature and a great selling point for 2005.
> Has anyone implemented this ? and if so how did you overcome this
> problem.
> Kind Regards & Thanks.sql

Database ''ReportServer$SQLExpressTempDB'' cannot be opened

When I try the open the report file, I got the following error message. Database 'ReportServer$SQLExpressTempDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Anyony have ideas to fix this error?Yes. Get sufficient access or more memory.

DataBase Problem

Hello
I have a problem with a Report Server installation.
The report server database is on another computer than the report server
frontend.
On the frontend Server SqlServer Service Pack2 was installed last night.
Now I can´t connect to the ReportServer Database.
How can I fix this problem, without creating a new Database?
I have tried to refresh the database but I get an error
thanx for any help
regards
BjörnCan you post the error you are getting? Also, check the Reporting Services
error logs located in C:\Program Files\Microsoft SQL
Server\MSSQL.#\Reporting Services\LogFiles and see if there is any more
detailed information in there.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Berger" <prodevteam@.gmx.net>
> Subject: DataBase Problem
> Date: Thu, 4 Oct 2007 13:31:32 +0200
> Hello
> I have a problem with a Report Server installation.
> The report server database is on another computer than the report server
> frontend.
> On the frontend Server SqlServer Service Pack2 was installed last night.
> Now I can´t connect to the ReportServer Database.
> How can I fix this problem, without creating a new Database?
> I have tried to refresh the database but I get an error
>
> thanx for any help
> regards
> Björn
>
>|||Hello
I´ve got the following error in windows eventlog:
http://www.eventid.net/display.asp?eventid=107&eventno=7858&source=Report%20Server&phase=1
Can´t paste from Windows because it´s german ;)
In the log directory I´ve found an error that the sqlaccount can´t login at
the database.
I also have read an article that descripes that the wmi provider changed in
SP2.
The database was sp2 but the reporting service frontend wasn´t sp2.
So I believe it´s because SP2 was installed on the reporting service
frontend
regards
Björn
"Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
news:zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl...
> Can you post the error you are getting? Also, check the Reporting Services
> error logs located in C:\Program Files\Microsoft SQL
> Server\MSSQL.#\Reporting Services\LogFiles and see if there is any more
> detailed information in there.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
>> From: "Berger" <prodevteam@.gmx.net>
>> Subject: DataBase Problem
>> Date: Thu, 4 Oct 2007 13:31:32 +0200
>> Hello
>> I have a problem with a Report Server installation.
>> The report server database is on another computer than the report server
>> frontend.
>> On the frontend Server SqlServer Service Pack2 was installed last night.
>> Now I can´t connect to the ReportServer Database.
>> How can I fix this problem, without creating a new Database?
>> I have tried to refresh the database but I get an error
>>
>> thanx for any help
>> regards
>> Björn
>>
>|||If the front end IIS Server does not have SP2 applied then it will not
connect to a SP2 backend ReportServer database. They both need to be at the
same SP level.
To check this do the following:
1. open up the "Reporting Services Configuration Tool" and go to the
"Database Setup" section.
2. Click "Connect" and connect to the backend database server you are using.
3. Make sure the credentials are specified correctly for the database
connection.
4. Click the "Apply" Button.
If you get an error saying "Expected version C.0.8.43 but found C.0.8.54'
or something similar that means that your versions are out of sync and you
need to upgrade one or the other.
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Berger" <prodevteam@.gmx.net>
> References: <O8KOeonBIHA.324@.TK2MSFTNGP04.phx.gbl>
<zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl>
> Subject: Re: DataBase Problem
> Date: Thu, 4 Oct 2007 16:58:34 +0200
> Hello
> I´ve got the following error in windows eventlog:
>
http://www.eventid.net/display.asp?eventid=107&eventno=7858&source=Report%20
Server&phase=1
> Can´t paste from Windows because it´s german ;)
> In the log directory I´ve found an error that the sqlaccount can´t login
at
> the database.
> I also have read an article that descripes that the wmi provider changed
in
> SP2.
> The database was sp2 but the reporting service frontend wasn´t sp2.
> So I believe it´s because SP2 was installed on the reporting service
> frontend
> regards
> Björn
> "Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
> news:zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl...
> > Can you post the error you are getting? Also, check the Reporting
Services
> > error logs located in C:\Program Files\Microsoft SQL
> > Server\MSSQL.#\Reporting Services\LogFiles and see if there is any more
> > detailed information in there.
> >
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > --
> >> From: "Berger" <prodevteam@.gmx.net>
> >> Subject: DataBase Problem
> >> Date: Thu, 4 Oct 2007 13:31:32 +0200
> >>
> >> Hello
> >>
> >> I have a problem with a Report Server installation.
> >> The report server database is on another computer than the report
server
> >> frontend.
> >> On the frontend Server SqlServer Service Pack2 was installed last
night.
> >> Now I can´t connect to the ReportServer Database.
> >> How can I fix this problem, without creating a new Database?
> >> I have tried to refresh the database but I get an error
> >>
> >>
> >> thanx for any help
> >>
> >> regards
> >>
> >> Björn
> >>
> >>
> >>
> >
>
>|||Hey Chris
Thank´s for your answer.
If I do this I´ve got the question if I wish to update the databse.
I say Yes and got an error in the SqlScript.
The error is from type System.Data.SqlClient.SqlException.
The error text: in sysdatabases no entry was found for "my Reporting
Database".
The Version of my reporting database is C.0.8.40
thanks
Björn
"Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
news:oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl...
> If the front end IIS Server does not have SP2 applied then it will not
> connect to a SP2 backend ReportServer database. They both need to be at
> the
> same SP level.
> To check this do the following:
> 1. open up the "Reporting Services Configuration Tool" and go to the
> "Database Setup" section.
> 2. Click "Connect" and connect to the backend database server you are
> using.
> 3. Make sure the credentials are specified correctly for the database
> connection.
> 4. Click the "Apply" Button.
> If you get an error saying "Expected version C.0.8.43 but found C.0.8.54'
> or something similar that means that your versions are out of sync and you
> need to upgrade one or the other.
>
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
>> From: "Berger" <prodevteam@.gmx.net>
>> References: <O8KOeonBIHA.324@.TK2MSFTNGP04.phx.gbl>
> <zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl>
>> Subject: Re: DataBase Problem
>> Date: Thu, 4 Oct 2007 16:58:34 +0200
>> Hello
>> I´ve got the following error in windows eventlog:
> http://www.eventid.net/display.asp?eventid=107&eventno=7858&source=Report%20
> Server&phase=1
>> Can´t paste from Windows because it´s german ;)
>> In the log directory I´ve found an error that the sqlaccount can´t login
> at
>> the database.
>> I also have read an article that descripes that the wmi provider changed
> in
>> SP2.
>> The database was sp2 but the reporting service frontend wasn´t sp2.
>> So I believe it´s because SP2 was installed on the reporting service
>> frontend
>> regards
>> Björn
>> "Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
>> news:zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl...
>> > Can you post the error you are getting? Also, check the Reporting
> Services
>> > error logs located in C:\Program Files\Microsoft SQL
>> > Server\MSSQL.#\Reporting Services\LogFiles and see if there is any more
>> > detailed information in there.
>> >
>> > --
>> > Chris Alton, Microsoft Corp.
>> > SQL Server Developer Support Engineer
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> > --
>> >> From: "Berger" <prodevteam@.gmx.net>
>> >> Subject: DataBase Problem
>> >> Date: Thu, 4 Oct 2007 13:31:32 +0200
>> >>
>> >> Hello
>> >>
>> >> I have a problem with a Report Server installation.
>> >> The report server database is on another computer than the report
> server
>> >> frontend.
>> >> On the frontend Server SqlServer Service Pack2 was installed last
> night.
>> >> Now I can´t connect to the ReportServer Database.
>> >> How can I fix this problem, without creating a new Database?
>> >> I have tried to refresh the database but I get an error
>> >>
>> >>
>> >> thanx for any help
>> >>
>> >> regards
>> >>
>> >> Björn
>> >>
>> >>
>> >>
>> >
>>
>|||Is "My Reporting Database" really your database name? If so you've run into
a bug with the create/upgrade script.
If your database name has spaces in it the script doesn't put [] around the
database name for some reason so it blows off.
Follow these steps to generate a script:
1. Open the Reporting Services Configuration Tool
2. Click the "Database Setup" link on the left side.
3. Click the "Script" button.
4. In the window that opens select either "New Database Script" or "Upgrade
Database Script" on the left hand side.
5. If you are creating a new database follow these steps:
a. Enter the name of the database you would like to create in the box.
(The default is ReportServer)
b. Select the language in the second drop down box.
c. Specify the location where you want to save the created script. (this
defaults to the current users "My Documents" folder)
d. Click "OK".
6. If you are creating an "Upgrade" database script follow these steps:
a. Type in the name of the Reporting Services database that you want to
upgrade in the first box.
b. Select the version of the database you are upgrading from in the
second drop down box.
c. Select the save file location in the 3rd drop down box. (this
defaults to the current users "My Documents" folder)
d. Click "OK".
7. Go to the "My Documents" folder.
8. Open the created script file in the file editor of your choice.
9. You should see a line in the script that looks similar to this: USE
ReportServer[ReportServer] (This can vary depending on the name of your
ReportServer database)
10. Do a find and replace on the following:
Find: USE ReportServer[
Replace USE [
11. Replace all instances of this in the script.
12. Save the file.
13. Execute this script in a Query Window on the database where you want to
perform the operation.
If that is not your issue then make sure you hit "Connect" first and then
select the existing database in the drop down. If it is in the drop down
then the databases exists on the server.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Berger" <prodevteam@.gmx.net>
> References: <O8KOeonBIHA.324@.TK2MSFTNGP04.phx.gbl>
<zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl>
<uPLCLcpBIHA.5980@.TK2MSFTNGP04.phx.gbl>
<oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl>
> Subject: Re: DataBase Problem
> Date: Fri, 5 Oct 2007 11:52:27 +0200
> Hey Chris
> Thank´s for your answer.
> If I do this I´ve got the question if I wish to update the databse.
> I say Yes and got an error in the SqlScript.
> The error is from type System.Data.SqlClient.SqlException.
> The error text: in sysdatabases no entry was found for "my Reporting
> Database".
> The Version of my reporting database is C.0.8.40
> thanks
> Björn
>
> "Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
> news:oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl...
> > If the front end IIS Server does not have SP2 applied then it will not
> > connect to a SP2 backend ReportServer database. They both need to be at
> > the
> > same SP level.
> >
> > To check this do the following:
> > 1. open up the "Reporting Services Configuration Tool" and go to the
> > "Database Setup" section.
> > 2. Click "Connect" and connect to the backend database server you are
> > using.
> > 3. Make sure the credentials are specified correctly for the database
> > connection.
> > 4. Click the "Apply" Button.
> > If you get an error saying "Expected version C.0.8.43 but found
C.0.8.54'
> > or something similar that means that your versions are out of sync and
you
> > need to upgrade one or the other.
> >
> >
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.|||Hey Chris
The script doesn´t work :(
Now I have tried to create a new report database.
I think I have a problem with the wmi provider.
In R.S. Configuration Tool I´ve got an error when I want to change to the
menu "Initialization"
the error text is something like: Error while changing scope. The reason can
be an error while calling WMI properties. Details: Reportserver can´t
connect to database
In windows eventviewer I´ve got the error 107 again.
Any ideas?
regards
Björn
"Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
news:NLAHUa1BIHA.240@.TK2MSFTNGHUB02.phx.gbl...
> Is "My Reporting Database" really your database name? If so you've run
> into
> a bug with the create/upgrade script.
> If your database name has spaces in it the script doesn't put [] around
> the
> database name for some reason so it blows off.
> Follow these steps to generate a script:
> 1. Open the Reporting Services Configuration Tool
> 2. Click the "Database Setup" link on the left side.
> 3. Click the "Script" button.
> 4. In the window that opens select either "New Database Script" or
> "Upgrade
> Database Script" on the left hand side.
> 5. If you are creating a new database follow these steps:
> a. Enter the name of the database you would like to create in the box.
> (The default is ReportServer)
> b. Select the language in the second drop down box.
> c. Specify the location where you want to save the created script. (this
> defaults to the current users "My Documents" folder)
> d. Click "OK".
> 6. If you are creating an "Upgrade" database script follow these steps:
> a. Type in the name of the Reporting Services database that you want to
> upgrade in the first box.
> b. Select the version of the database you are upgrading from in the
> second drop down box.
> c. Select the save file location in the 3rd drop down box. (this
> defaults to the current users "My Documents" folder)
> d. Click "OK".
> 7. Go to the "My Documents" folder.
> 8. Open the created script file in the file editor of your choice.
> 9. You should see a line in the script that looks similar to this: USE
> ReportServer[ReportServer] (This can vary depending on the name of your
> ReportServer database)
> 10. Do a find and replace on the following:
> Find: USE ReportServer[
> Replace USE [
> 11. Replace all instances of this in the script.
> 12. Save the file.
> 13. Execute this script in a Query Window on the database where you want
> to
> perform the operation.
> If that is not your issue then make sure you hit "Connect" first and then
> select the existing database in the drop down. If it is in the drop down
> then the databases exists on the server.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
>> From: "Berger" <prodevteam@.gmx.net>
>> References: <O8KOeonBIHA.324@.TK2MSFTNGP04.phx.gbl>
> <zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl>
> <uPLCLcpBIHA.5980@.TK2MSFTNGP04.phx.gbl>
> <oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl>
>> Subject: Re: DataBase Problem
>> Date: Fri, 5 Oct 2007 11:52:27 +0200
>> Hey Chris
>> Thank´s for your answer.
>> If I do this I´ve got the question if I wish to update the databse.
>> I say Yes and got an error in the SqlScript.
>> The error is from type System.Data.SqlClient.SqlException.
>> The error text: in sysdatabases no entry was found for "my Reporting
>> Database".
>> The Version of my reporting database is C.0.8.40
>> thanks
>> Björn
>>
>> "Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
>> news:oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl...
>> > If the front end IIS Server does not have SP2 applied then it will not
>> > connect to a SP2 backend ReportServer database. They both need to be at
>> > the
>> > same SP level.
>> >
>> > To check this do the following:
>> > 1. open up the "Reporting Services Configuration Tool" and go to the
>> > "Database Setup" section.
>> > 2. Click "Connect" and connect to the backend database server you are
>> > using.
>> > 3. Make sure the credentials are specified correctly for the database
>> > connection.
>> > 4. Click the "Apply" Button.
>> > If you get an error saying "Expected version C.0.8.43 but found
> C.0.8.54'
>> > or something similar that means that your versions are out of sync and
> you
>> > need to upgrade one or the other.
>> >
>> >
>> > --
>> > Chris Alton, Microsoft Corp.
>> > SQL Server Developer Support Engineer
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>|||You'll need to repair your database connection before you can finish the
initialization piece.
Get the script to work and then you should be able to get your database
connection functional. To finish the installation for SRS you shouldn't
need to go to the Initialization portion of the config tool.
Also, if the script failed what was the error when it failed?
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Berger" <prodevteam@.gmx.net>
> Hey Chris
> The script doesn´t work :(
> Now I have tried to create a new report database.
> I think I have a problem with the wmi provider.
> In R.S. Configuration Tool I´ve got an error when I want to change to the
> menu "Initialization"
> the error text is something like: Error while changing scope. The reason
can
> be an error while calling WMI properties. Details: Reportserver can´t
> connect to database
> In windows eventviewer I´ve got the error 107 again.
> Any ideas?
> regards
> Björn
> "Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im Newsbeitrag
> news:NLAHUa1BIHA.240@.TK2MSFTNGHUB02.phx.gbl...
> > Is "My Reporting Database" really your database name? If so you've run
> > into
> > a bug with the create/upgrade script.
> >
> > If your database name has spaces in it the script doesn't put [] around
> > the
> > database name for some reason so it blows off.
> >
> > Follow these steps to generate a script:
> > 1. Open the Reporting Services Configuration Tool
> > 2. Click the "Database Setup" link on the left side.
> > 3. Click the "Script" button.
> > 4. In the window that opens select either "New Database Script" or
> > "Upgrade
> > Database Script" on the left hand side.
> > 5. If you are creating a new database follow these steps:
> > a. Enter the name of the database you would like to create in the box.
> > (The default is ReportServer)
> > b. Select the language in the second drop down box.
> > c. Specify the location where you want to save the created script.
(this
> > defaults to the current users "My Documents" folder)
> > d. Click "OK".
> > 6. If you are creating an "Upgrade" database script follow these steps:
> > a. Type in the name of the Reporting Services database that you want
to
> > upgrade in the first box.
> > b. Select the version of the database you are upgrading from in the
> > second drop down box.
> > c. Select the save file location in the 3rd drop down box. (this
> > defaults to the current users "My Documents" folder)
> > d. Click "OK".
> > 7. Go to the "My Documents" folder.
> > 8. Open the created script file in the file editor of your choice.
> > 9. You should see a line in the script that looks similar to this: USE
> > ReportServer[ReportServer] (This can vary depending on the name of your
> > ReportServer database)
> > 10. Do a find and replace on the following:
> > Find: USE ReportServer[
> > Replace USE [
> > 11. Replace all instances of this in the script.
> > 12. Save the file.
> > 13. Execute this script in a Query Window on the database where you
want
> > to
> > perform the operation.
> >
> > If that is not your issue then make sure you hit "Connect" first and
then
> > select the existing database in the drop down. If it is in the drop down
> > then the databases exists on the server.
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > --
> >> From: "Berger" <prodevteam@.gmx.net>
> >> References: <O8KOeonBIHA.324@.TK2MSFTNGP04.phx.gbl>
> > <zjJvLkoBIHA.4648@.TK2MSFTNGHUB02.phx.gbl>
> > <uPLCLcpBIHA.5980@.TK2MSFTNGP04.phx.gbl>
> > <oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl>
> >> Subject: Re: DataBase Problem
> >> Date: Fri, 5 Oct 2007 11:52:27 +0200
> >>
> >> Hey Chris
> >>
> >> Thank´s for your answer.
> >>
> >> If I do this I´ve got the question if I wish to update the databse.
> >> I say Yes and got an error in the SqlScript.
> >> The error is from type System.Data.SqlClient.SqlException.
> >> The error text: in sysdatabases no entry was found for "my Reporting
> >> Database".
> >> The Version of my reporting database is C.0.8.40
> >>
> >> thanks
> >>
> >> Björn
> >>
> >>
> >> "Chris Alton [MSFT]" <calton@.online.microsoft.com> schrieb im
Newsbeitrag
> >> news:oVm8MbqBIHA.240@.TK2MSFTNGHUB02.phx.gbl...
> >> > If the front end IIS Server does not have SP2 applied then it will
not
> >> > connect to a SP2 backend ReportServer database. They both need to be
at
> >> > the
> >> > same SP level.
> >> >
> >> > To check this do the following:
> >> > 1. open up the "Reporting Services Configuration Tool" and go to the
> >> > "Database Setup" section.
> >> > 2. Click "Connect" and connect to the backend database server you are
> >> > using.
> >> > 3. Make sure the credentials are specified correctly for the database
> >> > connection.
> >> > 4. Click the "Apply" Button.
> >> > If you get an error saying "Expected version C.0.8.43 but found
> > C.0.8.54'
> >> > or something similar that means that your versions are out of sync
and
> > you
> >> > need to upgrade one or the other.
> >> >
> >> >
> >> > --
> >> > Chris Alton, Microsoft Corp.
> >> > SQL Server Developer Support Engineer
> >> > This posting is provided "AS IS" with no warranties, and confers no
> >> > rights.
> >
>
>

Database options in TPC-C test

I read full disclosure report for best TPC-C SQL Server results, and have
some questions. What is trace flag 828, where to find decription of the flag?
How useful it for OLTP system under heavy load? Why checkpoint were done
manually avery 15 min, not simply configure server to set checkpoint interval
15 min? Is it better in terms of performance? How big impact of "torn page
detection option"? - it was turned off in the test.
I have SQL Server database with 30-40 transactions per second average, and
300 transactions per second in peaks. And the load would increase 4-5 times
in next 2 monthes. So I trying to find what to do to be able to handle such
load.
You should not go by how the server was configured in a TPC test for a
normal production environment. It was tweaked for months to get every last
transaction with a massive amount of hardware. You would not do this in a
production env. The volumes you are talking about are well within the
limits of a normally configured server with default settings on SQL Server.
If you start messing with things liek the recovery interval without
understanding exactly what effect it will have you will most likely hurt
performance instead of increasing it. Never change a configuration setting
in SQL Server just because someone else did. You are better off making sure
you have a proper hardware configuration to handle your load. Especially the
disk configurations. The more trans per second you do the more important it
is to have a proper disk I/O subsystem and to isolate the log files onto
their own Raid 1 or Raid 10.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:F58FF223-B5F1-43C2-BB3A-17CB2A51495F@.microsoft.com...
>I read full disclosure report for best TPC-C SQL Server results, and have
> some questions. What is trace flag 828, where to find decription of the
> flag?
> How useful it for OLTP system under heavy load? Why checkpoint were done
> manually avery 15 min, not simply configure server to set checkpoint
> interval
> 15 min? Is it better in terms of performance? How big impact of "torn page
> detection option"? - it was turned off in the test.
> I have SQL Server database with 30-40 transactions per second average, and
> 300 transactions per second in peaks. And the load would increase 4-5
> times
> in next 2 monthes. So I trying to find what to do to be able to handle
> such
> load.
|||I know what I/O system is most important to handle the load. But additionally
to be able to handle the load, for the database necessary to have transaction
duration in good range - not more than 120 ms. Currenly average transaction
duration is 30 ms. But some transactions go up to 1.4 sec. Looking at
transaction log by Log Explorer, I found it usually happens during
checkpoints. And with increased load system would have checkpoints more
often. Note what during nearby all of long runned transactions had no waits
on locks - I have monitor of locking, it show nothing for most of such long
running transactions.
So I looking which options can help to decrease number of long-running
transactions. Its reason why I looking database options in TPC-C tests.
We upgrading our HW, I/O system. Currently data is on RAID10, log on RAID1.
We buying Dell CX300 storage system, database would be moved to the system.
Write cache would be enabled. What can be expected from such hardware in
terms of decreasing number of long running transactions? And about
checkpoints. Because most of long-runned transactions happens during
checkpoints, may be it is better to do the checkpoints manually, each 15
minutes as in the TPC-C test? How trace flag 828 (for which I not found
decription anywhere in Internet) affect checkpoints behavior? Can "torn page
detection" option cause some of long-running transaction?
|||The reason why checkpoints affect the length of the transaction is due to
the fact the disk subsystem can not handle the large spike presented to it
during the checkpoint. If you are using direct attached storage now it
probably does not have a lot of cache on the controller as compared to a
SAN. While the CX300 is the lower end of the DELL (really EMC ) line of
SAN's it still will probably have more cache than what you have now. The
larger cache will buffer or absorb the spikes of a checkpoint and keep the
response times more even. With direct attached storage you almost always
want the controller cache to be 100% write back and 0% read. Usually by
default they are around 50 / 50. Even with a SAN you still have to ensure
the drives are configured properly to handle the load. Often on a SAN the
administrators make one large RAID and split it up into several logical
partitions or LUNs that they hand you. So you will see the log file, data
and tempdb all on one physical Raid array even though it appears as 3
separate drives. This is only acceptable if the cache is so much that it
never gets saturated and then you are at the mercy of the Raid array and all
the luns tacked on to it. A CX300 has the smallest cache of that line so
make sure it is enough for what you need to do. Again adjusting the
checkpoint interval is not the answer. Sizing the hardware properly to
begin with is. I don't know what trace 828 is and again you should not
count on a trace flag to get the performance you need. With few exceptions
if it is designed right you should not need any trace flags. Torn Page
detection can add some overhead on very large I/O bound systems but you are
better off leaving it on to begin with. The type of volumes you are talking
about should not be affected that much by this being on if you have the
right hardware.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7FD2F25D-FE03-453C-AA09-8DD75B23C1B7@.microsoft.com...
>I know what I/O system is most important to handle the load. But
>additionally
> to be able to handle the load, for the database necessary to have
> transaction
> duration in good range - not more than 120 ms. Currenly average
> transaction
> duration is 30 ms. But some transactions go up to 1.4 sec. Looking at
> transaction log by Log Explorer, I found it usually happens during
> checkpoints. And with increased load system would have checkpoints more
> often. Note what during nearby all of long runned transactions had no
> waits
> on locks - I have monitor of locking, it show nothing for most of such
> long
> running transactions.
> So I looking which options can help to decrease number of long-running
> transactions. Its reason why I looking database options in TPC-C tests.
> We upgrading our HW, I/O system. Currently data is on RAID10, log on
> RAID1.
> We buying Dell CX300 storage system, database would be moved to the
> system.
> Write cache would be enabled. What can be expected from such hardware in
> terms of decreasing number of long running transactions? And about
> checkpoints. Because most of long-runned transactions happens during
> checkpoints, may be it is better to do the checkpoints manually, each 15
> minutes as in the TPC-C test? How trace flag 828 (for which I not found
> decription anywhere in Internet) affect checkpoints behavior? Can "torn
> page
> detection" option cause some of long-running transaction?
>

Database options in TPC-C test

I read full disclosure report for best TPC-C SQL Server results, and have
some questions. What is trace flag 828, where to find decription of the flag
?
How useful it for OLTP system under heavy load? Why checkpoint were done
manually avery 15 min, not simply configure server to set checkpoint interva
l
15 min? Is it better in terms of performance? How big impact of "torn page
detection option"? - it was turned off in the test.
I have SQL Server database with 30-40 transactions per second average, and
300 transactions per second in peaks. And the load would increase 4-5 times
in next 2 monthes. So I trying to find what to do to be able to handle such
load.You should not go by how the server was configured in a TPC test for a
normal production environment. It was tweaked for months to get every last
transaction with a massive amount of hardware. You would not do this in a
production env. The volumes you are talking about are well within the
limits of a normally configured server with default settings on SQL Server.
If you start messing with things liek the recovery interval without
understanding exactly what effect it will have you will most likely hurt
performance instead of increasing it. Never change a configuration setting
in SQL Server just because someone else did. You are better off making sure
you have a proper hardware configuration to handle your load. Especially the
disk configurations. The more trans per second you do the more important it
is to have a proper disk I/O subsystem and to isolate the log files onto
their own Raid 1 or Raid 10.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:F58FF223-B5F1-43C2-BB3A-17CB2A51495F@.microsoft.com...
>I read full disclosure report for best TPC-C SQL Server results, and have
> some questions. What is trace flag 828, where to find decription of the
> flag?
> How useful it for OLTP system under heavy load? Why checkpoint were done
> manually avery 15 min, not simply configure server to set checkpoint
> interval
> 15 min? Is it better in terms of performance? How big impact of "torn page
> detection option"? - it was turned off in the test.
> I have SQL Server database with 30-40 transactions per second average, and
> 300 transactions per second in peaks. And the load would increase 4-5
> times
> in next 2 monthes. So I trying to find what to do to be able to handle
> such
> load.|||I know what I/O system is most important to handle the load. But additionall
y
to be able to handle the load, for the database necessary to have transactio
n
duration in good range - not more than 120 ms. Currenly average transaction
duration is 30 ms. But some transactions go up to 1.4 sec. Looking at
transaction log by Log Explorer, I found it usually happens during
checkpoints. And with increased load system would have checkpoints more
often. Note what during nearby all of long runned transactions had no waits
on locks - I have monitor of locking, it show nothing for most of such long
running transactions.
So I looking which options can help to decrease number of long-running
transactions. Its reason why I looking database options in TPC-C tests.
We upgrading our HW, I/O system. Currently data is on RAID10, log on RAID1.
We buying Dell CX300 storage system, database would be moved to the system.
Write cache would be enabled. What can be expected from such hardware in
terms of decreasing number of long running transactions? And about
checkpoints. Because most of long-runned transactions happens during
checkpoints, may be it is better to do the checkpoints manually, each 15
minutes as in the TPC-C test? How trace flag 828 (for which I not found
decription anywhere in Internet) affect checkpoints behavior? Can "torn page
detection" option cause some of long-running transaction?|||The reason why checkpoints affect the length of the transaction is due to
the fact the disk subsystem can not handle the large spike presented to it
during the checkpoint. If you are using direct attached storage now it
probably does not have a lot of cache on the controller as compared to a
SAN. While the CX300 is the lower end of the DELL (really EMC ) line of
SAN's it still will probably have more cache than what you have now. The
larger cache will buffer or absorb the spikes of a checkpoint and keep the
response times more even. With direct attached storage you almost always
want the controller cache to be 100% write back and 0% read. Usually by
default they are around 50 / 50. Even with a SAN you still have to ensure
the drives are configured properly to handle the load. Often on a SAN the
administrators make one large RAID and split it up into several logical
partitions or LUNs that they hand you. So you will see the log file, data
and tempdb all on one physical Raid array even though it appears as 3
separate drives. This is only acceptable if the cache is so much that it
never gets saturated and then you are at the mercy of the Raid array and all
the luns tacked on to it. A CX300 has the smallest cache of that line so
make sure it is enough for what you need to do. Again adjusting the
checkpoint interval is not the answer. Sizing the hardware properly to
begin with is. I don't know what trace 828 is and again you should not
count on a trace flag to get the performance you need. With few exceptions
if it is designed right you should not need any trace flags. Torn Page
detection can add some overhead on very large I/O bound systems but you are
better off leaving it on to begin with. The type of volumes you are talking
about should not be affected that much by this being on if you have the
right hardware.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7FD2F25D-FE03-453C-AA09-8DD75B23C1B7@.microsoft.com...
>I know what I/O system is most important to handle the load. But
>additionally
> to be able to handle the load, for the database necessary to have
> transaction
> duration in good range - not more than 120 ms. Currenly average
> transaction
> duration is 30 ms. But some transactions go up to 1.4 sec. Looking at
> transaction log by Log Explorer, I found it usually happens during
> checkpoints. And with increased load system would have checkpoints more
> often. Note what during nearby all of long runned transactions had no
> waits
> on locks - I have monitor of locking, it show nothing for most of such
> long
> running transactions.
> So I looking which options can help to decrease number of long-running
> transactions. Its reason why I looking database options in TPC-C tests.
> We upgrading our HW, I/O system. Currently data is on RAID10, log on
> RAID1.
> We buying Dell CX300 storage system, database would be moved to the
> system.
> Write cache would be enabled. What can be expected from such hardware in
> terms of decreasing number of long running transactions? And about
> checkpoints. Because most of long-runned transactions happens during
> checkpoints, may be it is better to do the checkpoints manually, each 15
> minutes as in the TPC-C test? How trace flag 828 (for which I not found
> decription anywhere in Internet) affect checkpoints behavior? Can "torn
> page
> detection" option cause some of long-running transaction?
>

Database options in TPC-C test

I read full disclosure report for best TPC-C SQL Server results, and have
some questions. What is trace flag 828, where to find decription of the flag?
How useful it for OLTP system under heavy load? Why checkpoint were done
manually avery 15 min, not simply configure server to set checkpoint interval
15 min? Is it better in terms of performance? How big impact of "torn page
detection option"? - it was turned off in the test.
I have SQL Server database with 30-40 transactions per second average, and
300 transactions per second in peaks. And the load would increase 4-5 times
in next 2 monthes. So I trying to find what to do to be able to handle such
load.You should not go by how the server was configured in a TPC test for a
normal production environment. It was tweaked for months to get every last
transaction with a massive amount of hardware. You would not do this in a
production env. The volumes you are talking about are well within the
limits of a normally configured server with default settings on SQL Server.
If you start messing with things liek the recovery interval without
understanding exactly what effect it will have you will most likely hurt
performance instead of increasing it. Never change a configuration setting
in SQL Server just because someone else did. You are better off making sure
you have a proper hardware configuration to handle your load. Especially the
disk configurations. The more trans per second you do the more important it
is to have a proper disk I/O subsystem and to isolate the log files onto
their own Raid 1 or Raid 10.
--
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:F58FF223-B5F1-43C2-BB3A-17CB2A51495F@.microsoft.com...
>I read full disclosure report for best TPC-C SQL Server results, and have
> some questions. What is trace flag 828, where to find decription of the
> flag?
> How useful it for OLTP system under heavy load? Why checkpoint were done
> manually avery 15 min, not simply configure server to set checkpoint
> interval
> 15 min? Is it better in terms of performance? How big impact of "torn page
> detection option"? - it was turned off in the test.
> I have SQL Server database with 30-40 transactions per second average, and
> 300 transactions per second in peaks. And the load would increase 4-5
> times
> in next 2 monthes. So I trying to find what to do to be able to handle
> such
> load.|||I know what I/O system is most important to handle the load. But additionally
to be able to handle the load, for the database necessary to have transaction
duration in good range - not more than 120 ms. Currenly average transaction
duration is 30 ms. But some transactions go up to 1.4 sec. Looking at
transaction log by Log Explorer, I found it usually happens during
checkpoints. And with increased load system would have checkpoints more
often. Note what during nearby all of long runned transactions had no waits
on locks - I have monitor of locking, it show nothing for most of such long
running transactions.
So I looking which options can help to decrease number of long-running
transactions. Its reason why I looking database options in TPC-C tests.
We upgrading our HW, I/O system. Currently data is on RAID10, log on RAID1.
We buying Dell CX300 storage system, database would be moved to the system.
Write cache would be enabled. What can be expected from such hardware in
terms of decreasing number of long running transactions? And about
checkpoints. Because most of long-runned transactions happens during
checkpoints, may be it is better to do the checkpoints manually, each 15
minutes as in the TPC-C test? How trace flag 828 (for which I not found
decription anywhere in Internet) affect checkpoints behavior? Can "torn page
detection" option cause some of long-running transaction?|||The reason why checkpoints affect the length of the transaction is due to
the fact the disk subsystem can not handle the large spike presented to it
during the checkpoint. If you are using direct attached storage now it
probably does not have a lot of cache on the controller as compared to a
SAN. While the CX300 is the lower end of the DELL (really EMC ) line of
SAN's it still will probably have more cache than what you have now. The
larger cache will buffer or absorb the spikes of a checkpoint and keep the
response times more even. With direct attached storage you almost always
want the controller cache to be 100% write back and 0% read. Usually by
default they are around 50 / 50. Even with a SAN you still have to ensure
the drives are configured properly to handle the load. Often on a SAN the
administrators make one large RAID and split it up into several logical
partitions or LUNs that they hand you. So you will see the log file, data
and tempdb all on one physical Raid array even though it appears as 3
separate drives. This is only acceptable if the cache is so much that it
never gets saturated and then you are at the mercy of the Raid array and all
the luns tacked on to it. A CX300 has the smallest cache of that line so
make sure it is enough for what you need to do. Again adjusting the
checkpoint interval is not the answer. Sizing the hardware properly to
begin with is. I don't know what trace 828 is and again you should not
count on a trace flag to get the performance you need. With few exceptions
if it is designed right you should not need any trace flags. Torn Page
detection can add some overhead on very large I/O bound systems but you are
better off leaving it on to begin with. The type of volumes you are talking
about should not be affected that much by this being on if you have the
right hardware.
--
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:7FD2F25D-FE03-453C-AA09-8DD75B23C1B7@.microsoft.com...
>I know what I/O system is most important to handle the load. But
>additionally
> to be able to handle the load, for the database necessary to have
> transaction
> duration in good range - not more than 120 ms. Currenly average
> transaction
> duration is 30 ms. But some transactions go up to 1.4 sec. Looking at
> transaction log by Log Explorer, I found it usually happens during
> checkpoints. And with increased load system would have checkpoints more
> often. Note what during nearby all of long runned transactions had no
> waits
> on locks - I have monitor of locking, it show nothing for most of such
> long
> running transactions.
> So I looking which options can help to decrease number of long-running
> transactions. Its reason why I looking database options in TPC-C tests.
> We upgrading our HW, I/O system. Currently data is on RAID10, log on
> RAID1.
> We buying Dell CX300 storage system, database would be moved to the
> system.
> Write cache would be enabled. What can be expected from such hardware in
> terms of decreasing number of long running transactions? And about
> checkpoints. Because most of long-runned transactions happens during
> checkpoints, may be it is better to do the checkpoints manually, each 15
> minutes as in the TPC-C test? How trace flag 828 (for which I not found
> decription anywhere in Internet) affect checkpoints behavior? Can "torn
> page
> detection" option cause some of long-running transaction?
>