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 Sql2000 SP3 on W2003 Server

I'm running microsoft Sql for a Software called Everest FRom Icode. Two week
s
ago was but now when I check the w2003 performance screen sometimes sql use
over 60% of the resources. During top hours the server show 100% used. The
database is not even 1gb in size and we just have 20 users using the softwar
e
trought terminal services and the speed is bad and the performance is worst
any idea.
Thank you.Hi
Check that you have database maintenance scheduled to run perodically on
this database, this would re-organise indexes and update statistics. Your
vendor should be able to indicate what processes they have in place to carry
out this work. If they are carried out by scheduled tasks check in Enterpris
e
Manager for scheduled tasks which do this job that have failed.
In general SQL Server it is best to have SQL Server on a dedicate machine,
and not have it competing with other applications for resources.
John
"enriqueto" wrote:

> I'm running microsoft Sql for a Software called Everest FRom Icode. Two we
eks
> ago was but now when I check the w2003 performance screen sometimes sql us
e
> over 60% of the resources. During top hours the server show 100% used. The
> database is not even 1gb in size and we just have 20 users using the softw
are
> trought terminal services and the speed is bad and the performance is wors
t
> any idea.
> Thank you.
>

Performance Problems Sql2000 SP3 on W2003 Server

I'm running microsoft Sql for a Software called Everest FRom Icode. Two weeks
ago was but now when I check the w2003 performance screen sometimes sql use
over 60% of the resources. During top hours the server show 100% used. The
database is not even 1gb in size and we just have 20 users using the software
trought terminal services and the speed is bad and the performance is worst
any idea.
Thank you.
Hi
Check that you have database maintenance scheduled to run perodically on
this database, this would re-organise indexes and update statistics. Your
vendor should be able to indicate what processes they have in place to carry
out this work. If they are carried out by scheduled tasks check in Enterprise
Manager for scheduled tasks which do this job that have failed.
In general SQL Server it is best to have SQL Server on a dedicate machine,
and not have it competing with other applications for resources.
John
"enriqueto" wrote:

> I'm running microsoft Sql for a Software called Everest FRom Icode. Two weeks
> ago was but now when I check the w2003 performance screen sometimes sql use
> over 60% of the resources. During top hours the server show 100% used. The
> database is not even 1gb in size and we just have 20 users using the software
> trought terminal services and the speed is bad and the performance is worst
> any idea.
> Thank you.
>

Performance Problems Sql2000 SP3 on W2003 Server

I'm running microsoft Sql for a Software called Everest FRom Icode. Two weeks
ago was but now when I check the w2003 performance screen sometimes sql use
over 60% of the resources. During top hours the server show 100% used. The
database is not even 1gb in size and we just have 20 users using the software
trought terminal services and the speed is bad and the performance is worst
any idea.
Thank you.Hi
Check that you have database maintenance scheduled to run perodically on
this database, this would re-organise indexes and update statistics. Your
vendor should be able to indicate what processes they have in place to carry
out this work. If they are carried out by scheduled tasks check in Enterprise
Manager for scheduled tasks which do this job that have failed.
In general SQL Server it is best to have SQL Server on a dedicate machine,
and not have it competing with other applications for resources.
John
"enriqueto" wrote:
> I'm running microsoft Sql for a Software called Everest FRom Icode. Two weeks
> ago was but now when I check the w2003 performance screen sometimes sql use
> over 60% of the resources. During top hours the server show 100% used. The
> database is not even 1gb in size and we just have 20 users using the software
> trought terminal services and the speed is bad and the performance is worst
> any idea.
> Thank you.
>

Performance Problems - HELP, PLEASE

One of my SQL2000 sp3 servers has seems to be responding slower.
Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
on the C drive is the MASTER, there are no master files on the E drive.
One of the prime functions for this server is Report services.
I began looking around after one of the report writers mentioned that things
were responding slower then in the past, I run this query >
use master
select * From sysdatabases
I get this line for Master>
master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
00:00:00.000 0 80
e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
If I use EM and look at Properties for the Master DB it shows the data file
where it should be>
C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
I then ran
use master
dbcc checkdb ('master')
and there were no errors , the final line read like this>
"CHECKDB found 0 allocation errors and 0 consistency errors in database
'master'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Any and ALL recommendations and suggestions would be most appreciated!!!"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>
The location of the master database, as recorded in a table in the master
database is not necessarily correct. The location of the master database is
specified in the registry on the command line when starting SQL. Master, in
turn, stores the locations of all the other databses.
This is not your problem.
Davud|||> One of the prime functions for this server is Report services.
To expand on David's response, be aware that Reporting Services and SQL
Server will compete for the same resources when both are on the same server.
Large Reports can consume significant CPU and memory resources, contributing
to slowness of both SQL Server and RS.
You need to identify the immediate reason for the slowness (e.g. high CPU,
excessive paging) so that you can more easily identify and correct the
cause. Simply poking around for misconfigured items is like throwing darts
blindfolded.
Hope this helps.
Dan Guzman
SQL Server MVP
"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>|||Thank you both, very much....
You are absolutle right about throwing darts... I have started researching
how to use performance monitor and SQL profiler to better pin point the
source of the problem. I will post a new request for best resources to
understand these 2 tools..
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eG2vRWAfGHA.4976@.TK2MSFTNGP02.phx.gbl...
> To expand on David's response, be aware that Reporting Services and SQL
> Server will compete for the same resources when both are on the same
> server. Large Reports can consume significant CPU and memory resources,
> contributing to slowness of both SQL Server and RS.
> You need to identify the immediate reason for the slowness (e.g. high CPU,
> excessive paging) so that you can more easily identify and correct the
> cause. Simply poking around for misconfigured items is like throwing
> darts blindfolded.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
> news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
>sql

Performance Problems - HELP, PLEASE

One of my SQL2000 sp3 servers has seems to be responding slower.
Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
on the C drive is the MASTER, there are no master files on the E drive.
One of the prime functions for this server is Report services.
I began looking around after one of the report writers mentioned that things
were responding slower then in the past, I run this query >
use master
select * From sysdatabases
I get this line for Master>
master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
00:00:00.000 0 80
e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
If I use EM and look at Properties for the Master DB it shows the data file
where it should be>
C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
I then ran
use master
dbcc checkdb ('master')
and there were no errors , the final line read like this>
"CHECKDB found 0 allocation errors and 0 consistency errors in database
'master'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Any and ALL recommendations and suggestions would be most appreciated!!!"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>
The location of the master database, as recorded in a table in the master
database is not necessarily correct. The location of the master database is
specified in the registry on the command line when starting SQL. Master, in
turn, stores the locations of all the other databses.
This is not your problem.
Davud|||> One of the prime functions for this server is Report services.
To expand on David's response, be aware that Reporting Services and SQL
Server will compete for the same resources when both are on the same server.
Large Reports can consume significant CPU and memory resources, contributing
to slowness of both SQL Server and RS.
You need to identify the immediate reason for the slowness (e.g. high CPU,
excessive paging) so that you can more easily identify and correct the
cause. Simply poking around for misconfigured items is like throwing darts
blindfolded.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>|||Thank you both, very much....
You are absolutle right about throwing darts... I have started researching
how to use performance monitor and SQL profiler to better pin point the
source of the problem. I will post a new request for best resources to
understand these 2 tools..
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eG2vRWAfGHA.4976@.TK2MSFTNGP02.phx.gbl...
>> One of the prime functions for this server is Report services.
> To expand on David's response, be aware that Reporting Services and SQL
> Server will compete for the same resources when both are on the same
> server. Large Reports can consume significant CPU and memory resources,
> contributing to slowness of both SQL Server and RS.
> You need to identify the immediate reason for the slowness (e.g. high CPU,
> excessive paging) so that you can more easily identify and correct the
> cause. Simply poking around for misconfigured items is like throwing
> darts blindfolded.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
> news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
>> One of my SQL2000 sp3 servers has seems to be responding slower.
>> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual
>> processors.
>> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
>> on the C drive is the MASTER, there are no master files on the E drive.
>> One of the prime functions for this server is Report services.
>> I began looking around after one of the report writers mentioned that
>> things were responding slower then in the past, I run this query >
>> use master
>> select * From sysdatabases
>> I get this line for Master>
>> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
>> 00:00:00.000 0 80
>> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
>> If I use EM and look at Properties for the Master DB it shows the data
>> file where it should be>
>> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
>> I then ran
>> use master
>> dbcc checkdb ('master')
>> and there were no errors , the final line read like this>
>> "CHECKDB found 0 allocation errors and 0 consistency errors in database
>> 'master'.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator."
>> Any and ALL recommendations and suggestions would be most appreciated!!!
>>
>