performance tuning

how to increase performance through management studio
except making indexeshow to increase performance through management studio
except making indexes

Wow. Good luck with that. Let us know how it goes.

hmscott|||Hows about writing more efficient SQL - that's always a good way...

ankur: a vague question will only ever get a vague answer.|||Good database design. Well written queries. Well thought out indexes. A decent layout of data and log files on disk. Just a few of the things you want to consider, there's no single magic pill answer. Tuning a database server is like tuning a car - make the engine more powerful and you need a better exhaust, and a bigger gas tank as it's less fuel efficient, which add weight.
More power means better brakes and better cooling, which means more weight. Which in turn means more power and better brakes. Change one thing and it affects others, you can't alter anything in isolation in a database server without knowing the knock on effect. Point I'm making is you need to learn about the whole thing, not just one in isolation. There is no magic, single answer - unless you just turn it over to a decent DBA who's skilled in knowing what he's doing. This is where Management Studio stops poviding the answers and your brain has to start working hard, it's an aid to your mind, not a replacement ! Dig into SQL Server Books Online and start learning is my best advice :)|||I just have to say: what a fantastic answer! :beer:

Performance Reports

Hi,
Could you please tell me what kind of performance reports
do you hand in to your management? Any tips, templates,
recommendations are welcome.
Thanks,
OskHi
You can find some useful info there
http://www.sql-server-performance.com
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:2b9901c5079f$9ad6a580$a601280a@.phx.gbl...
> Hi,
> Could you please tell me what kind of performance reports
> do you hand in to your management? Any tips, templates,
> recommendations are welcome.
> --
> Thanks,
> Osk

Performance Reports

Hi,
Could you please tell me what kind of performance reports
do you hand in to your management? Any tips, templates,
recommendations are welcome.
--
Thanks,
OskHi
You can find some useful info there
http://www.sql-server-performance.com
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:2b9901c5079f$9ad6a580$a601280a@.phx.gbl...
> Hi,
> Could you please tell me what kind of performance reports
> do you hand in to your management? Any tips, templates,
> recommendations are welcome.
> --
> Thanks,
> Osk

Performance Reports

Hi,
Could you please tell me what kind of performance reports
do you hand in to your management? Any tips, templates,
recommendations are welcome.
Thanks,
Osk
Hi
You can find some useful info there
http://www.sql-server-performance.com
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:2b9901c5079f$9ad6a580$a601280a@.phx.gbl...
> Hi,
> Could you please tell me what kind of performance reports
> do you hand in to your management? Any tips, templates,
> recommendations are welcome.
> --
> Thanks,
> Osk
sql

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 SMI

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

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

-Francis

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

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

Have you tried looking into it with SQL Profiler?

HTH..

Joe

Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.
The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)
Any idea what could be the issue here? The server
is not really heavily taxed.
The tables are small. They have very few rows.
VGNCCB_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
The problem Query:
select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))
I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.
TIA,
Jack
.See my reply to your previous post from this morning.
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:fc6001c43e8b$7d505750$a301280a@.phx.gbl...
> Guys,
> I'm stumped. While its not pertinent to the
> matter, we are running a Vignette content management
> system on Win2k with Sql 2000 Enterprise on a cluster.
> The server has 2 Gig of RAM , 2 CPU's and the database
> size is 1.5G.
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)
> Any idea what could be the issue here? The server
> is not really heavily taxed.
> The tables are small. They have very few rows.
> VGNCCB_ROLE 939
> VGNCCB_ROLE_JT 62389
> VGNCCB_GROUP_USER_JT 1364
>
> The problem Query:
> select
> ROLE_ID,
> NAME,
> DESCRIPTION,
> CREATE_DATE,
> MODIFIED_DATE
> FROM
> vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
> WHERE
> ROLE_ID in
> (select ROLE_ID
> FROM
> vign.VGNCCB_ROLE_JT -- Non clustered indexes
> on USER_NAME AND non clustered on GROUP_ID
> WHERE
> USER_NAME = 'testRole' or GROUP_ID in (select
> GROUP_ID
> FROM
> vign.VGNCCB_GROUP_USER_JT -- Non clustered
> index on USER_NAME
> WHERE
> USER_NAME = 'testRole'))
> I'd appreciate it if someone could follow me in this
> thread to completion. Such a simple query should not take
> this long.
>
> TIA,
> Jack
> .
>