performance trace

Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?
You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>
|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:

> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
>
>
|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
David Gugick
Quest Software
www.imceda.com
www.quest.com

performance trace

Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
--
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> > Does anyone know how to track the actual execution plan the server uses? I
> > have one query designed for a frequently run report that normally takes
> > about
> > 3 seconds to return. However, the Profiler logs show me that in certain
> > cases, it took more than 3 minutes to return and it cost a huge amount of
> > CPU
> > time. However, when I test it manually, it always returns in less than 3
> > seconds and nothing wrong in the execution plan. I am wondering that at
> > runtime, the server might decide to choose a different plan, especially
> > when
> > the report is called by multiple users at the same time. If I can capture
> > the
> > execution plan at runtime, I might be able to explain why the performance
> > can
> > vary so dramatically, and find a way to tune it.
> >
> > Another thing, not sure if it is related or not. I am using table hint to
> > direct the server to use one specific index in order to speed it up.
> > Because
> > I noticed if I don't, server sometimes choose a cluster index scan which
> > is
> > too slow and costly. So, if I use table hint in the query and several
> > instances of it are called concurrently, is there any performance
> > concerns?
> >
> >
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

performance trace

Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes abou
t
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CP
U
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture th
e
execution plan at runtime, I might be able to explain why the performance ca
n
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:

> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint
is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorro
w.
> If you need to specify a hint it is possible that the query can be rewritt
en
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
David Gugick
Quest Software
www.imceda.com
www.quest.com

performance tips

got a friend with sql server 2000 that keeps running slow, he'll restart it
and it will run fine for awhile but will gradually get slower.
He's a developer that got DBA thrust upon him.
Wht are some performance tips to help fix this?
thankssome other links that are very useful
Overview of General SQL Server Performance methodology
http://support.microsoft.com/defaul...kb;en-us;298475
Online seminar on SQL2k Performance by a MS PSS guy who knows his stuff
http://support.microsoft.com/defaul...kb;en-us;838622
cheers,
Andy.
"mike w." <mikew@.discussions.microsoft.com> wrote in message
news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
> got a friend with sql server 2000 that keeps running slow, he'll restart
it and it will run fine for awhile but will gradually get slower.
> He's a developer that got DBA thrust upon him.
> Wht are some performance tips to help fix this?
> thanks|||Great Links Andy. Thanks a ton. I have seen some other
links posted in below threads. Very useful!
Regards
Thirumal
www.thirumal.com
>--Original Message--
>some other links that are very useful
>Overview of General SQL Server Performance methodology
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475
>Online seminar on SQL2k Performance by a MS PSS guy who
knows his stuff
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;838622
>cheers,
>Andy.
>
>"mike w." <mikew@.discussions.microsoft.com> wrote in
message
>news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
slow, he'll restart[vbcol=seagreen]
>it and it will run fine for awhile but will gradually get
slower.
>
>.
>

performance tips

got a friend with sql server 2000 that keeps running slow, he'll restart it and it will run fine for awhile but will gradually get slower.
He's a developer that got DBA thrust upon him.
Wht are some performance tips to help fix this?
thanksHi Mike,
You need to check whether any other applications running
on the same box are competing with SQL Server for memory
and CPU. Sounds he might be having blocking issues too.
With little information you provided its tough to guess
the problem. I suggest your friend to start here..
Great stuff on SQL Server performance tuning.
http://www.sql-server-performance.com
The Definitive GuideT to SQL Server Performance
Optimization eBook
http://www.veritas.com/Vrt/offer?
_requestid=28701&a_id=3807&
Also checkout the book must for a DBA on performance
tuning wrote by whalen and team at microsoft.
HTH
--
Regards
Thirumal
www.thirumal.com
>--Original Message--
>got a friend with sql server 2000 that keeps running
slow, he'll restart it and it will run fine for awhile but
will gradually get slower.
>He's a developer that got DBA thrust upon him.
>Wht are some performance tips to help fix this?
>thanks
>.
>|||some other links that are very useful
Overview of General SQL Server Performance methodology
http://support.microsoft.com/default.aspx?scid=kb;en-us;298475
Online seminar on SQL2k Performance by a MS PSS guy who knows his stuff
http://support.microsoft.com/default.aspx?scid=kb;en-us;838622
cheers,
Andy.
"mike w." <mikew@.discussions.microsoft.com> wrote in message
news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
> got a friend with sql server 2000 that keeps running slow, he'll restart
it and it will run fine for awhile but will gradually get slower.
> He's a developer that got DBA thrust upon him.
> Wht are some performance tips to help fix this?
> thanks|||Great Links Andy. Thanks a ton. I have seen some other
links posted in below threads. Very useful!
Regards
Thirumal
www.thirumal.com
>--Original Message--
>some other links that are very useful
>Overview of General SQL Server Performance methodology
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475
>Online seminar on SQL2k Performance by a MS PSS guy who
knows his stuff
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;838622
>cheers,
>Andy.
>
>"mike w." <mikew@.discussions.microsoft.com> wrote in
message
>news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
>> got a friend with sql server 2000 that keeps running
slow, he'll restart
>it and it will run fine for awhile but will gradually get
slower.
>> He's a developer that got DBA thrust upon him.
>> Wht are some performance tips to help fix this?
>> thanks
>
>.
>

performance tips

got a friend with sql server 2000 that keeps running slow, he'll restart it and it will run fine for awhile but will gradually get slower.
He's a developer that got DBA thrust upon him.
Wht are some performance tips to help fix this?
thanks
some other links that are very useful
Overview of General SQL Server Performance methodology
http://support.microsoft.com/default...b;en-us;298475
Online seminar on SQL2k Performance by a MS PSS guy who knows his stuff
http://support.microsoft.com/default...b;en-us;838622
cheers,
Andy.
"mike w." <mikew@.discussions.microsoft.com> wrote in message
news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
> got a friend with sql server 2000 that keeps running slow, he'll restart
it and it will run fine for awhile but will gradually get slower.
> He's a developer that got DBA thrust upon him.
> Wht are some performance tips to help fix this?
> thanks
|||Great Links Andy. Thanks a ton. I have seen some other
links posted in below threads. Very useful!
Regards
Thirumal
www.thirumal.com
>--Original Message--
>some other links that are very useful
>Overview of General SQL Server Performance methodology
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475
>Online seminar on SQL2k Performance by a MS PSS guy who
knows his stuff
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;838622
>cheers,
>Andy.
>
>"mike w." <mikew@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
slow, he'll restart
>it and it will run fine for awhile but will gradually get
slower.
>
>.
>

performance statistics

Hello, I want to use a command line utility to run queries and gather
performance statistics on them. I would like to dump my query results
to nul because all I really care about is the running time. Does anyone
know how to get this using osql? The -p option prints to stdout along
with the query.
Something similar to the db2batch -o r 0
Thanks,
Jen
--
Posted via http://dbforums.comTry:
OSQL -E -S Myserver -i"MyScriptFile.sql" -o NUL
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"moxie" <member44687@.dbforums.com> wrote in message
news:3495957.1066436974@.dbforums.com...
> Hello, I want to use a command line utility to run queries and gather
> performance statistics on them. I would like to dump my query results
> to nul because all I really care about is the running time. Does
anyone
> know how to get this using osql? The -p option prints to stdout along
> with the query.
>
> Something similar to the db2batch -o r 0
>
> Thanks,
> Jen
>
> --
> Posted via http://dbforums.com|||This seams like a resonable approach, but -o nul dumps all of the output
including the performance statistics from -p. I want to keep the
performance statistics.
I guess I want something like setting rowcount 1, but this seems to only
process the query as a top 1 query and stops query processing when the
first tuple is output, especially when it is a projection.
Thanks,
Jen
Posted via http://dbforums.com|||If you simply want to ignore the query output, you might consider
selecting the results into variables like the example below. This will
provide OSQL metrics that don't include resultset processing
DECLARE @.MyData int
SELECT @.MyData = MyData FROM MyTable
There may be better ways to gather performance metrics, though. Can you
elaborate on your objectives?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"moxie" <member44687@.dbforums.com> wrote in message
news:3503080.1066686834@.dbforums.com...
> This seams like a resonable approach, but -o nul dumps all of the
output
> including the performance statistics from -p. I want to keep the
> performance statistics.
>
> I guess I want something like setting rowcount 1, but this seems to
only
> process the query as a top 1 query and stops query processing when the
> first tuple is output, especially when it is a projection.
>
> Thanks,
> Jen
>
> --
> Posted via http://dbforums.com

Performance slow in Vista. Please help.

I have an application I'm trying to run under Vista using SQL 2005 as the
backend database. Right now the application is running super slow and I dont
know why. If I run SQL 2005 under a Virtual machine running xp, the app runs
fine but if I run the application with SQL 2005 under vista performance is
slow.
Configuration:
Vista all patches according to Windows Update
SQL 2005 - SP2
Doesnt work
Vista - running SQL 2005 in a VM under XP
Works
Can someone point me in the correct direction? Thanks in advance!!
MikeWhen an SQL-Server application is running slow, the first thing to do is to
make sure that the statistics are up to date by using sp_updatestats stored
procedure (or use UPDATE STATISTICS is you want to work on a more detailed
level) and cleaning the caches after that:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
(See
http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41 )
.
If this doesn't work, you can try reindexing everything:
http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/
Finally, things like using the option WITH RECOMPILE or playing with the
parameters sniffing (search this newsgroup with Google for more details) can
help you; as well as other things related to optimizing any sql-server
application.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:0EE6E1F8-2CEF-4B06-AE2B-B19F01D9C954@.microsoft.com...
>I have an application I'm trying to run under Vista using SQL 2005 as the
> backend database. Right now the application is running super slow and I
> dont
> know why. If I run SQL 2005 under a Virtual machine running xp, the app
> runs
> fine but if I run the application with SQL 2005 under vista performance is
> slow.
> Configuration:
> Vista all patches according to Windows Update
> SQL 2005 - SP2
> Doesnt work
>
> Vista - running SQL 2005 in a VM under XP
> Works
> Can someone point me in the correct direction? Thanks in advance!!
> Mike|||I have the same problem and I believe it has nothing to do with SQL Server
maintenance. It look to me that Vista has a bug when running SQL Server 2005.
I am running a .NET 2.0 winform application on a Vista machine. The app runs
about 200 updates on a single table. When updating the Vista SQL Server 2005
instance the time is more then double comparing to the time to complete when
updating the XP SQL Server 2005 instance. Plus the Vista machine is Intel
Core 2 Quad, while the XP machine is Intel Pentium D.
Alex
"Sylvain Lafontaine" wrote:
> When an SQL-Server application is running slow, the first thing to do is to
> make sure that the statistics are up to date by using sp_updatestats stored
> procedure (or use UPDATE STATISTICS is you want to work on a more detailed
> level) and cleaning the caches after that:
> DBCC FLUSHPROCINDB
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> (See
> http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41 )
> ..
> If this doesn't work, you can try reindexing everything:
> http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/
> Finally, things like using the option WITH RECOMPILE or playing with the
> parameters sniffing (search this newsgroup with Google for more details) can
> help you; as well as other things related to optimizing any sql-server
> application.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:0EE6E1F8-2CEF-4B06-AE2B-B19F01D9C954@.microsoft.com...
> >I have an application I'm trying to run under Vista using SQL 2005 as the
> > backend database. Right now the application is running super slow and I
> > dont
> > know why. If I run SQL 2005 under a Virtual machine running xp, the app
> > runs
> > fine but if I run the application with SQL 2005 under vista performance is
> > slow.
> >
> > Configuration:
> >
> > Vista all patches according to Windows Update
> > SQL 2005 - SP2
> >
> > Doesnt work
> >
> >
> > Vista - running SQL 2005 in a VM under XP
> >
> > Works
> >
> > Can someone point me in the correct direction? Thanks in advance!!
> >
> > Mike
>
>

Performance Question

I have a stored procedure that a report uses. When I run this sp in
management studio it takes about four and a half minutes to run. When I run
the report, the report takes more than 15 minutes to generate. Any idea why
there is such a large difference in the time it takes to run the sp vs
generate the report?
Thanks.How many records are returned? The number of records being rendered makes a
big difference with Reporting Services.
Also, a 4 1/2 minute report is very long, have you looked into optimizing
it. For instance, does your database need some additional indexes?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tim Kelley" <tkelley@.company.com> wrote in message
news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I have a stored procedure that a report uses. When I run this sp in
>management studio it takes about four and a half minutes to run. When I
>run the report, the report takes more than 15 minutes to generate. Any
>idea why there is such a large difference in the time it takes to run the
>sp vs generate the report?
> Thanks.
>|||Where is the data located?
Maybe on the reporting server it takes longer to transmit the info.
"Tim Kelley" wrote:
> I have a stored procedure that a report uses. When I run this sp in
> management studio it takes about four and a half minutes to run. When I run
> the report, the report takes more than 15 minutes to generate. Any idea why
> there is such a large difference in the time it takes to run the sp vs
> generate the report?
> Thanks.
>
>|||How is the data being rendered? Are you using a matrix versus a table
to display the data?
HTH
Jason Strate
On Oct 15, 2:27 pm, "Tim Kelley" <tkel...@.company.com> wrote:
> I have a stored procedure that a report uses. When I run this sp in
> management studio it takes about four and a half minutes to run. When I run
> the report, the report takes more than 15 minutes to generate. Any idea why
> there is such a large difference in the time it takes to run the sp vs
> generate the report?
> Thanks.|||The data is stored on a different SQL server.
Tim
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:99D473D3-5B0C-44DD-8326-5D63415A3510@.microsoft.com...
> Where is the data located?
> Maybe on the reporting server it takes longer to transmit the info.
>
>
> "Tim Kelley" wrote:
>> I have a stored procedure that a report uses. When I run this sp in
>> management studio it takes about four and a half minutes to run. When I
>> run
>> the report, the report takes more than 15 minutes to generate. Any idea
>> why
>> there is such a large difference in the time it takes to run the sp vs
>> generate the report?
>> Thanks.
>>|||Before optimizing the stored procedure it would run for 45 minutes and not
finish. The sp returns 998 records.
Tim
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eeQTgM2DIHA.5788@.TK2MSFTNGP05.phx.gbl...
> How many records are returned? The number of records being rendered makes
> a big difference with Reporting Services.
> Also, a 4 1/2 minute report is very long, have you looked into optimizing
> it. For instance, does your database need some additional indexes?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tim Kelley" <tkelley@.company.com> wrote in message
> news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>>I have a stored procedure that a report uses. When I run this sp in
>>management studio it takes about four and a half minutes to run. When I
>>run the report, the report takes more than 15 minutes to generate. Any
>>idea why there is such a large difference in the time it takes to run the
>>sp vs generate the report?
>> Thanks.
>|||That is not many records so rendering should not be an issue. Try using With
Recompile when creating the stored procedure and see if that makes any
difference.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ubvT7s2DIHA.4772@.TK2MSFTNGP02.phx.gbl...
> Before optimizing the stored procedure it would run for 45 minutes and not
> finish. The sp returns 998 records.
> Tim
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eeQTgM2DIHA.5788@.TK2MSFTNGP05.phx.gbl...
>> How many records are returned? The number of records being rendered makes
>> a big difference with Reporting Services.
>> Also, a 4 1/2 minute report is very long, have you looked into optimizing
>> it. For instance, does your database need some additional indexes?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Tim Kelley" <tkelley@.company.com> wrote in message
>> news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>>I have a stored procedure that a report uses. When I run this sp in
>>management studio it takes about four and a half minutes to run. When I
>>run the report, the report takes more than 15 minutes to generate. Any
>>idea why there is such a large difference in the time it takes to run the
>>sp vs generate the report?
>> Thanks.
>>
>

Performance problems with Nested SCOPE statements

I'm hoping someone has run across this particular issue and/or has identified some acceptable alternatives:

In using some scope statements to alter the behavior for certain measures, I've determined that when scope statements are nested, they will impact performance dramtically. For example

Scope [Measures].[myMeasure];
Scope Leaves( [Periods] ); -- Approx 100 members
Scope Leaves( [Markets] ); -- Approx 1500 members
This = { some simple calculation }
End scope;
End scope;
End scope;

NB. A 4th dimension, Products has about 450,000 members

At connect time, this statement is re-evaluated. The result is a long connect time. If the scope statement is removed for either Periods of Markets, the connection is immeadiate, but of course the results are wrong. Order of the scope statements doesn't affect the outcome (the [Measure] scope must be first).

I understand the difference in scale (100 or 1500 vs 150,000) but so far, the results seem to be out of line with reasonable expectations. (< 1 second vs ~65 seconds).

NB. The techqiue, causes the calculation to be evaluated at the product level, then SUM'ed over the Periods and Markets. All levels of Product aggregation use / evaluate the same calculation.

Thoughts & suggestions?

It probably depends on what is in your {some simple calculation}. There may be an alternative approach which would be faster, but it's impossible to say without more details. I don't think its the nesting of scopes that is the issue, it's more related to the size of the sub cube over which you are performing the assignment.

The same thing could be expressed as a single scope statement, but I would not expect this to change the performance at all.

Scope (Leaves([Periods]) * Leaves([Markets]));

([Measures].[myMeasure]) = "some simple calculation";

End Scope;

|||

Thanks for your feedback.

First, you're right that changing the "form" of the scope statements doesn't change the performance characteritics. (Already tried that). Indeed, the problem seems to the the size / scope of the sub cube being accessed.

The performance characteritics is indeed linked to the complexity of the calculation. For example, if I use this = 1; (the simplest possible expression), the connect returns immeadiately.

In this case, my target expression is:

this = Iif( [myMeasure], 1, null)

Essentually, this "resets" the expression to 1, for every level of the product attributes / hierarchies, then aggregates the result within stores and periods.

My basic problem is how to stop this calculation from occuring "during the connect" and instead have it invoked / calculated when the measure is actually used (when I'm willing to take the performance hit). It's easy to see / test, since in VS, I simply browse the cube and reconnect and watch Task Manager on the server. (It jumps to 100% for 60 seconds or so).

NB. The server in question is a x64, 4 way, dual core Opteron (2.6 Ghz) with 20 Gig of memory, running Enterprise edition (SP2). Everything is running 64 bit code.

|||

Found the answer, so I thought I'd post it for other people who might trip across this issue:

The problem actually exists in the front end "tool", not in Analysis Services. In this case, I was testing / debugging with Visual Studio, making small modifications to the calculation, then quickly browsing.

Turns out VS issues the following statement shortly after connecting:

select filter([Measures].members, [Measures].currentmember.member_caption = [Measures].currentmember.member_caption) on columns from [myCube]

This statement causes all of the facts to be referenced / resolved, including the measure in question. Therefore, a full bottom up calculation is performed. The query only ends after 60 seconds, because VS uses a 60 second timeout. (No wonder all of my tests came out the same!)

I can only speculate, that the reason VS does this, is to "warm" the cache, to make the subsequent queries faster. Results (and error messages) are discarded.

NB. For the record, this particular expression isn't intended to be used at the "higher levels". But, since I've isolated the key performance issue, at least alternatives can no be developed.

|||Glad you found the issue. I tend to test more complicated stuff with queries in SSMS, rather than using the browser, so I have not hit this issue myself. The OWC browser in VS does not really have a reputation for sending the "best" MDX.

Performance Problems Question

a lot of our users get Sql Server timeout errors from what appear to be
pretty basic queries run from our web app. the server should be well
equipped hardware wise to deal with server load (dual proc xeon with nearly
4 gb ram raid 5). concurrent user connections average around 300; it handles
an average of 4 transactions a second. running the queries themselves via QA
in some cases takes an extremely long time. otehr times, they run in a
timely fashion.
I notice on perfmon a something that raised an eyebrow - the database is
spread over 2 filegroups, primary is about 4.5GB, secondary index filegroup
is about 2.3 GB. but we are getting very high disk queue values - averaging
50 over an hour or so.
While I know that as general rule of thum the threshold for disk queue is 2
* physical drives, 2 of the drives are hardly used. One drive contains only
backups, the second contains Diskeeper files, the 3rd is the OS drive. In
theory, only 2 of the drives are being written to during normal database
usage.
My question - is THIS normal (can I rule out the queue as a bottleneck),
given that we arent separating the filegroups over the RAID?I notice the system gets tweaked during Page Reads... probably indexes then?
"Ick" <nospamplease> wrote in message
news:uFZMAsphDHA.4088@.tk2msftngp13.phx.gbl...
> a lot of our users get Sql Server timeout errors from what appear to be
> pretty basic queries run from our web app. the server should be well
> equipped hardware wise to deal with server load (dual proc xeon with
nearly
> 4 gb ram raid 5). concurrent user connections average around 300; it
handles
> an average of 4 transactions a second. running the queries themselves via
QA
> in some cases takes an extremely long time. otehr times, they run in a
> timely fashion.
> I notice on perfmon a something that raised an eyebrow - the database is
> spread over 2 filegroups, primary is about 4.5GB, secondary index
filegroup
> is about 2.3 GB. but we are getting very high disk queue values -
averaging
> 50 over an hour or so.
> While I know that as general rule of thum the threshold for disk queue is
2
> * physical drives, 2 of the drives are hardly used. One drive contains
only
> backups, the second contains Diskeeper files, the 3rd is the OS drive. In
> theory, only 2 of the drives are being written to during normal database
> usage.
> My question - is THIS normal (can I rule out the queue as a bottleneck),
> given that we arent separating the filegroups over the RAID?
>
>

Performance problem -- Execute stored procedure

Hello,
I have a problem on running one particular stored procedure. It takes
less than 1 second when I run this stored procedure using SQL query
analyzer. However, when I run the same stored procedure using
Reporting Services, it takes 3,4 minutes to execute. This stored
procedure returns 52 rows with 16 fields. Does anyone know why and
how to solve this problem?
Thanks!I have never seen this myself but have heard of it before. For whatever
reason the query plan is messed up for that stored procedure when executing
it from RS. Try one of the below (I would start off with the With Recompile
as a test of whether this is the problem).
Forcing a Stored Procedure to Recompile
SQL Server provides three ways to force a stored procedure to recompile:
a.. The sp_recompile system stored procedure forces a recompile of a
stored procedure the next time it is run.
b.. Creating a stored procedure that specifies the WITH RECOMPILE option
in its definition indicates that SQL Server does not cache a plan for this
stored procedure; the stored procedure is recompiled each time it is
executed. Use the WITH RECOMPILE option when stored procedures take
parameters whose values differ widely between executions of the stored
procedure, resulting in different execution plans to be created each time.
Use of this option is uncommon and causes the stored procedure to execute
more slowly, because the stored procedure must be recompiled each time it is
executed.
If you only want individual queries inside the stored procedure to be
recompiled, rather than the entire stored procedure, specify the RECOMPILE
query hint inside each query you want recompiled. This behavior mimics SQL
Server's statement-level recompilation behavior noted above, but in addition
to using the stored procedure's current parameter values, the RECOMPILE
query hint also uses the values of any local variables inside the stored
procedure when compiling the statement. Use this option when atypical or
temporary values are used in only a subset of queries belonging to the
stored procedure. For more information, see Query Hint (Transact-SQL).
c.. You can force the stored procedure to be recompiled by specifying the
WITH RECOMPILE option when you execute the stored procedure. Use this option
only if the parameter you are supplying is atypical or if the data has
significantly changed since the stored procedure was created.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<chiekot@.taiweb.com> wrote in message
news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> Hello,
> I have a problem on running one particular stored procedure. It takes
> less than 1 second when I run this stored procedure using SQL query
> analyzer. However, when I run the same stored procedure using
> Reporting Services, it takes 3,4 minutes to execute. This stored
> procedure returns 52 rows with 16 fields. Does anyone know why and
> how to solve this problem?
> Thanks!
>|||On Oct 15, 2:37 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have never seen this myself but have heard of it before. For whatever
> reason the query plan is messed up for that stored procedure when executing
> it from RS. Try one of the below (I would start off with the With Recompile
> as a test of whether this is the problem).
> Forcing a Stored Procedure to Recompile
> SQL Server provides three ways to force a stored procedure to recompile:
> a.. The sp_recompile system stored procedure forces a recompile of a
> stored procedure the next time it is run.
> b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> in its definition indicates that SQL Server does not cache a plan for this
> stored procedure; the stored procedure is recompiled each time it is
> executed. Use the WITH RECOMPILE option when stored procedures take
> parameters whose values differ widely between executions of the stored
> procedure, resulting in different execution plans to be created each time.
> Use of this option is uncommon and causes the stored procedure to execute
> more slowly, because the stored procedure must be recompiled each time it is
> executed.
> If you only want individual queries inside the stored procedure to be
> recompiled, rather than the entire stored procedure, specify the RECOMPILE
> query hint inside each query you want recompiled. This behavior mimics SQL
> Server's statement-level recompilation behavior noted above, but in addition
> to using the stored procedure's current parameter values, the RECOMPILE
> query hint also uses the values of any local variables inside the stored
> procedure when compiling the statement. Use this option when atypical or
> temporary values are used in only a subset of queries belonging to the
> stored procedure. For more information, see Query Hint (Transact-SQL).
> c.. You can force the stored procedure to be recompiled by specifying the
> WITH RECOMPILE option when you execute the stored procedure. Use this option
> only if the parameter you are supplying is atypical or if the data has
> significantly changed since the stored procedure was created.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <chie...@.taiweb.com> wrote in message
> news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> > Hello,
> > I have a problem on running one particular stored procedure. It takes
> > less than 1 second when I run this stored procedure using SQL query
> > analyzer. However, when I run the same stored procedure using
> > Reporting Services, it takes 3,4 minutes to execute. This stored
> > procedure returns 52 rows with 16 fields. Does anyone know why and
> > how to solve this problem?
> > Thanks!
Also, to improve the performance of the stored procedure in general,
you could evaluate it with the Database Engine Tuning Advisor and
implement the suggested indexes where acceptable. Hope this helps
further.
Regards,
Enrique Martinez
Sr. Software Consultant|||Bruce and Enrique, Thank you very much for your responses. We solved
this problem. What our DBA told me is that he changed that the stored
procedure is created with SET QUOTED_IDENTIFIER to ON. I appreciate
all your suggestions.
Retards,
Chieko
On Oct 15, 6:03 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Oct 15, 2:37 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>
>
> > I have never seen this myself but have heard of it before. For whatever
> > reason the query plan is messed up for that stored procedure when executing
> > it from RS. Try one of the below (I would start off with the With Recompile
> > as a test of whether this is the problem).
> > Forcing a Stored Procedure to Recompile
> > SQL Server provides three ways to force a stored procedure to recompile:
> > a.. The sp_recompile system stored procedure forces a recompile of a
> > stored procedure the next time it is run.
> > b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> > in its definition indicates that SQL Server does not cache a plan for this
> > stored procedure; the stored procedure is recompiled each time it is
> > executed. Use the WITH RECOMPILE option when stored procedures take
> > parameters whose values differ widely between executions of the stored
> > procedure, resulting in different execution plans to be created each time.
> > Use of this option is uncommon and causes the stored procedure to execute
> > more slowly, because the stored procedure must be recompiled each time it is
> > executed.
> > If you only want individual queries inside the stored procedure to be
> > recompiled, rather than the entire stored procedure, specify the RECOMPILE
> > query hint inside each query you want recompiled. This behavior mimics SQL
> > Server's statement-level recompilation behavior noted above, but in addition
> > to using the stored procedure's current parameter values, the RECOMPILE
> > query hint also uses the values of any local variables inside the stored
> > procedure when compiling the statement. Use this option when atypical or
> > temporary values are used in only a subset of queries belonging to the
> > stored procedure. For more information, see Query Hint (Transact-SQL).
> > c.. You can force the stored procedure to be recompiled by specifying the
> > WITH RECOMPILE option when you execute the stored procedure. Use this option
> > only if the parameter you are supplying is atypical or if the data has
> > significantly changed since the stored procedure was created.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > <chie...@.taiweb.com> wrote in message
> >news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> > > Hello,
> > > I have a problem on running one particular stored procedure. It takes
> > > less than 1 second when I run this stored procedure using SQL query
> > > analyzer. However, when I run the same stored procedure using
> > > Reporting Services, it takes 3,4 minutes to execute. This stored
> > > procedure returns 52 rows with 16 fields. Does anyone know why and
> > > how to solve this problem?
> > > Thanks!
> Also, to improve the performance of the stored procedure in general,
> you could evaluate it with the Database Engine Tuning Advisor and
> implement the suggested indexes where acceptable. Hope this helps
> further.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -

Performance Problem

The query below is taking 3-4 seconds to run under a light load, which
seems to be a bit lengthy for the indexes that are in place and the
amount of data that exists in the tables. I have outlined everything
below, including all table definitions, indexes, and row counts. Any
help at all will be appreciated. It seems no matter how I think an
index will function it never seems to work properly.
==
BEGIN QUERY
==
SELECT tblC.catDesc AS Category_Name,
COUNT(DISTINCT tblS.set_ID) AS Set_Count,
tblC.cat_ID AS Category_ID,
COUNT(tblI.Img_ID) AS Image_Count,
MIN(tblI.Img_ID) AS Image_ID,
(
SELECT COUNT(tblI2.Img_ID)
FROM tblImage tblI2
LEFT JOIN tblSets tblS2 ON tblS2.set_ID = tblI2.set_ID
WHERE tblI2.d_t > @.d_t
AND tblI2.cat_ID = tblC.cat_ID
AND tblI2.display_status = 1
) AS New_Image_Count,
(
SELECT COUNT(DISTINCT tblI3.set_ID)
FROM tblImage tblI3
LEFT JOIN tblSets tblS3 ON tblS3.set_ID = tblI3.set_ID
WHERE tblI3.d_t > @.d_t
AND tblI3.cat_ID = tblC.cat_ID
) AS New_Set_Count
FROM tblCategories tblC
LEFT JOIN tblImage tblI ON tblC.cat_ID = tblI.cat_ID
LEFT JOIN tblSets tblS ON tblI.set_ID = tblS.set_ID
WHERE tblC.skin_ID = @.skin_ID
GROUP BY tblC.cat_id, tblC.catDesc
==
END QUERY
==
==
tblImage (approx. 71000 rows)
==
Definition:
Img_ID (int, Not Null) - PK
set_ID (int, Null)
cat_ID (int, Null)
d_t (datetime, Null)
display_status (int, Null)
Indexes:
1. Img_ID (clustered)
2. cat_id DESC, display_status DESC, d_t DESC
3. d_t DESC, display_status DESC, set_ID, cat_ID
4. set_ID DESC
==
END tblImage
==
==
tblCategories (approx. 35 rows)
==
Definition:
cat_ID (int, Not Null) - PK
catDesc (varchar(25), Null)
skin_ID (int, Null)
Indexes:
1. cat_ID (clustered)
2. skin_ID, cat_ID
==
END tblCategories
==
==
tblSets (approx. 1500 rows)
==
Definition:
set_ID (int, Not Null) - PK
setName (varchar(25), Null)
setKeywords (varchar(500), Null)
Indexes:
1. set_ID (clustered)
==
END tblSets
==If I understand the query correctly, (If set_ID is unique in tblSets) Then
the following might work and should be faster since it doesn't have the
subquerys...
Select C.catDesc Category_Name,
Count(Distinct S.set_ID) Set_Count,
C.cat_ID Category_ID,
Count(tblI.Img_ID) Image_Count,
Min(I.Img_ID) Image_ID,
Sum(Case When I.d_t = @.d_t
And display_status = 1
Then 1 End) New_Image_Count,
Sum(Case When I.d_t = @.d_t
Then 1 End) New_Set_Count
From tblCategories C
Left Join tblImage I
On I.cat_ID = C.cat_ID
Left Join tblSets S
On S.set_ID = I.set_ID
Where C.skin_ID = @.skin_ID
Group By C.cat_id, C.catDesc
"iTISTIC@.gmail.com" wrote:

> The query below is taking 3-4 seconds to run under a light load, which
> seems to be a bit lengthy for the indexes that are in place and the
> amount of data that exists in the tables. I have outlined everything
> below, including all table definitions, indexes, and row counts. Any
> help at all will be appreciated. It seems no matter how I think an
> index will function it never seems to work properly.
> ==
> BEGIN QUERY
> ==
> SELECT tblC.catDesc AS Category_Name,
> COUNT(DISTINCT tblS.set_ID) AS Set_Count,
> tblC.cat_ID AS Category_ID,
> COUNT(tblI.Img_ID) AS Image_Count,
> MIN(tblI.Img_ID) AS Image_ID,
> (
> SELECT COUNT(tblI2.Img_ID)
> FROM tblImage tblI2
> LEFT JOIN tblSets tblS2 ON tblS2.set_ID = tblI2.set_ID
> WHERE tblI2.d_t > @.d_t
> AND tblI2.cat_ID = tblC.cat_ID
> AND tblI2.display_status = 1
> ) AS New_Image_Count,
> (
> SELECT COUNT(DISTINCT tblI3.set_ID)
> FROM tblImage tblI3
> LEFT JOIN tblSets tblS3 ON tblS3.set_ID = tblI3.set_ID
> WHERE tblI3.d_t > @.d_t
> AND tblI3.cat_ID = tblC.cat_ID
> ) AS New_Set_Count
> FROM tblCategories tblC
> LEFT JOIN tblImage tblI ON tblC.cat_ID = tblI.cat_ID
> LEFT JOIN tblSets tblS ON tblI.set_ID = tblS.set_ID
> WHERE tblC.skin_ID = @.skin_ID
> GROUP BY tblC.cat_id, tblC.catDesc
> ==
> END QUERY
> ==
>
> ==
> tblImage (approx. 71000 rows)
> ==
> Definition:
> Img_ID (int, Not Null) - PK
> set_ID (int, Null)
> cat_ID (int, Null)
> d_t (datetime, Null)
> display_status (int, Null)
> Indexes:
> 1. Img_ID (clustered)
> 2. cat_id DESC, display_status DESC, d_t DESC
> 3. d_t DESC, display_status DESC, set_ID, cat_ID
> 4. set_ID DESC
> ==
> END tblImage
> ==
> ==
> tblCategories (approx. 35 rows)
> ==
> Definition:
> cat_ID (int, Not Null) - PK
> catDesc (varchar(25), Null)
> skin_ID (int, Null)
> Indexes:
> 1. cat_ID (clustered)
> 2. skin_ID, cat_ID
> ==
> END tblCategories
> ==
>
> ==
> tblSets (approx. 1500 rows)
> ==
> Definition:
> set_ID (int, Not Null) - PK
> setName (varchar(25), Null)
> setKeywords (varchar(500), Null)
> Indexes:
> 1. set_ID (clustered)
> ==
> END tblSets
> ==
>

Performance problem

HI, I have a performance problem with one of my set of packages. They run fine on dev machine but much slower on test server which has twice (8gb) the memory of my dev server (4gb). The first time I run the set of package they execute in less than a minute which is normal. But subsequent executions in same conditions (tables are empty) takes 2-3 minutes. When I restart Sql server 2005, the loading time is back to less than a minute.

I enables the "use awe" for memory beyond 4 gb on test server and while executing, total memory is far beyond the 8gb of test server and on the four available processors, only one seems to be used. Also, deployment on other machines have no problems. We rebuilt the test server (re-installed windows, Sql server 2005, SP1 and patches) with no luck.

I would like to monitor the problem using performance monitor and Sql server profiler, is there some specific counters or trace events that I should focus on? My set of packages (one main package calling several execute package tasks) load approximately 100K lines and they are deployes on file system.

Thank you,
Ccote

Not sure about the cause of your problem. I would sugest to enable package logging and compare execution logs between server and try to identify possible botle necks.

Also there is an interesting document about performance tunning:

http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/SSISPerfTuning.doc

performance penalty using views

Is there much of a performance penalty for using views? When I use the view
it takes 42 seconds to return results but when I run the query inside the
view by itself it returns records in 5 to 7 seconds.
Would a stored procedure be better? Would the stored procedure be better at
keeping the execution plan?
Thanks,
Dan D.
Views require some extra time to look up info in system tables and although
they should offer the same execution time as the statement itself they may
have a slower execution time as a result. Although this may improve if the
execution plan is cached. A stored procedure would likely offer better
execution time over a view.
"Dan D." wrote:

> Is there much of a performance penalty for using views? When I use the view
> it takes 42 seconds to return results but when I run the query inside the
> view by itself it returns records in 5 to 7 seconds.
> Would a stored procedure be better? Would the stored procedure be better at
> keeping the execution plan?
> Thanks,
> --
> Dan D.
|||After some more testing, I've discovered that if I delete the view and then
recreate it, I get the same performance from both the view and the raw query.
I guess something got out of sync somewhere.
Thanks,
Dan D.
"Francis" wrote:
[vbcol=seagreen]
> Views require some extra time to look up info in system tables and although
> they should offer the same execution time as the statement itself they may
> have a slower execution time as a result. Although this may improve if the
> execution plan is cached. A stored procedure would likely offer better
> execution time over a view.
>
> "Dan D." wrote:

performance penalty using views

Is there much of a performance penalty for using views? When I use the view
it takes 42 seconds to return results but when I run the query inside the
view by itself it returns records in 5 to 7 seconds.
Would a stored procedure be better? Would the stored procedure be better at
keeping the execution plan?
Thanks,
--
Dan D.Views require some extra time to look up info in system tables and although
they should offer the same execution time as the statement itself they may
have a slower execution time as a result. Although this may improve if the
execution plan is cached. A stored procedure would likely offer better
execution time over a view.
"Dan D." wrote:
> Is there much of a performance penalty for using views? When I use the view
> it takes 42 seconds to return results but when I run the query inside the
> view by itself it returns records in 5 to 7 seconds.
> Would a stored procedure be better? Would the stored procedure be better at
> keeping the execution plan?
> Thanks,
> --
> Dan D.|||After some more testing, I've discovered that if I delete the view and then
recreate it, I get the same performance from both the view and the raw query.
I guess something got out of sync somewhere.
Thanks,
--
Dan D.
"Francis" wrote:
> Views require some extra time to look up info in system tables and although
> they should offer the same execution time as the statement itself they may
> have a slower execution time as a result. Although this may improve if the
> execution plan is cached. A stored procedure would likely offer better
> execution time over a view.
>
> "Dan D." wrote:
> > Is there much of a performance penalty for using views? When I use the view
> > it takes 42 seconds to return results but when I run the query inside the
> > view by itself it returns records in 5 to 7 seconds.
> >
> > Would a stored procedure be better? Would the stored procedure be better at
> > keeping the execution plan?
> >
> > Thanks,
> > --
> > Dan D.

performance penalty using views

Is there much of a performance penalty for using views? When I use the view
it takes 42 seconds to return results but when I run the query inside the
view by itself it returns records in 5 to 7 seconds.
Would a stored procedure be better? Would the stored procedure be better at
keeping the execution plan?
Thanks,
--
Dan D.Views require some extra time to look up info in system tables and although
they should offer the same execution time as the statement itself they may
have a slower execution time as a result. Although this may improve if the
execution plan is cached. A stored procedure would likely offer better
execution time over a view.
"Dan D." wrote:

> Is there much of a performance penalty for using views? When I use the vie
w
> it takes 42 seconds to return results but when I run the query inside the
> view by itself it returns records in 5 to 7 seconds.
> Would a stored procedure be better? Would the stored procedure be better a
t
> keeping the execution plan?
> Thanks,
> --
> Dan D.|||After some more testing, I've discovered that if I delete the view and then
recreate it, I get the same performance from both the view and the raw query
.
I guess something got out of sync somewhere.
Thanks,
--
Dan D.
"Francis" wrote:
[vbcol=seagreen]
> Views require some extra time to look up info in system tables and althoug
h
> they should offer the same execution time as the statement itself they may
> have a slower execution time as a result. Although this may improve if the
> execution plan is cached. A stored procedure would likely offer better
> execution time over a view.
>
> "Dan D." wrote:
>

Performance outside private network

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

Performance outside private network

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

Performance outside private network

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