Showing posts with label web. Show all posts
Showing posts with label web. Show all posts

Wednesday, March 28, 2012

Performance sqlclient data provider

Well, I hope, after my endless search in the web... maybe, somebody can help me here.

I'm passing a application to a web-based application, therefore I have to use ADO.net to access a sql server DB. But the performance is extremly poor!!!!

Just to compare: with the normal application or with the SQL Query Analyzer, the query needs about 2 seconds. Well, by using ado.net, it's about 200 seconds.....!!!!

Of course, the query is quite long. But the difference is extrem... too extrem for using the same query...

I already tried a lot... actually everything I found on the web. What is really strange is that neither the processor nor the memory is fully used... And the web server use more of the cpu power than the sql server itself (the 2 server are still on my machine where I'm programming). So, could it be that the problem is by filling up the dataset with the sqladapter or something similar?

Thx for your help!!

Two words, "stored procedure". If you query is long you're passing to the DB each time expensive in so many ways. Plus make sure you're re-using the connection by passing in exactly the same details...actually just use a stored procedure.

|||Thx pkr! I already read that point to improve the performance and I tried it. Unfortunately, it's not the solution. May be, it increases a little bit the performance, but it seems that the problem is elsewhere...

What I found out till now:
- it's not the hardware
- it's not the connection
- it's not the amount of data sent between the web server and the db server

actually, other queries are executed quite fast and without this performance leak.

so, quite possible is that the problem is the query itself. Only the fact, that the execution time of the query by the query analyzer is 100x faster than over ADO.net can't be normal. There has to be something ... but where?

Any idea?

also, with other queries, the performance of ADO.net and the query analyzer is almost the same. So, where does this performance leak coming from?
|||thx rtk! It's not the first time I've read about the stored procedure and I tried it. Unfortunately, it's not the solution to my problem. It gives my an improvement, but just a tiny one.

The problem has to be elsewhere...

What I found out until now:
- it's not the hardware
- it's not the connection
- it's not the amount of data sent between the web server and the db server

So, it sounds like it's the query itself. What is so strange is the performance difference between the sql query analyzer and the ado.net. The query analyer executes the query 100x faster than through ado.net. It's just that other queries execute fast through ado.net.

Here the query:

SELECT table1.col1, table2.col1, count(table2.col2) as counter
FROM table3,table1, table2
WHERE table3.Valid = 1
AND table3.SessionId = table2.SessionId
AND table2.col4 *= table1.col2
AND table2.col3 IN ('01','02','04')
AND table3.SessionId IN (
SELECT SessionId
FROM userTable
WHERE UserName = 'userABC' )
GROUP BY table1.col1, table2.col1

comment: I know the "*=" is not the nicest solution (should be a right outer join), but I used it in other queries too and it executes fast.

any idea, what I can improve... or if it's really the query, how to write the query new?|||by the way, here the query:

SELECT table1.col1, table2.col1, count(table2.col2) as counter
FROM table3,table1, table2
WHERE table3.Valid = 1
AND table3.SessionId = table2.SessionId
AND table2.col4 *= table1.col2
AND table2.col3 IN ('01','02','04')
AND table3.SessionId IN (
SELECT SessionId
FROM userTable
WHERE UserName = 'userABC' )
GROUP BY table1.col1, table2.col1

comment: I know the "*=" is not the nicest solution (should be a rightouter join), but I used it in other queries too and it executes fast.

any idea, what I can improve... or if it's really the query, how to write the query new?|||If you're telling me that the self same query as a stored procedure is 100x slower than in QA then the only thing I can think of is that it's the initial compile time. So in QA in DBCC FreeProcCache and Drop Clean Buffers then run the QA again. This should put the two mechanisms on a level playing field. That wold then suggest that the query is too complicated (although it doesn't look it). I would also move to use "inner join" syntax and remove the sub query.|||I didn't understand exactly what you meant with the FreeProcCache. But isn't it a modification in the QA. It can't be that.

What I also thought of is that the priority of a .net data provider is not that high like when a processing the same query with the QA? Could that be? How to change?

I will try to use the inner join. Actually I did some research on the query and there are 2 parts which ones are slowing down the whole thing.
1. the join... of course
2. the IN ('02', ...)

But the point is, that there has to be something different because of having this huge performance difference between QA and ado.net with the same query. So, how does ad.net handle the query exactly? Where are the diferences between the 2 methods? ... There has to be something and it is really important to find that for me.

Thanks for you help, anyway!!|||

First, create the stored procedure. Then call it (the stored procedure) from both QA and ADO.NET and see what the time difference is.

I'd recommend updating your SQL Syntax as well, but that shouldn't make a difference between the two.

To your question, yes, there are certain differences between the connection settings be default in QA and ADO.NET. One or more of these settings may be the culprit.

|||

Given a level playing field there are only a few minor differences between QA and a sqlClient call. The typical pitful of using QA is to do with the way SQL caches its query, and to some extent its data. I'll exaggerate to make the point. Consider you've developed a query that is complicated for SQL to compile a plan for, remember that a plan is cached for each user. In QA it take 60 secs to compile and cache the plan. So next time you run the query from QA the cache is ready and although the query is complex, it only take 1 second to run. Eureka you shout, and you make the call from your ADO client. However, you've decided to use a different connection string per user, e.g. via NT Authentication. So each time a new user runs the query for the first time you incur the 60 sec overhead.

The "free proc cache" et al, remove all the caches of procedures and data so next time you run the query in QA you can see if any of the above is the problem. They are command you can issue from QA, look up "DBCC" in the help for information on how to run them (they're very simple)...although don't do it on a production server!

sql

Friday, March 23, 2012

Performance Question

I have a need to configure a mission critical SQL and Web server and
also looking for possible failover/redundancy
We will be running Windows Server 2003 Enterprise Edition as well as
SQL 2000.
We want to go with 4GB of RAM. We want a SQL cluster environment which
would include having an external direct attached storage system. I
also want a good hardware backup solution.
Minimum Database Server Requirements:
Intel Xeon Processor 2GHz minimum (Dual Processor 3.06GHz, 1M Cache
Recommended)
70+ GB for database and temp database. Additional storage is reuired if
backup copies of the database are stored on the same server.
Raid 0+1 Recommended (4 drives connected to RAID card)
We will start off with about 100 users and grow to about 700-800 users
within the next 1 1/2 to 2 years.
Based on this, and the minimum requirements, could anyone make some
recommendations on what I should be looking at hardware wise to not
only accomodate our immediate needs, but also our future needs.
Thanks for you help!"Charles" <webmail2@.hotmail.com> wrote in message
news:1149728115.858242.294110@.i39g2000cwa.googlegroups.com...
>I have a need to configure a mission critical SQL and Web server and
> also looking for possible failover/redundancy
> We will be running Windows Server 2003 Enterprise Edition as well as
> SQL 2000.
> We want to go with 4GB of RAM. We want a SQL cluster environment which
> would include having an external direct attached storage system. I
> also want a good hardware backup solution.
> Minimum Database Server Requirements:
> Intel Xeon Processor 2GHz minimum (Dual Processor 3.06GHz, 1M Cache
> Recommended)
> 70+ GB for database and temp database. Additional storage is reuired if
> backup copies of the database are stored on the same server.
> Raid 0+1 Recommended (4 drives connected to RAID card)
> We will start off with about 100 users and grow to about 700-800 users
> within the next 1 1/2 to 2 years.
> Based on this, and the minimum requirements, could anyone make some
> recommendations on what I should be looking at hardware wise to not
> only accomodate our immediate needs, but also our future needs.
>
Clustering can be somewhat complicated to implement and manage correctly and
the hardware it requires can be expensive. It can be a great solution, but
a poorly implemented or managed cluster will reduce your availibility
instead of increasing it. And remember, there's no redundancy for your
databases in a cluster, so your storage solution is a single point of
failure.
For clusters your hardware must be certified.
The Microsoft support policy for server clusters, the Hardware Compatibility
List, and the Windows Server Catalog
http://support.microsoft.com/?id=309395
Database Mirroring in SQL Server 2005 provides a cheap and easy way to
achieve complete redundancy for your data and very high availibility. You
don't need external storage or matched, certified servers.
For hardware look at the Dell 2850 or the HP DL385. Both are 64bit machines
with plenty of power for you to grow. For an OS run Windows Server 2003 R2
x64 Enterprise Edition, if you can. It's the best OS for any edition of SQL
2000 or 2005.
David|||> For an OS run Windows Server 2003 R2 x64 Enterprise Edition, if you can.
> It's the best OS for any edition of SQL 2000 or 2005.
David;
I'm curious about why R2 is the best OS for any edition of SQL 2000/2005. It
seems to me that R2 is primarily a feature pack with many features of little
concern to running SQL Server. I'm very happy with Windows 2003 SP1.
Linchi
"David Browne" wrote:
> "Charles" <webmail2@.hotmail.com> wrote in message
> news:1149728115.858242.294110@.i39g2000cwa.googlegroups.com...
> >I have a need to configure a mission critical SQL and Web server and
> > also looking for possible failover/redundancy
> >
> > We will be running Windows Server 2003 Enterprise Edition as well as
> > SQL 2000.
> >
> > We want to go with 4GB of RAM. We want a SQL cluster environment which
> > would include having an external direct attached storage system. I
> > also want a good hardware backup solution.
> >
> > Minimum Database Server Requirements:
> > Intel Xeon Processor 2GHz minimum (Dual Processor 3.06GHz, 1M Cache
> > Recommended)
> > 70+ GB for database and temp database. Additional storage is reuired if
> > backup copies of the database are stored on the same server.
> > Raid 0+1 Recommended (4 drives connected to RAID card)
> >
> > We will start off with about 100 users and grow to about 700-800 users
> > within the next 1 1/2 to 2 years.
> >
> > Based on this, and the minimum requirements, could anyone make some
> > recommendations on what I should be looking at hardware wise to not
> > only accomodate our immediate needs, but also our future needs.
> >
>
> Clustering can be somewhat complicated to implement and manage correctly and
> the hardware it requires can be expensive. It can be a great solution, but
> a poorly implemented or managed cluster will reduce your availibility
> instead of increasing it. And remember, there's no redundancy for your
> databases in a cluster, so your storage solution is a single point of
> failure.
> For clusters your hardware must be certified.
> The Microsoft support policy for server clusters, the Hardware Compatibility
> List, and the Windows Server Catalog
> http://support.microsoft.com/?id=309395
> Database Mirroring in SQL Server 2005 provides a cheap and easy way to
> achieve complete redundancy for your data and very high availibility. You
> don't need external storage or matched, certified servers.
> For hardware look at the Dell 2850 or the HP DL385. Both are 64bit machines
> with plenty of power for you to grow. For an OS run Windows Server 2003 R2
> x64 Enterprise Edition, if you can. It's the best OS for any edition of SQL
> 2000 or 2005.
> David
>
>|||"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:AA2CB8F9-B7D1-43B9-BD1B-B8C402B7E880@.microsoft.com...
>> For an OS run Windows Server 2003 R2 x64 Enterprise Edition, if you can.
>> It's the best OS for any edition of SQL 2000 or 2005.
> David;
> I'm curious about why R2 is the best OS for any edition of SQL 2000/2005.
> It
> seems to me that R2 is primarily a feature pack with many features of
> little
> concern to running SQL Server. I'm very happy with Windows 2003 SP1.
>
Yes, R2 is SP1 with a supported feature pack. And the x64 Edition is SP1
already, so that's a bit redundant. I could have just said Windows Server
2003 x64 Enterprise Edition.
The Enterprise Edition supports larger memory sizes and has Windows Server
Resource manager for dynamically allocating CPU resources among multiple
instances. And the x64 edition provides 4GB user-mode address space for SQL
Server 2000 sp4, and virtually unlimited memory for SQL Server 2005 x64
edition.
David|||David - Thanks so much for your input. The Dell and HP both sound like
good solutions. I'll check them out. All I need to do now is find a
good backup solution!
David Browne wrote:
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:AA2CB8F9-B7D1-43B9-BD1B-B8C402B7E880@.microsoft.com...
> >> For an OS run Windows Server 2003 R2 x64 Enterprise Edition, if you can.
> >> It's the best OS for any edition of SQL 2000 or 2005.
> >
> > David;
> >
> > I'm curious about why R2 is the best OS for any edition of SQL 2000/2005.
> > It
> > seems to me that R2 is primarily a feature pack with many features of
> > little
> > concern to running SQL Server. I'm very happy with Windows 2003 SP1.
> >
> Yes, R2 is SP1 with a supported feature pack. And the x64 Edition is SP1
> already, so that's a bit redundant. I could have just said Windows Server
> 2003 x64 Enterprise Edition.
> The Enterprise Edition supports larger memory sizes and has Windows Server
> Resource manager for dynamically allocating CPU resources among multiple
> instances. And the x64 edition provides 4GB user-mode address space for SQL
> Server 2000 sp4, and virtually unlimited memory for SQL Server 2005 x64
> edition.
> David

Performance Question

I have a need to configure a mission critical SQL and Web server and
also looking for possible failover/redundancy
We will be running Windows Server 2003 Enterprise Edition as well as
SQL 2000.
We want to go with 4GB of RAM. We want a SQL cluster environment which
would include having an external direct attached storage system. I
also want a good hardware backup solution.
Minimum Database Server Requirements:
Intel Xeon Processor 2GHz minimum (Dual Processor 3.06GHz, 1M Cache
Recommended)
70+ GB for database and temp database. Additional storage is reuired if
backup copies of the database are stored on the same server.
Raid 0+1 Recommended (4 drives connected to RAID card)
We will start off with about 100 users and grow to about 700-800 users
within the next 1 1/2 to 2 years.
Based on this, and the minimum requirements, could anyone make some
recommendations on what I should be looking at hardware wise to not
only accomodate our immediate needs, but also our future needs.
Thanks for you help!"Charles" <webmail2@.hotmail.com> wrote in message
news:1149728115.858242.294110@.i39g2000cwa.googlegroups.com...
>I have a need to configure a mission critical SQL and Web server and
> also looking for possible failover/redundancy
> We will be running Windows Server 2003 Enterprise Edition as well as
> SQL 2000.
> We want to go with 4GB of RAM. We want a SQL cluster environment which
> would include having an external direct attached storage system. I
> also want a good hardware backup solution.
> Minimum Database Server Requirements:
> Intel Xeon Processor 2GHz minimum (Dual Processor 3.06GHz, 1M Cache
> Recommended)
> 70+ GB for database and temp database. Additional storage is reuired if
> backup copies of the database are stored on the same server.
> Raid 0+1 Recommended (4 drives connected to RAID card)
> We will start off with about 100 users and grow to about 700-800 users
> within the next 1 1/2 to 2 years.
> Based on this, and the minimum requirements, could anyone make some
> recommendations on what I should be looking at hardware wise to not
> only accomodate our immediate needs, but also our future needs.
>
Clustering can be somewhat complicated to implement and manage correctly and
the hardware it requires can be expensive. It can be a great solution, but
a poorly implemented or managed cluster will reduce your availibility
instead of increasing it. And remember, there's no redundancy for your
databases in a cluster, so your storage solution is a single point of
failure.
For clusters your hardware must be certified.
The Microsoft support policy for server clusters, the hardware Compatibility
List, and the Windows Server Catalog
http://support.microsoft.com/?id=309395
Database Mirroring in SQL Server 2005 provides a cheap and easy way to
achieve complete redundancy for your data and very high availibility. You
don't need external storage or matched, certified servers.
For hardware look at the Dell 2850 or the HP DL385. Both are 64bit machines
with plenty of power for you to grow. For an OS run Windows Server 2003 R2
x64 Enterprise Edition, if you can. It's the best OS for any edition of SQL
2000 or 2005.
David|||> For an OS run Windows Server 2003 R2 x64 Enterprise Edition, if you can.
> It's the best OS for any edition of SQL 2000 or 2005.
David;
I'm curious about why R2 is the best OS for any edition of SQL 2000/2005. It
seems to me that R2 is primarily a feature pack with many features of little
concern to running SQL Server. I'm very happy with Windows 2003 SP1.
Linchi
"David Browne" wrote:

> "Charles" <webmail2@.hotmail.com> wrote in message
> news:1149728115.858242.294110@.i39g2000cwa.googlegroups.com...
>
> Clustering can be somewhat complicated to implement and manage correctly a
nd
> the hardware it requires can be expensive. It can be a great solution, bu
t
> a poorly implemented or managed cluster will reduce your availibility
> instead of increasing it. And remember, there's no redundancy for your
> databases in a cluster, so your storage solution is a single point of
> failure.
> For clusters your hardware must be certified.
> The Microsoft support policy for server clusters, the hardware Compatibili
ty
> List, and the Windows Server Catalog
> http://support.microsoft.com/?id=309395
> Database Mirroring in SQL Server 2005 provides a cheap and easy way to
> achieve complete redundancy for your data and very high availibility. You
> don't need external storage or matched, certified servers.
> For hardware look at the Dell 2850 or the HP DL385. Both are 64bit machin
es
> with plenty of power for you to grow. For an OS run Windows Server 2003 R
2
> x64 Enterprise Edition, if you can. It's the best OS for any edition of S
QL
> 2000 or 2005.
> David
>
>|||"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:AA2CB8F9-B7D1-43B9-BD1B-B8C402B7E880@.microsoft.com...
> David;
> I'm curious about why R2 is the best OS for any edition of SQL 2000/2005.
> It
> seems to me that R2 is primarily a feature pack with many features of
> little
> concern to running SQL Server. I'm very happy with Windows 2003 SP1.
>
Yes, R2 is SP1 with a supported feature pack. And the x64 Edition is SP1
already, so that's a bit redundant. I could have just said Windows Server
2003 x64 Enterprise Edition.
The Enterprise Edition supports larger memory sizes and has Windows Server
Resource manager for dynamically allocating CPU resources among multiple
instances. And the x64 edition provides 4GB user-mode address space for SQL
Server 2000 sp4, and virtually unlimited memory for SQL Server 2005 x64
edition.
David|||David - Thanks so much for your input. The Dell and HP both sound like
good solutions. I'll check them out. All I need to do now is find a
good backup solution!
David Browne wrote:
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:AA2CB8F9-B7D1-43B9-BD1B-B8C402B7E880@.microsoft.com...
> Yes, R2 is SP1 with a supported feature pack. And the x64 Edition is SP1
> already, so that's a bit redundant. I could have just said Windows Server
> 2003 x64 Enterprise Edition.
> The Enterprise Edition supports larger memory sizes and has Windows Server
> Resource manager for dynamically allocating CPU resources among multiple
> instances. And the x64 edition provides 4GB user-mode address space for S
QL
> Server 2000 sp4, and virtually unlimited memory for SQL Server 2005 x64
> edition.
> David

Wednesday, March 21, 2012

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

Monday, March 12, 2012

Performance Problem

We have a web server and a database application on the backhand.
the use case is we wanna use minumum CPU resources.
So to achieve that we are considering two options
1.to put all the data into one 500 GB disk
2.to put data into 10 harddisk size of 50 GB
which one more performant ?Option 2 should be the fastest (especially when using RAID 1+0).

Performance probelm with large table

I have a web page to allow user to create Sales Order. For this i have 2 tables, one for the informations (Sales Order) about the order and the other table (Sales Order LineItems ) contains the details of the order. For exemple for one order if i order 2 CD i will have 2 rows for this order in the Sales Order LineItems table.

When i've created the page i've made some test by creating 100 orders. Everything was working fine. Now yesterday i've tried to create 100 000 orders and 400 000 details rows. Since i 've created all thoses order the performance are not good at all. When i tried to open an order on my web page it can take up to 20sec juste to load the page. This make no sense at all since all my queries return only information for one order.
The problem is probably with SQL Server since when i've tried to open my webpage if i tried to launch another query everything is really slow. All my columns have Indexable properties set to yes.

Any idea ? I'm using ADO.NET and most of my Queries are inside StoredProc.

Thanks!

What is your table structure like?

Indexes?

|||

Can u share with us your table structure and if possible query u are trying to execute.

Basic Performance suggestion:

1. Use of index (smart use) No overuse

2. Keep ur statistics up to date.As in ur case u added so many rows it might have invalidated statistics on table.

3. Look for fragmentation in the tables. use dbcc showcontig to find out if any.

4. avoid using select * use column name in select

5. Use show plan to find out index(If u have any on the tables) is being used by SQL Server or not

6. Look for scans (Table scan or index scan) ..any case seek is better

so many others ..

Friday, March 9, 2012

Performance of SQL

I am using Reporting Services and I have a heavily parameterised report which
update each others lists etc.
When I run the report through the web service, the sql; server goes
ballistic, and runs at 100% for several minutes.
I have looked in SQL Profiler at what is going on and the actual queries (my
SQL) is only taking a few seconds to run, but there is loads of other stuff
happening, moving around chunkdata and the like. Also looking at the current
activity, I can see
several Shared DB Locks on my database and it is all looking pretty nasty.
My reports are all dynamic so is there anyway that I can strip back all of
these other things. I am quite prepared to belive that there is a problem in
my code, but I could translate the entire database into French quicker than
my 3 second SQL runs.
Can anyone shed any light on this."Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Tom,
Try modifying your queries for using Stored Procedures. I am really not
aware how you are using the parameters or else prepare your data's in the
form of table or views so that you can run the reporting services on the view
or tables. if you are using 2005 prepare a report model and use it.
Regards
Amarnath
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thanks for the pointers, but I am confident that this issue is a little more
than that. I have about 40 reports, some of which are very straightforward
and some that arent.
All use a few parameters, and search through a chunk of data. It is the
difference between the speed of the SQL in Query Analyser and that in Report
Designer. I cant see how precomiled SQL ios going to make a significant
difference, and I cant do what I want to do with views as the query has these
parameters and performance would go down to several minutes if I did it that
way.|||You need to clean up the database, that would speed everything up. ^_^|||Make sure you are only returning the rows you need in your report ( no
filtering.)
Make sure you are not doing extra sorting in Groups, etc, that might already
be done in the SQL.
Take a look at the log in REporting Services, and it will tell you how much
time is spent doing each of three steps... rendering often can take quite a
while... All of the data has to be loaded into memory on the IIS server, then
aggregations etc are done there..
The best thing you can do otherwise is to begin to simplify a slow report...
reducing the number of rows, sorts, groups, filters etc until you can find
the culprit..
Also try to rending using different things html, pdf. etc and see if that
makes a difference..
Also try rendering from a snapshot and see..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thank you all fo0r your advice,
Except Sorcerdon. What makes you think you know anything about my database?
Anyway. I will have a look at the reports, maybe reinstall a few things and
see if it clears up the mess. It certainly appears that it relates to
something between my SQL and hitting nthe database. I know that my SQL is
fine, as I can run that against the db and it is fine. Does Report Server
dump the results off somewhere before rendering? Or is it straight back via
asp.net data providers.?

Saturday, February 25, 2012

Performance of "SELECT COUNT(*) FROM X WHERE Y" queries

I have a web application which has queues with different views for different
users. The site has several hundred users a day. With enough RAM in the
server (the application effectively living in RAM) is it quite reasonable
and ok performance wise to be able to use "SELECT COUNT(*) FROM X WHERE Y"
queries (inside an SP) to generate the counters on each page load or should
you use a trigger or perhaps another approach - like a middle tier component
to cache the values?
Thanks
RichardPerformance of a SELECT COUNT(*) query is depends largely on available
indexes. In you example, a non-clustered index on Y will provide an
efficient method to get the count and performance will be roughly
proportional to the number of qualifying rows.
I suggest you run a simple performance test with your worst-case scenario.
If response time is acceptable, you're done. It's best not to implement
techniques like caching or redundant data to address a performance problem
you don't have.
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Hollis" <richard_hollis@.hotmail.com> wrote in message
news:%23W2x5UEZFHA.2996@.TK2MSFTNGP10.phx.gbl...
>I have a web application which has queues with different views for
>different
> users. The site has several hundred users a day. With enough RAM in the
> server (the application effectively living in RAM) is it quite reasonable
> and ok performance wise to be able to use "SELECT COUNT(*) FROM X WHERE Y"
> queries (inside an SP) to generate the counters on each page load or
> should
> you use a trigger or perhaps another approach - like a middle tier
> component
> to cache the values?
> Thanks
> Richard
>

Monday, February 20, 2012

Performance Monitoring

Hi All
I am running web applications over multiple database and web servers. Each
web application runs on a Web Server and connects to a seperate Database
Server. As more web applications are created, we have to purchase more web
and database servers as the systems slow down. The problem is that we don't
really know if we need more database or more web servers. Does anyone know a
good tool for monitoring web AND database servers to help us to decide when
to buy new servers?
We are running windows 2003 (IIS 6) and SQL Server 2000.
Thanx in advance
Joe
Well Profiler & Perfmon should allow you to see how SQL Server is
performing. IF you want 3rd party tools you might want to look at Quest for
SQL Server. Veritas has a product line called i3 that will monitor all
levels of the app. From the web server to the db and give you plenty of
reports etc. to show where problems may be occurring. But they do cost a
few dollars.
Andrew J. Kelly SQL MVP
"Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
news:eQj6zKsjFHA.2920@.TK2MSFTNGP14.phx.gbl...
> Hi All
> I am running web applications over multiple database and web servers. Each
> web application runs on a Web Server and connects to a seperate Database
> Server. As more web applications are created, we have to purchase more web
> and database servers as the systems slow down. The problem is that we
> don't really know if we need more database or more web servers. Does
> anyone know a good tool for monitoring web AND database servers to help us
> to decide when to buy new servers?
> We are running windows 2003 (IIS 6) and SQL Server 2000.
> Thanx in advance
> Joe
>
|||I work for a company that might be able to help you or is partners with
companies who could. The website is www.gomez.com. Check it out. Just
thought it might help.
|||Cheers, I didn't think about built in SQL Server Tools. I shall try those
first.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ulh9B8sjFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Well Profiler & Perfmon should allow you to see how SQL Server is
> performing. IF you want 3rd party tools you might want to look at Quest
> for SQL Server. Veritas has a product line called i3 that will monitor
> all levels of the app. From the web server to the db and give you plenty
> of reports etc. to show where problems may be occurring. But they do cost
> a few dollars.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
> news:eQj6zKsjFHA.2920@.TK2MSFTNGP14.phx.gbl...
>
|||Check for http://www.agileinfollc.com DataStudio, it has a good performance
monitoring facility under Performance node.
"Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
news:eQj6zKsjFHA.2920@.TK2MSFTNGP14.phx.gbl...
> Hi All
> I am running web applications over multiple database and web servers. Each
> web application runs on a Web Server and connects to a seperate Database
> Server. As more web applications are created, we have to purchase more web
> and database servers as the systems slow down. The problem is that we
> don't really know if we need more database or more web servers. Does
> anyone know a good tool for monitoring web AND database servers to help us
> to decide when to buy new servers?
> We are running windows 2003 (IIS 6) and SQL Server 2000.
> Thanx in advance
> Joe
>