performance test issue

Hi,

Simple query:

select * from MyTable where Id=123456;

The first time I execute this query it takes 14 seconds. However, the second time I execute it takes 0 seconds.

I wanted to clear the cache so the second execution takes 14 second just like the first execution. So i tried:

CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

But no success. I'm using ms sql server management studio (2005). I also tried the option Reset client statistics. I even restarted the sql server. No matter what I try, it looks like the result keeps somewhere saved.

Does somebody have a solution for this?

Cheerswhat is the problem if the query is executing faster ?|||

Quote:

Originally Posted by koekie17

Hi,

Simple query:

select * from MyTable where Id=123456;

The first time I execute this query it takes 14 seconds. However, the second time I execute it takes 0 seconds.

I wanted to clear the cache so the second execution takes 14 second just like the first execution. So i tried:

CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

But no success. I'm using ms sql server management studio (2005). I also tried the option Reset client statistics. I even restarted the sql server. No matter what I try, it looks like the result keeps somewhere saved.

Does somebody have a solution for this?

Cheers

Well this is first time I have seen somebody asking to degrade the performance of the query.

performance slow down at a regurlar basis...

Hi,
I've a strange behavior on my server.
The response time is good after a reboot of the computer, but, after a long
work on it (half day or a day)
The performance slow down, so I reboot the server to recover my performance.
Why?
How to diagnostic this problem?
Thanks.
Jerome.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25Have a look at perfmon
If you have to do this every day/after using it a while then a resource is
being depleted seriously and a reboot frees it up again.
Look at Memory, Processor to start with.
What version and SP of SQL Server ?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:e6Ke4vrCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've a strange behavior on my server.
> The response time is good after a reboot of the computer, but, after a
long
> work on it (half day or a day)
> The performance slow down, so I reboot the server to recover my
performance.
> Why?
> How to diagnostic this problem?
> Thanks.
> Jerome.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25
>|||Fairly Normal with Windows
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:e6Ke4vrCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've a strange behavior on my server.
> The response time is good after a reboot of the computer, but, after a
long
> work on it (half day or a day)
> The performance slow down, so I reboot the server to recover my
performance.
> Why?
> How to diagnostic this problem?
> Thanks.
> Jerome.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25
>|||I've the SP3 on a Windows 2000 Server + SP4 server.
I've 3 drives, and I've splitted differents database files on these 3 drives
(tempdb on 1; data of my data warehouse on 2; data warehouse log file on 3)
"Allan Mitchell" <allan@.no-spam.sqldts.com> a crit dans le message de
news:OCcnxMCDEHA.580@.TK2MSFTNGP11.phx.gbl...
> Have a look at perfmon
> If you have to do this every day/after using it a while then a resource is
> being depleted seriously and a reboot frees it up again.
> Look at Memory, Processor to start with.
> What version and SP of SQL Server ?
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Jj" <willgart@._A_hAotmail_A_.com> wrote in message
> news:e6Ke4vrCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> long
> performance.
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25

Performance Question

Hi!
If I have a table in SQL 2000 with 500,000 registry and I do one query to
that table, what shall be the answer time? Is to slow? Should I separate the
data for more tables?
I am structuralizing one db.
Thanks
Albano Alves
Impossible to answer. It depends on:
A) What the datatypes of your columns are / how many columns there are / how
"wide" the table is
B) What indexes are created on the table / how the indexes are being used /
whether statistics are up to date
C) What kind of hardware you have: Disks / disk configuration / memory /
processor
D) What other activity is happening on the server
So to answer your question: Test it on your end.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate
the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
|||This depends upon a lot of factors such as the where query the index's on
your whether its using a cursor table, the speed of your hard disks, the
state of fragmentation,of your data files, and so in.
Please post the table structure then the query so we can have a look at it
and sugest improvments.
Formally 'Peter The Spate'
"All generalizations are false, including this one."
Mark Twain
"Albano Alves" wrote:

> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
>
|||I have more or less 20 field (varchar and int), and the ID can be related
with another Table. The server is a good machine, but it will have many
users, more or less 1,000
In my tests I feel that how much bigger will be I number it of returned
data, minor is the performance and that when to only return a small one
number of registers the performance is good. That is truth?
My debt is if I should have one alone table for, suppliers, customers and
all stakeholders... or some tables, one for each stakeholder.
Thanks
Albano Alves
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escreveu na mensagem
news:eCOVBj%23IFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Impossible to answer. It depends on:
> A) What the datatypes of your columns are / how many columns there are /
> how
> "wide" the table is
> B) What indexes are created on the table / how the indexes are being used
> /
> whether statistics are up to date
> C) What kind of hardware you have: Disks / disk configuration / memory /
> processor
> D) What other activity is happening on the server
> So to answer your question: Test it on your end.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
> news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> the
>
|||"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:ejb%23uF$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
> My debt is if I should have one alone table for, suppliers, customers and
> all stakeholders... or some tables, one for each stakeholder.
I don't recommend that -- it will mean that your application or stored
procedure will have to figure out what table to query at runtime, or you'll
have to have a bunch of partitioned views that you update every time data
changes. It will be a maintenence nightmare. Try to solve issues with
indexes first.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic

performance question

Hi , long time I didn't ask any questions
And now I am back.
I have a huge query
that looks like that

select emp.name,
a.field1, b.field1,c.field1,d.field1,e,field1,f.field1,g.fie ld1
from emp left join (select sum(field1),empid from table group by
empid ) as a
on emp.uniqueid = a.empid
left join (select sum(field1),empid from table group by empid ) as
b
on emp.uniqueid = b.empid
left join (select sum(field1),empid from table group by empid ) as
d
on emp.uniqueid = d.empid
left join (select sum(field1),empid from table group by empid )as c
on emp.uniqueid = c.empid
left join(select sum(field1),empid from table group by empid ) as e
on emp.uniqueid = e.empid
left join (select sum(field1),empid from table group by empid )as f
on emp.uniqueid = f.empid
left join (select sum(field1),empid from table group by empid )as g
on emp.uniqueid =g.empid
where condition1 and
condition2 and condition3
union
select emp.name,
a.field1, b.field1,c.field1,d.field1,e,field1,f.field1,g.fie ld1
from emp left join (select sum(field1),empid from table group by
empid ) as a
on emp.uniqueid = a.empid
left join (select sum(field1),empid from table group by empid ) as
b
on emp.uniqueid = b.empid
left join (select sum(field1),empid from table group by empid ) as
d
on emp.uniqueid = d.empid
left join (select sum(field1),empid from table group by empid )as c
on emp.uniqueid = c.empid
left join(select sum(field1),empid from table group by empid ) as e
on emp.uniqueid = e.empid
left join (select sum(field1),empid from table group by empid )as f
on emp.uniqueid = f.empid
left join (select sum(field1),empid from table group by empid )as g
on emp.uniqueid =g.empid
where condition4 and
condition5 and condition6
union
select emp.name,
a.field1, b.field1,c.field1,d.field1,e,field1,f.field1,g.fie ld1
from emp left join (select sum(field1),empid from table group by
empid ) as a
on emp.uniqueid = a.empid
left join (select sum(field1),empid from table group by empid ) as
b
on emp.uniqueid = b.empid
left join (select sum(field1),empid from table group by empid ) as
d
on emp.uniqueid = d.empid
left join (select sum(field1),empid from table group by empid )as c
on emp.uniqueid = c.empid
left join(select sum(field1),empid from table group by empid ) as e
on emp.uniqueid = e.empid
left join (select sum(field1),empid from table group by empid )as f
on emp.uniqueid = f.empid
left join (select sum(field1),empid from table group by empid )as g
on emp.uniqueid =g.empid
where condition7 and
condition8 and condition9
(If you need the real one I can include it (It is 1000 lines))

So when I run it on database it always takes a different time to
execute.
A lot of times it was timing out, sometimes it doesn't take long (with
the same parameters)
I reindexed all tables, ran
sp_updatestats
sp_configure 'min memory per query (KB)' ,10240
(with reconfigure of course.) It was the same instability.
I replaced all selects in from : (select sum(field1),empid from table
group by empid )
with functions in select , but it didn't help.
What can be wrong? what should I do in this case? I have a procedures
like that.The best way to resolve problem - analyze execution plan|||inna (mednyk@.hotmail.com) writes:
> Hi , long time I didn't ask any questions
> And now I am back.
> I have a huge query
> that looks like that
> select emp.name,
> a.field1, b.field1,c.field1,d.field1,e,field1,f.field1,g.fie ld1
> from emp left join (select sum(field1),empid from table group by
> empid ) as a
> on emp.uniqueid = a.empid
> left join (select sum(field1),empid from table group by empid ) as
> b
> on emp.uniqueid = b.empid
> left join (select sum(field1),empid from table group by empid ) as
> d

Why are you repeating the same derived table all over again? Obviously
your real query does not look like that, but in what you have obscured
that query, I don't know, and I don't want to guess.

With the CREATE TABLE and CREATE INDEX statements for the involved tables,
some information about that data size and distribution in the tables, as
well as the query of the table itself, people in this newsgroup might be
able to help you. Of course, if it is a 1000-line monster, then the sheer
size of the query may be an obstacle for assistance over newsgroups.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Performance Question

Hi!
If I have a table in SQL 2000 with 500,000 registry and I do one query to
that table, what shall be the answer time? Is to slow? Should I separate the
data for more tables?
I am structuralizing one db.
Thanks
Albano AlvesImpossible to answer. It depends on:
A) What the datatypes of your columns are / how many columns there are / how
"wide" the table is
B) What indexes are created on the table / how the indexes are being used /
whether statistics are up to date
C) What kind of hardware you have: Disks / disk configuration / memory /
processor
D) What other activity is happening on the server
So to answer your question: Test it on your end.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate
the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>|||This depends upon a lot of factors such as the where query the index's on
your whether its using a cursor table, the speed of your hard disks, the
state of fragmentation,of your data files, and so in.
Please post the table structure then the query so we can have a look at it
and sugest improvments.
Formally 'Peter The Spate'
"All generalizations are false, including this one."
Mark Twain
"Albano Alves" wrote:
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
>|||I have more or less 20 field (varchar and int), and the ID can be related
with another Table. The server is a good machine, but it will have many
users, more or less 1,000
In my tests I feel that how much bigger will be I number it of returned
data, minor is the performance and that when to only return a small one
number of registers the performance is good. That is truth?
My debt is if I should have one alone table for, suppliers, customers and
all stakeholders... or some tables, one for each stakeholder.
Thanks
Albano Alves
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escreveu na mensagem
news:eCOVBj%23IFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Impossible to answer. It depends on:
> A) What the datatypes of your columns are / how many columns there are /
> how
> "wide" the table is
> B) What indexes are created on the table / how the indexes are being used
> /
> whether statistics are up to date
> C) What kind of hardware you have: Disks / disk configuration / memory /
> processor
> D) What other activity is happening on the server
> So to answer your question: Test it on your end.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
> news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
>> Hi!
>> If I have a table in SQL 2000 with 500,000 registry and I do one query to
>> that table, what shall be the answer time? Is to slow? Should I separate
> the
>> data for more tables?
>> I am structuralizing one db.
>> Thanks
>> Albano Alves
>>
>|||"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:ejb%23uF$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
> My debt is if I should have one alone table for, suppliers, customers and
> all stakeholders... or some tables, one for each stakeholder.
I don't recommend that -- it will mean that your application or stored
procedure will have to figure out what table to query at runtime, or you'll
have to have a bunch of partitioned views that you update every time data
changes. It will be a maintenence nightmare. Try to solve issues with
indexes first.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Performance question

Hello Everybody,
There is an unexplained yet situation with
performance/time to return query results on 3 queries.
Queries must be identical and are executed from SQL S 2000
via Linked server to an Oracle database.
End table is the same (events).
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'FLORIDA'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'POWER2'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'APCEPKS'
Last query always takes ~5 sec to return results (
to 'APCEPKS'); there are much more records than others.
First and second queries take more than a minute!!!
The order of execution doesn't matter.
Execution plan shows no differences.
My question is:
What could cause performance difference like this?
Any suggestions are highly appreciated!
Thx,
DobbyAdd these commands after each query and see how does it affect ?
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...
> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>|||Basically, you are submitting the queries to an Oracle database, via
SQL-Server. So I would look at the Oracle side. If you submit the
queries directly on the Oracle database, do they behave the same?
Gert-Jan
Dobromir Rizov wrote:
> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby|||One thing I can think of -- not familiar with Oracle data/index structure --
is that Servername is non-cluster indexed, and most of the records for
Servername = 'APCEPKS' are located in a small range of data pages, whereas
the records for the other two servername values are spread wide.
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...
> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>|||Same all queries executed directly in Oracle take less
than a second to return result.
Dobromir
>--Original Message--
>Basically, you are submitting the queries to an Oracle
database, via
>SQL-Server. So I would look at the Oracle side. If you
submit the
>queries directly on the Oracle database, do they behave
the same?
>Gert-Jan
>
>Dobromir Rizov wrote:
>> Hello Everybody,
>> There is an unexplained yet situation with
>> performance/time to return query results on 3 queries.
>> Queries must be identical and are executed from SQL S
2000
>> via Linked server to an Oracle database.
>> End table is the same (events).
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'FLORIDA'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'POWER2'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'APCEPKS'
>> Last query always takes ~5 sec to return results (
>> to 'APCEPKS'); there are much more records than others.
>> First and second queries take more than a minute!!!
>> The order of execution doesn't matter.
>> Execution plan shows no differences.
>> My question is:
>> What could cause performance difference like this?
>> Any suggestions are highly appreciated!
>> Thx,
>> Dobby
>.
>|||In that case, I would consider moving to an OPENQUERY syntax:
SET QUOTED_IDENTIFIER OFF
SELECT MaxID FROM OPENQUERY(PHDT,
"SELECT MAX(ID) AS MaxID
FROM PHDT..PS_USER.EVENTS
WHERE SERVERNAME='FLORIDA'
")
I realize however, that this may not meet your requirement...
Gert-Jan
Dobromir Rizov wrote:
> Same all queries executed directly in Oracle take less
> than a second to return result.
> Dobromir
> >--Original Message--
> >Basically, you are submitting the queries to an Oracle
> database, via
> >SQL-Server. So I would look at the Oracle side. If you
> submit the
> >queries directly on the Oracle database, do they behave
> the same?
> >
> >Gert-Jan
> >
> >
> >Dobromir Rizov wrote:
> >>
> >> Hello Everybody,
> >>
> >> There is an unexplained yet situation with
> >> performance/time to return query results on 3 queries.
> >>
> >> Queries must be identical and are executed from SQL S
> 2000
> >> via Linked server to an Oracle database.
> >> End table is the same (events).
> >>
> >> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
> SERVERNAME
> >> = 'FLORIDA'
> >>
> >> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
> SERVERNAME
> >> = 'POWER2'
> >>
> >> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
> SERVERNAME
> >> = 'APCEPKS'
> >>
> >> Last query always takes ~5 sec to return results (
> >> to 'APCEPKS'); there are much more records than others.
> >>
> >> First and second queries take more than a minute!!!
> >>
> >> The order of execution doesn't matter.
> >>
> >> Execution plan shows no differences.
> >>
> >> My question is:
> >>
> >> What could cause performance difference like this?
> >>
> >> Any suggestions are highly appreciated!
> >>
> >> Thx,
> >>
> >> Dobby
> >.
> >|||The execution plan shows:
Row count 1 on the shortest query and full table scans on
others.
My understanding is that indexes are used only (but
always) with the query on 'APCEPKS', but never with any
other queries.
What makes queries to differ?
Is there a way to force a query to use a particular index?
Thx,
Dobby
>--Original Message--
>Hello Everybody,
>There is an unexplained yet situation with
>performance/time to return query results on 3 queries.
>Queries must be identical and are executed from SQL S
2000
>via Linked server to an Oracle database.
>End table is the same (events).
>
>SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
>= 'FLORIDA'
>SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
>= 'POWER2'
>SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
>= 'APCEPKS'
>Last query always takes ~5 sec to return results (
>to 'APCEPKS'); there are much more records than others.
>First and second queries take more than a minute!!!
>The order of execution doesn't matter.
>Execution plan shows no differences.
>My question is:
>What could cause performance difference like this?
>Any suggestions are highly appreciated!
>Thx,
>Dobby
>
>
>.
>|||Directly in SQL*Plus queries retutn instant results.
There are indexes in place.
Dobby
>--Original Message--
>Basically, you are submitting the queries to an Oracle
database, via
>SQL-Server. So I would look at the Oracle side. If you
submit the
>queries directly on the Oracle database, do they behave
the same?
>Gert-Jan
>
>Dobromir Rizov wrote:
>> Hello Everybody,
>> There is an unexplained yet situation with
>> performance/time to return query results on 3 queries.
>> Queries must be identical and are executed from SQL S
2000
>> via Linked server to an Oracle database.
>> End table is the same (events).
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'FLORIDA'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'POWER2'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'APCEPKS'
>> Last query always takes ~5 sec to return results (
>> to 'APCEPKS'); there are much more records than others.
>> First and second queries take more than a minute!!!
>> The order of execution doesn't matter.
>> Execution plan shows no differences.
>> My question is:
>> What could cause performance difference like this?
>> Any suggestions are highly appreciated!
>> Thx,
>> Dobby
>.
>sql

Performance Question

Hi!
If I have a table in SQL 2000 with 500,000 registry and I do one query to
that table, what shall be the answer time? Is to slow? Should I separate the
data for more tables?
I am structuralizing one db.
Thanks
Albano AlvesImpossible to answer. It depends on:
A) What the datatypes of your columns are / how many columns there are / how
"wide" the table is
B) What indexes are created on the table / how the indexes are being used /
whether statistics are up to date
C) What kind of hardware you have: Disks / disk configuration / memory /
processor
D) What other activity is happening on the server
So to answer your question: Test it on your end.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate
the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>|||This depends upon a lot of factors such as the where query the index's on
your whether its using a cursor table, the speed of your hard disks, the
state of fragmentation,of your data files, and so in.
Please post the table structure then the query so we can have a look at it
and sugest improvments.
Formally 'Peter The Spate'
"All generalizations are false, including this one."
Mark Twain
"Albano Alves" wrote:

> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate t
he
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
>|||I have more or less 20 field (varchar and int), and the ID can be related
with another Table. The server is a good machine, but it will have many
users, more or less 1,000
In my tests I feel that how much bigger will be I number it of returned
data, minor is the performance and that when to only return a small one
number of registers the performance is good. That is truth?
My debt is if I should have one alone table for, suppliers, customers and
all stakeholders... or some tables, one for each stakeholder.
Thanks
Albano Alves
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escreveu na mensagem
news:eCOVBj%23IFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Impossible to answer. It depends on:
> A) What the datatypes of your columns are / how many columns there are /
> how
> "wide" the table is
> B) What indexes are created on the table / how the indexes are being used
> /
> whether statistics are up to date
> C) What kind of hardware you have: Disks / disk configuration / memory /
> processor
> D) What other activity is happening on the server
> So to answer your question: Test it on your end.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
> news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> the
>|||"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:ejb%23uF$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
> My debt is if I should have one alone table for, suppliers, customers and
> all stakeholders... or some tables, one for each stakeholder.
I don't recommend that -- it will mean that your application or stored
procedure will have to figure out what table to query at runtime, or you'll
have to have a bunch of partitioned views that you update every time data
changes. It will be a maintenence nightmare. Try to solve issues with
indexes first.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Performance question

Hello Everybody,
There is an unexplained yet situation with
performance/time to return query results on 3 queries.
Queries must be identical and are executed from SQL S 2000
via Linked server to an Oracle database.
End table is the same (events).
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'FLORIDA'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'POWER2'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'APCEPKS'
Last query always takes ~5 sec to return results (
to 'APCEPKS'); there are much more records than others.
First and second queries take more than a minute!!!
The order of execution doesn't matter.
Execution plan shows no differences.
My question is:
What could cause performance difference like this?
Any suggestions are highly appreciated!
Thx,
DobbyAdd these commands after each query and see how does it affect ?
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>

---

|||Basically, you are submitting the queries to an Oracle database, via
SQL-Server. So I would look at the Oracle side. If you submit the
queries directly on the Oracle database, do they behave the same?
Gert-Jan
Dobromir Rizov wrote:

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby

---

|||One thing I can think of -- not familiar with Oracle data/index structure --
is that Servername is non-cluster indexed, and most of the records for
Servername = 'APCEPKS' are located in a small range of data pages, whereas
the records for the other two servername values are spread wide.
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>

---

|||Same all queries executed directly in Oracle take less
than a second to return result.
Dobromir

quote:

---

>--Original Message--
>Basically, you are submitting the queries to an Oracle

---

database, via

quote:

---

>SQL-Server. So I would look at the Oracle side. If you

---

submit the

quote:

---

>queries directly on the Oracle database, do they behave

---

the same?

quote:

---

>Gert-Jan
>
>Dobromir Rizov wrote:
2000[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
>.
>

---

|||In that case, I would consider moving to an OPENQUERY syntax:
SET QUOTED_IDENTIFIER OFF
SELECT MaxID FROM OPENQUERY(PHDT,
"SELECT MAX(ID) AS MaxID
FROM PHDT..PS_USER.EVENTS
WHERE SERVERNAME='FLORIDA'
")
I realize however, that this may not meet your requirement...
Gert-Jan
Dobromir Rizov wrote:[QUOTE]
> Same all queries executed directly in Oracle take less
> than a second to return result.
> Dobromir
>
> database, via
> submit the
> the same?
> 2000
> SERVERNAME
> SERVERNAME
> SERVERNAME|||Directly in SQL*Plus queries retutn instant results.
There are indexes in place.
Dobby

quote:

---

>--Original Message--
>Basically, you are submitting the queries to an Oracle

---

database, via

quote:

---

>SQL-Server. So I would look at the Oracle side. If you

---

submit the

quote:

---

>queries directly on the Oracle database, do they behave

---

the same?

quote:

---

>Gert-Jan
>
>Dobromir Rizov wrote:
2000[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
>.
>

---

sql

Performance Problems with SMI

I have a subscription management interface that seems a reasonably thin wrapper of the NS API. Whenever I do gets, it takes a tremendously long time to process. For example, when I do a GetSubscriptions for a user (very thin wrapper around SubscriptionEnumeration()) it takes roughly 40s to return on one of my servers which is an old P3-866, and 20s to return on my dev VM (on a physical Intel Dual Core 2.0Ghz). CPU is hit pretty hard during those calls. This also seems to happen when I get SubscriptionClasses.

I've looked through several samples, some from the NS Samples included, some from 3rd party books, and all seem to have similar calls, without mention of performance problems. Is there something I might be doing wrong? Or is this expected behavior? Should I be caching the data? Anyone else run into this issue? This seems like a reasonably common use case, so I was surprised to not readily find anything about this after a quick search on Google.

-Francis

I realized that I had made a change which ended up creating too many connections, which slowed things down alot. Reverting that improved performance considerably. Still pegs the CPU though.|||

Just to offer another data point - I haven't had performance problems with SMI. Could there be something else going on do you think?

Have you tried looking into it with SQL Profiler?

HTH..

Joe

Performance problems

Hi,
I have two questions:
1. I have an SQL server with over 400 databases. I takes
quite some time to load the databases in Enterprise
manager. Does anyone have any suggestions on how I can
speed up this process.
2. I also have one database that is 2 GB in size and it
takes some time to run queries from the database. Can
anyone recommend how I can speed up the database - for
example reindex it.
Thanks for your help.
Regards,
Matthew ReedFor 2. - need more info. What type of indexes are existing on the table?
What are the major types of queries ran from it? Any locking/blocking
issues? Percentage of inserts/updates/deletes versus queries, etc. For
straight index info you might try the Inside SQL Server 2000 book.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Matthew Reed" <anonymous@.discussions.microsoft.com> wrote in message
news:088a01c3d3a1$0c6d4930$a601280a@.phx.gbl...
> Hi,
> I have two questions:
> 1. I have an SQL server with over 400 databases. I takes
> quite some time to load the databases in Enterprise
> manager. Does anyone have any suggestions on how I can
> speed up this process.
> 2. I also have one database that is 2 GB in size and it
> takes some time to run queries from the database. Can
> anyone recommend how I can speed up the database - for
> example reindex it.
> Thanks for your help.
> Regards,
> Matthew Reedsql

Performance problem while using Linked Servers

I am transferring data from SQl Server 2000 to Oracle through Linked
Servers.It takes considerable amount of time while transferring data
from SQL Server to oracle.Both these databases are at remote
locations.I am executing a Stored procedure on SQL Server and what it
does is,It has an Insert statement which reads the data from a table in
SQL Server and inserts it into the Oracle table. It is a very time
consuming process.Vice-Versa it takes very less time for Oracle-SQL
Server transfer.If anybody has a clue towards reducing the time in SQL
Server-ORACLE transfer.How about extracting the data, sending the file and then inserting at
the other end ? i.e. dump to csv then import from csv. Not sure about
the Oracle side, but doing this from SQL is straight forward enough.

Ryan

Raja wrote:
> I am transferring data from SQl Server 2000 to Oracle through Linked
> Servers.It takes considerable amount of time while transferring data
> from SQL Server to oracle.Both these databases are at remote
> locations.I am executing a Stored procedure on SQL Server and what it
> does is,It has an Insert statement which reads the data from a table
in
> SQL Server and inserts it into the Oracle table. It is a very time
> consuming process.Vice-Versa it takes very less time for Oracle-SQL
> Server transfer.If anybody has a clue towards reducing the time in
SQL
> Server-ORACLE transfer.|||Thanks,Ryan
Yes,but the entire process has to be automated.At present I have an
Interface where in the records selected are transferred through the
execution of a Stored procedure.As suggested by you,we can also
transfer the data in the form of an XML through Webservice.For some
reasons WebService is not used,hence the only option is Linked Server.
Dumping the data into a File(CSV or XML) and inserting the same
into Oracle isnt a problem.Do you have any suggestions for sending the
file?.

Regards,
Raja|||How about using a DTS package to extract the data into a CSV and then
saving / moving this file onto the other server ? You can script moving
the file to wherever you need, or saving it there in the first place.
You'll need to understand how to script this, but it should be
reasonably straight forward. The bit I don't know is the Oracle end for
importing it once you have the file. As you are extracting the data and
then importing it anyway, this might be quicker than copying directly
as you are doing.

So, your DTS package would contain a connection to your database, a SQL
task for running your SP linked to a Text File (source) for your CSV
(selects everything from the SP into this file), and a VB ActiveX
script to move the file to the new server.....Then do whatever you
need to do on the Oracle side. OK, so it's split the task into a
process for SQL and a process for Oracle and doesn't automate the
'entire' process, so hopefully someone can suggest a better way.

Ryan

Performance outside private network

Can SQL run more than one request at the same time from the same IP address?
I've noticed that if I open two instances of my site in IE they tend to ru
n sluggish, and it seems like the second instance does not start to respond
to a request until the firs
t request is completed on my first instance. I'm trying to run both request
s simultaneously. I'm running SQL Server 2000 Developer's Edition, by the w
ay. Any takers?>
> Can SQL run more than one request at the same time from the
> same IP address? I've noticed that if I open two instances of
> my site in IE they tend to run sluggish, and it seems like the
> second instance does not start to respond to a request until the
> first request is completed on my first instance. I'm trying to run
> both requests simultaneously. I'm running SQL Server 2000 Developer's
> Edition, by the way. Any takers?
>
--
You address this as a normal application performance issue. This is the
place to start:
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Performance outside private network

Can SQL run more than one request at the same time from the same IP address? I've noticed that if I open two instances of my site in IE they tend to run sluggish, and it seems like the second instance does not start to respond to a request until the firs
t request is completed on my first instance. I'm trying to run both requests simultaneously. I'm running SQL Server 2000 Developer's Edition, by the way. Any takers?
>
> Can SQL run more than one request at the same time from the
> same IP address? I've noticed that if I open two instances of
> my site in IE they tend to run sluggish, and it seems like the
> second instance does not start to respond to a request until the
> first request is completed on my first instance. I'm trying to run
> both requests simultaneously. I'm running SQL Server 2000 Developer's
> Edition, by the way. Any takers?
>
You address this as a normal application performance issue. This is the
place to start:
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Performance outside private network

Can SQL run more than one request at the same time from the same IP address? I've noticed that if I open two instances of my site in IE they tend to run sluggish, and it seems like the second instance does not start to respond to a request until the first request is completed on my first instance. I'm trying to run both requests simultaneously. I'm running SQL Server 2000 Developer's Edition, by the way. Any takers?>
> Can SQL run more than one request at the same time from the
> same IP address? I've noticed that if I open two instances of
> my site in IE they tend to run sluggish, and it seems like the
> second instance does not start to respond to a request until the
> first request is completed on my first instance. I'm trying to run
> both requests simultaneously. I'm running SQL Server 2000 Developer's
> Edition, by the way. Any takers?
>
--
You address this as a normal application performance issue. This is the
place to start:
HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Performance on wan

Hi,I have 2 server SQL 2000, 15 days ago everything worked well,

but days back, the performance on WAN is poor, it takes long time in login and make querys, in the local net everything work well, my problem is only on WAN, i dont have problem with Memory and CPU, the use of CPU never exceed 25 %, although i have poor performance on WAN local its fine, my bandwith is excelent, with other services i dont have problem(Terminal Services, Ftp, etc)

Some idea?

Thanks.

Javier Muro

When you connect, can you confirm your network library is TCP/IP? You can query against sysprocesses to find this out:
SELECT loginame, net_library
FROM sysprocesses
WHERE loginame = '<your login>'
If it's not, run cliconfg.exe and check the order of your enabled protocols. If you've got TCP/IP first, was the SQL Server restarted recently? If so, check the SQL Server log to ensure it was able to listen on TCP/IP.
If none of that, were any changes made on the networking equipment for the WAN? For instance, did the network engineers adjust QoS or anything of that sort?

Performance on RS

Hi All,
Everytime when I run the report (by URL access), it need a very long time to
wait. After such init step, then it return normal speed when I run another
report!
Anyone can help ! Thanks!
TonyI noticed this when I first started with RS and what I did to get around it
is to have a report open on my desktop that auto executes every 5 minutes,
this keeps this going. I have been told that the below will work. If you
want to do my way then get a simple report, in report->properties set it to
refresh every 5 minutes (or 10 or 15, whatever).
Here is what Chris suggests:
>>>>>>>>>>
If you are running Windows 2003 server for your IIS reportserver, then this
is a simple issue - I'll explain what happens:
The report service engine, once it is idle for more than the default 20
minutes, the worker process is shutdown.
This is controlled by IIS.
Open up the Internet Information Services (IIS) Manager
Expand the server node then the application pools.
On my IIS machine, I created an application pool dedicated to the
reportserver & reportmanager virtual webs.
But anyways, for the application pool that the reportserver is pointing to
if you left everything to their defaults will be the DefaultAppPool.
Right click the default app pool and select properties.
There are two things that are checked by default - On the recycling tab
there is a checkbox for recycling worker processes - it is currently set to
1740 minutes (29 hours). Leave it.
The other one is on the performance tab - which is the one you are
interested in changing...
See the "Idle Timeout" section and increase the number of minutes to be 8
hours a typical working day - 8*60 = 480 minutes.
Next, to be sure the "morning person" that runs the first report doesn't get
the delay, set up a schedule for either a dummy or adhoc report to fire off
like at 6am so that the report component worker processes get loaded.
I hope this helps you.
There is no need to have a report fire off every minute to keep things
alive - it is just that the report service was "unloaded" and needed to load
back up.
=-Chris
>>>>>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:35CB04BD-BB0B-4D38-A4FF-03A52557E4A3@.microsoft.com...
> Hi All,
> Everytime when I run the report (by URL access), it need a very long time
> to
> wait. After such init step, then it return normal speed when I run
> another
> report!
> Anyone can help ! Thanks!
> Tony
>|||Thank you Bruce! I try it!
"Bruce L-C [MVP]" wrote:
> I noticed this when I first started with RS and what I did to get around it
> is to have a report open on my desktop that auto executes every 5 minutes,
> this keeps this going. I have been told that the below will work. If you
> want to do my way then get a simple report, in report->properties set it to
> refresh every 5 minutes (or 10 or 15, whatever).
> Here is what Chris suggests:
> >>>>>>>>>>
> If you are running Windows 2003 server for your IIS reportserver, then this
> is a simple issue - I'll explain what happens:
> The report service engine, once it is idle for more than the default 20
> minutes, the worker process is shutdown.
> This is controlled by IIS.
> Open up the Internet Information Services (IIS) Manager
> Expand the server node then the application pools.
> On my IIS machine, I created an application pool dedicated to the
> reportserver & reportmanager virtual webs.
> But anyways, for the application pool that the reportserver is pointing to
> if you left everything to their defaults will be the DefaultAppPool.
> Right click the default app pool and select properties.
> There are two things that are checked by default - On the recycling tab
> there is a checkbox for recycling worker processes - it is currently set to
> 1740 minutes (29 hours). Leave it.
> The other one is on the performance tab - which is the one you are
> interested in changing...
> See the "Idle Timeout" section and increase the number of minutes to be 8
> hours a typical working day - 8*60 = 480 minutes.
> Next, to be sure the "morning person" that runs the first report doesn't get
> the delay, set up a schedule for either a dummy or adhoc report to fire off
> like at 6am so that the report component worker processes get loaded.
> I hope this helps you.
> There is no need to have a report fire off every minute to keep things
> alive - it is just that the report service was "unloaded" and needed to load
> back up.
> =-Chris
> >>>>>>>>>>>>>>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:35CB04BD-BB0B-4D38-A4FF-03A52557E4A3@.microsoft.com...
> > Hi All,
> >
> > Everytime when I run the report (by URL access), it need a very long time
> > to
> > wait. After such init step, then it return normal speed when I run
> > another
> > report!
> >
> > Anyone can help ! Thanks!
> >
> > Tony
> >
>
>

performance of indexed views

I see their benefit -- trust me. one question I have is that it looks like my indexed view has to get updated each time the underlying base table changes. What happens if I do an insert or bcp into the underlying table -- will me table go offline while this data gets re-aggregated -- is there a way for me to schedule this? I see UDAs as well, and I think they are more flexible, but I'd be concerned that they are getting too far away from the optimizer and how are they refreshed -- recompile of the code?

Help..and I know it's marketing -- but what direction is the best for people that use the product, UDAs or Indexed Views.

Indexed views get updated in-line with table updates, just as if you basically had an additional index on the base table. So, if you perform an insert into the base table, it will not commit/finish until all indexes on the base table AND indexes from materialized views are updated. They basically are very similair in the way that the engine ensures additional indexes on the base table are kept updated. The same applies to how statistics are updated and managed, the same as the corresponding base table. An indexed view has an associated b-tree(s) structure backing it that must be kept up to date just as the base table is in real-time with data modifications.

As for Indexed Views vs. UDA's, they are 2 totally different technologies for different uses...I really don't see how the 2 would even compare. A UDA is calculated from an instruction and dataset you pass it at runtime, with no physical backing at all (unless you create for example an indexed computed column that is based on the UDA, but I won't go there). A UDA is basically the same as using any of the existing built-in aggregate functions in SQL Server (i.e. sum(), count(), min(), max(), etc.).

HTH

|||

The other thing to be aware of is that you always have to have a clustered index on an indexed view (this is what defines it) If you therefore have additional indexes on the indexed view, you will have the additional knock on impact when the underlying data is changed.

Indexed views are great in that they provide any easy way of providing the indexed view data quickly to the user with little effort, however you need to appreciate the impact on performance as well as the restrictions on how data is updated (SET statements etc).

|||I don't understand the 2nd part of the response you say they are different technologies, I agree, but they can be used for the same things no? If I wnatd to sum the sales of a bunch of regions and roll them up I could use an Indexed View for that -- aka summary table -- no? That's what I can do with a UDA? Help me here..|||Hi Chad -- I think there's something I just picked up on that I didn't relase -- that the Indexed View is a preset aggregate, while the UDA takes the data set and perform the aggregation dynamically, correct? If so, then is this just not a set of logic to run on the data set? Would you recommend a UDA over calling a SP to do the work? Do you see a lot of people using this, or would suggest it? What does it buy me -- just trying to understand how much CLR objects we want and should use based on the countless resources that say it's good for some stuff and not for others :)|||

Yes, that is correct. You could use either to achieve what you are attempting to get, however the biggest difference between the 2 technologies is that an indexed view is materialized on-disk, just like a table, and a UDA performs the aggregation on the data-set at time of request, like a query against a table.

A UDA is just like you mentioned, basically logic that is performed against the data set...the advantage of a UDA in 2005 is that you can create your own aggregates that don't already exist as pre-defined (i.e. sum(), min(), max())...for example, you could create a median() aggregate for example.

As for why you would use a UDA over a stored procedure would come down to a couple of things that would be different for many different scenarios, including performance, encapsulation, type of use, etc. For example, you could use a UDA just like you could use an existing pre-defined aggregate (i.e. within a select statement), like this:

select sum(column), myuda(column) from table

whereas you couldn't do the above with a stored procedure. Also, you may need to perform complex computational logic on the data, which the CLR would be better at. However, if you're just grabbing data, a stored procedure may be better...all would depend on the scenario.

HTH,

Performance of EM

Hi,
I have no large database on my notebook. The only databases that I use for
my tests are Northwind and Pubs. Each time I install Windows and SQL Server,
I notice that EM considerably becomes slow after 1 or 2 months. This is what
all my colleagues complain about. Is it normal?! Is there any solution?
Defragging the disk did not help.
Any help would be greatly appreciated.
Leila"Leila" <Leilas@.hotpop.com> wrote in message
news:urPZ8AOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have no large database on my notebook. The only databases that I use for
> my tests are Northwind and Pubs. Each time I install Windows and SQL
> Server, I notice that EM considerably becomes slow after 1 or 2 months.
> This is what all my colleagues complain about. Is it normal?! Is there any
> solution?
> Defragging the disk did not help.
> Any help would be greatly appreciated.
> Leila
>
Fix your clock.
David|||Check if ODBC tracing is turned on.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Leila" <Leilas@.hotpop.com> wrote in message news:urPZ8AOWGHA.1192@.TK2MSFTNGP03.phx.gbl...[v
bcol=seagreen]
> Hi,
> I have no large database on my notebook. The only databases that I use for
> my tests are Northwind and Pubs. Each time I install Windows and SQL Serve
r,
> I notice that EM considerably becomes slow after 1 or 2 months. This is wh
at
> all my colleagues complain about. Is it normal?! Is there any solution?
> Defragging the disk did not help.
> Any help would be greatly appreciated.
> Leila
>[/vbcol]|||Thanks Tibor,
But this is off. Don't you really have this problem?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u88pJVOWGHA.4768@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Check if ODBC tracing is turned on.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:urPZ8AOWGHA.1192@.TK2MSFTNGP03.phx.gbl...|||Thanks David,
But I didn't get it!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eccxuEOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:urPZ8AOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Fix your clock.
> David
>|||Didn't get what? David was NOT addressing your issue of EM performance.
Both David and Aaron have asked you to fix your system clock, which is
causing your posts to be marked with tomorrow's date. Some people do this so
that their posts will show up at the top of the list for longer, and it is
considered very rude. Many very knowledgeable people on these forums will
refuse to answer posts that are post-dated like this. So do yourself a
favor, and make sure your system clock is correct.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:OBJ6AHPWGHA.1192@.TK2MSFTNGP04.phx.gbl...
> Thanks David,
> But I didn't get it!
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:eccxuEOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
>|||Leila, do you know where your taskbar is? Do you see a clock there? Can
you please set the proper date?

> Thanks Tibor,
> But this is off. Don't you really have this problem?|||Leila (Leilas@.hotpop.com) writes:
> Date: Thu, 6 Apr 2006 22:08:56 +0430
That moment in time has not occurred yet. That's why people is yelling
at you to fix the clock.

> Thanks Tibor,
> But this is off. Don't you really have this problem?
The other standard thing to look out for is databases that are set to
autoclose. That can be a major peformance hog, if your are not sysadmin,
as EM needs to open each database to check the access rights.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Oh... thanks indeed Kalen!
Honestly I was working on point-in-time recovery and I used the clock to
move the time forward when modifying the data! I did not need to check the
calendar on my notebook (I use my watch!) to fix it since few days ago :-)
I must check if I have made any mistakes on some data entry or timestamps in
my word documents!!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O32lhMPWGHA.4920@.TK2MSFTNGP02.phx.gbl...
> Didn't get what? David was NOT addressing your issue of EM performance.
> Both David and Aaron have asked you to fix your system clock, which is
> causing your posts to be marked with tomorrow's date. Some people do this
> so that their posts will show up at the top of the list for longer, and it
> is considered very rude. Many very knowledgeable people on these forums
> will refuse to answer posts that are post-dated like this. So do yourself
> a favor, and make sure your system clock is correct.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OBJ6AHPWGHA.1192@.TK2MSFTNGP04.phx.gbl...
>|||Oh... thanks indeed Kalen!
Honestly I was working on point-in-time recovery and I used the clock to
move the time forward when modifying the data! I did not need to check the
calendar on my notebook (I use my watch!) to fix it since few days ago :-)
I must check if I have made any mistakes on some data entries (with default
date) or timestamps in
my word documents!!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O32lhMPWGHA.4920@.TK2MSFTNGP02.phx.gbl...
> Didn't get what? David was NOT addressing your issue of EM performance.
> Both David and Aaron have asked you to fix your system clock, which is
> causing your posts to be marked with tomorrow's date. Some people do this
> so that their posts will show up at the top of the list for longer, and it
> is considered very rude. Many very knowledgeable people on these forums
> will refuse to answer posts that are post-dated like this. So do yourself
> a favor, and make sure your system clock is correct.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OBJ6AHPWGHA.1192@.TK2MSFTNGP04.phx.gbl...
>

Performance of EM

Hi,
I have no large database on my notebook. The only databases that I use for
my tests are Northwind and Pubs. Each time I install Windows and SQL Server,
I notice that EM considerably becomes slow after 1 or 2 months. This is what
all my colleagues complain about. Is it normal?! Is there any solution?
Defragging the disk did not help.
Any help would be greatly appreciated.
Leila
"Leila" <Leilas@.hotpop.com> wrote in message
news:urPZ8AOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have no large database on my notebook. The only databases that I use for
> my tests are Northwind and Pubs. Each time I install Windows and SQL
> Server, I notice that EM considerably becomes slow after 1 or 2 months.
> This is what all my colleagues complain about. Is it normal?! Is there any
> solution?
> Defragging the disk did not help.
> Any help would be greatly appreciated.
> Leila
>
Fix your clock.
David
|||Check if ODBC tracing is turned on.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Leila" <Leilas@.hotpop.com> wrote in message news:urPZ8AOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have no large database on my notebook. The only databases that I use for
> my tests are Northwind and Pubs. Each time I install Windows and SQL Server,
> I notice that EM considerably becomes slow after 1 or 2 months. This is what
> all my colleagues complain about. Is it normal?! Is there any solution?
> Defragging the disk did not help.
> Any help would be greatly appreciated.
> Leila
>
|||Thanks Tibor,
But this is off. Don't you really have this problem?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u88pJVOWGHA.4768@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Check if ODBC tracing is turned on.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:urPZ8AOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
|||Thanks David,
But I didn't get it!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eccxuEOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:urPZ8AOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Fix your clock.
> David
>
|||Didn't get what? David was NOT addressing your issue of EM performance.
Both David and Aaron have asked you to fix your system clock, which is
causing your posts to be marked with tomorrow's date. Some people do this so
that their posts will show up at the top of the list for longer, and it is
considered very rude. Many very knowledgeable people on these forums will
refuse to answer posts that are post-dated like this. So do yourself a
favor, and make sure your system clock is correct.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:OBJ6AHPWGHA.1192@.TK2MSFTNGP04.phx.gbl...
> Thanks David,
> But I didn't get it!
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:eccxuEOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
>
|||Leila, do you know where your taskbar is? Do you see a clock there? Can
you please set the proper date?

> Thanks Tibor,
> But this is off. Don't you really have this problem?
|||Leila (Leilas@.hotpop.com) writes:
> Date: Thu, 6 Apr 2006 22:08:56 +0430
That moment in time has not occurred yet. That's why people is yelling
at you to fix the clock.

> Thanks Tibor,
> But this is off. Don't you really have this problem?
The other standard thing to look out for is databases that are set to
autoclose. That can be a major peformance hog, if your are not sysadmin,
as EM needs to open each database to check the access rights.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Oh... thanks indeed Kalen!
Honestly I was working on point-in-time recovery and I used the clock to
move the time forward when modifying the data! I did not need to check the
calendar on my notebook (I use my watch!) to fix it since few days ago :-)
I must check if I have made any mistakes on some data entry or timestamps in
my word documents!!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O32lhMPWGHA.4920@.TK2MSFTNGP02.phx.gbl...
> Didn't get what? David was NOT addressing your issue of EM performance.
> Both David and Aaron have asked you to fix your system clock, which is
> causing your posts to be marked with tomorrow's date. Some people do this
> so that their posts will show up at the top of the list for longer, and it
> is considered very rude. Many very knowledgeable people on these forums
> will refuse to answer posts that are post-dated like this. So do yourself
> a favor, and make sure your system clock is correct.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OBJ6AHPWGHA.1192@.TK2MSFTNGP04.phx.gbl...
>
|||Oh... thanks indeed Kalen!
Honestly I was working on point-in-time recovery and I used the clock to
move the time forward when modifying the data! I did not need to check the
calendar on my notebook (I use my watch!) to fix it since few days ago :-)
I must check if I have made any mistakes on some data entries (with default
date) or timestamps in
my word documents!!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O32lhMPWGHA.4920@.TK2MSFTNGP02.phx.gbl...
> Didn't get what? David was NOT addressing your issue of EM performance.
> Both David and Aaron have asked you to fix your system clock, which is
> causing your posts to be marked with tomorrow's date. Some people do this
> so that their posts will show up at the top of the list for longer, and it
> is considered very rude. Many very knowledgeable people on these forums
> will refuse to answer posts that are post-dated like this. So do yourself
> a favor, and make sure your system clock is correct.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OBJ6AHPWGHA.1192@.TK2MSFTNGP04.phx.gbl...
>

Performance of EM

Hi,
I have no large database on my notebook. The only databases that I use for
my tests are Northwind and Pubs. Each time I install Windows and SQL Server,
I notice that EM considerably becomes slow after 1 or 2 months. This is what
all my colleagues complain about. Is it normal?! Is there any solution?
Defragging the disk did not help.
Any help would be greatly appreciated.
Leila"Leila" <Leilas@.hotpop.com> wrote in message
news:urPZ8AOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have no large database on my notebook. The only databases that I use for
> my tests are Northwind and Pubs. Each time I install Windows and SQL
> Server, I notice that EM considerably becomes slow after 1 or 2 months.
> This is what all my colleagues complain about. Is it normal?! Is there any
> solution?
> Defragging the disk did not help.
> Any help would be greatly appreciated.
> Leila
>
Fix your clock.
David|||Check if ODBC tracing is turned on.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Leila" <Leilas@.hotpop.com> wrote in message news:urPZ8AOWGHA.1192@.TK2MSFTNGP03.phx.gbl...[
color=darkred]
> Hi,
> I have no large database on my notebook. The only databases that I use for
> my tests are Northwind and Pubs. Each time I install Windows and SQL Serve
r,
> I notice that EM considerably becomes slow after 1 or 2 months. This is wh
at
> all my colleagues complain about. Is it normal?! Is there any solution?
> Defragging the disk did not help.
> Any help would be greatly appreciated.
> Leila
>[/color]|||Thanks Tibor,
But this is off. Don't you really have this problem?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u88pJVOWGHA.4768@.TK2MSFTNGP05.phx.gbl...
> Check if ODBC tracing is turned on.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:urPZ8AOWGHA.1192@.TK2MSFTNGP03.phx.gbl...|||Thanks David,
But I didn't get it!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eccxuEOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:urPZ8AOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Fix your clock.
> David
>|||Didn't get what? David was NOT addressing your issue of EM performance.
Both David and Aaron have asked you to fix your system clock, which is
causing your posts to be marked with tomorrow's date. Some people do this so
that their posts will show up at the top of the list for longer, and it is
considered very rude. Many very knowledgeable people on these forums will
refuse to answer posts that are post-dated like this. So do yourself a
favor, and make sure your system clock is correct.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:OBJ6AHPWGHA.1192@.TK2MSFTNGP04.phx.gbl...
> Thanks David,
> But I didn't get it!
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:eccxuEOWGHA.1192@.TK2MSFTNGP03.phx.gbl...
>|||Leila, do you know where your taskbar is? Do you see a clock there? Can
you please set the proper date?

> Thanks Tibor,
> But this is off. Don't you really have this problem?|||Leila (Leilas@.hotpop.com) writes:
> Date: Thu, 6 Apr 2006 22:08:56 +0430
That moment in time has not occurred yet. That's why people is yelling
at you to fix the clock.

> Thanks Tibor,
> But this is off. Don't you really have this problem?
The other standard thing to look out for is databases that are set to
autoclose. That can be a major peformance hog, if your are not symin,
as EM needs to open each database to check the access rights.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Oh... thanks indeed Kalen!
Honestly I was working on point-in-time recovery and I used the clock to
move the time forward when modifying the data! I did not need to check the
calendar on my notebook (I use my watch!) to fix it since few days ago :-)
I must check if I have made any mistakes on some data entry or timestamps in
my word documents!!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O32lhMPWGHA.4920@.TK2MSFTNGP02.phx.gbl...
> Didn't get what? David was NOT addressing your issue of EM performance.
> Both David and Aaron have asked you to fix your system clock, which is
> causing your posts to be marked with tomorrow's date. Some people do this
> so that their posts will show up at the top of the list for longer, and it
> is considered very rude. Many very knowledgeable people on these forums
> will refuse to answer posts that are post-dated like this. So do yourself
> a favor, and make sure your system clock is correct.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OBJ6AHPWGHA.1192@.TK2MSFTNGP04.phx.gbl...
>|||Oh... thanks indeed Kalen!
Honestly I was working on point-in-time recovery and I used the clock to
move the time forward when modifying the data! I did not need to check the
calendar on my notebook (I use my watch!) to fix it since few days ago :-)
I must check if I have made any mistakes on some data entries (with default
date) or timestamps in
my word documents!!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O32lhMPWGHA.4920@.TK2MSFTNGP02.phx.gbl...
> Didn't get what? David was NOT addressing your issue of EM performance.
> Both David and Aaron have asked you to fix your system clock, which is
> causing your posts to be marked with tomorrow's date. Some people do this
> so that their posts will show up at the top of the list for longer, and it
> is considered very rude. Many very knowledgeable people on these forums
> will refuse to answer posts that are post-dated like this. So do yourself
> a favor, and make sure your system clock is correct.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OBJ6AHPWGHA.1192@.TK2MSFTNGP04.phx.gbl...
>