SQL Server 2000 EE SP3a.
Through PerfMon the sql server specific perfomance
counters are not displayed. I have revisited the
installation CD and per the installation SQL Server
Components/Performance Counters SubComponent are installed.
Any Ideas on how I can see these counters. This is a
production machine so all maintenance is late night.
TIA,hi,
take a look at this
article
http://support.microsoft.com/?id=152513
>--Original Message--
>SQL Server 2000 EE SP3a.
>Through PerfMon the sql server specific perfomance
>counters are not displayed. I have revisited the
>installation CD and per the installation SQL Server
>Components/Performance Counters SubComponent are
installed.
>Any Ideas on how I can see these counters. This is a
>production machine so all maintenance is late night.
>TIA,
>.
>|||I have looked at this article. Again the only counters
that do not show up are the SQL Server specific counters.
Joe
>--Original Message--
>hi,
>take a look at this
>article
>http://support.microsoft.com/?id=152513
>>--Original Message--
>>SQL Server 2000 EE SP3a.
>>Through PerfMon the sql server specific perfomance
>>counters are not displayed. I have revisited the
>>installation CD and per the installation SQL Server
>>Components/Performance Counters SubComponent are
>installed.
>>Any Ideas on how I can see these counters. This is a
>>production machine so all maintenance is late night.
>>TIA,
>>.
>.
>|||Did you restart the server before looking at the perfmon,
To get u'r counters back just restart the entire system
and look at it
>--Original Message--
>I have looked at this article. Again the only counters
>that do not show up are the SQL Server specific counters.
>Joe
>>--Original Message--
>>hi,
>>take a look at this
>>article
>>http://support.microsoft.com/?id=152513
>>--Original Message--
>>SQL Server 2000 EE SP3a.
>>Through PerfMon the sql server specific perfomance
>>counters are not displayed. I have revisited the
>>installation CD and per the installation SQL Server
>>Components/Performance Counters SubComponent are
>>installed.
>>Any Ideas on how I can see these counters. This is a
>>production machine so all maintenance is late night.
>>TIA,
>>.
>>.
>.
>
Showing posts with label perfomance. Show all posts
Showing posts with label perfomance. Show all posts
Friday, March 23, 2012
Saturday, February 25, 2012
Perfomance tuning
Dear friends
We have one problem in our existing system.We are expecting some expert comment on this.We have one corebanking system back end as MS SQL server with IIS server.Our system is always very slow in the peak times of tranasactions.We are planning to optimize this with a short time plan .So pls give some suggestions that our DBA team can implement in a short time with SQL SERVER 2000
Thanks in Advance
Filson
A possible course of action is this:
Try to check your IIS's and SQL's processor during the peak times to see if the problem is with your SQL or IIS
If your problem is not in IIS:
Try to see if your sql server's processor is 100% or near. If it is, then your queries are probably too heavy - if it is not, then you possibly have locking problems.
Try to find if there are specific queries that are taking longer. You could use Sql Profiler for this, by specifying a duration minimum threshold for the events - for instance, only show queries that take more than 5 seconds (warning: this might deterioate even further your performance while the trace is active). After finding out the worse queries, try to understand why they are running slow - you could use SQL Query Analyzer for this, using the SP's execution plans.
perfomance related questions
Why would a stored procedure take longer to run on an OLTP environment
although %processor time doesnt seem to be pegged ( 30%), %disk time is
around 10% and pages/sec is 0. It takes around 10 secs to run everytime on
an average but runs faster on development boxes like around 1 sec. It has
the same exact execution plan on both environments. My question is what else
do i need to look into since it doesnt seem to be hardware bound here. Using
SQL 2000. CPU and reads in trace are also higher than in our dev
environment. but the perfmon for h/w looks decent or atleast not that bad .
Any other counters to look at or suggestionsDo you have the same amount of data on both servers? For example if you
have a cursor that runs through all the records in one table and in the
development database you have only 100 records but in the production server
you have 100000 records, then I'd exepect that the stored procedure would
take longer to run on the production server. Also if you have lots of
users that are working with the database in the production, you might have
locking problems, that slow the execution.
Adi
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eY8NNr7YDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Why would a stored procedure take longer to run on an OLTP environment
> although %processor time doesnt seem to be pegged ( 30%), %disk time is
> around 10% and pages/sec is 0. It takes around 10 secs to run everytime on
> an average but runs faster on development boxes like around 1 sec. It has
> the same exact execution plan on both environments. My question is what
else
> do i need to look into since it doesnt seem to be hardware bound here.
Using
> SQL 2000. CPU and reads in trace are also higher than in our dev
> environment. but the perfmon for h/w looks decent or atleast not that bad
.
> Any other counters to look at or suggestions
>
>|||perfmon cpu is the instantaneous cpu usage, profiler cpu
is the cumulative cpu for the query, regardless of how
long it took,
unless a query takes several cpu secs, and the difference
between profiler duration and cpu is not larger, you may
not even notice the cpu in perfmon
>--Original Message--
>Assuming the stored procedure was only selecting , why
would the CPU from
>perfmon do not show it as busy yet consume more time in
CPU in profiler to
>execute the sproc. There is no parallelism. There are
some temp tables
>..There are some nested joins.
>"joe chang" <jchang6@.yahoo.com> wrote in message
>news:000d01c36401$27d19f60$a101280a@.phx.gbl...
>> what specifically are the production and development
>> systems?
>> processor, frequency, cache, #of cpu, memory, # of
disks,
>> status of hyper-threading?
>> what is in the execution plan of your stored proc ?
>> joins - what type, loop, hash, merge, row count for each
>> table, is there a temp table involved? is a parallel
plan
>> involved? if so, disabled HT or try OPTION (MAXDOP 1)
>> sometimes the dev env is JBOD (no raid) the prod env is
>> raid5, with absolutely horrible write performance, so
>> anything that involves writes will performance much
worse
>> on the $40k production system than the $1k dev system
>> >--Original Message--
>> >Why would a stored procedure take longer to run on an
>> OLTP environment
>> >although %processor time doesnt seem to be pegged (
30%),
>> %disk time is
>> >around 10% and pages/sec is 0. It takes around 10 secs
to
>> run everytime on
>> >an average but runs faster on development boxes like
>> around 1 sec. It has
>> >the same exact execution plan on both environments. My
>> question is what else
>> >do i need to look into since it doesnt seem to be
>> hardware bound here. Using
>> >SQL 2000. CPU and reads in trace are also higher than
in
>> our dev
>> >environment. but the perfmon for h/w looks decent or
>> atleast not that bad .
>> >Any other counters to look at or suggestions
>> >
>> >
>> >
>> >.
>> >
>
>.
>
although %processor time doesnt seem to be pegged ( 30%), %disk time is
around 10% and pages/sec is 0. It takes around 10 secs to run everytime on
an average but runs faster on development boxes like around 1 sec. It has
the same exact execution plan on both environments. My question is what else
do i need to look into since it doesnt seem to be hardware bound here. Using
SQL 2000. CPU and reads in trace are also higher than in our dev
environment. but the perfmon for h/w looks decent or atleast not that bad .
Any other counters to look at or suggestionsDo you have the same amount of data on both servers? For example if you
have a cursor that runs through all the records in one table and in the
development database you have only 100 records but in the production server
you have 100000 records, then I'd exepect that the stored procedure would
take longer to run on the production server. Also if you have lots of
users that are working with the database in the production, you might have
locking problems, that slow the execution.
Adi
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eY8NNr7YDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Why would a stored procedure take longer to run on an OLTP environment
> although %processor time doesnt seem to be pegged ( 30%), %disk time is
> around 10% and pages/sec is 0. It takes around 10 secs to run everytime on
> an average but runs faster on development boxes like around 1 sec. It has
> the same exact execution plan on both environments. My question is what
else
> do i need to look into since it doesnt seem to be hardware bound here.
Using
> SQL 2000. CPU and reads in trace are also higher than in our dev
> environment. but the perfmon for h/w looks decent or atleast not that bad
.
> Any other counters to look at or suggestions
>
>|||perfmon cpu is the instantaneous cpu usage, profiler cpu
is the cumulative cpu for the query, regardless of how
long it took,
unless a query takes several cpu secs, and the difference
between profiler duration and cpu is not larger, you may
not even notice the cpu in perfmon
>--Original Message--
>Assuming the stored procedure was only selecting , why
would the CPU from
>perfmon do not show it as busy yet consume more time in
CPU in profiler to
>execute the sproc. There is no parallelism. There are
some temp tables
>..There are some nested joins.
>"joe chang" <jchang6@.yahoo.com> wrote in message
>news:000d01c36401$27d19f60$a101280a@.phx.gbl...
>> what specifically are the production and development
>> systems?
>> processor, frequency, cache, #of cpu, memory, # of
disks,
>> status of hyper-threading?
>> what is in the execution plan of your stored proc ?
>> joins - what type, loop, hash, merge, row count for each
>> table, is there a temp table involved? is a parallel
plan
>> involved? if so, disabled HT or try OPTION (MAXDOP 1)
>> sometimes the dev env is JBOD (no raid) the prod env is
>> raid5, with absolutely horrible write performance, so
>> anything that involves writes will performance much
worse
>> on the $40k production system than the $1k dev system
>> >--Original Message--
>> >Why would a stored procedure take longer to run on an
>> OLTP environment
>> >although %processor time doesnt seem to be pegged (
30%),
>> %disk time is
>> >around 10% and pages/sec is 0. It takes around 10 secs
to
>> run everytime on
>> >an average but runs faster on development boxes like
>> around 1 sec. It has
>> >the same exact execution plan on both environments. My
>> question is what else
>> >do i need to look into since it doesnt seem to be
>> hardware bound here. Using
>> >SQL 2000. CPU and reads in trace are also higher than
in
>> our dev
>> >environment. but the perfmon for h/w looks decent or
>> atleast not that bad .
>> >Any other counters to look at or suggestions
>> >
>> >
>> >
>> >.
>> >
>
>.
>
Perfomance Questions
I have a few questions.
First: We have a quad Xeon 500MHz server running SQL2K
and Win2K. All SPs are applied. Sometimes the
performance becomes an issue. My boss wants me to go
through and kill some processes when that happens. Are
there any adverse reactions to that?
Second: We also have a Terminal Server with Win2k3
running. Does anyone know of any performance issues with
this?
Thanks.
DonDon,
If the solution is to kill processes, you need a better solution.
Killing a process naturally affects the user of that process. Depending on
how the client and T-SQL code is written this can range from harmless but
annoying to leaving data in a logically incomplete state. (It will not
physically corrupt the database, but logical corruption is just as
troublesome.)
The thing to do is investigate the processes that are candidates for
killing, figure out what is wrong, and help the developer/user to correct
the problems. (Famous bad query: multi-table cartesian product to get just
a few rows. Done by people who do not understand joining.)
Russell Fields
"Don" <donolwert@.hotmail.com> wrote in message
news:0d3301c35c30$a7ecb780$a401280a@.phx.gbl...
> I have a few questions.
> First: We have a quad Xeon 500MHz server running SQL2K
> and Win2K. All SPs are applied. Sometimes the
> performance becomes an issue. My boss wants me to go
> through and kill some processes when that happens. Are
> there any adverse reactions to that?
> Second: We also have a Terminal Server with Win2k3
> running. Does anyone know of any performance issues with
> this?
> Thanks.
> Don|||One time I puked because a process was killed.. I couldn't stand it.
MS
"chris" <chrisr@.fingps.com> wrote in message
news:057901c35c45$0c1de350$a101280a@.phx.gbl...
> My boss wants me to go
> >through and kill some processes when that happens. Are
> >there any adverse reactions to that?
> Other than users losing the work they were doing, no.
>
> >--Original Message--
> >I have a few questions.
> >
> >First: We have a quad Xeon 500MHz server running SQL2K
> >and Win2K. All SPs are applied. Sometimes the
> >performance becomes an issue. My boss wants me to go
> >through and kill some processes when that happens. Are
> >there any adverse reactions to that?
> >
> >Second: We also have a Terminal Server with Win2k3
> >running. Does anyone know of any performance issues with
> >this?
> >
> >Thanks.
> >Don
> >.
> >|||Russell,
Thanks for the good detailed information.
Don
>--Original Message--
>Don,
>If the solution is to kill processes, you need a better
solution.
>Killing a process naturally affects the user of that
process. Depending on
>how the client and T-SQL code is written this can range
from harmless but
>annoying to leaving data in a logically incomplete
state. (It will not
>physically corrupt the database, but logical corruption
is just as
>troublesome.)
>The thing to do is investigate the processes that are
candidates for
>killing, figure out what is wrong, and help the
developer/user to correct
>the problems. (Famous bad query: multi-table cartesian
product to get just
>a few rows. Done by people who do not understand
joining.)
>Russell Fields
>"Don" <donolwert@.hotmail.com> wrote in message
>news:0d3301c35c30$a7ecb780$a401280a@.phx.gbl...
>> I have a few questions.
>> First: We have a quad Xeon 500MHz server running SQL2K
>> and Win2K. All SPs are applied. Sometimes the
>> performance becomes an issue. My boss wants me to go
>> through and kill some processes when that happens. Are
>> there any adverse reactions to that?
>> Second: We also have a Terminal Server with Win2k3
>> running. Does anyone know of any performance issues
with
>> this?
>> Thanks.
>> Don
>
>.
>
First: We have a quad Xeon 500MHz server running SQL2K
and Win2K. All SPs are applied. Sometimes the
performance becomes an issue. My boss wants me to go
through and kill some processes when that happens. Are
there any adverse reactions to that?
Second: We also have a Terminal Server with Win2k3
running. Does anyone know of any performance issues with
this?
Thanks.
DonDon,
If the solution is to kill processes, you need a better solution.
Killing a process naturally affects the user of that process. Depending on
how the client and T-SQL code is written this can range from harmless but
annoying to leaving data in a logically incomplete state. (It will not
physically corrupt the database, but logical corruption is just as
troublesome.)
The thing to do is investigate the processes that are candidates for
killing, figure out what is wrong, and help the developer/user to correct
the problems. (Famous bad query: multi-table cartesian product to get just
a few rows. Done by people who do not understand joining.)
Russell Fields
"Don" <donolwert@.hotmail.com> wrote in message
news:0d3301c35c30$a7ecb780$a401280a@.phx.gbl...
> I have a few questions.
> First: We have a quad Xeon 500MHz server running SQL2K
> and Win2K. All SPs are applied. Sometimes the
> performance becomes an issue. My boss wants me to go
> through and kill some processes when that happens. Are
> there any adverse reactions to that?
> Second: We also have a Terminal Server with Win2k3
> running. Does anyone know of any performance issues with
> this?
> Thanks.
> Don|||One time I puked because a process was killed.. I couldn't stand it.
MS
"chris" <chrisr@.fingps.com> wrote in message
news:057901c35c45$0c1de350$a101280a@.phx.gbl...
> My boss wants me to go
> >through and kill some processes when that happens. Are
> >there any adverse reactions to that?
> Other than users losing the work they were doing, no.
>
> >--Original Message--
> >I have a few questions.
> >
> >First: We have a quad Xeon 500MHz server running SQL2K
> >and Win2K. All SPs are applied. Sometimes the
> >performance becomes an issue. My boss wants me to go
> >through and kill some processes when that happens. Are
> >there any adverse reactions to that?
> >
> >Second: We also have a Terminal Server with Win2k3
> >running. Does anyone know of any performance issues with
> >this?
> >
> >Thanks.
> >Don
> >.
> >|||Russell,
Thanks for the good detailed information.
Don
>--Original Message--
>Don,
>If the solution is to kill processes, you need a better
solution.
>Killing a process naturally affects the user of that
process. Depending on
>how the client and T-SQL code is written this can range
from harmless but
>annoying to leaving data in a logically incomplete
state. (It will not
>physically corrupt the database, but logical corruption
is just as
>troublesome.)
>The thing to do is investigate the processes that are
candidates for
>killing, figure out what is wrong, and help the
developer/user to correct
>the problems. (Famous bad query: multi-table cartesian
product to get just
>a few rows. Done by people who do not understand
joining.)
>Russell Fields
>"Don" <donolwert@.hotmail.com> wrote in message
>news:0d3301c35c30$a7ecb780$a401280a@.phx.gbl...
>> I have a few questions.
>> First: We have a quad Xeon 500MHz server running SQL2K
>> and Win2K. All SPs are applied. Sometimes the
>> performance becomes an issue. My boss wants me to go
>> through and kill some processes when that happens. Are
>> there any adverse reactions to that?
>> Second: We also have a Terminal Server with Win2k3
>> running. Does anyone know of any performance issues
with
>> this?
>> Thanks.
>> Don
>
>.
>
Perfomance Question
I look after a database which is part of a third party CRM product. The
users of the product complain of intermittant poor performance, the
suspicion is that some more senior users are running their own queries
(the product allows users to do this). I've been asked by the
development team to try to capture the details of long running queries.
users of the product complain of intermittant poor performance, the
suspicion is that some more senior users are running their own queries
(the product allows users to do this). I've been asked by the
development team to try to capture the details of long running queries.
I've looked at the events listed in profiler and can't see one that
would be useful. Ideally I want to know who is running which query that
is taking longer than x seconds.
Any suggestions
TIA
LaurencePersonally, I would say 4-6 seconds is to long for a query. You may have a
different expectation.
Run the Profiler for a period of time _ i aim for at least 3 hrs - depnds on
server traffic etc
Events to capture : Stored Procedures--RPC:Completed &&
TSQL--SQL:BatchCompleted
(all sps and t-sql statement)
The critical columns to capture are: Duration and textdata . Others as well
for whatever other analysis you may need
Use the "duration" filter , do it by db id.
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message
news:448ED88F.1000104@.open.ac.uk...
> I look after a database which is part of a third party CRM product. The
> users of the product complain of intermittant poor performance, the
> suspicion is that some more senior users are running their own queries
> (the product allows users to do this). I've been asked by the
> development team to try to capture the details of long running queries.
> I've looked at the events listed in profiler and can't see one that
> would be useful. Ideally I want to know who is running which query that
> is taking longer than x seconds.
> Any suggestions
> TIA
> Laurence|||Thanks Jack,
I'll give that a go.
Laurence
Jack Vamvas wrote:
> Personally, I would say 4-6 seconds is to long for a query. You may have a
> different expectation.
> Run the Profiler for a period of time _ i aim for at least 3 hrs - depnds on
> server traffic etc
> Events to capture : Stored Procedures--RPC:Completed &&
> TSQL--SQL:BatchCompleted
> (all sps and t-sql statement)
> The critical columns to capture are: Duration and textdata . Others as well
> for whatever other analysis you may need
> Use the "duration" filter , do it by db id.
>
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
>
> "Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message
> news:448ED88F.1000104@.open.ac.uk...
>>I look after a database which is part of a third party CRM product. The
>>users of the product complain of intermittant poor performance, the
>>suspicion is that some more senior users are running their own queries
>>(the product allows users to do this). I've been asked by the
>>development team to try to capture the details of long running queries.
>>
>>I've looked at the events listed in profiler and can't see one that
>>would be useful. Ideally I want to know who is running which query that
>>is taking longer than x seconds.
>>
>>Any suggestions
>>
>>TIA
>>
>>Laurence
>>
Perfomance monitor
Hi all,
I am thinking how bad it can be if I have perfomance monitor and sql
profiler running for my production SQL server? do they affact the perfomance
a lot? If there a better way for me to do the samilar thing?
Thanks.See if this helps:
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Tips On Using the SQL Server Profiler
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
Tips for Using Performance Monitor
http://www.sql-server-performance.com/performance_monitor_tips.asp
AMB
"Catelin Wang" wrote:
> Hi all,
> I am thinking how bad it can be if I have perfomance monitor and sql
> profiler running for my production SQL server? do they affact the perfomance
> a lot? If there a better way for me to do the samilar thing?
> Thanks.|||Very good infomation. Thanks a lot .
"Alejandro Mesa" wrote:
> See if this helps:
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
> Tips On Using the SQL Server Profiler
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
> Tips for Using Performance Monitor
> http://www.sql-server-performance.com/performance_monitor_tips.asp
>
> AMB
> "Catelin Wang" wrote:
> > Hi all,
> > I am thinking how bad it can be if I have perfomance monitor and sql
> > profiler running for my production SQL server? do they affact the perfomance
> > a lot? If there a better way for me to do the samilar thing?
> >
> > Thanks.
I am thinking how bad it can be if I have perfomance monitor and sql
profiler running for my production SQL server? do they affact the perfomance
a lot? If there a better way for me to do the samilar thing?
Thanks.See if this helps:
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Tips On Using the SQL Server Profiler
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
Tips for Using Performance Monitor
http://www.sql-server-performance.com/performance_monitor_tips.asp
AMB
"Catelin Wang" wrote:
> Hi all,
> I am thinking how bad it can be if I have perfomance monitor and sql
> profiler running for my production SQL server? do they affact the perfomance
> a lot? If there a better way for me to do the samilar thing?
> Thanks.|||Very good infomation. Thanks a lot .
"Alejandro Mesa" wrote:
> See if this helps:
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
> Tips On Using the SQL Server Profiler
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
> Tips for Using Performance Monitor
> http://www.sql-server-performance.com/performance_monitor_tips.asp
>
> AMB
> "Catelin Wang" wrote:
> > Hi all,
> > I am thinking how bad it can be if I have perfomance monitor and sql
> > profiler running for my production SQL server? do they affact the perfomance
> > a lot? If there a better way for me to do the samilar thing?
> >
> > Thanks.
Perfomance monitor
Hi all,
I am thinking how bad it can be if I have perfomance monitor and sql
profiler running for my production SQL server? do they affact the perfomanc
e
a lot? If there a better way for me to do the samilar thing?
Thanks.See if this helps:
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_sid..._sql_server.htm
Tips On Using the SQL Server Profiler
http://www.sql-server-performance.c...ofiler_tips.asp
Tips for Using Performance Monitor
http://www.sql-server-performance.c...onitor_tips.asp
AMB
"Catelin Wang" wrote:
> Hi all,
> I am thinking how bad it can be if I have perfomance monitor and sql
> profiler running for my production SQL server? do they affact the perfoma
nce
> a lot? If there a better way for me to do the samilar thing?
> Thanks.|||Very good infomation. Thanks a lot .
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See if this helps:
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> Tips On Using the SQL Server Profiler
> http://www.sql-server-performance.c...ofiler_tips.asp
> Tips for Using Performance Monitor
> http://www.sql-server-performance.c...onitor_tips.asp
>
> AMB
> "Catelin Wang" wrote:
>
I am thinking how bad it can be if I have perfomance monitor and sql
profiler running for my production SQL server? do they affact the perfomanc
e
a lot? If there a better way for me to do the samilar thing?
Thanks.See if this helps:
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_sid..._sql_server.htm
Tips On Using the SQL Server Profiler
http://www.sql-server-performance.c...ofiler_tips.asp
Tips for Using Performance Monitor
http://www.sql-server-performance.c...onitor_tips.asp
AMB
"Catelin Wang" wrote:
> Hi all,
> I am thinking how bad it can be if I have perfomance monitor and sql
> profiler running for my production SQL server? do they affact the perfoma
nce
> a lot? If there a better way for me to do the samilar thing?
> Thanks.|||Very good infomation. Thanks a lot .
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See if this helps:
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> Tips On Using the SQL Server Profiler
> http://www.sql-server-performance.c...ofiler_tips.asp
> Tips for Using Performance Monitor
> http://www.sql-server-performance.c...onitor_tips.asp
>
> AMB
> "Catelin Wang" wrote:
>
Perfomance monitor
Hi all,
I am thinking how bad it can be if I have perfomance monitor and sql
profiler running for my production SQL server? do they affact the perfomance
a lot? If there a better way for me to do the samilar thing?
Thanks.
See if this helps:
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_side...sql_server.htm
Tips On Using the SQL Server Profiler
http://www.sql-server-performance.co...filer_tips.asp
Tips for Using Performance Monitor
http://www.sql-server-performance.co...nitor_tips.asp
AMB
"Catelin Wang" wrote:
> Hi all,
> I am thinking how bad it can be if I have perfomance monitor and sql
> profiler running for my production SQL server? do they affact the perfomance
> a lot? If there a better way for me to do the samilar thing?
> Thanks.
|||Very good infomation. Thanks a lot .
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See if this helps:
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_side...sql_server.htm
> Tips On Using the SQL Server Profiler
> http://www.sql-server-performance.co...filer_tips.asp
> Tips for Using Performance Monitor
> http://www.sql-server-performance.co...nitor_tips.asp
>
> AMB
> "Catelin Wang" wrote:
I am thinking how bad it can be if I have perfomance monitor and sql
profiler running for my production SQL server? do they affact the perfomance
a lot? If there a better way for me to do the samilar thing?
Thanks.
See if this helps:
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_side...sql_server.htm
Tips On Using the SQL Server Profiler
http://www.sql-server-performance.co...filer_tips.asp
Tips for Using Performance Monitor
http://www.sql-server-performance.co...nitor_tips.asp
AMB
"Catelin Wang" wrote:
> Hi all,
> I am thinking how bad it can be if I have perfomance monitor and sql
> profiler running for my production SQL server? do they affact the perfomance
> a lot? If there a better way for me to do the samilar thing?
> Thanks.
|||Very good infomation. Thanks a lot .
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See if this helps:
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_side...sql_server.htm
> Tips On Using the SQL Server Profiler
> http://www.sql-server-performance.co...filer_tips.asp
> Tips for Using Performance Monitor
> http://www.sql-server-performance.co...nitor_tips.asp
>
> AMB
> "Catelin Wang" wrote:
Perfomance issue
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist a
t
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are goin
g
to 1 table while the majority are going to another table in the same DB. Thi
s
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
DonTry updating stats with FULLSCAN. Also, trace the Lock Escalation event.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don|||On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT an
d
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist a
t
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are goin
g
to 1 table while the majority are going to another table in the same DB. Thi
s
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
DonTry updating stats with FULLSCAN. Also, trace the Lock Escalation event.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don|||On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT an
d
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.
Perfomance issue
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are going
to 1 table while the majority are going to another table in the same DB. This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
DonTry updating stats with FULLSCAN. Also, trace the Lock Escalation event.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don|||On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT and
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are going
to 1 table while the majority are going to another table in the same DB. This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
DonTry updating stats with FULLSCAN. Also, trace the Lock Escalation event.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don|||On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT and
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.
Perfomance issue
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are going
to 1 table while the majority are going to another table in the same DB. This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
|||On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT and
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are going
to 1 table while the majority are going to another table in the same DB. This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
|||On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT and
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.
Perfomance Enhancement through proper database designing
Dear Reader
I am trying to design a database. How can I make best Judgement that Indexing (which I am trying to fix during Diagram Desingning process)is ok.
I am able to identify the best candidate for the indexing.
Below is the details I want to understand:
Area
ZIP
City
County
District
State/Province
Country
Now I want the data retrival optimization through Index. (you can suggest another idea, also)
Entities Area,..., Country have independent tables.
Example:
Area_Table
AreaID (PK)
Area
They have relationship- one to many- if you go from Country to Area.
There is one more table:
Location_Table (PK)
LocationID
AreaID
ZIPID
CityID
CountyID
DistrictID
State/ProvinceID
CountryID
(Location_ID is further related to the Address of the contact.)
GUI has a single form to enter these details.On a save command details in all the tables -Area to Country- (individually) being inserted.
& simultaniously Location_Table is also being inserted with the details.
Following is the situation of being queried these tables:
(1) GUI user can select an Area than the related details of ZIP .., ..., ...upto Country etc. should be loaded automatically (id it is previously stored by the user entry in the database.)
(2) Contacts have to be retrived on the basis of Area, ZIP, ....County. (Necessary Groupings are required )
Example:
If Contacts are queried Country Wise then the Display should be
Country1
State1
District1
County1
City1
ZIP1
Area1
Area2
ZIP2
City2
County2
District2
Country2
Please Guide.
SuryaPrakash
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...0255a1765491f15
*****************************************SuryaPrakash Patel via SQLMonster.com (forum@.SQLMonster.com) writes:
> I am trying to design a database. How can I make best Judgement that
> Indexing (which I am trying to fix during Diagram Desingning process)is
> ok.
I was not really able to understand exactly what the queries would look
like. While a trained database designer certainly puts indexes already
in the early design stage, it's better to focus to get the data model
right to support the functional requirements first. Once we have the
queries and the tables, it may be eaesier to say what would be the
best indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 09 Nov 2004 04:32:02 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:
>Dear Reader
>I am trying to design a database.
(snip)
Hi SuryaPrakash,
Further to Erlands's comments: you should also not think about GUI or
report formats when designing a database. Database design should be driven
by the structure of the data only.
Only when you have a properly normalized database that will store all data
the application needs and that will reject all modifications that would
hurt data integrity comes the time to think about the user interface (both
for in- and output screens and for reports) and about adding extra indexes
for performance enhancement.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Dear Erland & Hugo
Thanks
I got your suggestions.
I have specific needs to get the certain ways of output. So it is obvious that I incorporate the needs while doing the database design.
But nowonwards I will try to stick the database structure, only. And leave the GUI part and Indexing part for later stages of development.
Point to be noted is that I will not have any chance to deploy any index after the software has been installed at user's end. Any comments.
Thanks again
SuryaPrakash
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...sql-server/5093
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...4931f7dbd3d74e1
*****************************************|||On Wed, 10 Nov 2004 00:59:23 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:
>Point to be noted is that I will not have any chance to deploy any index after the software has been installed at user's end. Any comments.
Hi SuryaPrakash,
That's a logical and quite common scenario. That's why you should only
install the software when it's completely finished. I'd recommend to build
the software first, then test and debug until it works as desired, then
start doing performance test and tweaking things (adding indexes,
rewriting queries, etc) until the speed is as desired. Then do a final
test to check that performance tweaking didn't break functionality.
Installing at the user's end should be postponed until all these stages
are done and the product is completely finished.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||SuryaPrakash Patel via SQLMonster.com (forum@.SQLMonster.com) writes:
> Point to be noted is that I will not have any chance to deploy any index
> after the software has been installed at user's end. Any comments.
So you need to test carefully with real-world data before you ship.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Perfomance
I have stored procedure that is taking longer time in production than in
Test. I narrowed the longer time being taken to the order by clause in a
subquery. The execution plans look different in the test than in production
server. The databases are identical. What could be causing this?
The production server is much beefier than the test.
Thanks
Rahul
Plenty of things, e.g. out of date statistics, missing indexes,
fragmentation, different size of data...
Can you be more specific about how the "execution plans look different"? Is
one doing a seek and the other a scan, or is one doing a different kind of
join, or...?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:uwMMzLbuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have stored procedure that is taking longer time in production than in
>Test. I narrowed the longer time being taken to the order by clause in a
>subquery. The execution plans look different in the test than in production
>server. The databases are identical. What could be causing this?
> The production server is much beefier than the test.
> Thanks
> Rahul
>
Test. I narrowed the longer time being taken to the order by clause in a
subquery. The execution plans look different in the test than in production
server. The databases are identical. What could be causing this?
The production server is much beefier than the test.
Thanks
Rahul
Plenty of things, e.g. out of date statistics, missing indexes,
fragmentation, different size of data...
Can you be more specific about how the "execution plans look different"? Is
one doing a seek and the other a scan, or is one doing a different kind of
join, or...?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:uwMMzLbuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have stored procedure that is taking longer time in production than in
>Test. I narrowed the longer time being taken to the order by clause in a
>subquery. The execution plans look different in the test than in production
>server. The databases are identical. What could be causing this?
> The production server is much beefier than the test.
> Thanks
> Rahul
>
Perfomance
Hi All,
I have a little bit of code that looks something like this
SELECT
code,
[description],
clientDescription,
band,
AVG(rental) AS value,
COUNT(tableTemp.surveyID) AS [count],
CASE clientID
WHEN @.clientID THEN 1
ELSE 0
END AS color
FROM
(
SELECT tblSurvey.surveyID,
tblSurvey.clientID AS clientID,
client_alias AS code,
CONVERT(varchar(25), client_alias) AS [description],
client_desc AS clientDescription,
#StartOfPeriod.band,
rental
FROM tblSurvey
INNER JOIN tblClient
ON tblClient.clientID = tblSurvey.clientID
INNER JOIN #surveys
ON #surveys.surveyID = tblSurvey.surveyID
INNER JOIN #StartOfPeriod
ON #StartOfPeriod.editionID=tblSurvey.editionID
WHERE (mileage = @.mileageint OR @.mileageint = 0)
AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
AND tblSurvey.editionID >= @.startEdition
AND tblSurvey.editionID < @.limitEdition
AND client_alias IS NOT NULL
AND rental IS NOT NULL
) AS tableTemp
GROUP BY code,
[description],
clientDescription,
band,
tableTemp.clientID
UNION ALL
SELECT
'99' AS code,
'IMV' AS [description],
'IMV' AS clientDescription,
band,
AVG(rental) AS value,
COUNT(averageTemp.surveyID) AS [count],
0 AS color
FROM (SELECT tblSurvey.surveyID,
#StartOfPeriod.band,
rental
FROM tblSurvey
INNER JOIN tblClient
ON tblClient.clientID = tblSurvey.clientID
INNER JOIN #surveys
ON #surveys.surveyID = tblSurvey.surveyID
INNER JOIN #StartOfPeriod
ON #StartOfPeriod.editionID=tblSurvey.editionID
WHERE (mileage = @.mileageint OR @.mileageint = 0)
AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
AND tblSurvey.editionID >= @.startEdition
AND tblSurvey.editionID < @.limitEdition
AND client_alias IS NOT NULL
AND rental IS NOT NULL
) AS averageTemp
GROUP BY band
I have tried everything I can think of to make this run quicker, even though
it's not really slow, just want to get the best performance possible. There
are no way's around the temp tables that I need to use so I am just wonderin
g
if there is a better way that I can write the select statement'
Any help would be great.
Thanks PhilHave you tried something like this already? Your using select statements
with selects statements in an Union. I can imagine that your solutions isn't
the fasted way to get the information.
SELECT code
, [description]
, clientDescription
, COUNT(tableTemp.surveyID) AS [count]
, tblSurvey.clientID AS clientID
, client_alias AS code
, CONVERT(varchar(25), client_alias) AS [description], client_desc AS
clientDescription
, #StartOfPeriod.band
, rental
, CASE clientID WHEN @.clientID THEN 1 ELSE 0 END AS color
FROM tblSurvey
<-- Rest of Query -->
GROUP BY code, [description], clientDescription, band, tableTemp.clientID
UNION ALL
SELECT '99' AS code
,'IMV' AS [description]
,'IMV' AS clientDescription
,COUNT(averageTemp.surveyID) AS [count]
, #StartOfPeriod.band as [band]
,AVG(rental) AS value
,0 AS color
fROM tblSurvey
INNER JOIN tblClient
AND client_alias IS NOT NULL
AND rental IS NOT NULL
<-- Rest of Query -->
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:CE9CEEE5-9D1E-4735-9A1A-72B01C5105FA@.microsoft.com...
> Hi All,
> I have a little bit of code that looks something like this
> SELECT
> code,
> [description],
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM
> (
> SELECT tblSurvey.surveyID,
> tblSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS [description],
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> [description],
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS [description],
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I have tried everything I can think of to make this run quicker, even
though
> it's not really slow, just want to get the best performance possible.
There
> are no way's around the temp tables that I need to use so I am just
wondering
> if there is a better way that I can write the select statement'
> Any help would be great.
> Thanks Phil|||Hi there,
Thanks for the reply, it doesn't look like something that I have tired yet
but I am havinbg a few problems understanding the replacement code that you
put in, is there any chance you could explain it a little further.
Thanks in advance, Phil
"M. de Jong" wrote:
> Have you tried something like this already? Your using select statements
> with selects statements in an Union. I can imagine that your solutions isn
't
> the fasted way to get the information.
> SELECT code
> , [description]
> , clientDescription
> , COUNT(tableTemp.surveyID) AS [count]
> , tblSurvey.clientID AS clientID
> , client_alias AS code
> , CONVERT(varchar(25), client_alias) AS [description], client_desc AS
> clientDescription
> , #StartOfPeriod.band
> , rental
> , CASE clientID WHEN @.clientID THEN 1 ELSE 0 END AS color
> FROM tblSurvey
> <-- Rest of Query -->
> GROUP BY code, [description], clientDescription, band, tableTemp.clientID
> UNION ALL
> SELECT '99' AS code
> ,'IMV' AS [description]
> ,'IMV' AS clientDescription
> ,COUNT(averageTemp.surveyID) AS [count]
> , #StartOfPeriod.band as [band]
> ,AVG(rental) AS value
> ,0 AS color
> fROM tblSurvey
> INNER JOIN tblClient
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> <-- Rest of Query -->
>
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:CE9CEEE5-9D1E-4735-9A1A-72B01C5105FA@.microsoft.com...
> though
> There
> wondering
>
>|||Hi
A couple of things:
Can this be done with ROLLUP?
#Surveys does not seem to be doing much.
Use owner prefixes and aliases.
Look at the query plans/indexes.
John
"Phil" wrote:
> Hi All,
> I have a little bit of code that looks something like this
> SELECT
> code,
> [description],
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM
> (
> SELECT tblSurvey.surveyID,
> tblSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS [description],
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> [description],
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS [description],
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I have tried everything I can think of to make this run quicker, even thou
gh
> it's not really slow, just want to get the best performance possible. Ther
e
> are no way's around the temp tables that I need to use so I am just wonder
ing
> if there is a better way that I can write the select statement'
> Any help would be great.
> Thanks Phil|||Hi,
Thanks for the reply, you are correct #surveys doesn't do much, when you say
use ownder alias and prefixes, I am not quite sure what you mean, sorry abou
t
that. I have looked at the query plan and the biggest percentage comes from
Index scans but I am afriad I am not able to change them. I am also not sur
e
how ROLLUP would be of any help.
Sorry about all the question!!!
"John Bell" wrote:
> Hi
> A couple of things:
> Can this be done with ROLLUP?
> #Surveys does not seem to be doing much.
> Use owner prefixes and aliases.
> Look at the query plans/indexes.
> John
> "Phil" wrote:
>|||Hi
Your UNIONED line seems to be a summary line, in which case using
ROLLUP could do that for you.
Using table aliases throughout would make everything clearer (you don't
need the column aliases in the unioned part!).
Using the owner prefix will minimise name resolution as described in
the paragraph
"Fully Qualify Database Objects" See
http://msdn.microsoft.com/library/d...
etchapt14.asp
John
Phil wrote:
> Hi,
> Thanks for the reply, you are correct #surveys doesn't do much, when
you say
> use ownder alias and prefixes, I am not quite sure what you mean,
sorry about
> that. I have looked at the query plan and the biggest percentage
comes from
> Index scans but I am afriad I am not able to change them. I am also
not sure
> how ROLLUP would be of any help.
> Sorry about all the question!!!
> "John Bell" wrote:
>
even though
possible. There
just wondering|||Phil,
There are a few things you can do.
1) What M. de Jong tried to explain, is that your current query is
something like this:
SELECT ... FROM (
SELECT ... FROM <T1> GROUP BY <GB1>
UNION ALL
SELECT ... FROM <T2> GROUP BY <GB2>
) GROUP BY <GB3>
This query can be rewritten as:
SELECT ... FROM <1> GROUP BY <GB1>,<GB3>
UNION ALL
SELECT ... FROM <2> GROUP BY <GB2>,<GB3>
This eliminates one GROUP BY action.
2) As Phil mentioned, table #Surveys doesn't seem to do much. But if it
is necessary, and there is a one-to-one relation with tblSurvey, then
you could remove the INNER JOIN with #Surveys, and add
AND EXISTS (SELECT 1 FROM #Surveys WHERE #surveys.surveyID =
tblSurvey.surveyID)
and see if that makes any difference in performance.
3) If the columns mileage and age_in_months do not contain NULLs, then
you could rewrite the predicates with these columns in such a way that
the OR is removed and an index s
could potentially be used. For
example, you could rewrite:
AND (mileage = @.mileageint OR @.mileageint = 0)
with:
AND mileage BETWEEN COALESCE(NULLIF(@.mileageint,0), -2147483648)
AND COALESCE(NULLIF(@.mileageint,0), 2147483647)
4) Make sure all join keys are indexed. In addition, you could try the
Index Tuning Wizard.
Note that this is based on what you posted. Since you did not post any
DDL, we cannot see for all columns from which table they are selected
(the missing table aliases John was talking about).
Hope this helps,
Gert-Jan
Phil wrote:
> Hi All,
> I have a little bit of code that looks something like this
> SELECT
> code,
> [description],
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM
> (
> SELECT tblSurvey.surveyID,
> tblSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS [description],
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> [description],
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS [description],
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I have tried everything I can think of to make this run quicker, even thou
gh
> it's not really slow, just want to get the best performance possible. Ther
e
> are no way's around the temp tables that I need to use so I am just wonder
ing
> if there is a better way that I can write the select statement'
> Any help would be great.
> Thanks Phil|||Hi Phil
A possible alternative to Gert-Jan's point2 would be to change the way
that #surveys is constructed so that it includes (when constructed) the
information you re-select from tblSurveys and therefore tblSurveys can
be excluded from the statement.
John
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi there,
I would just like to say a quick thanks to everyone that has posted a
reply to my question, I have now managed to get it working better with a
combination of your solutions.
Sorry I didn't post exactly what you needed but I will rememeber for
next time, thanks again.
Phil
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
I have a little bit of code that looks something like this
SELECT
code,
[description],
clientDescription,
band,
AVG(rental) AS value,
COUNT(tableTemp.surveyID) AS [count],
CASE clientID
WHEN @.clientID THEN 1
ELSE 0
END AS color
FROM
(
SELECT tblSurvey.surveyID,
tblSurvey.clientID AS clientID,
client_alias AS code,
CONVERT(varchar(25), client_alias) AS [description],
client_desc AS clientDescription,
#StartOfPeriod.band,
rental
FROM tblSurvey
INNER JOIN tblClient
ON tblClient.clientID = tblSurvey.clientID
INNER JOIN #surveys
ON #surveys.surveyID = tblSurvey.surveyID
INNER JOIN #StartOfPeriod
ON #StartOfPeriod.editionID=tblSurvey.editionID
WHERE (mileage = @.mileageint OR @.mileageint = 0)
AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
AND tblSurvey.editionID >= @.startEdition
AND tblSurvey.editionID < @.limitEdition
AND client_alias IS NOT NULL
AND rental IS NOT NULL
) AS tableTemp
GROUP BY code,
[description],
clientDescription,
band,
tableTemp.clientID
UNION ALL
SELECT
'99' AS code,
'IMV' AS [description],
'IMV' AS clientDescription,
band,
AVG(rental) AS value,
COUNT(averageTemp.surveyID) AS [count],
0 AS color
FROM (SELECT tblSurvey.surveyID,
#StartOfPeriod.band,
rental
FROM tblSurvey
INNER JOIN tblClient
ON tblClient.clientID = tblSurvey.clientID
INNER JOIN #surveys
ON #surveys.surveyID = tblSurvey.surveyID
INNER JOIN #StartOfPeriod
ON #StartOfPeriod.editionID=tblSurvey.editionID
WHERE (mileage = @.mileageint OR @.mileageint = 0)
AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
AND tblSurvey.editionID >= @.startEdition
AND tblSurvey.editionID < @.limitEdition
AND client_alias IS NOT NULL
AND rental IS NOT NULL
) AS averageTemp
GROUP BY band
I have tried everything I can think of to make this run quicker, even though
it's not really slow, just want to get the best performance possible. There
are no way's around the temp tables that I need to use so I am just wonderin
g
if there is a better way that I can write the select statement'
Any help would be great.
Thanks PhilHave you tried something like this already? Your using select statements
with selects statements in an Union. I can imagine that your solutions isn't
the fasted way to get the information.
SELECT code
, [description]
, clientDescription
, COUNT(tableTemp.surveyID) AS [count]
, tblSurvey.clientID AS clientID
, client_alias AS code
, CONVERT(varchar(25), client_alias) AS [description], client_desc AS
clientDescription
, #StartOfPeriod.band
, rental
, CASE clientID WHEN @.clientID THEN 1 ELSE 0 END AS color
FROM tblSurvey
<-- Rest of Query -->
GROUP BY code, [description], clientDescription, band, tableTemp.clientID
UNION ALL
SELECT '99' AS code
,'IMV' AS [description]
,'IMV' AS clientDescription
,COUNT(averageTemp.surveyID) AS [count]
, #StartOfPeriod.band as [band]
,AVG(rental) AS value
,0 AS color
fROM tblSurvey
INNER JOIN tblClient
AND client_alias IS NOT NULL
AND rental IS NOT NULL
<-- Rest of Query -->
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:CE9CEEE5-9D1E-4735-9A1A-72B01C5105FA@.microsoft.com...
> Hi All,
> I have a little bit of code that looks something like this
> SELECT
> code,
> [description],
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM
> (
> SELECT tblSurvey.surveyID,
> tblSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS [description],
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> [description],
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS [description],
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I have tried everything I can think of to make this run quicker, even
though
> it's not really slow, just want to get the best performance possible.
There
> are no way's around the temp tables that I need to use so I am just
wondering
> if there is a better way that I can write the select statement'
> Any help would be great.
> Thanks Phil|||Hi there,
Thanks for the reply, it doesn't look like something that I have tired yet
but I am havinbg a few problems understanding the replacement code that you
put in, is there any chance you could explain it a little further.
Thanks in advance, Phil
"M. de Jong" wrote:
> Have you tried something like this already? Your using select statements
> with selects statements in an Union. I can imagine that your solutions isn
't
> the fasted way to get the information.
> SELECT code
> , [description]
> , clientDescription
> , COUNT(tableTemp.surveyID) AS [count]
> , tblSurvey.clientID AS clientID
> , client_alias AS code
> , CONVERT(varchar(25), client_alias) AS [description], client_desc AS
> clientDescription
> , #StartOfPeriod.band
> , rental
> , CASE clientID WHEN @.clientID THEN 1 ELSE 0 END AS color
> FROM tblSurvey
> <-- Rest of Query -->
> GROUP BY code, [description], clientDescription, band, tableTemp.clientID
> UNION ALL
> SELECT '99' AS code
> ,'IMV' AS [description]
> ,'IMV' AS clientDescription
> ,COUNT(averageTemp.surveyID) AS [count]
> , #StartOfPeriod.band as [band]
> ,AVG(rental) AS value
> ,0 AS color
> fROM tblSurvey
> INNER JOIN tblClient
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> <-- Rest of Query -->
>
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:CE9CEEE5-9D1E-4735-9A1A-72B01C5105FA@.microsoft.com...
> though
> There
> wondering
>
>|||Hi
A couple of things:
Can this be done with ROLLUP?
#Surveys does not seem to be doing much.
Use owner prefixes and aliases.
Look at the query plans/indexes.
John
"Phil" wrote:
> Hi All,
> I have a little bit of code that looks something like this
> SELECT
> code,
> [description],
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM
> (
> SELECT tblSurvey.surveyID,
> tblSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS [description],
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> [description],
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS [description],
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I have tried everything I can think of to make this run quicker, even thou
gh
> it's not really slow, just want to get the best performance possible. Ther
e
> are no way's around the temp tables that I need to use so I am just wonder
ing
> if there is a better way that I can write the select statement'
> Any help would be great.
> Thanks Phil|||Hi,
Thanks for the reply, you are correct #surveys doesn't do much, when you say
use ownder alias and prefixes, I am not quite sure what you mean, sorry abou
t
that. I have looked at the query plan and the biggest percentage comes from
Index scans but I am afriad I am not able to change them. I am also not sur
e
how ROLLUP would be of any help.
Sorry about all the question!!!
"John Bell" wrote:
> Hi
> A couple of things:
> Can this be done with ROLLUP?
> #Surveys does not seem to be doing much.
> Use owner prefixes and aliases.
> Look at the query plans/indexes.
> John
> "Phil" wrote:
>|||Hi
Your UNIONED line seems to be a summary line, in which case using
ROLLUP could do that for you.
Using table aliases throughout would make everything clearer (you don't
need the column aliases in the unioned part!).
Using the owner prefix will minimise name resolution as described in
the paragraph
"Fully Qualify Database Objects" See
http://msdn.microsoft.com/library/d...
etchapt14.asp
John
Phil wrote:
> Hi,
> Thanks for the reply, you are correct #surveys doesn't do much, when
you say
> use ownder alias and prefixes, I am not quite sure what you mean,
sorry about
> that. I have looked at the query plan and the biggest percentage
comes from
> Index scans but I am afriad I am not able to change them. I am also
not sure
> how ROLLUP would be of any help.
> Sorry about all the question!!!
> "John Bell" wrote:
>
even though
possible. There
just wondering|||Phil,
There are a few things you can do.
1) What M. de Jong tried to explain, is that your current query is
something like this:
SELECT ... FROM (
SELECT ... FROM <T1> GROUP BY <GB1>
UNION ALL
SELECT ... FROM <T2> GROUP BY <GB2>
) GROUP BY <GB3>
This query can be rewritten as:
SELECT ... FROM <1> GROUP BY <GB1>,<GB3>
UNION ALL
SELECT ... FROM <2> GROUP BY <GB2>,<GB3>
This eliminates one GROUP BY action.
2) As Phil mentioned, table #Surveys doesn't seem to do much. But if it
is necessary, and there is a one-to-one relation with tblSurvey, then
you could remove the INNER JOIN with #Surveys, and add
AND EXISTS (SELECT 1 FROM #Surveys WHERE #surveys.surveyID =
tblSurvey.surveyID)
and see if that makes any difference in performance.
3) If the columns mileage and age_in_months do not contain NULLs, then
you could rewrite the predicates with these columns in such a way that
the OR is removed and an index s
could potentially be used. Forexample, you could rewrite:
AND (mileage = @.mileageint OR @.mileageint = 0)
with:
AND mileage BETWEEN COALESCE(NULLIF(@.mileageint,0), -2147483648)
AND COALESCE(NULLIF(@.mileageint,0), 2147483647)
4) Make sure all join keys are indexed. In addition, you could try the
Index Tuning Wizard.
Note that this is based on what you posted. Since you did not post any
DDL, we cannot see for all columns from which table they are selected
(the missing table aliases John was talking about).
Hope this helps,
Gert-Jan
Phil wrote:
> Hi All,
> I have a little bit of code that looks something like this
> SELECT
> code,
> [description],
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM
> (
> SELECT tblSurvey.surveyID,
> tblSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS [description],
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> [description],
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS [description],
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I have tried everything I can think of to make this run quicker, even thou
gh
> it's not really slow, just want to get the best performance possible. Ther
e
> are no way's around the temp tables that I need to use so I am just wonder
ing
> if there is a better way that I can write the select statement'
> Any help would be great.
> Thanks Phil|||Hi Phil
A possible alternative to Gert-Jan's point2 would be to change the way
that #surveys is constructed so that it includes (when constructed) the
information you re-select from tblSurveys and therefore tblSurveys can
be excluded from the statement.
John
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi there,
I would just like to say a quick thanks to everyone that has posted a
reply to my question, I have now managed to get it working better with a
combination of your solutions.
Sorry I didn't post exactly what you needed but I will rememeber for
next time, thanks again.
Phil
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
Perfomance
I have stored procedure that is taking longer time in production than in
Test. I narrowed the longer time being taken to the order by clause in a
subquery. The execution plans look different in the test than in production
server. The databases are identical. What could be causing this?
The production server is much beefier than the test.
Thanks
RahulPlenty of things, e.g. out of date statistics, missing indexes,
fragmentation, different size of data...
Can you be more specific about how the "execution plans look different"? Is
one doing a seek and the other a scan, or is one doing a different kind of
join, or...?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:uwMMzLbuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have stored procedure that is taking longer time in production than in
>Test. I narrowed the longer time being taken to the order by clause in a
>subquery. The execution plans look different in the test than in production
>server. The databases are identical. What could be causing this?
> The production server is much beefier than the test.
> Thanks
> Rahul
>
Test. I narrowed the longer time being taken to the order by clause in a
subquery. The execution plans look different in the test than in production
server. The databases are identical. What could be causing this?
The production server is much beefier than the test.
Thanks
RahulPlenty of things, e.g. out of date statistics, missing indexes,
fragmentation, different size of data...
Can you be more specific about how the "execution plans look different"? Is
one doing a seek and the other a scan, or is one doing a different kind of
join, or...?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:uwMMzLbuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have stored procedure that is taking longer time in production than in
>Test. I narrowed the longer time being taken to the order by clause in a
>subquery. The execution plans look different in the test than in production
>server. The databases are identical. What could be causing this?
> The production server is much beefier than the test.
> Thanks
> Rahul
>
Perfomance
I have stored procedure that is taking longer time in production than in
Test. I narrowed the longer time being taken to the order by clause in a
subquery. The execution plans look different in the test than in production
server. The databases are identical. What could be causing this?
The production server is much beefier than the test.
Thanks
RahulPlenty of things, e.g. out of date statistics, missing indexes,
fragmentation, different size of data...
Can you be more specific about how the "execution plans look different"? Is
one doing a seek and the other a scan, or is one doing a different kind of
join, or...?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:uwMMzLbuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have stored procedure that is taking longer time in production than in
>Test. I narrowed the longer time being taken to the order by clause in a
>subquery. The execution plans look different in the test than in production
>server. The databases are identical. What could be causing this?
> The production server is much beefier than the test.
> Thanks
> Rahul
>
Test. I narrowed the longer time being taken to the order by clause in a
subquery. The execution plans look different in the test than in production
server. The databases are identical. What could be causing this?
The production server is much beefier than the test.
Thanks
RahulPlenty of things, e.g. out of date statistics, missing indexes,
fragmentation, different size of data...
Can you be more specific about how the "execution plans look different"? Is
one doing a seek and the other a scan, or is one doing a different kind of
join, or...?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message
news:uwMMzLbuHHA.4916@.TK2MSFTNGP04.phx.gbl...
>I have stored procedure that is taking longer time in production than in
>Test. I narrowed the longer time being taken to the order by clause in a
>subquery. The execution plans look different in the test than in production
>server. The databases are identical. What could be causing this?
> The production server is much beefier than the test.
> Thanks
> Rahul
>
Subscribe to:
Posts (Atom)