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
>>
>