Performance Question

Hi, I have a sql2000 server running and I have a asp.net app that accesses this server. At the moment I am using 2 different databases to get information from and such, one database I have a bit of control over the other I am not allowed to touch without special permissions due to the sesitivity of the data. The issue is that my app is not going very fast when it comes to database calls.

I need to index some tables but I am unsure what actually happens to the data when I do this, I have one table that has ~600,000 rows and that is alot to sort through as you can imagine. This table is on the sensitive database, so I am hesitant to touch it, but I really need a performance boost somehow, can anyone suggest something to help me solve my performance problem?

I have posted around on the asp.net forums but the help is limited and most people say 99% of the time its the database calls that are the problem.

Thanks for the time,
MarkIts hard to just suggest anything without knowing your physical and logical design, but if you have many sort operations, try to put indexes on the sorted columns. If you are joining a lot of data, do also put an index on the joined columns. 600.000 is not that much data, perhaps you might also have an issue on your physical design ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for the reply Jens, I am quite new to Indexes in SQL Server 2000, I suppose it is always best to use them no matter what database you have, but is there any risk to the data by putting them on? I just dont want to corrupt anything. And as far as I can tell, there are no indexes on any tables in the database that I am allowed to touch.

I should mention too, that I am using views to look onto the sensitive database, will this affect permormance?

thanks
Mark|||Actually there is no risks using indexes. If you use them on a separate physical storage location you can also put the load of the indexes from the database (but you don′t have to) The best way would be to evaluate the uqery plans of your queries fired against the database. They should tell you if data is retrieved by using cost extensive scans etc.

HTH, Jens Sü?meyer.

http://www.sqlserver2005.de|||Ok thats good advice thanks. When you say that I should evaluate the query plans, is that using the feature in the management studio that shows the execution plan of a given query? If so what should I be looking for? I dont really understand the details I am being shown

Thanks Jens|||Many sites already discussed about this topic, it would be too much describing the different symbols in here, but have a look on www.sqlserverperformance.com this iste can be a good starting point for finding out ways to tweak the performance of the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
sql

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 of named instance

Hi,
I have an app that ran just fine with the default instance of MSDE. I
changed the connection string in the app to work with a named instance
instead, like this: (local)/MyNamedInstance.
Database performance slowed measurably as soon as I did that, particularly
with DDL commands, like:
CREATE DEFAULT BOOLEAN_DEFAULT AS ''F''
CREATE RULE BOOLEAN_RULE AS @.list IN (''T'', ''F'')
sp_addtype DOM_BOOLEAN, ''VARCHAR(1)'', ''NOT NULL''
sp_bindefault BOOLEAN_DEFAULT, DOM_BOOLEAN
sp_bindrule BOOLEAN_RULE, DOM_BOOLEAN
It literally takes 10-15 seconds to execute about 4 of these lines of code,
where they used to execute pretty much immediately.
Is there anything special I need to do with a named instance of MSDE to
obtain the same performance levels?
Thanks in advance.
-Eric Harmon
If the instances are on the same machine it is possible that the first
instance has taken the majority of the memory and is still using it. If the
first instance needs the memory it will not free it up for the second
instance.
I have never seen a case where the cause of a performance problem was a
default vs. a named instance, so you need to concentrate on the other
differences; resources available, differences in data, schema differences,
disk speed, etc.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.