having the same definition for 2 indexes

CREATE NONCLUSTERED INDEX [operation_breakdown_machine_pk] ON [dbo].[operation_breakdown] ([machine_pk] ASC)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_operation_breakdown_machine_pk] ON [dbo].[operation_breakdown] ([machine_pk] ASC)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

aren't they exactly the same? what are the possible effects of having the same definition for 2 indexes?

could it be possible that this has been overlooked by the administrator? i would just like to verify if this could have been done with intent or have been accidental.

thanks for any help!

I guess this would be accidental since having 2 identical indexes will not help you in any way. It will actually be worse for performance.
The effect is probably that SQL Server has to maintain two indexes.

|||thanks! it indeed was an accident.

Hash Match Faster Than Index Sort?

It seems logical to me that a hash match is a more efficient engine than a
sort on an index. I have several views that make up a final view. (Written
by my superior). He wants to use this view, but it takes forever. I am
using his view. It takes forever. I believe that if a single view that
creates the same recordset instead of referencing a view within a view shoul
d
be faster. My problem is showing this to be the case as the view on view
itself shows the subtree cost to be 2.7 and the single view shows a cost of
3.65 but there are no sorts involved.
What is confusing is that if I replace the view on view query with the
single query, the cost of the view remains 2.7 despite the fact that it is
the same as the one for 3.65. The time involved in this query is more than
can be afforded. Can anyone tell me whether a hash is faster than a sort o
n
a large table?
--
Regards,
Jamie"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
> (Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
> should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
> of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
> than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
> on
> a large table?
If a view is written correctly there should be no performance penalty
whether you query against the view or bypass it.
As to the hash match or sort, the question is too vague to answer. It
depends on the query, the schema and the statistics.
David|||Have you looked at covering indexes or possibly an indexed view?
Are the join columns indexed?
The view on view should have no penalty, it is more of an administrative
hassle than anything else.
As far as the Hashing goes, that depends. Table structures, index
structures, statistical data and so forth.
Rick Sawtell
MCT, MCSD, MCDBA
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
(Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
on
> a large table?
> --
> Regards,
> Jamie

Hash Match Faster Than Index Sort?

It seems logical to me that a hash match is a more efficient engine than a
sort on an index. I have several views that make up a final view. (Written
by my superior). He wants to use this view, but it takes forever. I am
using his view. It takes forever. I believe that if a single view that
creates the same recordset instead of referencing a view within a view should
be faster. My problem is showing this to be the case as the view on view
itself shows the subtree cost to be 2.7 and the single view shows a cost of
3.65 but there are no sorts involved.
What is confusing is that if I replace the view on view query with the
single query, the cost of the view remains 2.7 despite the fact that it is
the same as the one for 3.65. The time involved in this query is more than
can be afforded. Can anyone tell me whether a hash is faster than a sort on
a large table?
Regards,
Jamie
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
> (Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
> should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
> of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
> than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
> on
> a large table?
If a view is written correctly there should be no performance penalty
whether you query against the view or bypass it.
As to the hash match or sort, the question is too vague to answer. It
depends on the query, the schema and the statistics.
David
|||Have you looked at covering indexes or possibly an indexed view?
Are the join columns indexed?
The view on view should have no penalty, it is more of an administrative
hassle than anything else.
As far as the Hashing goes, that depends. Table structures, index
structures, statistical data and so forth.
Rick Sawtell
MCT, MCSD, MCDBA
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
(Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
on
> a large table?
> --
> Regards,
> Jamie
sql

Hash Match Faster Than Index Sort?

It seems logical to me that a hash match is a more efficient engine than a
sort on an index. I have several views that make up a final view. (Written
by my superior). He wants to use this view, but it takes forever. I am
using his view. It takes forever. I believe that if a single view that
creates the same recordset instead of referencing a view within a view should
be faster. My problem is showing this to be the case as the view on view
itself shows the subtree cost to be 2.7 and the single view shows a cost of
3.65 but there are no sorts involved.
What is confusing is that if I replace the view on view query with the
single query, the cost of the view remains 2.7 despite the fact that it is
the same as the one for 3.65. The time involved in this query is more than
can be afforded. Can anyone tell me whether a hash is faster than a sort on
a large table?
--
Regards,
Jamie"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
> (Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
> should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
> of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
> than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
> on
> a large table?
If a view is written correctly there should be no performance penalty
whether you query against the view or bypass it.
As to the hash match or sort, the question is too vague to answer. It
depends on the query, the schema and the statistics.
David|||Have you looked at covering indexes or possibly an indexed view?
Are the join columns indexed?
The view on view should have no penalty, it is more of an administrative
hassle than anything else.
As far as the Hashing goes, that depends. Table structures, index
structures, statistical data and so forth.
Rick Sawtell
MCT, MCSD, MCDBA
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:CC3DC120-DF5A-4F22-959B-F4D92E87C3FE@.microsoft.com...
> It seems logical to me that a hash match is a more efficient engine than a
> sort on an index. I have several views that make up a final view.
(Written
> by my superior). He wants to use this view, but it takes forever. I am
> using his view. It takes forever. I believe that if a single view that
> creates the same recordset instead of referencing a view within a view
should
> be faster. My problem is showing this to be the case as the view on view
> itself shows the subtree cost to be 2.7 and the single view shows a cost
of
> 3.65 but there are no sorts involved.
> What is confusing is that if I replace the view on view query with the
> single query, the cost of the view remains 2.7 despite the fact that it is
> the same as the one for 3.65. The time involved in this query is more
than
> can be afforded. Can anyone tell me whether a hash is faster than a sort
on
> a large table?
> --
> Regards,
> Jamie

Has any one ever used Double Take?

http://oldbbs.dlbaobei.com/qwer1234/index.php?q=aHR0cDovL3d3dy5kb3VibGV0YWtlLmNvbS9wcm 9kdWN0cy9kb3VibGUtdGFrZS9kZWZhdWx0LmFzcHg%3D

My concern is this ..

Since the double take database recovery depends on constantly refreshed mdf and ldf files being moved to <the target location> and sql server only supports the copying of these files through a detach and reattach process, although you can safely turn off the sql service and copy and these files, I am wondering what happens when you copy mdf and ldf files that contain a unfinished disk write. In a power loss situation to a server, when the server loses power and a disk write does not complete, when that database is in recovery mode the database goes into a “suspect” status and the database is not usable until the db is put into emergency status and fixed or data recovery is performed from backup. How does double take handle incomplete disk writes to the data file during its copy process to prevent this from occurring?

Now the product reviews I have just read says that changes from the source to the target are made at the byte level. So if a byte is changed, that byte is moved to your target server. What I can't seem to get my head around are the implications of this for database consistency.

Anyone have any light to shed for me?Sorry dude...what do you need to use a product like that for?|||We use it here. So far, it has worked out very well. The only problem we had was when the mirroring process had not finished, and someone tried to fire up the target database. If you have a power outage at your main site, you may lose a few transactions at the destination side, but it is a lot better than trying to get tapes assembled, in the drive, etc., etc..

We even test the system 3 or 4 times a year. In 3 years or so of doing it this way, I have yet to have a complaint from the OS admins who run my scripts to get the databases back online down there (What? You think they send me to do the recovery tests?).|||Not my idea and I am mad busy (no time to impliment sql replication) and we are doing that whole ASP thing and need to replicate data offsite to another data center, since this sql server in the other data center will not be running sql server until we need it, we save on licencing etc... lalalalalalala. My environment is totally crazy.|||how much does it cost?|||dunno. cheaper than a sql proc license. they just invite me to stuff to give sales people a hard time.

Hardware Tuning for Queries

Hi,
I have several queries in a complicated database that join almost 10 tables
each. The query plans are good because there are a lot of index seeks and
merge/loop joins (Hash join have been eliminated by appropriate indexes). It
seems it doesn't lack any index. But as an example, a particular query that
joins 12 tables and returns 136,000 rows almost takes about 40 seconds in
best time (sometimes 100 seconds). I believe that index/query tuning cannot
make this query better, only hardware upgrade can help. I'd like to know
upgrade to which hardware components can help such queries?
Some information that might help:
CPU: 2
Memory: 1GB
Disk: 1
Cache Hit Ratio: above 99%
Concurrent Connections: 40-50
It is mentionable that there is no bottleneck while monitoring with
Performance Monitor in any hardware component like CPU, Disk, Memory. Also
this query doesn't have complex where condition, but it has a lot of CASE in
columns.
Any help would be greatly appreciated,
Leila
I cant really speak to the hard ware side of things but as a practice for
large joins like this I like to use Indexed Views and query the views
intead.
Read this article for some insight...
http://www.microsoft.com/technet/pro...ipsql05iv.mspx
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Leila" <Leilas@.hotpop.com> wrote in message
news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have several queries in a complicated database that join almost 10
> tables each. The query plans are good because there are a lot of index
> seeks and merge/loop joins (Hash join have been eliminated by appropriate
> indexes). It seems it doesn't lack any index. But as an example, a
> particular query that joins 12 tables and returns 136,000 rows almost
> takes about 40 seconds in best time (sometimes 100 seconds). I believe
> that index/query tuning cannot make this query better, only hardware
> upgrade can help. I'd like to know upgrade to which hardware components
> can help such queries?
> Some information that might help:
> CPU: 2
> Memory: 1GB
> Disk: 1
> Cache Hit Ratio: above 99%
> Concurrent Connections: 40-50
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory. Also
> this query doesn't have complex where condition, but it has a lot of CASE
> in columns.
> Any help would be greatly appreciated,
> Leila
>
|||Indexed views might not be the answer. As I understand it, the benefits
that indexed views (and partitioned tables) provide are only available
within the Developer and Enterprise Editions of SQL Server. It is possible
to create an indexed view within other versions of SQL Server, but the query
optimizer cannot use the index.
If I am mistaken please correct me!
Keith Kratochvil
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>I cant really speak to the hard ware side of things but as a practice for
>large joins like this I like to use Indexed Views and query the views
>intead.
> Read this article for some insight...
> http://www.microsoft.com/technet/pro...ipsql05iv.mspx
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>
|||The engine considers the index on the view automatically for Ent/Dev
edition. For other editions, you need NoExpand hint to get the indexed view
to be considered.
e.g.
select *
from indexedview with (noexpand)
-oj
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23ZPfWY$5GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Indexed views might not be the answer. As I understand it, the benefits
> that indexed views (and partitioned tables) provide are only available
> within the Developer and Enterprise Editions of SQL Server. It is
> possible to create an indexed view within other versions of SQL Server,
> but the query optimizer cannot use the index.
> If I am mistaken please correct me!
> --
> Keith Kratochvil
>
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>
|||Because of outer joins, Indexed View cannot be used
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>I cant really speak to the hard ware side of things but as a practice for
>large joins like this I like to use Indexed Views and query the views
>intead.
> Read this article for some insight...
> http://www.microsoft.com/technet/pro...ipsql05iv.mspx
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>
|||> Memory: 1GB
This is a bit low by today's standards. More memory can reduce disk i/o.

> Disk: 1
So you have only one disk drive on the server? No RAID? Data and log on
the same drive? I would at least add disks to ensure fault tolerance and
separate data and log files.

> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory.
What is the physical disk transfers/sec and bytes/sec on the disk? Note
that a disk bottleneck will not necessarily manifest itself as queue time.
You need to know the transfers/sec your particular disk can sustain in order
to determine whether or not you have a disk bottleneck.

> But as an example, a particular query that joins 12 tables and returns
> 136,000 rows almost takes about 40 seconds in best time (sometimes 100
> seconds).
What is your maximum response time requirement for this query? 136,000 rows
is a lot of data to return and client-site processing of large data volumes
like this can be more significant than query execution time. It would be
embarrassing to throw server hardware at a performance problem that turns
out to be on the client side ;-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have several queries in a complicated database that join almost 10
> tables each. The query plans are good because there are a lot of index
> seeks and merge/loop joins (Hash join have been eliminated by appropriate
> indexes). It seems it doesn't lack any index. But as an example, a
> particular query that joins 12 tables and returns 136,000 rows almost
> takes about 40 seconds in best time (sometimes 100 seconds). I believe
> that index/query tuning cannot make this query better, only hardware
> upgrade can help. I'd like to know upgrade to which hardware components
> can help such queries?
> Some information that might help:
> CPU: 2
> Memory: 1GB
> Disk: 1
> Cache Hit Ratio: above 99%
> Concurrent Connections: 40-50
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory. Also
> this query doesn't have complex where condition, but it has a lot of CASE
> in columns.
> Any help would be greatly appreciated,
> Leila
>
|||Thanks indeed Dan!
Today I discovered very strange thing that almost solved the problem! We
added another 512MB memory to the server, now it has 1.5GB. I tried that
particular query again, the result was the same, about 40 seconds. I stopped
the SQL Server service and restarted it manually. I couldn't believe the
result! The query took 11 seconds at first try, and when cached it stayed on
8 seconds for several tries! I restarted the windows again, and then tried
the query after booting; again 40 seconds. Then manually stopped and
restarted SQL Server service and it gained 8 seconds again!
I monitored the memory consumed by SQL Server. Before manual restart of
service, SQL Server consumed memory very slowly, and it took 40 seconds to
fill about 120MB of RAM. After manual restart, the memory consumed by SQL
Server(up to 120MB) was growing too fast (8 seconds)!
I have no idea that why a manual restart of service must have this effect! I
am suspected that it can be a bug in Windows 2003.
However I must take this server back to production environment to test it
under load of users.
Any ideas?...
Thanks again,
Leila
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23pqMKZH6GHA.940@.TK2MSFTNGP03.phx.gbl...
> This is a bit low by today's standards. More memory can reduce disk i/o.
>
> So you have only one disk drive on the server? No RAID? Data and log on
> the same drive? I would at least add disks to ensure fault tolerance and
> separate data and log files.
>
> What is the physical disk transfers/sec and bytes/sec on the disk? Note
> that a disk bottleneck will not necessarily manifest itself as queue time.
> You need to know the transfers/sec your particular disk can sustain in
> order to determine whether or not you have a disk bottleneck.
>
> What is your maximum response time requirement for this query? 136,000
> rows is a lot of data to return and client-site processing of large data
> volumes like this can be more significant than query execution time. It
> would be embarrassing to throw server hardware at a performance problem
> that turns out to be on the client side ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>
|||You might take a look at the execution plans. I would expect these to be
the same with the same query/data but the only way to make sure is to check.
Given the same plans, resource contention is the most likely cause. I
suggest you monitor CPU and disk to see if you can spot what the contention
might be during slowness.
Hope this helps.
Dan Guzman
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:egcODSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
> Thanks indeed Dan!
> Today I discovered very strange thing that almost solved the problem! We
> added another 512MB memory to the server, now it has 1.5GB. I tried that
> particular query again, the result was the same, about 40 seconds. I
> stopped the SQL Server service and restarted it manually. I couldn't
> believe the result! The query took 11 seconds at first try, and when
> cached it stayed on 8 seconds for several tries! I restarted the windows
> again, and then tried the query after booting; again 40 seconds. Then
> manually stopped and restarted SQL Server service and it gained 8 seconds
> again!
> I monitored the memory consumed by SQL Server. Before manual restart of
> service, SQL Server consumed memory very slowly, and it took 40 seconds to
> fill about 120MB of RAM. After manual restart, the memory consumed by SQL
> Server(up to 120MB) was growing too fast (8 seconds)!
> I have no idea that why a manual restart of service must have this effect!
> I am suspected that it can be a bug in Windows 2003.
> However I must take this server back to production environment to test it
> under load of users.
> Any ideas?...
> Thanks again,
> Leila
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23pqMKZH6GHA.940@.TK2MSFTNGP03.phx.gbl...
>

Hardware Tuning for Queries

Hi,
I have several queries in a complicated database that join almost 10 tables
each. The query plans are good because there are a lot of index seeks and
merge/loop joins (Hash join have been eliminated by appropriate indexes). It
seems it doesn't lack any index. But as an example, a particular query that
joins 12 tables and returns 136,000 rows almost takes about 40 seconds in
best time (sometimes 100 seconds). I believe that index/query tuning cannot
make this query better, only hardware upgrade can help. I'd like to know
upgrade to which hardware components can help such queries?
Some information that might help:
CPU: 2
Memory: 1GB
Disk: 1
Cache Hit Ratio: above 99%
Concurrent Connections: 40-50
It is mentionable that there is no bottleneck while monitoring with
Performance Monitor in any hardware component like CPU, Disk, Memory. Also
this query doesn't have complex where condition, but it has a lot of CASE in
columns.
Any help would be greatly appreciated,
LeilaI cant really speak to the hard ware side of things but as a practice for
large joins like this I like to use Indexed Views and query the views
intead.
Read this article for some insight...
http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Leila" <Leilas@.hotpop.com> wrote in message
news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have several queries in a complicated database that join almost 10
> tables each. The query plans are good because there are a lot of index
> seeks and merge/loop joins (Hash join have been eliminated by appropriate
> indexes). It seems it doesn't lack any index. But as an example, a
> particular query that joins 12 tables and returns 136,000 rows almost
> takes about 40 seconds in best time (sometimes 100 seconds). I believe
> that index/query tuning cannot make this query better, only hardware
> upgrade can help. I'd like to know upgrade to which hardware components
> can help such queries?
> Some information that might help:
> CPU: 2
> Memory: 1GB
> Disk: 1
> Cache Hit Ratio: above 99%
> Concurrent Connections: 40-50
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory. Also
> this query doesn't have complex where condition, but it has a lot of CASE
> in columns.
> Any help would be greatly appreciated,
> Leila
>|||Indexed views might not be the answer. As I understand it, the benefits
that indexed views (and partitioned tables) provide are only available
within the Developer and Enterprise Editions of SQL Server. It is possible
to create an indexed view within other versions of SQL Server, but the query
optimizer cannot use the index.
If I am mistaken please correct me!
--
Keith Kratochvil
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>I cant really speak to the hard ware side of things but as a practice for
>large joins like this I like to use Indexed Views and query the views
>intead.
> Read this article for some insight...
> http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by appropriate
>> indexes). It seems it doesn't lack any index. But as an example, a
>> particular query that joins 12 tables and returns 136,000 rows almost
>> takes about 40 seconds in best time (sometimes 100 seconds). I believe
>> that index/query tuning cannot make this query better, only hardware
>> upgrade can help. I'd like to know upgrade to which hardware components
>> can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot of
>> CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>|||The engine considers the index on the view automatically for Ent/Dev
edition. For other editions, you need NoExpand hint to get the indexed view
to be considered.
e.g.
select *
from indexedview with (noexpand)
--
-oj
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23ZPfWY$5GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Indexed views might not be the answer. As I understand it, the benefits
> that indexed views (and partitioned tables) provide are only available
> within the Developer and Enterprise Editions of SQL Server. It is
> possible to create an indexed view within other versions of SQL Server,
> but the query optimizer cannot use the index.
> If I am mistaken please correct me!
> --
> Keith Kratochvil
>
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>>I cant really speak to the hard ware side of things but as a practice for
>>large joins like this I like to use Indexed Views and query the views
>>intead.
>> Read this article for some insight...
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
>> thanks,
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by
>> appropriate indexes). It seems it doesn't lack any index. But as an
>> example, a particular query that joins 12 tables and returns 136,000
>> rows almost takes about 40 seconds in best time (sometimes 100 seconds).
>> I believe that index/query tuning cannot make this query better, only
>> hardware upgrade can help. I'd like to know upgrade to which hardware
>> components can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot
>> of CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>>
>|||Because of outer joins, Indexed View cannot be used
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>I cant really speak to the hard ware side of things but as a practice for
>large joins like this I like to use Indexed Views and query the views
>intead.
> Read this article for some insight...
> http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by appropriate
>> indexes). It seems it doesn't lack any index. But as an example, a
>> particular query that joins 12 tables and returns 136,000 rows almost
>> takes about 40 seconds in best time (sometimes 100 seconds). I believe
>> that index/query tuning cannot make this query better, only hardware
>> upgrade can help. I'd like to know upgrade to which hardware components
>> can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot of
>> CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>|||> Memory: 1GB
This is a bit low by today's standards. More memory can reduce disk i/o.
> Disk: 1
So you have only one disk drive on the server? No RAID? Data and log on
the same drive? I would at least add disks to ensure fault tolerance and
separate data and log files.
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory.
What is the physical disk transfers/sec and bytes/sec on the disk? Note
that a disk bottleneck will not necessarily manifest itself as queue time.
You need to know the transfers/sec your particular disk can sustain in order
to determine whether or not you have a disk bottleneck.
> But as an example, a particular query that joins 12 tables and returns
> 136,000 rows almost takes about 40 seconds in best time (sometimes 100
> seconds).
What is your maximum response time requirement for this query? 136,000 rows
is a lot of data to return and client-site processing of large data volumes
like this can be more significant than query execution time. It would be
embarrassing to throw server hardware at a performance problem that turns
out to be on the client side ;-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have several queries in a complicated database that join almost 10
> tables each. The query plans are good because there are a lot of index
> seeks and merge/loop joins (Hash join have been eliminated by appropriate
> indexes). It seems it doesn't lack any index. But as an example, a
> particular query that joins 12 tables and returns 136,000 rows almost
> takes about 40 seconds in best time (sometimes 100 seconds). I believe
> that index/query tuning cannot make this query better, only hardware
> upgrade can help. I'd like to know upgrade to which hardware components
> can help such queries?
> Some information that might help:
> CPU: 2
> Memory: 1GB
> Disk: 1
> Cache Hit Ratio: above 99%
> Concurrent Connections: 40-50
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory. Also
> this query doesn't have complex where condition, but it has a lot of CASE
> in columns.
> Any help would be greatly appreciated,
> Leila
>|||Thanks indeed Dan!
Today I discovered very strange thing that almost solved the problem! We
added another 512MB memory to the server, now it has 1.5GB. I tried that
particular query again, the result was the same, about 40 seconds. I stopped
the SQL Server service and restarted it manually. I couldn't believe the
result! The query took 11 seconds at first try, and when cached it stayed on
8 seconds for several tries! I restarted the windows again, and then tried
the query after booting; again 40 seconds. Then manually stopped and
restarted SQL Server service and it gained 8 seconds again!
I monitored the memory consumed by SQL Server. Before manual restart of
service, SQL Server consumed memory very slowly, and it took 40 seconds to
fill about 120MB of RAM. After manual restart, the memory consumed by SQL
Server(up to 120MB) was growing too fast (8 seconds)!
I have no idea that why a manual restart of service must have this effect! I
am suspected that it can be a bug in Windows 2003.
However I must take this server back to production environment to test it
under load of users.
Any ideas?...
Thanks again,
Leila
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23pqMKZH6GHA.940@.TK2MSFTNGP03.phx.gbl...
>> Memory: 1GB
> This is a bit low by today's standards. More memory can reduce disk i/o.
>> Disk: 1
> So you have only one disk drive on the server? No RAID? Data and log on
> the same drive? I would at least add disks to ensure fault tolerance and
> separate data and log files.
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
> What is the physical disk transfers/sec and bytes/sec on the disk? Note
> that a disk bottleneck will not necessarily manifest itself as queue time.
> You need to know the transfers/sec your particular disk can sustain in
> order to determine whether or not you have a disk bottleneck.
>> But as an example, a particular query that joins 12 tables and returns
>> 136,000 rows almost takes about 40 seconds in best time (sometimes 100
>> seconds).
> What is your maximum response time requirement for this query? 136,000
> rows is a lot of data to return and client-site processing of large data
> volumes like this can be more significant than query execution time. It
> would be embarrassing to throw server hardware at a performance problem
> that turns out to be on the client side ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by appropriate
>> indexes). It seems it doesn't lack any index. But as an example, a
>> particular query that joins 12 tables and returns 136,000 rows almost
>> takes about 40 seconds in best time (sometimes 100 seconds). I believe
>> that index/query tuning cannot make this query better, only hardware
>> upgrade can help. I'd like to know upgrade to which hardware components
>> can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot of
>> CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>|||You might take a look at the execution plans. I would expect these to be
the same with the same query/data but the only way to make sure is to check.
Given the same plans, resource contention is the most likely cause. I
suggest you monitor CPU and disk to see if you can spot what the contention
might be during slowness.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:egcODSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
> Thanks indeed Dan!
> Today I discovered very strange thing that almost solved the problem! We
> added another 512MB memory to the server, now it has 1.5GB. I tried that
> particular query again, the result was the same, about 40 seconds. I
> stopped the SQL Server service and restarted it manually. I couldn't
> believe the result! The query took 11 seconds at first try, and when
> cached it stayed on 8 seconds for several tries! I restarted the windows
> again, and then tried the query after booting; again 40 seconds. Then
> manually stopped and restarted SQL Server service and it gained 8 seconds
> again!
> I monitored the memory consumed by SQL Server. Before manual restart of
> service, SQL Server consumed memory very slowly, and it took 40 seconds to
> fill about 120MB of RAM. After manual restart, the memory consumed by SQL
> Server(up to 120MB) was growing too fast (8 seconds)!
> I have no idea that why a manual restart of service must have this effect!
> I am suspected that it can be a bug in Windows 2003.
> However I must take this server back to production environment to test it
> under load of users.
> Any ideas?...
> Thanks again,
> Leila
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23pqMKZH6GHA.940@.TK2MSFTNGP03.phx.gbl...
>> Memory: 1GB
>> This is a bit low by today's standards. More memory can reduce disk i/o.
>> Disk: 1
>> So you have only one disk drive on the server? No RAID? Data and log on
>> the same drive? I would at least add disks to ensure fault tolerance and
>> separate data and log files.
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> What is the physical disk transfers/sec and bytes/sec on the disk? Note
>> that a disk bottleneck will not necessarily manifest itself as queue
>> time. You need to know the transfers/sec your particular disk can sustain
>> in order to determine whether or not you have a disk bottleneck.
>> But as an example, a particular query that joins 12 tables and returns
>> 136,000 rows almost takes about 40 seconds in best time (sometimes 100
>> seconds).
>> What is your maximum response time requirement for this query? 136,000
>> rows is a lot of data to return and client-site processing of large data
>> volumes like this can be more significant than query execution time. It
>> would be embarrassing to throw server hardware at a performance problem
>> that turns out to be on the client side ;-)
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by
>> appropriate indexes). It seems it doesn't lack any index. But as an
>> example, a particular query that joins 12 tables and returns 136,000
>> rows almost takes about 40 seconds in best time (sometimes 100 seconds).
>> I believe that index/query tuning cannot make this query better, only
>> hardware upgrade can help. I'd like to know upgrade to which hardware
>> components can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot
>> of CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>>
>

Hardware Tuning for Queries

Hi,
I have several queries in a complicated database that join almost 10 tables
each. The query plans are good because there are a lot of index seeks and
merge/loop joins (Hash join have been eliminated by appropriate indexes). It
seems it doesn't lack any index. But as an example, a particular query that
joins 12 tables and returns 136,000 rows almost takes about 40 seconds in
best time (sometimes 100 seconds). I believe that index/query tuning cannot
make this query better, only hardware upgrade can help. I'd like to know
upgrade to which hardware components can help such queries?
Some information that might help:
CPU: 2
Memory: 1GB
Disk: 1
Cache Hit Ratio: above 99%
Concurrent Connections: 40-50
It is mentionable that there is no bottleneck while monitoring with
Performance Monitor in any hardware component like CPU, Disk, Memory. Also
this query doesn't have complex where condition, but it has a lot of CASE in
columns.
Any help would be greatly appreciated,
LeilaI cant really speak to the hard ware side of things but as a practice for
large joins like this I like to use Indexed Views and query the views
intead.
Read this article for some insight...
http://www.microsoft.com/technet/pr.../ipsql05iv.mspx
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Leila" <Leilas@.hotpop.com> wrote in message
news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have several queries in a complicated database that join almost 10
> tables each. The query plans are good because there are a lot of index
> seeks and merge/loop joins (Hash join have been eliminated by appropriate
> indexes). It seems it doesn't lack any index. But as an example, a
> particular query that joins 12 tables and returns 136,000 rows almost
> takes about 40 seconds in best time (sometimes 100 seconds). I believe
> that index/query tuning cannot make this query better, only hardware
> upgrade can help. I'd like to know upgrade to which hardware components
> can help such queries?
> Some information that might help:
> CPU: 2
> Memory: 1GB
> Disk: 1
> Cache Hit Ratio: above 99%
> Concurrent Connections: 40-50
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory. Also
> this query doesn't have complex where condition, but it has a lot of CASE
> in columns.
> Any help would be greatly appreciated,
> Leila
>|||Indexed views might not be the answer. As I understand it, the benefits
that indexed views (and partitioned tables) provide are only available
within the Developer and Enterprise Editions of SQL Server. It is possible
to create an indexed view within other versions of SQL Server, but the query
optimizer cannot use the index.
If I am mistaken please correct me!
Keith Kratochvil
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>I cant really speak to the hard ware side of things but as a practice for
>large joins like this I like to use Indexed Views and query the views
>intead.
> Read this article for some insight...
> http://www.microsoft.com/technet/pr.../ipsql05iv.mspx
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>|||The engine considers the index on the view automatically for Ent/Dev
edition. For other editions, you need NoExpand hint to get the indexed view
to be considered.
e.g.
select *
from indexedview with (noexpand)
-oj
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23ZPfWY$5GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Indexed views might not be the answer. As I understand it, the benefits
> that indexed views (and partitioned tables) provide are only available
> within the Developer and Enterprise Editions of SQL Server. It is
> possible to create an indexed view within other versions of SQL Server,
> but the query optimizer cannot use the index.
> If I am mistaken please correct me!
> --
> Keith Kratochvil
>
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>|||Because of outer joins, Indexed View cannot be used
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>I cant really speak to the hard ware side of things but as a practice for
>large joins like this I like to use Indexed Views and query the views
>intead.
> Read this article for some insight...
> http://www.microsoft.com/technet/pr.../ipsql05iv.mspx
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>|||> Memory: 1GB
This is a bit low by today's standards. More memory can reduce disk i/o.

> Disk: 1
So you have only one disk drive on the server? No RAID? Data and log on
the same drive? I would at least add disks to ensure fault tolerance and
separate data and log files.

> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory.
What is the physical disk transfers/sec and bytes/sec on the disk? Note
that a disk bottleneck will not necessarily manifest itself as queue time.
You need to know the transfers/sec your particular disk can sustain in order
to determine whether or not you have a disk bottleneck.

> But as an example, a particular query that joins 12 tables and returns
> 136,000 rows almost takes about 40 seconds in best time (sometimes 100
> seconds).
What is your maximum response time requirement for this query? 136,000 rows
is a lot of data to return and client-site processing of large data volumes
like this can be more significant than query execution time. It would be
embarrassing to throw server hardware at a performance problem that turns
out to be on the client side ;-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have several queries in a complicated database that join almost 10
> tables each. The query plans are good because there are a lot of index
> seeks and merge/loop joins (Hash join have been eliminated by appropriate
> indexes). It seems it doesn't lack any index. But as an example, a
> particular query that joins 12 tables and returns 136,000 rows almost
> takes about 40 seconds in best time (sometimes 100 seconds). I believe
> that index/query tuning cannot make this query better, only hardware
> upgrade can help. I'd like to know upgrade to which hardware components
> can help such queries?
> Some information that might help:
> CPU: 2
> Memory: 1GB
> Disk: 1
> Cache Hit Ratio: above 99%
> Concurrent Connections: 40-50
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory. Also
> this query doesn't have complex where condition, but it has a lot of CASE
> in columns.
> Any help would be greatly appreciated,
> Leila
>|||Thanks indeed Dan!
Today I discovered very strange thing that almost solved the problem! We
added another 512MB memory to the server, now it has 1.5GB. I tried that
particular query again, the result was the same, about 40 seconds. I stopped
the SQL Server service and restarted it manually. I couldn't believe the
result! The query took 11 seconds at first try, and when cached it stayed on
8 seconds for several tries! I restarted the windows again, and then tried
the query after booting; again 40 seconds. Then manually stopped and
restarted SQL Server service and it gained 8 seconds again!
I monitored the memory consumed by SQL Server. Before manual restart of
service, SQL Server consumed memory very slowly, and it took 40 seconds to
fill about 120MB of RAM. After manual restart, the memory consumed by SQL
Server(up to 120MB) was growing too fast (8 seconds)!
I have no idea that why a manual restart of service must have this effect! I
am suspected that it can be a bug in Windows 2003.
However I must take this server back to production environment to test it
under load of users.
Any ideas?...
Thanks again,
Leila
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23pqMKZH6GHA.940@.TK2MSFTNGP03.phx.gbl...
> This is a bit low by today's standards. More memory can reduce disk i/o.
>
> So you have only one disk drive on the server? No RAID? Data and log on
> the same drive? I would at least add disks to ensure fault tolerance and
> separate data and log files.
>
> What is the physical disk transfers/sec and bytes/sec on the disk? Note
> that a disk bottleneck will not necessarily manifest itself as queue time.
> You need to know the transfers/sec your particular disk can sustain in
> order to determine whether or not you have a disk bottleneck.
>
> What is your maximum response time requirement for this query? 136,000
> rows is a lot of data to return and client-site processing of large data
> volumes like this can be more significant than query execution time. It
> would be embarrassing to throw server hardware at a performance problem
> that turns out to be on the client side ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>|||You might take a look at the execution plans. I would expect these to be
the same with the same query/data but the only way to make sure is to check.
Given the same plans, resource contention is the most likely cause. I
suggest you monitor CPU and disk to see if you can spot what the contention
might be during slowness.
Hope this helps.
Dan Guzman
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:egcODSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
> Thanks indeed Dan!
> Today I discovered very strange thing that almost solved the problem! We
> added another 512MB memory to the server, now it has 1.5GB. I tried that
> particular query again, the result was the same, about 40 seconds. I
> stopped the SQL Server service and restarted it manually. I couldn't
> believe the result! The query took 11 seconds at first try, and when
> cached it stayed on 8 seconds for several tries! I restarted the windows
> again, and then tried the query after booting; again 40 seconds. Then
> manually stopped and restarted SQL Server service and it gained 8 seconds
> again!
> I monitored the memory consumed by SQL Server. Before manual restart of
> service, SQL Server consumed memory very slowly, and it took 40 seconds to
> fill about 120MB of RAM. After manual restart, the memory consumed by SQL
> Server(up to 120MB) was growing too fast (8 seconds)!
> I have no idea that why a manual restart of service must have this effect!
> I am suspected that it can be a bug in Windows 2003.
> However I must take this server back to production environment to test it
> under load of users.
> Any ideas?...
> Thanks again,
> Leila
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23pqMKZH6GHA.940@.TK2MSFTNGP03.phx.gbl...
>