Performance tuning and measure on MSSQL 2000

Hi

I am trying to design an IO subsystem for my SQL Server and for that I
need to try and predict IO activity on each table in my MSSQL
Database. My idea is to move the hottest tables into special disk
subsystem away from the less hotter tables. So far I have gathered
that we have three tables more hot than the others but I have no
feeling on ratio on how hot each is and how much activity is on the
less hotter tables. I need to predict how many disks I should assign
to each subsystem and so far...
I haven't found a reasonable way to do this.

The only way I found to see read/writes and physical read/writes is on
filelevel. but I've also managed to do a trace in sqlprofiler to get
the logical read and writes per query but since my queries are often
joins I have no way of spliting that IO between the tables included in
the join and no idea on which hit the buffer pool and which didn'nt.
Is there maybe a counter or some way that I have not found?

Any input would be greatly appriciated.

best regards & thanks
Arni Snorriarnie@.gormur.com (Arni Snorri Eggertsson) wrote in message news:<c8d15bfa.0404280125.6f1dadcf@.posting.google.com>...
> Hi
> I am trying to design an IO subsystem for my SQL Server and for that I
> need to try and predict IO activity on each table in my MSSQL
> Database. My idea is to move the hottest tables into special disk
> subsystem away from the less hotter tables. So far I have gathered
> that we have three tables more hot than the others but I have no
> feeling on ratio on how hot each is and how much activity is on the
> less hotter tables. I need to predict how many disks I should assign
> to each subsystem and so far...
> I haven't found a reasonable way to do this.
> The only way I found to see read/writes and physical read/writes is on
> filelevel. but I've also managed to do a trace in sqlprofiler to get
> the logical read and writes per query but since my queries are often
> joins I have no way of spliting that IO between the tables included in
> the join and no idea on which hit the buffer pool and which didn'nt.
> Is there maybe a counter or some way that I have not found?
> Any input would be greatly appriciated.
> best regards & thanks
> Arni Snorri

I'm not sure if it's possible to do exactly what you want - MSSQL will
probably cache a lot of the data from the 'hot' tables anyway, so the
issue is not so much the physical disk access as how much RAM you
have, and how well MSSQL uses the cache. There are a lot of
performance monitor counters for buffer and cache management you can
use to look at this.

As for the disks, I would start by identifying how much space is
required on disk, then try to use lots of smaller disks instead of
fewer bigger ones for the 'hot' filegroups. Placing the transaction
logs on separate disks would also help, of course.

Simon|||"Arni Snorri Eggertsson" <arnie@.gormur.com> wrote in message
news:c8d15bfa.0404280125.6f1dadcf@.posting.google.c om...
> Hi
> I am trying to design an IO subsystem for my SQL Server and for that I
> need to try and predict IO activity on each table in my MSSQL
> Database. My idea is to move the hottest tables into special disk
> subsystem away from the less hotter tables. So far I have gathered
> that we have three tables more hot than the others but I have no
> feeling on ratio on how hot each is and how much activity is on the
> less hotter tables. I need to predict how many disks I should assign
> to each subsystem and so far...
> I haven't found a reasonable way to do this.

If you don't have it, get the Microsoft Press book on SQL Server Performance
tuning. Lots of good help here.

> The only way I found to see read/writes and physical read/writes is on
> filelevel. but I've also managed to do a trace in sqlprofiler to get
> the logical read and writes per query but since my queries are often
> joins I have no way of spliting that IO between the tables included in
> the join and no idea on which hit the buffer pool and which didn'nt.
> Is there maybe a counter or some way that I have not found?
> Any input would be greatly appriciated.
> best regards & thanks
> Arni Snorri

Performance problems ADODB connection

Hi,
My programm (24/7 - VB6) is connected to a MSSQL Database with only one
table where data is stored with a time_stamp field as index. Automatically
the software updates each 2 minutes the table and with the same frequency
querys and display data for the last 3 days.
I have also one function where the user can select the amount of the
displayed data. When the software is working for a few ws in automatic
mode (allways 3 day - data displaying) and the a user start a request to
display all the datas in the table, at the first time the query is failing
with timeout and at the second request is returning the requested data. I
use the ADODB Connection object with client side cursor which is used in a
ADODB.Recordset Open method to return the data.
Has anybody a ideea why is the first time query failing and /or how to
prevent this ?
Thank you !Hi
It appears to be contention problem.Try using lower level locks and if
needed you may use NOLOCK HINT with the select query in backend if possible
.
Regards
R.D
"Marius Cecon" wrote:

> Hi,
> My programm (24/7 - VB6) is connected to a MSSQL Database with only one
> table where data is stored with a time_stamp field as index. Automatically
> the software updates each 2 minutes the table and with the same frequency
> querys and display data for the last 3 days.
> I have also one function where the user can select the amount of the
> displayed data. When the software is working for a few ws in automatic
> mode (allways 3 day - data displaying) and the a user start a request to
> display all the datas in the table, at the first time the query is failing
> with timeout and at the second request is returning the requested data. I
> use the ADODB Connection object with client side cursor which is used in a
> ADODB.Recordset Open method to return the data.
> Has anybody a ideea why is the first time query failing and /or how to
> prevent this ?
> Thank you !
>
>|||Hi RD,
Thank you for trying to help me.
I've changed my select query adding the NOLOCK hint but is no change in the
behaviour.
They are no multiuser access to the table, my programm is the only one
connected to the table over a Connection object created at programm start
and closed when programm ends.
Marius.
"R.D" <RD@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AF677CC9-1EB4-4EA9-A588-BC7FA12E4E09@.microsoft.com...
> Hi
> It appears to be contention problem.Try using lower level locks and if
> needed you may use NOLOCK HINT with the select query in backend if
possible.
> Regards
> R.D
> "Marius Cecon" wrote:
>
Automatically
frequency
automatic
failing
I
a|||On Wed, 31 Aug 2005 09:33:20 +0200, "Marius Cecon" <m.cecon@.hydrovision.de>
wrote:
in <ugELI5frFHA.2076@.TK2MSFTNGP14.phx.gbl>

>Hi,
>My programm (24/7 - VB6) is connected to a MSSQL Database with only one
>table where data is stored with a time_stamp field as index. Automatically
>the software updates each 2 minutes the table and with the same frequency
>querys and display data for the last 3 days.
>I have also one function where the user can select the amount of the
>displayed data. When the software is working for a few ws in automatic
>mode (allways 3 day - data displaying) and the a user start a request to
>display all the datas in the table, at the first time the query is failing
>with timeout and at the second request is returning the requested data. I
>use the ADODB Connection object with client side cursor which is used in a
>ADODB.Recordset Open method to return the data.
>Has anybody a ideea why is the first time query failing and /or how to
>prevent this ?
>Thank you !
Try setting the connection object's CommandTimeout property to zero (infinit
e)
just prior to the recordset Open method.
Stefan Berglund

Performance monitoring

Hi
We are running a Windows 2003 Std. and a MSSQL std. on a 2xPIII having 2Gb
RAM and SCSI disks in RAID1.
The server handles 80 Databases and has some 80-100 connections from
webapplications.
I guess most of you are used to 4xXeon and 128Gb Ram ;-) anyway - the
performance is at the moment very good, but I am of cause a bit concernt
about the future. Does et actually perfom OK as we think it does? Will it
keep performing if we add 10 databases for it to handle? And so on. When
will "it break" performance wise.
I was looking at windows "performance monitor" adding and deleting all sorts
of performance counters - but which one should I look at and what is "in
range"? Any general ideas?
And of cause - any handson books/tutorials that describes this?
Best regards
JanHere are some links to have a look at:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
<msdn@.csite.com> wrote in message
news:OrxgP391FHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi
> We are running a Windows 2003 Std. and a MSSQL std. on a 2xPIII having 2Gb
> RAM and SCSI disks in RAID1.
> The server handles 80 Databases and has some 80-100 connections from
> webapplications.
> I guess most of you are used to 4xXeon and 128Gb Ram ;-) anyway - the
> performance is at the moment very good, but I am of cause a bit concernt
> about the future. Does et actually perfom OK as we think it does? Will it
> keep performing if we add 10 databases for it to handle? And so on. When
> will "it break" performance wise.
> I was looking at windows "performance monitor" adding and deleting all
> sorts of performance counters - but which one should I look at and what is
> "in range"? Any general ideas?
> And of cause - any handson books/tutorials that describes this?
> Best regards
> Jan
>

Performance monitoring

Hi
We are running a Windows 2003 Std. and a MSSQL std. on a 2xPIII having 2Gb
RAM and SCSI disks in RAID1.
The server handles 80 Databases and has some 80-100 connections from
webapplications.
I guess most of you are used to 4xXeon and 128Gb Ram ;-) anyway - the
performance is at the moment very good, but I am of cause a bit concernt
about the future. Does et actually perfom OK as we think it does? Will it
keep performing if we add 10 databases for it to handle? And so on. When
will "it break" performance wise.
I was looking at windows "performance monitor" adding and deleting all sorts
of performance counters - but which one should I look at and what is "in
range"? Any general ideas?
And of cause - any handson books/tutorials that describes this?
Best regards
Jan
Here are some links to have a look at:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
<msdn@.csite.com> wrote in message
news:OrxgP391FHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi
> We are running a Windows 2003 Std. and a MSSQL std. on a 2xPIII having 2Gb
> RAM and SCSI disks in RAID1.
> The server handles 80 Databases and has some 80-100 connections from
> webapplications.
> I guess most of you are used to 4xXeon and 128Gb Ram ;-) anyway - the
> performance is at the moment very good, but I am of cause a bit concernt
> about the future. Does et actually perfom OK as we think it does? Will it
> keep performing if we add 10 databases for it to handle? And so on. When
> will "it break" performance wise.
> I was looking at windows "performance monitor" adding and deleting all
> sorts of performance counters - but which one should I look at and what is
> "in range"? Any general ideas?
> And of cause - any handson books/tutorials that describes this?
> Best regards
> Jan
>

Performance monitoring

Hi
We are running a Windows 2003 Std. and a MSSQL std. on a 2xPIII having 2Gb
RAM and SCSI disks in RAID1.
The server handles 80 Databases and has some 80-100 connections from
webapplications.
I guess most of you are used to 4xXeon and 128Gb Ram ;-) anyway - the
performance is at the moment very good, but I am of cause a bit concernt
about the future. Does et actually perfom OK as we think it does? Will it
keep performing if we add 10 databases for it to handle? And so on. When
will "it break" performance wise.
I was looking at windows "performance monitor" adding and deleting all sorts
of performance counters - but which one should I look at and what is "in
range"? Any general ideas?
And of cause - any handson books/tutorials that describes this?
Best regards
JanHere are some links to have a look at:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
<msdn@.csite.com> wrote in message
news:OrxgP391FHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi
> We are running a Windows 2003 Std. and a MSSQL std. on a 2xPIII having 2Gb
> RAM and SCSI disks in RAID1.
> The server handles 80 Databases and has some 80-100 connections from
> webapplications.
> I guess most of you are used to 4xXeon and 128Gb Ram ;-) anyway - the
> performance is at the moment very good, but I am of cause a bit concernt
> about the future. Does et actually perfom OK as we think it does? Will it
> keep performing if we add 10 databases for it to handle? And so on. When
> will "it break" performance wise.
> I was looking at windows "performance monitor" adding and deleting all
> sorts of performance counters - but which one should I look at and what is
> "in range"? Any general ideas?
> And of cause - any handson books/tutorials that describes this?
> Best regards
> Jan
>

Performance monitor question

I'm trying to setup logging of my perfmon data into and MSSQL database, but
what I can't seem to find is the schema definition for the SQL Table(s) that
I need to create. Anyone know where I can find this at?
Thanks,
Tom
The schema will be created automatically by the Performance Logs and Alerts
service as long as the account has CREATE TABLE permissions in the target
database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'm trying to setup logging of my perfmon data into and MSSQL database,
but
> what I can't seem to find is the schema definition for the SQL Table(s)
that
> I need to create. Anyone know where I can find this at?
> Thanks,
> Tom
>
|||Thanks for the quick reply. I'll give it a try.
Tom
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ewG1puOgEHA.4040@.TK2MSFTNGP10.phx.gbl...
> The schema will be created automatically by the Performance Logs and
Alerts
> service as long as the account has CREATE TABLE permissions in the target
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
> news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> but
> that
>

Performance monitor question

I'm trying to setup logging of my perfmon data into and MSSQL database, but
what I can't seem to find is the schema definition for the SQL Table(s) that
I need to create. Anyone know where I can find this at?
Thanks,
TomThe schema will be created automatically by the Performance Logs and Alerts
service as long as the account has CREATE TABLE permissions in the target
database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> I'm trying to setup logging of my perfmon data into and MSSQL database,
but
> what I can't seem to find is the schema definition for the SQL Table(s)
that
> I need to create. Anyone know where I can find this at?
> Thanks,
> Tom
>|||Thanks for the quick reply. I'll give it a try.
Tom
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ewG1puOgEHA.4040@.TK2MSFTNGP10.phx.gbl...
> The schema will be created automatically by the Performance Logs and
Alerts
> service as long as the account has CREATE TABLE permissions in the target
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tom Pennington" <NONEt2pennington@.comcast.net> wrote in message
> news:%23%23c5wYOgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> > I'm trying to setup logging of my perfmon data into and MSSQL database,
> but
> > what I can't seem to find is the schema definition for the SQL Table(s)
> that
> > I need to create. Anyone know where I can find this at?
> >
> > Thanks,
> > Tom
> >
> >
>