Performance Tuning Help needed

This is a Sql 2000 server running on Win 2k. This is a 1
CPU box connected to a SAN.
I ran a profiler trace for 3 hours and collected all the
data. Some of the queries show a large amount of reads.
When I run those queries individually the # of reads is
fairly smaller. For example the trace file shows 36503
reads and the individual query plan shows 91 CPU reads.
Whats going on ? Is the profiler tracking all I/O activity
at that time ? I mean, is it tracking all tempdb usage and
any other I/O activity at this time. What would explain
this large drop in I/O ?
THanks"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
I'm new to SQL Server so take what I say with a grain of salt.. But does
the profiler keep track of how many executions for each SQL statement?
Executed once the number of reads may be small but if it was executed 1000
times during that timeframe I would expect the number to be large.|||i assume you ran the query with the same parameters?
if so, then this large a difference in reads might
indicate the trace captured that query with a different
plan. if the choice of parameters can results in large
differences in rows involved, then depending on what
parameters were used when the query was last compiled,
different executes could get different plans.
a smaller difference on the order of several hundred or
even more than 1k might indicate compile or recompile.
>--Original Message--
>This is a Sql 2000 server running on Win 2k. This is a 1
>CPU box connected to a SAN.
>I ran a profiler trace for 3 hours and collected all the
>data. Some of the queries show a large amount of reads.
>When I run those queries individually the # of reads is
>fairly smaller. For example the trace file shows 36503
>reads and the individual query plan shows 91 CPU reads.
>Whats going on ? Is the profiler tracking all I/O
activity
>at that time ? I mean, is it tracking all tempdb usage
and
>any other I/O activity at this time. What would explain
>this large drop in I/O ?
>THanks
>.
>|||Jack,
are you comparing Profiler output to Query Analyzer query plan?
If so... the difference is "normal".
Trust the Profiler, if it shows 36000 reads, that's really happening
in the DB.
peksi
"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> This is a Sql 2000 server running on Win 2k. This is a 1
> CPU box connected to a SAN.
> I ran a profiler trace for 3 hours and collected all the
> data. Some of the queries show a large amount of reads.
> When I run those queries individually the # of reads is
> fairly smaller. For example the trace file shows 36503
> reads and the individual query plan shows 91 CPU reads.
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
> THanks|||Hi Jack
The profiler shows what's happening at run time, which can be different from
what happens when you run the query for a few reasons, eg:
(a) The level & type of SQL Server activity is different when you run the
query from when the same query was captured by the profiler.
(b) The security context from which you ran the query may be different from
the query picked up by profiler.
(c) General resource levels on the server may be different.
Capturing the execution plans & comparing them may reveal some explanation
of what accounts for the different levels of reads, but this only takes you
part way to resolving the actual problem.
HTH
Regards,
Greg Linwood
SQL Server MVP
"JackA" <anonymous@.discussions.microsoft.com> wrote in message
news:061701c3a972$5d6cc080$a301280a@.phx.gbl...
> This is a Sql 2000 server running on Win 2k. This is a 1
> CPU box connected to a SAN.
> I ran a profiler trace for 3 hours and collected all the
> data. Some of the queries show a large amount of reads.
> When I run those queries individually the # of reads is
> fairly smaller. For example the trace file shows 36503
> reads and the individual query plan shows 91 CPU reads.
> Whats going on ? Is the profiler tracking all I/O activity
> at that time ? I mean, is it tracking all tempdb usage and
> any other I/O activity at this time. What would explain
> this large drop in I/O ?
> THankssql

performance tuning as a DBA for ERP system

Hi,
I have an ERP system running on SQL server 2000. How do i actually go abt
doing performance tuning as a DBA ?
i can check the no of ppl using the ERP and their activities. but come to
slow performance , how do i actually go abt doing performance tuning ?
for example , for this functional module of the ERP system i will not be
able to tell what stored procedures/queries that are being executed ? even if
i do know , i won't be able to change as that might cause further problem (do
i need to call the vendor support in this case ?) and my support agreement
will not cover any issues caused by changing the database ..
In the above scenario , how should a dba go abt perfoming a db performance
tunning ?
hope those experienced dbas can give me some ideas on how it works
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
You should be able to use profiler to watch for long running procs, or procs
which are doing a lot of reads or writes. These are candidates to be tuned.
Also watch for stored procedure recompilations, etc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:591855ccaf94c@.uwe...
> Hi,
> I have an ERP system running on SQL server 2000. How do i actually go abt
> doing performance tuning as a DBA ?
> i can check the no of ppl using the ERP and their activities. but come to
> slow performance , how do i actually go abt doing performance tuning ?
> for example , for this functional module of the ERP system i will not be
> able to tell what stored procedures/queries that are being executed ? even
> if
> i do know , i won't be able to change as that might cause further problem
> (do
> i need to call the vendor support in this case ?) and my support agreement
> will not cover any issues caused by changing the database ..
> In the above scenario , how should a dba go abt perfoming a db performance
> tunning ?
> hope those experienced dbas can give me some ideas on how it works
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200512/1
|||Hi,
would you recommend using a 3rd party s/w to monitor the SPs/Queries being
acccessed as i read somewhere that profiler is cpu-intensive and might "hang"
the SQL Server if running for a long period of time ?
tks & rdgs
Hilary Cotter wrote:[vbcol=seagreen]
>You should be able to use profiler to watch for long running procs, or procs
>which are doing a lot of reads or writes. These are candidates to be tuned.
>Also watch for stored procedure recompilations, etc.
>[quoted text clipped - 18 lines]
Message posted via http://www.droptable.com

performance tips

got a friend with sql server 2000 that keeps running slow, he'll restart it
and it will run fine for awhile but will gradually get slower.
He's a developer that got DBA thrust upon him.
Wht are some performance tips to help fix this?
thankssome other links that are very useful
Overview of General SQL Server Performance methodology
http://support.microsoft.com/defaul...kb;en-us;298475
Online seminar on SQL2k Performance by a MS PSS guy who knows his stuff
http://support.microsoft.com/defaul...kb;en-us;838622
cheers,
Andy.
"mike w." <mikew@.discussions.microsoft.com> wrote in message
news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
> got a friend with sql server 2000 that keeps running slow, he'll restart
it and it will run fine for awhile but will gradually get slower.
> He's a developer that got DBA thrust upon him.
> Wht are some performance tips to help fix this?
> thanks|||Great Links Andy. Thanks a ton. I have seen some other
links posted in below threads. Very useful!
Regards
Thirumal
www.thirumal.com
>--Original Message--
>some other links that are very useful
>Overview of General SQL Server Performance methodology
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475
>Online seminar on SQL2k Performance by a MS PSS guy who
knows his stuff
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;838622
>cheers,
>Andy.
>
>"mike w." <mikew@.discussions.microsoft.com> wrote in
message
>news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
slow, he'll restart[vbcol=seagreen]
>it and it will run fine for awhile but will gradually get
slower.
>
>.
>

performance tips

got a friend with sql server 2000 that keeps running slow, he'll restart it and it will run fine for awhile but will gradually get slower.
He's a developer that got DBA thrust upon him.
Wht are some performance tips to help fix this?
thanksHi Mike,
You need to check whether any other applications running
on the same box are competing with SQL Server for memory
and CPU. Sounds he might be having blocking issues too.
With little information you provided its tough to guess
the problem. I suggest your friend to start here..
Great stuff on SQL Server performance tuning.
http://www.sql-server-performance.com
The Definitive GuideT to SQL Server Performance
Optimization eBook
http://www.veritas.com/Vrt/offer?
_requestid=28701&a_id=3807&
Also checkout the book must for a DBA on performance
tuning wrote by whalen and team at microsoft.
HTH
--
Regards
Thirumal
www.thirumal.com
>--Original Message--
>got a friend with sql server 2000 that keeps running
slow, he'll restart it and it will run fine for awhile but
will gradually get slower.
>He's a developer that got DBA thrust upon him.
>Wht are some performance tips to help fix this?
>thanks
>.
>|||some other links that are very useful
Overview of General SQL Server Performance methodology
http://support.microsoft.com/default.aspx?scid=kb;en-us;298475
Online seminar on SQL2k Performance by a MS PSS guy who knows his stuff
http://support.microsoft.com/default.aspx?scid=kb;en-us;838622
cheers,
Andy.
"mike w." <mikew@.discussions.microsoft.com> wrote in message
news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
> got a friend with sql server 2000 that keeps running slow, he'll restart
it and it will run fine for awhile but will gradually get slower.
> He's a developer that got DBA thrust upon him.
> Wht are some performance tips to help fix this?
> thanks|||Great Links Andy. Thanks a ton. I have seen some other
links posted in below threads. Very useful!
Regards
Thirumal
www.thirumal.com
>--Original Message--
>some other links that are very useful
>Overview of General SQL Server Performance methodology
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475
>Online seminar on SQL2k Performance by a MS PSS guy who
knows his stuff
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;838622
>cheers,
>Andy.
>
>"mike w." <mikew@.discussions.microsoft.com> wrote in
message
>news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
>> got a friend with sql server 2000 that keeps running
slow, he'll restart
>it and it will run fine for awhile but will gradually get
slower.
>> He's a developer that got DBA thrust upon him.
>> Wht are some performance tips to help fix this?
>> thanks
>
>.
>

performance tips

got a friend with sql server 2000 that keeps running slow, he'll restart it and it will run fine for awhile but will gradually get slower.
He's a developer that got DBA thrust upon him.
Wht are some performance tips to help fix this?
thanks
some other links that are very useful
Overview of General SQL Server Performance methodology
http://support.microsoft.com/default...b;en-us;298475
Online seminar on SQL2k Performance by a MS PSS guy who knows his stuff
http://support.microsoft.com/default...b;en-us;838622
cheers,
Andy.
"mike w." <mikew@.discussions.microsoft.com> wrote in message
news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
> got a friend with sql server 2000 that keeps running slow, he'll restart
it and it will run fine for awhile but will gradually get slower.
> He's a developer that got DBA thrust upon him.
> Wht are some performance tips to help fix this?
> thanks
|||Great Links Andy. Thanks a ton. I have seen some other
links posted in below threads. Very useful!
Regards
Thirumal
www.thirumal.com
>--Original Message--
>some other links that are very useful
>Overview of General SQL Server Performance methodology
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475
>Online seminar on SQL2k Performance by a MS PSS guy who
knows his stuff
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;838622
>cheers,
>Andy.
>
>"mike w." <mikew@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
slow, he'll restart
>it and it will run fine for awhile but will gradually get
slower.
>
>.
>

Performance testing - tips

I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performing
like it should. What we expericence is ok performance running single long
running queries. But running 2-3 long running queries at the same time
really makes the whole server very slow. Imagine this:
1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
2) At the same time, run a select on a small table not involved in the query
above: Sometimes the result is coming after 15-30 secs. Sometimes it will
not display until the first query is done. There is no lock-situation
involved.
I have done the same thing on SQL Server 2000 with slower hardware for
several years. Sadly I don't have access to the old machine anymore, so it's
hard to tell the real difference.
CPU is not an issue - SQL server is using only 25-50 percent. So my first
conclusion is that the disk-system is not performing well. We separate RAIDs
for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
(raid1). The raids are spread on 2 controllers with 2 channels each.
But where to start? Does anyone have some stored procedures+testdata which I
can use as a start to compare with?
Per SchjetneOne place I'd start is to look at the disk-related perfmon counter values. In
particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
latency counters. If there is a significantly higher I/O latency during the
period you are running your queries concurrently as compared to when you are
not running them at the same time, you may have a disk I/O issue.
To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to be
very useful. Every time when you get a new disk system, run sqlio.exe to get
a comprehensive performance profile of the disk system. This establishes a
baseline for you. Later, if you suspect something is not behaving properly in
your disk subsystem, you can run the same benchmark and compare the results
with the saved baseline.
Linchi
"Per Schjetne" wrote:
> I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performing
> like it should. What we expericence is ok performance running single long
> running queries. But running 2-3 long running queries at the same time
> really makes the whole server very slow. Imagine this:
> 1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
> 2) At the same time, run a select on a small table not involved in the query
> above: Sometimes the result is coming after 15-30 secs. Sometimes it will
> not display until the first query is done. There is no lock-situation
> involved.
> I have done the same thing on SQL Server 2000 with slower hardware for
> several years. Sadly I don't have access to the old machine anymore, so it's
> hard to tell the real difference.
> CPU is not an issue - SQL server is using only 25-50 percent. So my first
> conclusion is that the disk-system is not performing well. We separate RAIDs
> for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
> (raid1). The raids are spread on 2 controllers with 2 channels each.
> But where to start? Does anyone have some stored procedures+testdata which I
> can use as a start to compare with?
> Per Schjetne
>
>|||Thanks a lot for the tip! I'll try it.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:92E26405-BF1A-4BBD-9E80-BED6D7CA246E@.microsoft.com...
> One place I'd start is to look at the disk-related perfmon counter values.
> In
> particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
> latency counters. If there is a significantly higher I/O latency during
> the
> period you are running your queries concurrently as compared to when you
> are
> not running them at the same time, you may have a disk I/O issue.
> To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to
> be
> very useful. Every time when you get a new disk system, run sqlio.exe to
> get
> a comprehensive performance profile of the disk system. This establishes a
> baseline for you. Later, if you suspect something is not behaving properly
> in
> your disk subsystem, you can run the same benchmark and compare the
> results
> with the saved baseline.
> Linchi
> "Per Schjetne" wrote:
>> I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not
>> performing
>> like it should. What we expericence is ok performance running single long
>> running queries. But running 2-3 long running queries at the same time
>> really makes the whole server very slow. Imagine this:
>> 1) Start 1 long running query accessing 2 large tables. CPU is used
>> 25-30%
>> 2) At the same time, run a select on a small table not involved in the
>> query
>> above: Sometimes the result is coming after 15-30 secs. Sometimes it will
>> not display until the first query is done. There is no lock-situation
>> involved.
>> I have done the same thing on SQL Server 2000 with slower hardware for
>> several years. Sadly I don't have access to the old machine anymore, so
>> it's
>> hard to tell the real difference.
>> CPU is not an issue - SQL server is using only 25-50 percent. So my first
>> conclusion is that the disk-system is not performing well. We separate
>> RAIDs
>> for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
>> (raid1). The raids are spread on 2 controllers with 2 channels each.
>> But where to start? Does anyone have some stored procedures+testdata
>> which I
>> can use as a start to compare with?
>> Per Schjetne
>>

Performance testing - tips

I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performing
like it should. What we expericence is ok performance running single long
running queries. But running 2-3 long running queries at the same time
really makes the whole server very slow. Imagine this:
1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
2) At the same time, run a select on a small table not involved in the query
above: Sometimes the result is coming after 15-30 secs. Sometimes it will
not display until the first query is done. There is no lock-situation
involved.
I have done the same thing on SQL Server 2000 with slower hardware for
several years. Sadly I don't have access to the old machine anymore, so it's
hard to tell the real difference.
CPU is not an issue - SQL server is using only 25-50 percent. So my first
conclusion is that the disk-system is not performing well. We separate RAIDs
for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
(raid1). The raids are spread on 2 controllers with 2 channels each.
But where to start? Does anyone have some stored procedures+testdata which I
can use as a start to compare with?
Per SchjetneOne place I'd start is to look at the disk-related perfmon counter values. I
n
particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
latency counters. If there is a significantly higher I/O latency during the
period you are running your queries concurrently as compared to when you are
not running them at the same time, you may have a disk I/O issue.
To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to be
very useful. Every time when you get a new disk system, run sqlio.exe to get
a comprehensive performance profile of the disk system. This establishes a
baseline for you. Later, if you suspect something is not behaving properly i
n
your disk subsystem, you can run the same benchmark and compare the results
with the saved baseline.
Linchi
"Per Schjetne" wrote:

> I'm suspecting our new Dell 2850/ SQL Server 2005 machine is not performin
g
> like it should. What we expericence is ok performance running single long
> running queries. But running 2-3 long running queries at the same time
> really makes the whole server very slow. Imagine this:
> 1) Start 1 long running query accessing 2 large tables. CPU is used 25-30%
> 2) At the same time, run a select on a small table not involved in the que
ry
> above: Sometimes the result is coming after 15-30 secs. Sometimes it will
> not display until the first query is done. There is no lock-situation
> involved.
> I have done the same thing on SQL Server 2000 with slower hardware for
> several years. Sadly I don't have access to the old machine anymore, so it
's
> hard to tell the real difference.
> CPU is not an issue - SQL server is using only 25-50 percent. So my first
> conclusion is that the disk-system is not performing well. We separate RAI
Ds
> for system (Raid1), tempdb (raid1), log (raid1), data (raid5), index
> (raid1). The raids are spread on 2 controllers with 2 channels each.
> But where to start? Does anyone have some stored procedures+testdata which
I
> can use as a start to compare with?
> Per Schjetne
>
>|||Thanks a lot for the tip! I'll try it.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:92E26405-BF1A-4BBD-9E80-BED6D7CA246E@.microsoft.com...[vbcol=seagreen]
> One place I'd start is to look at the disk-related perfmon counter values.
> In
> particular, I'd look at Disk Sec/Read and Disk sec/Write; these are disk
> latency counters. If there is a significantly higher I/O latency during
> the
> period you are running your queries concurrently as compared to when you
> are
> not running them at the same time, you may have a disk I/O issue.
> To benchmark the disk subsystem, I have found sqlio.exe from Microsoft to
> be
> very useful. Every time when you get a new disk system, run sqlio.exe to
> get
> a comprehensive performance profile of the disk system. This establishes a
> baseline for you. Later, if you suspect something is not behaving properly
> in
> your disk subsystem, you can run the same benchmark and compare the
> results
> with the saved baseline.
> Linchi
> "Per Schjetne" wrote:
>

Performance slow in Vista. Please help.

I have an application I'm trying to run under Vista using SQL 2005 as the
backend database. Right now the application is running super slow and I dont
know why. If I run SQL 2005 under a Virtual machine running xp, the app runs
fine but if I run the application with SQL 2005 under vista performance is
slow.
Configuration:
Vista all patches according to Windows Update
SQL 2005 - SP2
Doesnt work
Vista - running SQL 2005 in a VM under XP
Works
Can someone point me in the correct direction? Thanks in advance!!
MikeWhen an SQL-Server application is running slow, the first thing to do is to
make sure that the statistics are up to date by using sp_updatestats stored
procedure (or use UPDATE STATISTICS is you want to work on a more detailed
level) and cleaning the caches after that:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
(See
http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41 )
.
If this doesn't work, you can try reindexing everything:
http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/
Finally, things like using the option WITH RECOMPILE or playing with the
parameters sniffing (search this newsgroup with Google for more details) can
help you; as well as other things related to optimizing any sql-server
application.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:0EE6E1F8-2CEF-4B06-AE2B-B19F01D9C954@.microsoft.com...
>I have an application I'm trying to run under Vista using SQL 2005 as the
> backend database. Right now the application is running super slow and I
> dont
> know why. If I run SQL 2005 under a Virtual machine running xp, the app
> runs
> fine but if I run the application with SQL 2005 under vista performance is
> slow.
> Configuration:
> Vista all patches according to Windows Update
> SQL 2005 - SP2
> Doesnt work
>
> Vista - running SQL 2005 in a VM under XP
> Works
> Can someone point me in the correct direction? Thanks in advance!!
> Mike|||I have the same problem and I believe it has nothing to do with SQL Server
maintenance. It look to me that Vista has a bug when running SQL Server 2005.
I am running a .NET 2.0 winform application on a Vista machine. The app runs
about 200 updates on a single table. When updating the Vista SQL Server 2005
instance the time is more then double comparing to the time to complete when
updating the XP SQL Server 2005 instance. Plus the Vista machine is Intel
Core 2 Quad, while the XP machine is Intel Pentium D.
Alex
"Sylvain Lafontaine" wrote:
> When an SQL-Server application is running slow, the first thing to do is to
> make sure that the statistics are up to date by using sp_updatestats stored
> procedure (or use UPDATE STATISTICS is you want to work on a more detailed
> level) and cleaning the caches after that:
> DBCC FLUSHPROCINDB
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> (See
> http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41 )
> ..
> If this doesn't work, you can try reindexing everything:
> http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/
> Finally, things like using the option WITH RECOMPILE or playing with the
> parameters sniffing (search this newsgroup with Google for more details) can
> help you; as well as other things related to optimizing any sql-server
> application.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:0EE6E1F8-2CEF-4B06-AE2B-B19F01D9C954@.microsoft.com...
> >I have an application I'm trying to run under Vista using SQL 2005 as the
> > backend database. Right now the application is running super slow and I
> > dont
> > know why. If I run SQL 2005 under a Virtual machine running xp, the app
> > runs
> > fine but if I run the application with SQL 2005 under vista performance is
> > slow.
> >
> > Configuration:
> >
> > Vista all patches according to Windows Update
> > SQL 2005 - SP2
> >
> > Doesnt work
> >
> >
> > Vista - running SQL 2005 in a VM under XP
> >
> > Works
> >
> > Can someone point me in the correct direction? Thanks in advance!!
> >
> > Mike
>
>

Performance Recommendations for SQL Server - Paging Related

I just became in charge of a server running SQL Server 2000 Enterprise on a
Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB RAM
of the 32 GB RAM in use. The database on the server is 120GB. Approximately
60GB is actual data, 49GB of indexes and the rest is empty space. For
reference, this database is for our ClarifyCRM application.
I am starting to do performance monitoring and I am seeing a high amount of
system paging - 1,000-2000 or more pages/sec.
Even though RAM utilization is low with plenty to spare, I see that the
paging file on the server is only 2GB in size (40.8 MB of it is actually in
use). Other than increasing the size of the paging file (which I will be
doing soon) while is extremely small, are there any other recommendations to
decrease the amount of system paging that is occurring?What are the memory settings set to, especially the MAX Memory one? It
sounds odd that only 17.8 out of 32GB is being used. I assume this is 32 bit
so with AWE the memory should not be dynamic and I would expect it to be
much higher. What other apps are running on this server?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>I just became in charge of a server running SQL Server 2000 Enterprise on a
> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
> RAM
> of the 32 GB RAM in use. The database on the server is 120GB.
> Approximately
> 60GB is actual data, 49GB of indexes and the rest is empty space. For
> reference, this database is for our ClarifyCRM application.
> I am starting to do performance monitoring and I am seeing a high amount
> of
> system paging - 1,000-2000 or more pages/sec.
> Even though RAM utilization is low with plenty to spare, I see that the
> paging file on the server is only 2GB in size (40.8 MB of it is actually
> in
> use). Other than increasing the size of the paging file (which I will be
> doing soon) while is extremely small, are there any other recommendations
> to
> decrease the amount of system paging that is occurring?|||I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
of the actual memory you can get at.
Tony.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
> What are the memory settings set to, especially the MAX Memory one? It
> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
> bit so with AWE the memory should not be dynamic and I would expect it to
> be much higher. What other apps are running on this server?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise on
>>a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
>> RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high amount
>> of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is actually
>> in
>> use). Other than increasing the size of the paging file (which I will be
>> doing soon) while is extremely small, are there any other recommendations
>> to
>> decrease the amount of system paging that is occurring?
>|||You may not actually have a problem.
Microsoft has an excellent (and short) article on paging and monitorint it,
I suggect you spend a few minutes reading it.
http://support.microsoft.com/kb/555223
Good luck,
Jay
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>I just became in charge of a server running SQL Server 2000 Enterprise on a
> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
> RAM
> of the 32 GB RAM in use. The database on the server is 120GB.
> Approximately
> 60GB is actual data, 49GB of indexes and the rest is empty space. For
> reference, this database is for our ClarifyCRM application.
> I am starting to do performance monitoring and I am seeing a high amount
> of
> system paging - 1,000-2000 or more pages/sec.
> Even though RAM utilization is low with plenty to spare, I see that the
> paging file on the server is only 2GB in size (40.8 MB of it is actually
> in
> use). Other than increasing the size of the paging file (which I will be
> doing soon) while is extremely small, are there any other recommendations
> to
> decrease the amount of system paging that is occurring?|||Yes Tony probably hit the nail on the head here. You are using SQL2000 so
you most likely have SP4 without the Hotfix and it will only use approx half
the memory.
http://support.microsoft.com/kb/899761
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tony Rogerson" <tonyrogerson@.torver.net> wrote in message
news:64222C7B-A187-4BE8-AB4D-AA309F75377A@.microsoft.com...
>I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
>of the actual memory you can get at.
> Tony.
> --
> Tony Rogerson, SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson
> [Ramblings from the field from a SQL consultant]
> http://sqlserverfaq.com
> [UK SQL User Community]
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> What are the memory settings set to, especially the MAX Memory one? It
>> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
>> bit so with AWE the memory should not be dynamic and I would expect it to
>> be much higher. What other apps are running on this server?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise on
>>a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8
>> GB RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high amount
>> of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is actually
>> in
>> use). Other than increasing the size of the paging file (which I will be
>> doing soon) while is extremely small, are there any other
>> recommendations to
>> decrease the amount of system paging that is occurring?
>|||Thats the one.
Thanks Andrew - I was being lazy, well, time constrained :(
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OhLBUbbDIHA.5976@.TK2MSFTNGP02.phx.gbl...
> Yes Tony probably hit the nail on the head here. You are using SQL2000 so
> you most likely have SP4 without the Hotfix and it will only use approx
> half the memory.
> http://support.microsoft.com/kb/899761
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tony Rogerson" <tonyrogerson@.torver.net> wrote in message
> news:64222C7B-A187-4BE8-AB4D-AA309F75377A@.microsoft.com...
>>I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
>>of the actual memory you can get at.
>> Tony.
>> --
>> Tony Rogerson, SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson
>> [Ramblings from the field from a SQL consultant]
>> http://sqlserverfaq.com
>> [UK SQL User Community]
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> What are the memory settings set to, especially the MAX Memory one? It
>> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
>> bit so with AWE the memory should not be dynamic and I would expect it
>> to be much higher. What other apps are running on this server?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise
>>on a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8
>> GB RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high
>> amount of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is
>> actually in
>> use). Other than increasing the size of the paging file (which I will
>> be
>> doing soon) while is extremely small, are there any other
>> recommendations to
>> decrease the amount of system paging that is occurring?
>>
>

Performance Questions on SQL 2000 from perfmon

I am a new admin at our office and I am trying to get a handle on the SQL
systems running.
I followed a few online guides to performance monitoring and I have a few
concerns that I wanted to run past some experts.
This SQL server is a dual PIII 1.4 GHz with 2 GB of RAM.
So far I have monitored a few SQL specific counters along with the proc and
memory. Under normal load, everything has seemed good so far, but when we
run reports, the SQL server goes under a heavy load that appears to be
stressing the system. Under Task Manager, the procs spike a lot (not a
curtain though) and the server becomes sluggish. This is what I have
discovered so far from perfmon:
1) Under the processor queue length, the graph is spiking over 200, but it
lists a maximum of 49 on the chart. Either way, this seems to be pretty
high.
2) Total proc time is averaging at around 50%
3) The sqlserver Working Set is at 1.6 GB
4) Pages/Sec frequently exceeds 50 with an average at 20 and a maximum of
755
5) Available MBytes is at 10 - 16
6) Page File % usage is at 38%
7) Non-Paged Pool Memory was around 26 MB
During heavy load, I also lost some collection of data via perfmon at 5
second intervals. My interpretation from this is that SQL has sucked up all
available RAM and wants more. I have not rebooted to see how this effects
the server. The proc's seem to be overloaded under heavy load with the
queue length filing up, but an avg of 50% shouldn't need more processing.
Also I attribute the loss of data collection to the high queue length.
What are possible resolutions? Do I need to collect more data? I suspect
that SQL is not using the 1.6 GB assigned to it very effectively. Is it
possible that some of the applications we have running are somehow either
causing a memory leak, or locking the memory in SQL? If that is an issue,
how can I detect what memory inside SQL is locked/wasted?
Thank You for any assistance,
Kevin Hammonddatabase engines are essentially a disk cache, so it so
should be using all the memory it needs if it is
available, otherwise, it would have to go to disk for the
data,
if you are concerned as to whether SQL server really needs
all that memory, monitor disk read/sec, writes/sec, avg
disk queue len, avg sec/read and avg sec/write for the
physical disk containing the main data files (not the log)
then try reducing the amount of memory SQL Server can use,
if the disk counters rise, then SQL Server was making good
use of that memory
>--Original Message--
>I am a new admin at our office and I am trying to get a
handle on the SQL
>systems running.
>I followed a few online guides to performance monitoring
and I have a few
>concerns that I wanted to run past some experts.
>This SQL server is a dual PIII 1.4 GHz with 2 GB of RAM.
>So far I have monitored a few SQL specific counters along
with the proc and
>memory. Under normal load, everything has seemed good so
far, but when we
>run reports, the SQL server goes under a heavy load that
appears to be
>stressing the system. Under Task Manager, the procs
spike a lot (not a
>curtain though) and the server becomes sluggish. This is
what I have
>discovered so far from perfmon:
>1) Under the processor queue length, the graph is
spiking over 200, but it
>lists a maximum of 49 on the chart. Either way, this
seems to be pretty
>high.
>2) Total proc time is averaging at around 50%
>3) The sqlserver Working Set is at 1.6 GB
>4) Pages/Sec frequently exceeds 50 with an average at 20
and a maximum of
>755
>5) Available MBytes is at 10 - 16
>6) Page File % usage is at 38%
>7) Non-Paged Pool Memory was around 26 MB
>During heavy load, I also lost some collection of data
via perfmon at 5
>second intervals. My interpretation from this is that
SQL has sucked up all
>available RAM and wants more. I have not rebooted to see
how this effects
>the server. The proc's seem to be overloaded under heavy
load with the
>queue length filing up, but an avg of 50% shouldn't need
more processing.
>Also I attribute the loss of data collection to the high
queue length.
>What are possible resolutions? Do I need to collect more
data? I suspect
>that SQL is not using the 1.6 GB assigned to it very
effectively. Is it
>possible that some of the applications we have running
are somehow either
>causing a memory leak, or locking the memory in SQL? If
that is an issue,
>how can I detect what memory inside SQL is locked/wasted?
>Thank You for any assistance,
>Kevin Hammond
>
>.
>

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 Question

Hello,
The database we have is pretty CPU intensive.We have a trace running on the
server 24*7 although it doesn't have lot of events in trace and we run this
through stored procedures and not through GUI.Does anybody think trace is
going to affect the performance and the drive the CPU little more up.
Thanks
Yes, but it is impossible to say how much. Key for tracing without affecting performance too much is to use a
server side trace (define through stored procedures not GUI), have the output file local to the SQL Server
(not a network drive) and minimize the events.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:8D5D15B2-D664-489A-AD46-56C353BF5F2E@.microsoft.com...
> Hello,
> The database we have is pretty CPU intensive.We have a trace running on the
> server 24*7 although it doesn't have lot of events in trace and we run this
> through stored procedures and not through GUI.Does anybody think trace is
> going to affect the performance and the drive the CPU little more up.
> Thanks

Performance Question

Newbie question on SQL, running 8 processors with 8GB of RAM. All but maybe
500MB of RAM is showing in use on Task Mgr with sql.exe only using
approximately 200MB. Nothing appears to be using the RAM, anyway or any idea
what is taking up all of the memory?
Thanks, Sean
> Newbie question on SQL, running 8 processors with 8GB of RAM. All but
maybe
> 500MB of RAM is showing in use on Task Mgr with sql.exe only using
> approximately 200MB. Nothing appears to be using the RAM, anyway or any
idea
> what is taking up all of the memory?
Make sure you have checked "show processes from all users" and then click on
Mem Usage to make that column order...
http://www.aspfaq.com/
(Reverse address to reply.)

Performance Question

Newbie question on SQL, running 8 processors with 8GB of RAM. All but maybe
500MB of RAM is showing in use on Task Mgr with sql.exe only using
approximately 200MB. Nothing appears to be using the RAM, anyway or any idea
what is taking up all of the memory?
Thanks, Sean> Newbie question on SQL, running 8 processors with 8GB of RAM. All but
maybe
> 500MB of RAM is showing in use on Task Mgr with sql.exe only using
> approximately 200MB. Nothing appears to be using the RAM, anyway or any
idea
> what is taking up all of the memory?
Make sure you have checked "show processes from all users" and then click on
Mem Usage to make that column order...
--
http://www.aspfaq.com/
(Reverse address to reply.)

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

Hello,
The database we have is pretty CPU intensive.We have a trace running on the
server 24*7 although it doesn't have lot of events in trace and we run this
through stored procedures and not through GUI.Does anybody think trace is
going to affect the performance and the drive the CPU little more up.
ThanksYes, but it is impossible to say how much. Key for tracing without affecting
performance too much is to use a
server side trace (define through stored procedures not GUI), have the outpu
t file local to the SQL Server
(not a network drive) and minimize the events.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:8D5D15B2-D664-489A-AD46-56C353BF5F2E@.microsoft.com...
> Hello,
> The database we have is pretty CPU intensive.We have a trace running on th
e
> server 24*7 although it doesn't have lot of events in trace and we run th
is
> through stored procedures and not through GUI.Does anybody think trace is
> going to affect the performance and the drive the CPU little more up.
> Thanks

Performance Question

Newbie question on SQL, running 8 processors with 8GB of RAM. All but maybe
500MB of RAM is showing in use on Task Mgr with sql.exe only using
approximately 200MB. Nothing appears to be using the RAM, anyway or any ide
a
what is taking up all of the memory?
Thanks, Sean> Newbie question on SQL, running 8 processors with 8GB of RAM. All but
maybe
> 500MB of RAM is showing in use on Task Mgr with sql.exe only using
> approximately 200MB. Nothing appears to be using the RAM, anyway or any
idea
> what is taking up all of the memory?
Make sure you have checked "show processes from all users" and then click on
Mem Usage to make that column order...
http://www.aspfaq.com/
(Reverse address to reply.)

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

Performance puzzle

We have a new Compaq Proliant DL760 server running
Windows 2003 that we bought to replace a Dell 8450
running Windows 2000
The new machine has 4 2.5 Ghz processors with hyper-
threading (when you open Task Mgr it looks like it
has 8 processors)
The old machine has 8 P3 700 Mhz processors.
Both machines have direct attached fiber channel
arrays configured identically except the new one uses
72 Gig hard drives and the old one has 36 Gig drives.
I am using 9 drives in Raid5 for data and 4 drives in Raid
1+0 for logs on both machines. Both arrays have 15000 RPM
drives.
Both machines have SQL Server 2000. My test database is
about 4 Gig and it is identical on both servers.
I have a stress test script with a variety of operations
such as bulk insert, create clustered index, calculations,
etc.
As you would expect, the new machine runs processor
intensive operations much faster. But the old machine runs
disk intensive operations faster than the new one. We had
expected at least comparable performance.
Overall, the test runs in 39 minutes on the old machine
and 40 minutes on the new one (pretty close, I know, but
the new one should win by a larger margin).
Is there anything special about running SQL Server 2000 on
Windows 2003 that we need to know?
Does the Compaq drive array have some limitation that the
Dell does not?
Thanks
Dave GDave,
don't know whether you have considered the following two variants:
1. seek time. it is a specification of the hard drive, and though not much,
different drives can have different seek times. With the rotation speed on
the higher end, the seek times' difference plays a larger role
2. sorting mechanism of the RAID controllers: some controllers support
elevator sorting that is clearly at advantage than without.
hth
Quentin
"DaveG" <anonymous@.discussions.microsoft.com> wrote in message
news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
> We have a new Compaq Proliant DL760 server running
> Windows 2003 that we bought to replace a Dell 8450
> running Windows 2000
> The new machine has 4 2.5 Ghz processors with hyper-
> threading (when you open Task Mgr it looks like it
> has 8 processors)
> The old machine has 8 P3 700 Mhz processors.
> Both machines have direct attached fiber channel
> arrays configured identically except the new one uses
> 72 Gig hard drives and the old one has 36 Gig drives.
> I am using 9 drives in Raid5 for data and 4 drives in Raid
> 1+0 for logs on both machines. Both arrays have 15000 RPM
> drives.
> Both machines have SQL Server 2000. My test database is
> about 4 Gig and it is identical on both servers.
> I have a stress test script with a variety of operations
> such as bulk insert, create clustered index, calculations,
> etc.
> As you would expect, the new machine runs processor
> intensive operations much faster. But the old machine runs
> disk intensive operations faster than the new one. We had
> expected at least comparable performance.
> Overall, the test runs in 39 minutes on the old machine
> and 40 minutes on the new one (pretty close, I know, but
> the new one should win by a larger margin).
> Is there anything special about running SQL Server 2000 on
> Windows 2003 that we need to know?
> Does the Compaq drive array have some limitation that the
> Dell does not?
> Thanks
> Dave G
>|||Thanks, I'll see if I can find the specs.
DG
>--Original Message--
>Dave,
>don't know whether you have considered the following two
variants:
>1. seek time. it is a specification of the hard drive,
and though not much,
>different drives can have different seek times. With the
rotation speed on
>the higher end, the seek times' difference plays a larger
role
>2. sorting mechanism of the RAID controllers: some
controllers support
>elevator sorting that is clearly at advantage than
without.
>hth
>Quentin
>
>"DaveG" <anonymous@.discussions.microsoft.com> wrote in
message
>news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
>> We have a new Compaq Proliant DL760 server running
>> Windows 2003 that we bought to replace a Dell 8450
>> running Windows 2000
>> The new machine has 4 2.5 Ghz processors with hyper-
>> threading (when you open Task Mgr it looks like it
>> has 8 processors)
>> The old machine has 8 P3 700 Mhz processors.
>> Both machines have direct attached fiber channel
>> arrays configured identically except the new one uses
>> 72 Gig hard drives and the old one has 36 Gig drives.
>> I am using 9 drives in Raid5 for data and 4 drives in
Raid
>> 1+0 for logs on both machines. Both arrays have 15000
RPM
>> drives.
>> Both machines have SQL Server 2000. My test database is
>> about 4 Gig and it is identical on both servers.
>> I have a stress test script with a variety of operations
>> such as bulk insert, create clustered index,
calculations,
>> etc.
>> As you would expect, the new machine runs processor
>> intensive operations much faster. But the old machine
runs
>> disk intensive operations faster than the new one. We
had
>> expected at least comparable performance.
>> Overall, the test runs in 39 minutes on the old machine
>> and 40 minutes on the new one (pretty close, I know, but
>> the new one should win by a larger margin).
>> Is there anything special about running SQL Server 2000
on
>> Windows 2003 that we need to know?
>> Does the Compaq drive array have some limitation that
the
>> Dell does not?
>> Thanks
>> Dave G
>>
>
>.
>|||You don't mention which drive arrays you have but from what I've seen of our
Compaq RA8000's there is more to the configuration than just the RAID level.
So it might still be something between the way the arrays are configured. If
you're familiar with IOMeter from Intel you might want to use that to test
the disk systems performance.
Also, from the tests I've seen hyperthreading isn't the same as having more
full processors, so maybe the difference in processor count plays a larger
part than expected. It may be possible that hyperthreading is actually
causing worse performance, you might want to test disabling it.
Mike Kruchten
"DaveG" <anonymous@.discussions.microsoft.com> wrote in message
news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
> We have a new Compaq Proliant DL760 server running
> Windows 2003 that we bought to replace a Dell 8450
> running Windows 2000
> The new machine has 4 2.5 Ghz processors with hyper-
> threading (when you open Task Mgr it looks like it
> has 8 processors)
> The old machine has 8 P3 700 Mhz processors.
> Both machines have direct attached fiber channel
> arrays configured identically except the new one uses
> 72 Gig hard drives and the old one has 36 Gig drives.
> I am using 9 drives in Raid5 for data and 4 drives in Raid
> 1+0 for logs on both machines. Both arrays have 15000 RPM
> drives.
> Both machines have SQL Server 2000. My test database is
> about 4 Gig and it is identical on both servers.
> I have a stress test script with a variety of operations
> such as bulk insert, create clustered index, calculations,
> etc.
> As you would expect, the new machine runs processor
> intensive operations much faster. But the old machine runs
> disk intensive operations faster than the new one. We had
> expected at least comparable performance.
> Overall, the test runs in 39 minutes on the old machine
> and 40 minutes on the new one (pretty close, I know, but
> the new one should win by a larger margin).
> Is there anything special about running SQL Server 2000 on
> Windows 2003 that we need to know?
> Does the Compaq drive array have some limitation that the
> Dell does not?
> Thanks
> Dave G
>|||> Also, from the tests I've seen hyperthreading isn't the same as having
more
> full processors, so maybe the difference in processor count plays a larger
> part than expected. It may be possible that hyperthreading is actually
> causing worse performance, you might want to test disabling it.
Absolutely right.
> "DaveG" <anonymous@.discussions.microsoft.com> wrote in message
> news:02c701c3dae2$a256fde0$a401280a@.phx.gbl...
> > We have a new Compaq Proliant DL760 server running
> > Windows 2003 that we bought to replace a Dell 8450
> > running Windows 2000
> > The new machine has 4 2.5 Ghz processors with hyper-
> > threading (when you open Task Mgr it looks like it
> > has 8 processors)
> > The old machine has 8 P3 700 Mhz processors.
> > Both machines have direct attached fiber channel
> > arrays configured identically except the new one uses
> > 72 Gig hard drives and the old one has 36 Gig drives.
> > I am using 9 drives in Raid5 for data and 4 drives in Raid
> > 1+0 for logs on both machines. Both arrays have 15000 RPM
> > drives.
> >
> > Both machines have SQL Server 2000. My test database is
> > about 4 Gig and it is identical on both servers.
> >
> > I have a stress test script with a variety of operations
> > such as bulk insert, create clustered index, calculations,
> > etc.
> >
> > As you would expect, the new machine runs processor
> > intensive operations much faster. But the old machine runs
> > disk intensive operations faster than the new one. We had
> > expected at least comparable performance.
> >
> > Overall, the test runs in 39 minutes on the old machine
> > and 40 minutes on the new one (pretty close, I know, but
> > the new one should win by a larger margin).
> >
> > Is there anything special about running SQL Server 2000 on
> > Windows 2003 that we need to know?
> > Does the Compaq drive array have some limitation that the
> > Dell does not?
> >
> > Thanks
> > Dave G
> >
> >
>sql

Performance problems, SP 3 and SP 4

We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly a
little behind the current technology.
The main application is a Dairy records processing system that was converted
from mainframe COBOL to SQL Server. The data resides in 30 user databases,
with hundreds of tables, some of them with 10 - 15 million rows.
OLTP applications are a very small part of the system. The vast majority is
large Transact SQL procedures, many of them 5 - 10,000 lines long with 100's
of internal variables and multiple cursor driven processes. These procedure
s
are executed by ACCESS 2000 client applications. The ultimate output is
packets of very complex statistical-type paper, pdf, and text file reports
that are mailed or emailed to individual dairy farmers and agribusiness
consultants.
There are many hierarchical table relationships with cascading updates and
deletes and lots of smaller procedures inside update, delete, and
instead-of-update triggers. The entire system is basically batch driven and
there is NO WAY that it could ever be re-written in an OLTP environment.
That said, we started production in July of 2003 and had no problems
completing an entire days production between 7:00 a.m and 5:30 p.m. We were
able to downsize our operations staff and have automated nightly jobs and
backups that run unattended overnight.
In April of 2003 we installed SP 3 and things went to hell in a hand basket.
We had to extend operations till 11:00 p.m to finish the daily production.
Faced with having to hire and train a third shift operator, we reverted back
to SP 2 and all went well until we installed SP 4 3 weeks ago.
I have to admit that SP 4 was a big improvement over SP 3, but it still
extended our operations day by 3 hours. I again reverted back to SP 2.
I was told by MS technical support that each new version of SQL Server has a
differently tuned query optimizer and that our site with it's heavy reliance
on complex multi-table outer joins, cursors and procedural processing is the
problem. It appears that each new version is being tuned more for small OLT
P
transactions.
I was also told that large "traditional installations" have test-bed
systems that exactly duplicate production environments, and that each new
service pack is tested by a team of programmers who re-tune all SQL
statements to match the parameters of the new query optimizer.
We are a small shop with very tight budget, very small staff, and a large
application that took us 4 years to convert from mainframe COBOL. We cannot
afford to tear our procedures appart, rebuild them, and then re-validate the
very complex data, every time a new service pack comes out.
Short of buying new more powerful hardware (which we have budgeted for in
2007) is there anything that we can do to make SP 4 as fast and efficient as
SP 2?
I would appreciate hearing from anyone who has experienced performance
problems with SP 3 and SP 4, and what if anything you were able to do abouit
it.Not what you want to hear, but this may be one of those times where hardware
is actually the answer. SQL Server is very heavily dependent on RAM and the
disk subsystem (regardless of the service pack level). IMHO, the processor
has less to do with it than RAM and disk speed.
If you can't throw hardware at it, you should definitely run SQL Profiler
and determine what processes and queries are taking the longest and start
tuning those to see if you can get some performance boosts. You may be in a
situation where the 80/20 rule comes into play...80% of the time spent
running 20% of the procedures...
Are the Access clients doing pass-through queries, or is it all moving to
the desktop to process in teh Jet engine?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> a
> little behind the current technology.
> The main application is a Dairy records processing system that was
> converted
> from mainframe COBOL to SQL Server. The data resides in 30 user
> databases,
> with hundreds of tables, some of them with 10 - 15 million rows.
> OLTP applications are a very small part of the system. The vast majority
> is
> large Transact SQL procedures, many of them 5 - 10,000 lines long with
> 100's
> of internal variables and multiple cursor driven processes. These
> procedures
> are executed by ACCESS 2000 client applications. The ultimate output is
> packets of very complex statistical-type paper, pdf, and text file reports
> that are mailed or emailed to individual dairy farmers and agribusiness
> consultants.
> There are many hierarchical table relationships with cascading updates and
> deletes and lots of smaller procedures inside update, delete, and
> instead-of-update triggers. The entire system is basically batch driven
> and
> there is NO WAY that it could ever be re-written in an OLTP environment.
> That said, we started production in July of 2003 and had no problems
> completing an entire days production between 7:00 a.m and 5:30 p.m. We
> were
> able to downsize our operations staff and have automated nightly jobs and
> backups that run unattended overnight.
> In April of 2003 we installed SP 3 and things went to hell in a hand
> basket.
> We had to extend operations till 11:00 p.m to finish the daily production.
> Faced with having to hire and train a third shift operator, we reverted
> back
> to SP 2 and all went well until we installed SP 4 3 weeks ago.
> I have to admit that SP 4 was a big improvement over SP 3, but it still
> extended our operations day by 3 hours. I again reverted back to SP 2.
> I was told by MS technical support that each new version of SQL Server has
> a
> differently tuned query optimizer and that our site with it's heavy
> reliance
> on complex multi-table outer joins, cursors and procedural processing is
> the
> problem. It appears that each new version is being tuned more for small
> OLTP
> transactions.
> I was also told that large "traditional installations" have test-bed
> systems that exactly duplicate production environments, and that each new
> service pack is tested by a team of programmers who re-tune all SQL
> statements to match the parameters of the new query optimizer.
> We are a small shop with very tight budget, very small staff, and a large
> application that took us 4 years to convert from mainframe COBOL. We
> cannot
> afford to tear our procedures appart, rebuild them, and then re-validate
> the
> very complex data, every time a new service pack comes out.
> Short of buying new more powerful hardware (which we have budgeted for in
> 2007) is there anything that we can do to make SP 4 as fast and efficient
> as
> SP 2?
> I would appreciate hearing from anyone who has experienced performance
> problems with SP 3 and SP 4, and what if anything you were able to do
> abouit
> it.
>|||Thanks for the quick response. I will definitely try SQL Profiler to
identify the bottle-necks. FYI, our ACCESS apps use pass through queries an
d
linked tables.
"Kevin3NF" wrote:

> Not what you want to hear, but this may be one of those times where hardwa
re
> is actually the answer. SQL Server is very heavily dependent on RAM and t
he
> disk subsystem (regardless of the service pack level). IMHO, the processo
r
> has less to do with it than RAM and disk speed.
> If you can't throw hardware at it, you should definitely run SQL Profiler
> and determine what processes and queries are taking the longest and start
> tuning those to see if you can get some performance boosts. You may be in
a
> situation where the 80/20 rule comes into play...80% of the time spent
> running 20% of the procedures...
> Are the Access clients doing pass-through queries, or is it all moving to
> the desktop to process in teh Jet engine?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
> news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
>
>sql