Performance Tuning Help needed

This is a Sql 2000 server running on Win 2k. This is a 1
CPU box connected to a SAN.
I ran a profiler trace for 3 hours and collected all the
data. Some of the queries show a large amount of reads.
When I run those queries individually the # of reads is
fairly smaller. For example the trace file shows 36503
reads and the individual query plan shows 91 CPU reads.
Whats going on ? Is the profiler tracking all I/O activity
at that time ? I mean, is it tracking all tempdb usage and
any other I/O activity at this time. What would explain
this large drop in I/O ?
THanks"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
I'm new to SQL Server so take what I say with a grain of salt.. But does
the profiler keep track of how many executions for each SQL statement?
Executed once the number of reads may be small but if it was executed 1000
times during that timeframe I would expect the number to be large.|||i assume you ran the query with the same parameters?
if so, then this large a difference in reads might
indicate the trace captured that query with a different
plan. if the choice of parameters can results in large
differences in rows involved, then depending on what
parameters were used when the query was last compiled,
different executes could get different plans.
a smaller difference on the order of several hundred or
even more than 1k might indicate compile or recompile.
>--Original Message--
>This is a Sql 2000 server running on Win 2k. This is a 1
>CPU box connected to a SAN.
>I ran a profiler trace for 3 hours and collected all the
>data. Some of the queries show a large amount of reads.
>When I run those queries individually the # of reads is
>fairly smaller. For example the trace file shows 36503
>reads and the individual query plan shows 91 CPU reads.
>Whats going on ? Is the profiler tracking all I/O
activity
>at that time ? I mean, is it tracking all tempdb usage
and
>any other I/O activity at this time. What would explain
>this large drop in I/O ?
>THanks
>.
>|||Jack,
are you comparing Profiler output to Query Analyzer query plan?
If so... the difference is "normal".
Trust the Profiler, if it shows 36000 reads, that's really happening
in the DB.
peksi
"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> This is a Sql 2000 server running on Win 2k. This is a 1
> CPU box connected to a SAN.
> I ran a profiler trace for 3 hours and collected all the
> data. Some of the queries show a large amount of reads.
> When I run those queries individually the # of reads is
> fairly smaller. For example the trace file shows 36503
> reads and the individual query plan shows 91 CPU reads.
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
> THanks|||Hi Jack
The profiler shows what's happening at run time, which can be different from
what happens when you run the query for a few reasons, eg:
(a) The level & type of SQL Server activity is different when you run the
query from when the same query was captured by the profiler.
(b) The security context from which you ran the query may be different from
the query picked up by profiler.
(c) General resource levels on the server may be different.
Capturing the execution plans & comparing them may reveal some explanation
of what accounts for the different levels of reads, but this only takes you
part way to resolving the actual problem.
HTH
Regards,
Greg Linwood
SQL Server MVP
"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> This is a Sql 2000 server running on Win 2k. This is a 1
> CPU box connected to a SAN.
> I ran a profiler trace for 3 hours and collected all the
> data. Some of the queries show a large amount of reads.
> When I run those queries individually the # of reads is
> fairly smaller. For example the trace file shows 36503
> reads and the individual query plan shows 91 CPU reads.
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
> THankssql

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 on Fact table connected in View

Hi,

I have a Fact table and I'm accessing it through SQL Views where I have "group by". This is the one I feed to the Analysis Services. Would it be faster to access data if I have group by's in Views and feed that to Analysis Services or would it be better to just do the select statement in View and let the Analysis do all the aggregations?

cherriesh

If SQL and SSAS are on the same box then removing the group by should make things go faster as SQL will not need to sort the results and spool them temporarily out to tempdb until all the results have been read and there will be little latency to deal with between the two services.

If they are on separate machines it depends if the amount of time it takes to do the group by is more or less than the time it would take to transmitt the additional data over the network.