ASP & SQL Server on separate machines causes performance degragation

I am currently running a windows 2000 machine with asp, sql server,
mail server, ftp server etc all on the same box.

The site runs several hundred ecommerce stores. Recently the
processor utilization has been spiking and I have decided to get
another server and use sql server on one and asp on the other.

So now I have a new windows 2003 server that I have setup all of the
asp code on. Problem is that when I run the asp code from the new
windows 2003 server it is extremely slow compared to the code running
on the old windows 2000 server which is where the sql server database
is also located.

From everything I have read the best way to optimize your site is to
use 2 separate servers one for iis/asp and one for sql server.

Am I doing something wrong here or is this normal??

Could this possibly be just because the old server is still serving
many requests and is pushing the requests from the new server to the
back of the line?

Does anyone have any ideas?

The syntax I am using to open the connection string is:

db_ConnectionString = "Driver=" & db_Driver & ";Server=" & db_Server &
";UID=" & db_UIN & ";PWD=" & db_pwd & ";Database=" & db_Database & ";"

conn_store.Open db_ConnectionString
where db_server is the ip address of the windows 2000 server

Is there a better way to do it across a network??

Any help or ideas would be much appreciated.Melanie:

>>From everything I have read the best way to optimize your site is to
use 2 separate servers one for iis/asp and one for sql server.

Adding a new layer to an environment will rarely gain you performance.
There is an extra network hop that the server has to make to retrieve
the data.
Having said that, you will most likely gain scalability which is
different from performance.

You might want to have some form of high speed or dedicated network
connection in between your web server and sql server. Although you
should first try to determine if the connection in between the servers
is really the problem here.

FYI, your server configuration is widely used. Unless you are
retrieving a very large amount of data, you shouldn't be able to tell
that BIG of a difference.

Hope that helps.

BZ

mblack@.deltad.com (Melanie Black) wrote in message news:<3efc718d.0310291500.661bbaba@.posting.google.com>...
> I am currently running a windows 2000 machine with asp, sql server,
> mail server, ftp server etc all on the same box.
> The site runs several hundred ecommerce stores. Recently the
> processor utilization has been spiking and I have decided to get
> another server and use sql server on one and asp on the other.
> So now I have a new windows 2003 server that I have setup all of the
> asp code on. Problem is that when I run the asp code from the new
> windows 2003 server it is extremely slow compared to the code running
> on the old windows 2000 server which is where the sql server database
> is also located.
> From everything I have read the best way to optimize your site is to
> use 2 separate servers one for iis/asp and one for sql server.
> Am I doing something wrong here or is this normal??
> Could this possibly be just because the old server is still serving
> many requests and is pushing the requests from the new server to the
> back of the line?
> Does anyone have any ideas?
> The syntax I am using to open the connection string is:
> db_ConnectionString = "Driver=" & db_Driver & ";Server=" & db_Server &
> ";UID=" & db_UIN & ";PWD=" & db_pwd & ";Database=" & db_Database & ";"
> conn_store.Open db_ConnectionString
> where db_server is the ip address of the windows 2000 server
> Is there a better way to do it across a network??
> Any help or ideas would be much appreciated.|||On 29 Oct 2003 15:00:06 -0800, Melanie Black wrote:

> I am currently running a windows 2000 machine with asp, sql server,
> mail server, ftp server etc all on the same box.
> The site runs several hundred ecommerce stores. Recently the
> processor utilization has been spiking and I have decided to get
> another server and use sql server on one and asp on the other.
> So now I have a new windows 2003 server that I have setup all of the
> asp code on. Problem is that when I run the asp code from the new
> windows 2003 server it is extremely slow compared to the code running
> on the old windows 2000 server which is where the sql server database
> is also located.

Not a solution but:
Wouldn't it have been easier to move just SQL Server to Windows 2003 and
keep the ASP sites on the Windows 2000 machine?

--
Karim
Recommended host: http://www.cheapesthosting.com - Affordable hosting since
1998|||Actually that was originally the idea but the windows 2003 server turned
out to be windows 2003 web edition which does not support sql server.

Also I was able to figure out that by changing the database connection
type the performance increased signifigantly. I can still notice a bit
of a slowdown but not nearly what I saw before.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Ascending Date Column Statistics "dbcc traceon( 2389 )"

I am having trouble with query performance on SQL Server 2005. The database
is a basic book ordering application. A simple one to many Orders to Order
Items. The orders table contains 80,000 rows and the Order Items table
contains 280,000 rows. The problem query contains an inner join with Orders
and Order Items with where clause on Order Date. The problem is when you
execute the query on old data the inner join is based on a hash (1 second
execution). When you execute the query on new data the inner join is based
on a nested loop (2 minute execution). The reason for this is the stats.
The stats for the order date column do not contain the new data so the
estimated rows for the given date range is 1 (when the query actually returns
1320 rows). If I update the stats for order date the query will perform as
expected (hash join). The following blog explains the issue.
http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
In order to fix this issue it appears I have to schedule a manual update of
the stats or use dbcc traceon( 2389 ). This application ran on SQL server
2000 for 4 years with no problems. Why did I not see an issue with SQL
server 2000? Does anyone have any better ideas except manually updating the
stats?First of all, have you tried a HASH JOIN hint? If the hash is working well,
force it.
I have also had a lot of statistics problems in some of the applications
I've been working on, and although I haven't had a chance to try these trace
flags yet, I have found workarounds to every issue (generally, involving
query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
not doing as well as SQL Server 2000 did, but overall I think performance is
improved in most scenarios. Hopefully as MS continues to evaluate use cases
these issues will go away and we can go back to thinking about other things!
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>I am having trouble with query performance on SQL Server 2005. The
>database
> is a basic book ordering application. A simple one to many Orders to
> Order
> Items. The orders table contains 80,000 rows and the Order Items table
> contains 280,000 rows. The problem query contains an inner join with
> Orders
> and Order Items with where clause on Order Date. The problem is when you
> execute the query on old data the inner join is based on a hash (1 second
> execution). When you execute the query on new data the inner join is
> based
> on a nested loop (2 minute execution). The reason for this is the stats.
> The stats for the order date column do not contain the new data so the
> estimated rows for the given date range is 1 (when the query actually
> returns
> 1320 rows). If I update the stats for order date the query will perform
> as
> expected (hash join). The following blog explains the issue.
> http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> In order to fix this issue it appears I have to schedule a manual update
> of
> the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> 2000 for 4 years with no problems. Why did I not see an issue with SQL
> server 2000? Does anyone have any better ideas except manually updating
> the
> stats?
>|||Thanks for the help. The HASH Join hint does work.
"Adam Machanic" wrote:
> First of all, have you tried a HASH JOIN hint? If the hash is working well,
> force it.
> I have also had a lot of statistics problems in some of the applications
> I've been working on, and although I haven't had a chance to try these trace
> flags yet, I have found workarounds to every issue (generally, involving
> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
> not doing as well as SQL Server 2000 did, but overall I think performance is
> improved in most scenarios. Hopefully as MS continues to evaluate use cases
> these issues will go away and we can go back to thinking about other things!
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "achopp" <achopp@.discussions.microsoft.com> wrote in message
> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
> >I am having trouble with query performance on SQL Server 2005. The
> >database
> > is a basic book ordering application. A simple one to many Orders to
> > Order
> > Items. The orders table contains 80,000 rows and the Order Items table
> > contains 280,000 rows. The problem query contains an inner join with
> > Orders
> > and Order Items with where clause on Order Date. The problem is when you
> > execute the query on old data the inner join is based on a hash (1 second
> > execution). When you execute the query on new data the inner join is
> > based
> > on a nested loop (2 minute execution). The reason for this is the stats.
> > The stats for the order date column do not contain the new data so the
> > estimated rows for the given date range is 1 (when the query actually
> > returns
> > 1320 rows). If I update the stats for order date the query will perform
> > as
> > expected (hash join). The following blog explains the issue.
> >
> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> >
> > In order to fix this issue it appears I have to schedule a manual update
> > of
> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
> > server 2000? Does anyone have any better ideas except manually updating
> > the
> > stats?
> >
>
>|||Or possibly the date_correlation_optimization_option database option might help. See also
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/275fb44b-417a-426a-bf7c-f23308ed72c0.htm.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:609F8143-0EB4-4400-B84A-F3D75FBDDE95@.microsoft.com...
> Thanks for the help. The HASH Join hint does work.
> "Adam Machanic" wrote:
>> First of all, have you tried a HASH JOIN hint? If the hash is working well,
>> force it.
>> I have also had a lot of statistics problems in some of the applications
>> I've been working on, and although I haven't had a chance to try these trace
>> flags yet, I have found workarounds to every issue (generally, involving
>> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
>> not doing as well as SQL Server 2000 did, but overall I think performance is
>> improved in most scenarios. Hopefully as MS continues to evaluate use cases
>> these issues will go away and we can go back to thinking about other things!
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "achopp" <achopp@.discussions.microsoft.com> wrote in message
>> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>> >I am having trouble with query performance on SQL Server 2005. The
>> >database
>> > is a basic book ordering application. A simple one to many Orders to
>> > Order
>> > Items. The orders table contains 80,000 rows and the Order Items table
>> > contains 280,000 rows. The problem query contains an inner join with
>> > Orders
>> > and Order Items with where clause on Order Date. The problem is when you
>> > execute the query on old data the inner join is based on a hash (1 second
>> > execution). When you execute the query on new data the inner join is
>> > based
>> > on a nested loop (2 minute execution). The reason for this is the stats.
>> > The stats for the order date column do not contain the new data so the
>> > estimated rows for the given date range is 1 (when the query actually
>> > returns
>> > 1320 rows). If I update the stats for order date the query will perform
>> > as
>> > expected (hash join). The following blog explains the issue.
>> >
>> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
>> >
>> > In order to fix this issue it appears I have to schedule a manual update
>> > of
>> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
>> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
>> > server 2000? Does anyone have any better ideas except manually updating
>> > the
>> > stats?
>> >
>>

AS2005: MemberUniqueNameStyle = NamePath

Hi,

After having performance problems we implemented the Attribute Relationship in the cubes, but 99% of the hierarchies do not have a unique key for the levels. The suggestion from MS was to create the key as a collection from the parents to the level itself. Isn't it equivalent to MemberUniqueNameStyle = NamePath?

MemberUniqueNameStyle = NamePath (Analysis Services generates a compound name consisting of the name of each level and the caption of the member.) http://msdn2.microsoft.com/en-us/library/ms174900(SQL.90).aspx.

Thanks.

No, MemberUniqueNameStyle simply controls how Analysis Services generates the unique names of members - it doesn't affect the members or attribute relationships themselves. By using composite keys for your attributes, however, you will see changes in unique names generated using the default key-based algorithm.

HTH,

Chris

AS2005... Using Attribute Relationships

I assume defining attribute relationships can be advantageous for performance. However, I don't quite understand them.

I know by default, the dimension is created with all attributes having an attribute relationship under the key -- with all having a cardnality of Many.

My Customer dimension points to the following named query:

SELECT customer_key, customer_id, customer_name, city, post_code, region_code, country_code
FROM Customer C
LEFT OUTER JOIN [Customer Address] CA
ON C.customer_key = CA.customer_Key
AND CA.[Address Type] = 'MAIN'

Right now, here's what I have done for the attribute relations in additon the ones created by default:

I dragged Country Code under Region code, Region Code under Post Code, and Post Code under City. The cardinality for all of these relationships is Many (not sure I understand this property). Does that sound correct?

So what do I do for Customer Id and Customer Name? I assume there should be a relationship between the two, but which field do I drag to which? Customer Name under Customer Id or vice-versa? How does it make a difference in this case, if it does at all? Cardinality?

And finally, do I want to delete the relationships that were created by default under customer_key? By the way, the customer_key attribute is not visible.

Sorry for so many questions, but I definitely appreciate your help!

There is an extensive discussion of attribute relationships and their importance in the Project REAL Analysis Services Technical Drilldown white paper.

It is an EXTREMELY important topic (in my estimation probably *the* most important to understanding the basics for how AS2K5 is different from AS2K

AS2K5 is an attribute-based system; AS2K is a hierarchy-based system.

And the core of that is attribute relationships.

See http://www.microsoft.com/sql/bi/ProjectREAL

_-_-_ Dave

AS cube performance question

We have a very huge cube almost 200m rows with exponential growth rate. I have another cube which is not so big.

I've noticed when I pivot the dimensions in the big cube, it takes more time than the latter.

Could you guys give me some advices to make this work much faster.

I've partitioned the warehouse but have'nt created any indexes so far, could this be a problem?

thanks,

Prok

Hello! Have you read the "Performance Guide" pointed to at the top of this newsgroup?

HTH

Thomas Ivarsson

|||

Yeah that's definitly the best advice. The problem could be any of several.

Most likely, like you said, aggregations. 200m row cube isn't all that big as far as i'm concerned. We house a cube with ~2billion rows (180GB cube), once aggregations are designed "perfectly", you should not have an issue as long as the cube is designed well.

|||

Thanks Zach84,

Yeah, I have read the pf guide before and have implemented accordingly. I must have done a bad design regarding aggregations. I'll check on that.

I appreciate your advice.

Rok.

Articles on performance comparisons of sql2k5 on x32 and x64

We are looking for performance comparisons between SQL Server 2005 x32 and
x64.
Any out there?Hi,
Microsoft has a webccast showing the perf differences, watch that an
you'll see SQL2005 x64 SCREEEEEEEM , yeah a huge difference by man
miles.
64 bit has monster performance gains.
Regards
Anthon
--
9anthony
----
9anthony9's Profile: http://www.dbtalk.net/m1
View this thread: http://www.dbtalk.net/t29047

Articles on performance comparisons of sql2k5 on x32 and x64

We are looking for performance comparisons between SQL Server 2005 x32 and
x64.
Any out there?
Hi,
Microsoft has a webccast showing the perf differences, watch that and
you'll see SQL2005 x64 SCREEEEEEEM , yeah a huge difference by many
miles.
64 bit has monster performance gains.
Regards
Anthony
9anthony9
9anthony9's Profile: http://www.dbtalk.net/m12
View this thread: http://www.dbtalk.net/t290479

Articles on performance comparisons of sql2k5 on x32 and x64

We are looking for performance comparisons between SQL Server 2005 x32 and
x64.
Any out there?Hi,
Microsoft has a webccast showing the perf differences, watch that and
you'll see SQL2005 x64 SCREEEEEEEM , yeah a huge difference by many
miles.
64 bit has monster performance gains.
Regards
Anthony
9anthony9
---
9anthony9's Profile: http://www.dbtalk.net/m12
View this thread: http://www.dbtalk.net/t290479

Arithmetic overflow error converting expression to data type datetime

I tried this new SQL2K5 Performance Dashboard Reports using custom reports in Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

But running it first on any server gives me this error:

Difference of two datetime columns caused overflow at runtime.

Has anybody come across this error? How to fix it?

Thanks in advance!

- Rupesh

first check the sp2 is applied or not.

ref : http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx

Because DATEDIFF returns and int once you have connection that is more than 24 days or so old it will overflow the dattype if you modify the procedure so caluclates the differnce in minutes first converts this to milliseconds then add the number of minutes diffrence onto the start time and then calculate the remianing number of milli seconds it will work so basicalyy if you modify trhe offending line

sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

to

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS

BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute,

DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

then it will work

hopes this helps the rest of you who have the same problem.

Madhu

|||

Hi,

I am facing an error saying ‘Arithmetic overflow error converting expression to data type datetime.’ In data base due to my following query.

Then I tried with cast and convert function too, still I got the error.

select*

from datetable

wherecast(('May 29 20076:30:00:000PM' - endtime) as int) >=2

andcast(('May 29 20076:30:00:000PM' - endtime)as int)<=3

anddatetable_id= 102

order by datetable_iddesc

I got this beacause of some bad ‘endtime’ data in datetable for datetable_id102 : 5465-08-12 12:00:00.000.

But I need to support all type of date here and the table is also huge. So I have this col as indexed.

I thought of to use datediff func here. again I am not sure what will be the performance impact on my query, coz it will diff and convert to int and compare for each of the row.

So can any body suggest how efficiently can I handle this?

Thanks

~Dhiru

ARITHABORT and Indexed Views

I've just implemented indexed views to increase the performance of one of my
stored procedures. However,
it turns out that you cannot insert into a table that is referenced by an
index view unless the ARITHABORT is set to ON.
You cannot set it to ON inside the procedure. (see
http://support.microsoft.com/default.aspx?kbid=305333).
The recommendation from Microsoft is to turn this on from the
application. -- Unfortunately, I don't have control over that. I did find a
solution: "ALTER DATABASE nwind SET ARITHABORT ON".
I'm a little hesitant to use this in production, as many apps use this
particular database. Does anybody know of any potential gotchas? It seems
to me that if my apps were generating divide-by-zero or overflow errors,
they wouldn't be working anyway.The chances of this causing a problem are pretty slim but you should try it
on a backup copy of the database to make sure there aren't some unforeseen
issues.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:eKGlalNLGHA.2392@.TK2MSFTNGP09.phx.gbl...
> I've just implemented indexed views to increase the performance of one of
> my stored procedures. However,
> it turns out that you cannot insert into a table that is referenced by an
> index view unless the ARITHABORT is set to ON.
> You cannot set it to ON inside the procedure. (see
> http://support.microsoft.com/default.aspx?kbid=305333).
> The recommendation from Microsoft is to turn this on from the
> application. -- Unfortunately, I don't have control over that. I did find
> a solution: "ALTER DATABASE nwind SET ARITHABORT ON".
> I'm a little hesitant to use this in production, as many apps use this
> particular database. Does anybody know of any potential gotchas? It
> seems to me that if my apps were generating divide-by-zero or overflow
> errors, they wouldn't be working anyway.
>
>

Are there any good performance tuning tools that I can use to moni

Are there any good performance tuning tools that I can use to monitor SQL
Server database? Thanks.
You can try:
perfmon (comes with windows)
For sql 05 you can use "SQL Server Profiler". I am not sure if it comes
with 2k coz I never used it.
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:146ECA05-943B-4ACE-9418-6FD09859872E@.microsoft.com...[vbcol=seagreen]
> In addition, it can use for monitoring blocking. Thanks.
> "Iter" wrote:
|||If you are looking for tools available in SQL Server and/or Windows there
are several choices like Performance/System Monitor in Windows, SQL Server
Profiler, DMVs, DMFs, some system stored procedures, etc.
For more details look for 'Tools for Performance Monitoring and Tuning' in
SQL Server 2005 BOL.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Iter" wrote:
[vbcol=seagreen]
> In addition, it can use for monitoring blocking. Thanks.
> "Iter" wrote:
|||Is there any third party tools that I can use? Thanks.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> If you are looking for tools available in SQL Server and/or Windows there
> are several choices like Performance/System Monitor in Windows, SQL Server
> Profiler, DMVs, DMFs, some system stored procedures, etc.
> For more details look for 'Tools for Performance Monitoring and Tuning' in
> SQL Server 2005 BOL.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Iter" wrote:
|||Also take a look at DMVStats at http://www.codeplex.com/sqldmvstats
Linchi
"Iter" wrote:
[vbcol=seagreen]
> In addition, it can use for monitoring blocking. Thanks.
> "Iter" wrote:
|||Else than Database Tuning Advisor and SQL Server Profiler?
I'm not sure if you are aware of these tools however these tools come with
SQL Server 2005 (in case you install Management Tools)
Ekrem ?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:E0686DDF-3C36-4354-9120-E41B32A2C37C@.microsoft.com...
> Are there any good performance tuning tools that I can use to monitor SQL
> Server database? Thanks.

Are there any good performance tuning tools that I can use to moni

Are there any good performance tuning tools that I can use to monitor SQL
Server database? Thanks.In addition, it can use for monitoring blocking. Thanks.
"Iter" wrote:
> Are there any good performance tuning tools that I can use to monitor SQL
> Server database? Thanks.|||You can try:
perfmon (comes with windows)
For sql 05 you can use "SQL Server Profiler". I am not sure if it comes
with 2k coz I never used it.
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:146ECA05-943B-4ACE-9418-6FD09859872E@.microsoft.com...
> In addition, it can use for monitoring blocking. Thanks.
> "Iter" wrote:
>> Are there any good performance tuning tools that I can use to monitor SQL
>> Server database? Thanks.|||If you are looking for tools available in SQL Server and/or Windows there
are several choices like Performance/System Monitor in Windows, SQL Server
Profiler, DMVs, DMFs, some system stored procedures, etc.
For more details look for 'Tools for Performance Monitoring and Tuning' in
SQL Server 2005 BOL.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Iter" wrote:
> In addition, it can use for monitoring blocking. Thanks.
> "Iter" wrote:
> > Are there any good performance tuning tools that I can use to monitor SQL
> > Server database? Thanks.|||Is there any third party tools that I can use? Thanks.
"Ben Nevarez" wrote:
> If you are looking for tools available in SQL Server and/or Windows there
> are several choices like Performance/System Monitor in Windows, SQL Server
> Profiler, DMVs, DMFs, some system stored procedures, etc.
> For more details look for 'Tools for Performance Monitoring and Tuning' in
> SQL Server 2005 BOL.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Iter" wrote:
> > In addition, it can use for monitoring blocking. Thanks.
> >
> > "Iter" wrote:
> >
> > > Are there any good performance tuning tools that I can use to monitor SQL
> > > Server database? Thanks.|||Also take a look at DMVStats at http://www.codeplex.com/sqldmvstats
Linchi
"Iter" wrote:
> In addition, it can use for monitoring blocking. Thanks.
> "Iter" wrote:
> > Are there any good performance tuning tools that I can use to monitor SQL
> > Server database? Thanks.|||In addition to the other posts, check out SQLDIAG.EXE (comes with 2005). It'll help you automate the
collection of this information.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:E0686DDF-3C36-4354-9120-E41B32A2C37C@.microsoft.com...
> Are there any good performance tuning tools that I can use to monitor SQL
> Server database? Thanks.|||Else than Database Tuning Advisor and SQL Server Profiler?
I'm not sure if you are aware of these tools however these tools come with
SQL Server 2005 (in case you install Management Tools)
--
Ekrem Ã?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:E0686DDF-3C36-4354-9120-E41B32A2C37C@.microsoft.com...
> Are there any good performance tuning tools that I can use to monitor SQL
> Server database? Thanks.

Are there any good performance tuning tools that I can use to moni

Are there any good performance tuning tools that I can use to monitor SQL
Server database? Thanks.You can try:
perfmon (comes with windows)
For sql 05 you can use "SQL Server Profiler". I am not sure if it comes
with 2k coz I never used it.
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:146ECA05-943B-4ACE-9418-6FD09859872E@.microsoft.com...[vbcol=seagreen]
> In addition, it can use for monitoring blocking. Thanks.
> "Iter" wrote:
>|||In addition to the other posts, check out SQLDIAG.EXE (comes with 2005). It'
ll help you automate the
collection of this information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:E0686DDF-3C36-4354-9120-E41B32A2C37C@.microsoft.com...
> Are there any good performance tuning tools that I can use to monitor SQL
> Server database? Thanks.|||Else than Database Tuning Advisor and SQL Server Profiler?
I'm not sure if you are aware of these tools however these tools come with
SQL Server 2005 (in case you install Management Tools)
Ekrem ?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:E0686DDF-3C36-4354-9120-E41B32A2C37C@.microsoft.com...
> Are there any good performance tuning tools that I can use to monitor SQL
> Server database? Thanks.

Are stored procedures more performant than on the fly sql?

Hello.

I've read the other day (don't remember where :( ) that there is no performance advantages on executing stored procedures than executing sql strings made "on the fly". It said that both operations are precompiled on first execution, and then cached to posterior executions... Is that correct?

Thanks in advance,
Matias

Yes they are as compared to the normal TSQL statements provided the stored procedures a recompiled whenever they are modified for update of cache plan.

You need to refer to the books online and these links http://www.sql-server-performance.com/tn_stored_procedures.asp and http://www.awprofessional.com/articles/article.asp?p=25288 to know more about SPs.

|||

Satya,

your answer is a bit confusing....

are you saying that Stored Procedures ARE faster then on the fly SQL ? or are you saying they are NOT ?

my understanding is that they ARE especially when the SQL is more complex.

This is not the only reason to use sprocs however, other reasons are improved security, improved maintainability and modularity, etc.

cheers,

|||

Yes, I thought that too. Does sql 2005 precompiles and caches text t-sql statements, making them as performant as stored procedures on subsequent calls? I'm not discussing all the advantages of using stored procedures vs on the fly sql, I just want to know if there is any performance penalty using on the fly sql.

Thanks,
Matias

|||

as per this article, "ALL" Sql Statements are cached\precompiled.

http://msdn2.microsoft.com/en-us/library/ms181055.aspx

so that would suggest that there may NOT be a huge performance issue in using sprocs.

The argument to use sprocs for Security and maintainability is more viable.

It would be fairly easy to test this use SQL Profiler......

cheers

Are nulls slow?

I have several tables anywhere from 50,000 to 100,000 rows with about 100 columns in each that allow nulls. How bad will this affect performance (if any)?

If it's significant, how can I replace nulls with zeros without doing it column by column? (100 columns will take too much time)

thanksI dont think null will affect the perfomance.Below code will generate update statement for ur tables,columns which allow null values.Run this code, copy and paste the result to query analyser and execute.

select 'update '+TABLE_NAME+' set '+COLUMN_NAME+'=0 where '+COLUMN_NAME+' is null'
from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE='YES'

Are indexed updated?

Dear MSSQL DBAs,
I am trying to research about indexes and find out performance issues
related with indexes. My Questions are :
1) are indexes useful if we are using Bulk operations?
2) does indexes have any effect on simple INSERT or DELETE statements?
3) Do we need to drop indexes before Bulk operations
4) Is individual insert statement in a loop treated as a bulk insertion?
Regards,
Abdul-RahmanFor bulk insert issues, please see the whitepaper below:
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx[/u
rl]
Indexes do have an effect on DML (insert/update/delete) performance. Any DML
you perform has to be reflected in the non-clustered indexes. For example,
if you have a table with 10 non-clustered indexes and you insert a single
row, a row must be inserted into each non-clustered index. This is called
index maintenance and depending on the way you define the indexes can have a
noticeable effect on performance.
Please let me know if you have any further questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Abdul-Rahman" <rahman.mahmood@.pk.softecheww.com> wrote in message
news:ev$STuHCFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Dear MSSQL DBAs,
> I am trying to research about indexes and find out performance issues
> related with indexes. My Questions are :
> 1) are indexes useful if we are using Bulk operations?
> 2) does indexes have any effect on simple INSERT or DELETE statements?
> 3) Do we need to drop indexes before Bulk operations
> 4) Is individual insert statement in a loop treated as a bulk insertion?
> Regards,
> Abdul-Rahman
>