Performance tuning

I have a server with just 4 databases,
the issue is that the sql server does not perform well. In the task manager
it occupies the maximum mount of memory in the system, and maximum cpu time,
the database is just a few gb's(10 -15) and the server has 1 gb ram,
and especially if i use a reporting tool made in vb as to query the database
it works exceptionally slow,
one of my guys says it bcoz of a utility we have working, it bring data from
another server connected to our network via an IPLC link(2mb link), this
utility works on just 2 tables, brings data from the table up there and
imports it in our local database, would there be some better way of
accomplishing this?
is there some way i can constantly monitor what resources the data base is
using?
any suggestions will be greatly appreciated,
Thx & Regards
SantuSantu,
How often do you run the reports? Is the data required to be current and
up-to-date? If not, you could transfer the data locally during non-peak
hours.
As for performance monitoring, SQL Server Profiler and Windows System
Monitor should help.
HTH
Jerry
"Santu" <Santu@.discussions.microsoft.com> wrote in message
news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
>I have a server with just 4 databases,
> the issue is that the sql server does not perform well. In the task
> manager
> it occupies the maximum mount of memory in the system, and maximum cpu
> time,
> the database is just a few gb's(10 -15) and the server has 1 gb ram,
> and especially if i use a reporting tool made in vb as to query the
> database
> it works exceptionally slow,
> one of my guys says it bcoz of a utility we have working, it bring data
> from
> another server connected to our network via an IPLC link(2mb link), this
> utility works on just 2 tables, brings data from the table up there and
> imports it in our local database, would there be some better way of
> accomplishing this?
> is there some way i can constantly monitor what resources the data base is
> using?
> any suggestions will be greatly appreciated,
> Thx & Regards
> Santu
>|||Thank you for ur reply Jerry,
About 7-8 ppl use this tool , its constantly running,
and our co is an outbound call center, so the replication of data is
required to be realtime.
THX
"Jerry Spivey" wrote:
> Santu,
> How often do you run the reports? Is the data required to be current and
> up-to-date? If not, you could transfer the data locally during non-peak
> hours.
> As for performance monitoring, SQL Server Profiler and Windows System
> Monitor should help.
> HTH
> Jerry
> "Santu" <Santu@.discussions.microsoft.com> wrote in message
> news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
> >I have a server with just 4 databases,
> > the issue is that the sql server does not perform well. In the task
> > manager
> > it occupies the maximum mount of memory in the system, and maximum cpu
> > time,
> >
> > the database is just a few gb's(10 -15) and the server has 1 gb ram,
> >
> > and especially if i use a reporting tool made in vb as to query the
> > database
> > it works exceptionally slow,
> >
> > one of my guys says it bcoz of a utility we have working, it bring data
> > from
> > another server connected to our network via an IPLC link(2mb link), this
> > utility works on just 2 tables, brings data from the table up there and
> > imports it in our local database, would there be some better way of
> > accomplishing this?
> >
> > is there some way i can constantly monitor what resources the data base is
> > using?
> >
> > any suggestions will be greatly appreciated,
> > Thx & Regards
> > Santu
> >
>
>

Performance problems

Hi,
I have two questions:
1. I have an SQL server with over 400 databases. I takes
quite some time to load the databases in Enterprise
manager. Does anyone have any suggestions on how I can
speed up this process.
2. I also have one database that is 2 GB in size and it
takes some time to run queries from the database. Can
anyone recommend how I can speed up the database - for
example reindex it.
Thanks for your help.
Regards,
Matthew ReedFor 2. - need more info. What type of indexes are existing on the table?
What are the major types of queries ran from it? Any locking/blocking
issues? Percentage of inserts/updates/deletes versus queries, etc. For
straight index info you might try the Inside SQL Server 2000 book.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Matthew Reed" <anonymous@.discussions.microsoft.com> wrote in message
news:088a01c3d3a1$0c6d4930$a601280a@.phx.gbl...
> Hi,
> I have two questions:
> 1. I have an SQL server with over 400 databases. I takes
> quite some time to load the databases in Enterprise
> manager. Does anyone have any suggestions on how I can
> speed up this process.
> 2. I also have one database that is 2 GB in size and it
> takes some time to run queries from the database. Can
> anyone recommend how I can speed up the database - for
> example reindex it.
> Thanks for your help.
> Regards,
> Matthew Reedsql

Performance problem loading packages

We're having a performance problem with a package since an error occurred. The original error came from the Job Manager whicj was unable to start a thread. Our understanding is that this is a memory related issue and we'll deal with that.

What is realy odd is what happened after that. The package executes from a SQL Agent job that includes 3 other packages. Each package is stored on the file system. Package execution time for the affected package changed from less than a minute to over 5 minutes. The other packages continued to execute normally.

In checking the logs there is large time gap between the start of the SQL Agent step and the first pre-validation message that accounts for 4 minutes, as if there is some issue in loading the package. The issue is ongoing. Does anyone know what happens between the start of the SQL Agent job and the first prevalidate message? Is this some type of caching issue?

SQL Agent step starts at 4:05:27

First Prevalidate message: 4:09:24

Package Execution start: 4:09:57

End Execution 4:10:59

thanks

Peter

Rebooting the server fixed the immediate problem.

performance parameters.,,,

Dear all

Can any one provide me details of the parameters to be monitored in sql server 2005

Currently i am monitoring

SQLServer : Buffer Manager : Buffer Cache Hit Ratio

SQLServer : Databases : Log Flushes / Sec

SQLServer : Databases: Transactions / Sec

SQLServer : Access Methods : Page Splits / Sec.....

1) Please provide if any other relevant parameters to be monitored by perfmon.

2) Which are the most essential parameters to be monitored.

3) Is there any query or trace that will provide me performance details in SQL.

4) Can i have a query that get me the most resource consuming process or query in sql server.

5) wht parameters do i need to monitor on weekly and monthly basis for sql server 2005.

It would be very helpful if you guys could provide me help on these.

Thanks in advance

That always depends upon what you are attempting to monitor. However, that said, there are a few 'base' counters to monitor. I've included a group of articles that will provide you excellent information about performance monitoring and performance tuning.

Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Performance -Link Server Performance Tips
http://www.sql-server-performance.com/linked_server.asp

Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking

Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp

Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp

|||

As Arnie said, it really depends on your workload, and what your normal baseline values are. Having said that, here are some basic "rules of thumb" for those counters:

SQLServer : Buffer Manager : Buffer Cache Hit Ratio - Higher is better, below 90% is considered very bad. Its one sign of memory pressure.

SQLServer : Databases : Log Flushes / Sec - Lower is generally better

SQLServer : Databases: Transactions / Sec - This is one measure of how "busy" your system is. Batch Requests/Second is another measure that can be more helpful.

SQLServer : Access Methods : Page Splits / Sec - Lower is better. Having indexes with very high fill factors on volatile tables will cause more page splits, which leads to fragmented indexes, which causes more I/O.

Performance monitor How to change server

I'm using SQL Server 2000.
On my desktop is Win XP Pro
I have Enterprise manager, Performance Monitor and SQL profiler
installed.
Each time I start performance monitor, the server monitored is the
local server.
How do I change the server I want to monitor? I've searched all the
menus, did right clicks, etc. and I can't change the server.
Thanks,
DWUnderneth the graph there is a histogram showing what you
are watching, it has the menu Color, Scale, Counter,
Instance ect.
Right Click on it and select 'Add Counter'
After that its fairly intuitive ;)
Peter
peternolan67REMOVETHIS@.hotmail.com
"I may be drunk, Miss, but in the morning I will be sober
and you will still be ugly."
Winston Churchill
>--Original Message--
>I'm using SQL Server 2000.
>On my desktop is Win XP Pro
>I have Enterprise manager, Performance Monitor and SQL
profiler
>installed.
>Each time I start performance monitor, the server
monitored is the
>local server.
>How do I change the server I want to monitor? I've
searched all the
>menus, did right clicks, etc. and I can't change the
server.
>Thanks,
>DW
>.
>