Performance questions

We have a production database that sits on a 4 proc server with 4 GB of memory and SAN disk storage via fiber. There are some stored procedures that run and they take approximately 10 minutes to run. A developer has SQL Server installed on his local pc that has 1 2.5 GHz processor and 2 GB of memory and the stored procedures run in approximately 2 minutes. I have updated statistics and rebuilt indexes to no avail. He is questioning why it runs so much faster on his smaller pc compared to the production environment. I have monitored CPU, Memory, and Disk Queue Length and none of these performance counters look concerning to me while the stored procedures are running.

Can anyone out there give me some input on what I could check to figure out why we are experiencing this performance difference?

Thanks,

Corey

Do both databases have the same volume of data? You did not mention what speed and type of processors were in the server. If it is an older server, with say, 1GHz Pentium 3 based Xeon CPU's, you could easily see much slower query performance than on a developer's workstation, with a single faster CPU.

You should also look at the graphical execution plan for the query on the server, and see whether or not the plan is being parallelized or not. BTW, what version of SQL Server are you running?

|||

The data is the same between the two environments. His pc has a 2.5 GHz Intel processor and our server has 4 2.7 GHz Intel processors. We are running SQL Server 2000 SP3a. I will try to look at the queries but their are hundreds. Should the query plan be the same since I ran update statistics, rebuild indexes, and then the process so that I was comparing as each as close as possible.

|||

Just pick two or three commonly executed queries and run them in Query Analyzer with the graphical execution plan turned on and with SET STATISTICS IO on (just run that statement before you run the queries).

Do you see similar query plans and I/O statistics between the developer's workstation and your server?

Are we comparing a developer's workstation with no load with a production server with a full-work load?

If so, I would start looking at the production server to see if you see signs of CPU pressure, memory pressure, I/O bottlenecks, etc.

|||

I will try to get a couple of queries and compare them as suggested.

The developer's workstation has no load and the production server does have other work going on but this server does have plenty of resources left and it is a much larger box than the developer's workstation. I will go ahead and grab some perfmon counters to verify this too.

sql

Performance question : One single MDF file ? Or multiple MDF file ?

Dear,
My friend told me : using multiple MDF files is much better than use one
single MDF file even within the same hard disk as SQL Server's multi thread
feature can handle multiple files concurrently.
My argument : Within the same hard disk, we should use one single MDF file,
but we can split the MDF file into small files if there are different
physical hard disks.
How do you feel ?
In order to have better performance, we should use 1 or multiple MDF files
(I mean within the same hard disk) ?
Any Microsoft documents supported ?
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
This is an urban legend:
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx.
Also, there should only be one MDF per database, which is the primary data
file. The file extention NDF should be used for the secondary

> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
Yes, it's best to distribute the workload over as many disks as possible.
For large data files, you might want to create multiple files even if on a
single physical disk for manageability.
Hope this helps.
Dan Guzman
SQL Server MVP
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>
|||I would say that if you aren't seeing I/O issues (disk queue length, avg ms
per read/write, etc) with a single file then you can increase to more than
one file. Be aware however that when you do hit that break-over point and
have too many files, performance can decrease dramatically and quickly due
to disk head thrashing.
Assuming you have well-indexed structures, I would recommend first simply
moving all non-clustered indexes off to a separate file and see how that
works. You could also consider moving a very large table off to it's own
filegroup. But if you have that you really should be on multiple disks.
Also, what is your tempdb and transaction log file placement? If everything
is one same drive, separating those is job number 1.
BTW, you do have a baseline for your performance to compare to, right? :-))
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>
|||See also
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Linchi
"cpchan" wrote:

> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>
|||noted and thanks.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:45ECE387-9152-4E2A-8B07-31572C29152B@.microsoft.com...
> See also
>
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-
should-i-create-for-a-user-database.aspx[vbcol=seagreen]
> Linchi
> "cpchan" wrote:
thread[vbcol=seagreen]
file,[vbcol=seagreen]
files[vbcol=seagreen]
|||noted and thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64cso0q4stec@.corp.supernews.com...
> I would say that if you aren't seeing I/O issues (disk queue length, avg
ms
> per read/write, etc) with a single file then you can increase to more than
> one file. Be aware however that when you do hit that break-over point and
> have too many files, performance can decrease dramatically and quickly due
> to disk head thrashing.
> Assuming you have well-indexed structures, I would recommend first simply
> moving all non-clustered indexes off to a separate file and see how that
> works. You could also consider moving a very large table off to it's own
> filegroup. But if you have that you really should be on multiple disks.
> Also, what is your tempdb and transaction log file placement? If
everything
> is one same drive, separating those is job number 1.
> BTW, you do have a baseline for your performance to compare to, right?
:-))[vbcol=seagreen]
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
files
>
|||noted and thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5764EB3B-20BA-4663-8ADF-F7BDE6757654@.microsoft.com...
> This is an urban legend:
>
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-dis
cussed.aspx.[vbcol=seagreen]
> Also, there should only be one MDF per database, which is the primary data
> file. The file extention NDF should be used for the secondary
>
> Yes, it's best to distribute the workload over as many disks as possible.
> For large data files, you might want to create multiple files even if on a
> single physical disk for manageability.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
files
>

Performance question : One single MDF file ? Or multiple MDF file ?

Dear,
My friend told me : using multiple MDF files is much better than use one
single MDF file even within the same hard disk as SQL Server's multi thread
feature can handle multiple files concurrently.
My argument : Within the same hard disk, we should use one single MDF file,
but we can split the MDF file into small files if there are different
physical hard disks.
How do you feel ?
In order to have better performance, we should use 1 or multiple MDF files
(I mean within the same hard disk) ?
Any Microsoft documents supported ?> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
This is an urban legend:
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx.
Also, there should only be one MDF per database, which is the primary data
file. The file extention NDF should be used for the secondary
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
Yes, it's best to distribute the workload over as many disks as possible.
For large data files, you might want to create multiple files even if on a
single physical disk for manageability.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||I would say that if you aren't seeing I/O issues (disk queue length, avg ms
per read/write, etc) with a single file then you can increase to more than
one file. Be aware however that when you do hit that break-over point and
have too many files, performance can decrease dramatically and quickly due
to disk head thrashing.
Assuming you have well-indexed structures, I would recommend first simply
moving all non-clustered indexes off to a separate file and see how that
works. You could also consider moving a very large table off to it's own
filegroup. But if you have that you really should be on multiple disks.
Also, what is your tempdb and transaction log file placement? If everything
is one same drive, separating those is job number 1.
BTW, you do have a baseline for your performance to compare to, right' :-))
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||See also
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Linchi
"cpchan" wrote:
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||noted and thanks.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:45ECE387-9152-4E2A-8B07-31572C29152B@.microsoft.com...
> See also
>
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-
should-i-create-for-a-user-database.aspx
> Linchi
> "cpchan" wrote:
> > Dear,
> >
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
thread
> > feature can handle multiple files concurrently.
> >
> > My argument : Within the same hard disk, we should use one single MDF
file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> >
> > How do you feel ?
> >
> > In order to have better performance, we should use 1 or multiple MDF
files
> > (I mean within the same hard disk) ?
> >
> > Any Microsoft documents supported ?
> >
> >
> >
> >|||noted and thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5764EB3B-20BA-4663-8ADF-F7BDE6757654@.microsoft.com...
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
> > thread
> > feature can handle multiple files concurrently.
> This is an urban legend:
>
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-dis
cussed.aspx.
> Also, there should only be one MDF per database, which is the primary data
> file. The file extention NDF should be used for the secondary
> > My argument : Within the same hard disk, we should use one single MDF
> > file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> Yes, it's best to distribute the workload over as many disks as possible.
> For large data files, you might want to create multiple files even if on a
> single physical disk for manageability.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
> > Dear,
> >
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
> > thread
> > feature can handle multiple files concurrently.
> >
> > My argument : Within the same hard disk, we should use one single MDF
> > file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> >
> > How do you feel ?
> >
> > In order to have better performance, we should use 1 or multiple MDF
files
> > (I mean within the same hard disk) ?
> >
> > Any Microsoft documents supported ?
> >
> >
> >
>|||noted and thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64cso0q4stec@.corp.supernews.com...
> I would say that if you aren't seeing I/O issues (disk queue length, avg
ms
> per read/write, etc) with a single file then you can increase to more than
> one file. Be aware however that when you do hit that break-over point and
> have too many files, performance can decrease dramatically and quickly due
> to disk head thrashing.
> Assuming you have well-indexed structures, I would recommend first simply
> moving all non-clustered indexes off to a separate file and see how that
> works. You could also consider moving a very large table off to it's own
> filegroup. But if you have that you really should be on multiple disks.
> Also, what is your tempdb and transaction log file placement? If
everything
> is one same drive, separating those is job number 1.
> BTW, you do have a baseline for your performance to compare to, right'
:-))
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
> > Dear,
> >
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
> > thread
> > feature can handle multiple files concurrently.
> >
> > My argument : Within the same hard disk, we should use one single MDF
> > file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> >
> > How do you feel ?
> >
> > In order to have better performance, we should use 1 or multiple MDF
files
> > (I mean within the same hard disk) ?
> >
> > Any Microsoft documents supported ?
> >
> >
> >
>sql

Performance question : One single MDF file ? Or multiple MDF file ?

Dear,
My friend told me : using multiple MDF files is much better than use one
single MDF file even within the same hard disk as SQL Server's multi thread
feature can handle multiple files concurrently.
My argument : Within the same hard disk, we should use one single MDF file,
but we can split the MDF file into small files if there are different
physical hard disks.
How do you feel ?
In order to have better performance, we should use 1 or multiple MDF files
(I mean within the same hard disk) ?
Any Microsoft documents supported ?> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
This is an urban legend:
http://blogs.msdn.com/psssql/archiv...d
.aspx.
Also, there should only be one MDF per database, which is the primary data
file. The file extention NDF should be used for the secondary

> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
Yes, it's best to distribute the workload over as many disks as possible.
For large data files, you might want to create multiple files even if on a
single physical disk for manageability.
Hope this helps.
Dan Guzman
SQL Server MVP
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||I would say that if you aren't seeing I/O issues (disk queue length, avg ms
per read/write, etc) with a single file then you can increase to more than
one file. Be aware however that when you do hit that break-over point and
have too many files, performance can decrease dramatically and quickly due
to disk head thrashing.
Assuming you have well-indexed structures, I would recommend first simply
moving all non-clustered indexes off to a separate file and see how that
works. You could also consider moving a very large table off to it's own
filegroup. But if you have that you really should be on multiple disks.
Also, what is your tempdb and transaction log file placement? If everything
is one same drive, separating those is job number 1.
BTW, you do have a baseline for your performance to compare to, right' :-))
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||noted and thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64cso0q4stec@.corp.supernews.com...
> I would say that if you aren't seeing I/O issues (disk queue length, avg
ms
> per read/write, etc) with a single file then you can increase to more than
> one file. Be aware however that when you do hit that break-over point and
> have too many files, performance can decrease dramatically and quickly due
> to disk head thrashing.
> Assuming you have well-indexed structures, I would recommend first simply
> moving all non-clustered indexes off to a separate file and see how that
> works. You could also consider moving a very large table off to it's own
> filegroup. But if you have that you really should be on multiple disks.
> Also, what is your tempdb and transaction log file placement? If
everything
> is one same drive, separating those is job number 1.
> BTW, you do have a baseline for your performance to compare to, right'
:-))
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
files[vbcol=seagreen]
>|||noted and thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5764EB3B-20BA-4663-8ADF-F7BDE6757654@.microsoft.com...
> This is an urban legend:
>
http://blogs.msdn.com/psssql/archiv...ban-legends-dis
cussed.aspx.
> Also, there should only be one MDF per database, which is the primary data
> file. The file extention NDF should be used for the secondary
>
> Yes, it's best to distribute the workload over as many disks as possible.
> For large data files, you might want to create multiple files even if on a
> single physical disk for manageability.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
files[vbcol=seagreen]
>

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

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 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.
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 and
linked tables.
"Kevin3NF" wrote:

> 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...
>
>

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 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.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 and
linked tables.
"Kevin3NF" wrote:
> 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.
> >
>
>

Performance problem, lots of disk activity, running out of memory

Fellas!!
This is a very complicated one and it took me a few days to figure out
exactly what's going on, but here's the final story:
I have a production environment running on .NET with a SQL Server
(2000, SP3). The SQL Server is on a dedicated Proliant computer with
2GB RAM (the actual SQLServer.exe process has dynamic memory
assignment and can reach up to 1.6GB RAM). Nothing else is running on
that specific computer.
Once the SQLServer is started, it hits 300MB RAM (the minimum that was
set in the configuration of the server - remember, it is dynamically
aquired).
Then there is a .NET program that requests just about all the data the
SQL Server contains (apart from a single table that contains roughly
1.6 million rows and another table that contains about 10000 rows
which are all of type IMAGE).
Once all the data is retrieved, the RAM is at about 400MB. From there
on, every update I make to the data on the server causes the RAM to go
up by a bit (that updates are done in a Transaction which of course is
committed at the end). It seems that BLOB updates are the major
problem in all of this. For some reason, uploading a blob of size 9MB
causes the RAM to go up by roughly 20MB and after commit it gose down
10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
process hits its upper limit (1.6GB) and at this point it starts
slowing down.
Some performance checks showed me the SQLServer has a lot of disk
activity, it seems it is reading and writing pages of data from/to the
HD all the time (which causes the queries to be much much much
slower).
We have a development environment running the exact same code (it is
the exact same in everything, except for the amount of data stored in
the DB). This does not happen there at all.
I have a few questions:
1. Why is the RAM going up after BLOB updates?
2. Why is the RAM going up at all?
3. How can I tell the DB which tables should remain in the RAM at all
time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
do the job.
It does not seem to have anything to do with the .NET code.
Thank you very much,
M Yamo.Mee
my 2 cents
1)
I you store image data , the actual data is not stored on the data
pages,instead it stores a 16 -byte pointer in the data row that indicates
where the actual data can be found.
2)

quote:

---

> We have a development environment running the exact same code (it is
> the exact same in everything, except for the amount of data stored in
> the DB). This does not happen there at all.

---

I think you have answered on your own question.
3)
DBCC PINTABLE is best used to keep small, frequently referenced tables in
memory. The pages for the small table are read into memory one time, then
all future references to their data do not require a disk read.
"Mee Yamo" <meeyamo@.hotmail.com> wrote in message
news:b5f0434e.0402010522.625a7a4d@.posting.google.com...

quote:

---

> Fellas!!
> This is a very complicated one and it took me a few days to figure out
> exactly what's going on, but here's the final story:
> I have a production environment running on .NET with a SQL Server
> (2000, SP3). The SQL Server is on a dedicated Proliant computer with
> 2GB RAM (the actual SQLServer.exe process has dynamic memory
> assignment and can reach up to 1.6GB RAM). Nothing else is running on
> that specific computer.
> Once the SQLServer is started, it hits 300MB RAM (the minimum that was
> set in the configuration of the server - remember, it is dynamically
> aquired).
> Then there is a .NET program that requests just about all the data the
> SQL Server contains (apart from a single table that contains roughly
> 1.6 million rows and another table that contains about 10000 rows
> which are all of type IMAGE).
> Once all the data is retrieved, the RAM is at about 400MB. From there
> on, every update I make to the data on the server causes the RAM to go
> up by a bit (that updates are done in a Transaction which of course is
> committed at the end). It seems that BLOB updates are the major
> problem in all of this. For some reason, uploading a blob of size 9MB
> causes the RAM to go up by roughly 20MB and after commit it gose down
> 10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
> process hits its upper limit (1.6GB) and at this point it starts
> slowing down.
> Some performance checks showed me the SQLServer has a lot of disk
> activity, it seems it is reading and writing pages of data from/to the
> HD all the time (which causes the queries to be much much much
> slower).
> We have a development environment running the exact same code (it is
> the exact same in everything, except for the amount of data stored in
> the DB). This does not happen there at all.
> I have a few questions:
> 1. Why is the RAM going up after BLOB updates?
> 2. Why is the RAM going up at all?
> 3. How can I tell the DB which tables should remain in the RAM at all
> time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
> do the job.
> It does not seem to have anything to do with the .NET code.
> Thank you very much,
> M Yamo.

---

|||Mee Yamo (meeyamo@.hotmail.com) writes:

quote:

---

> I have a production environment running on .NET with a SQL Server
> (2000, SP3). The SQL Server is on a dedicated Proliant computer with
> 2GB RAM (the actual SQLServer.exe process has dynamic memory
> assignment and can reach up to 1.6GB RAM). Nothing else is running on
> that specific computer.
> ...
> Once all the data is retrieved, the RAM is at about 400MB. From there
> on, every update I make to the data on the server causes the RAM to go
> up by a bit (that updates are done in a Transaction which of course is
> committed at the end). It seems that BLOB updates are the major
> problem in all of this. For some reason, uploading a blob of size 9MB
> causes the RAM to go up by roughly 20MB and after commit it gose down
> 10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
> process hits its upper limit (1.6GB) and at this point it starts
> slowing down.
>...
> I have a few questions:
> 1. Why is the RAM going up after BLOB updates?
> 2. Why is the RAM going up at all?
> 3. How can I tell the DB which tables should remain in the RAM at all
> time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
> do the job.

---

I guess since you post, you have a problem with your application. However,
your posting gives little information of what that problem might be.
The default behaviour of SQL Server is go grab as much as memory as
possible. The more data, SQL Server have in cache, the less it has to
read from disks. If there are competing applications on the machine,
this can be a problem if SQL Server does not yield memory fast enough.
But you say that this is dedicated to SQL Server, so if SQL Server gets
some more memory that is no cause for alarm.
DBCC PINTABLE is something you have little reason to play with. If you have
data that you access rarely, but when you access it, you need it quick,
then PINTABLE may be an option. For instance, the Managing Director wants a
report the first day of each months, and he cannot wait those two minutes
it would take to get the data from disk. But using DBCC PINTABLE is likely
to mean that you sacrifice overall performance.

quote:

---

> We have a development environment running the exact same code (it is
> the exact same in everything, except for the amount of data stored in
> the DB). This does not happen there at all.

---

The amount of data is surely the clue here. Assuming that you really
have performance problems, they are only likely to show when you have
a full-size database.
Since I don't know what your real problems are, it is diffiult to
give some relevant advice, but a good starting point is to review
indexing, if you have not done this already.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,
Thanks for trying to help.
Well the problem still goes on. The SQLServer process has hit its max
RAM allowance and started to slow down again (DISK ACTIVITY). I
restarted the SQLServer service and that solved it.
Why is it memory hogging? Every update seems to leave a residue on the
process's memory consumption.
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9482AB02DAFF4Yazorman@.127.0.0.1>..
.

quote:

---

> Mee Yamo (meeyamo@.hotmail.com) writes:
> I guess since you post, you have a problem with your application. However,
> your posting gives little information of what that problem might be.
> The default behaviour of SQL Server is go grab as much as memory as
> possible. The more data, SQL Server have in cache, the less it has to
> read from disks. If there are competing applications on the machine,
> this can be a problem if SQL Server does not yield memory fast enough.
> But you say that this is dedicated to SQL Server, so if SQL Server gets
> some more memory that is no cause for alarm.
> DBCC PINTABLE is something you have little reason to play with. If you hav
e
> data that you access rarely, but when you access it, you need it quick,
> then PINTABLE may be an option. For instance, the Managing Director wants
a
> report the first day of each months, and he cannot wait those two minutes
> it would take to get the data from disk. But using DBCC PINTABLE is likel
y
> to mean that you sacrifice overall performance.
>
> The amount of data is surely the clue here. Assuming that you really
> have performance problems, they are only likely to show when you have
> a full-size database.
> Since I don't know what your real problems are, it is diffiult to
> give some relevant advice, but a good starting point is to review
> indexing, if you have not done this already.

---

|||Mee Yamo (meeyamo@.hotmail.com) writes:

quote:

---

> Well the problem still goes on. The SQLServer process has hit its max
> RAM allowance and started to slow down again (DISK ACTIVITY). I
> restarted the SQLServer service and that solved it.
> Why is it memory hogging? Every update seems to leave a residue on the
> process's memory consumption.

---

As I tried to explain, this by design. You should not worry about it.
If you have real performance problem with long response times etc, the
people in these newsgroups can assist, but you need to provide more
information.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Performance problem, lots of disk activity, running out of memory

Fellas!!

This is a very complicated one and it took me a few days to figure out
exactly what's going on, but here's the final story:

I have a production environment running on .NET with a SQL Server
(2000, SP3). The SQL Server is on a dedicated Proliant computer with
2GB RAM (the actual SQLServer.exe process has dynamic memory
assignment and can reach up to 1.6GB RAM). Nothing else is running on
that specific computer.

Once the SQLServer is started, it hits 300MB RAM (the minimum that was
set in the configuration of the server - remember, it is dynamically
aquired).

Then there is a .NET program that requests just about all the data the
SQL Server contains (apart from a single table that contains roughly
1.6 million rows and another table that contains about 10000 rows
which are all of type IMAGE).

Once all the data is retrieved, the RAM is at about 400MB. From there
on, every update I make to the data on the server causes the RAM to go
up by a bit (that updates are done in a Transaction which of course is
committed at the end). It seems that BLOB updates are the major
problem in all of this. For some reason, uploading a blob of size 9MB
causes the RAM to go up by roughly 20MB and after commit it gose down
10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
process hits its upper limit (1.6GB) and at this point it starts
slowing down.

Some performance checks showed me the SQLServer has a lot of disk
activity, it seems it is reading and writing pages of data from/to the
HD all the time (which causes the queries to be much much much
slower).

We have a development environment running the exact same code (it is
the exact same in everything, except for the amount of data stored in
the DB). This does not happen there at all.

I have a few questions:
1. Why is the RAM going up after BLOB updates?
2. Why is the RAM going up at all?
3. How can I tell the DB which tables should remain in the RAM at all
time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
do the job.

It does not seem to have anything to do with the .NET code.

Thank you very much,

M Yamo.Mee
my 2 cents
1)
I you store image data , the actual data is not stored on the data
pages,instead it stores a 16 -byte pointer in the data row that indicates
where the actual data can be found.
2)
> We have a development environment running the exact same code (it is
> the exact same in everything, except for the amount of data stored in
> the DB). This does not happen there at all.
I think you have answered on your own question.
3)
DBCC PINTABLE is best used to keep small, frequently referenced tables in
memory. The pages for the small table are read into memory one time, then
all future references to their data do not require a disk read.

"Mee Yamo" <meeyamo@.hotmail.com> wrote in message
news:b5f0434e.0402010522.625a7a4d@.posting.google.c om...
> Fellas!!
> This is a very complicated one and it took me a few days to figure out
> exactly what's going on, but here's the final story:
> I have a production environment running on .NET with a SQL Server
> (2000, SP3). The SQL Server is on a dedicated Proliant computer with
> 2GB RAM (the actual SQLServer.exe process has dynamic memory
> assignment and can reach up to 1.6GB RAM). Nothing else is running on
> that specific computer.
> Once the SQLServer is started, it hits 300MB RAM (the minimum that was
> set in the configuration of the server - remember, it is dynamically
> aquired).
> Then there is a .NET program that requests just about all the data the
> SQL Server contains (apart from a single table that contains roughly
> 1.6 million rows and another table that contains about 10000 rows
> which are all of type IMAGE).
> Once all the data is retrieved, the RAM is at about 400MB. From there
> on, every update I make to the data on the server causes the RAM to go
> up by a bit (that updates are done in a Transaction which of course is
> committed at the end). It seems that BLOB updates are the major
> problem in all of this. For some reason, uploading a blob of size 9MB
> causes the RAM to go up by roughly 20MB and after commit it gose down
> 10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
> process hits its upper limit (1.6GB) and at this point it starts
> slowing down.
> Some performance checks showed me the SQLServer has a lot of disk
> activity, it seems it is reading and writing pages of data from/to the
> HD all the time (which causes the queries to be much much much
> slower).
> We have a development environment running the exact same code (it is
> the exact same in everything, except for the amount of data stored in
> the DB). This does not happen there at all.
> I have a few questions:
> 1. Why is the RAM going up after BLOB updates?
> 2. Why is the RAM going up at all?
> 3. How can I tell the DB which tables should remain in the RAM at all
> time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
> do the job.
> It does not seem to have anything to do with the .NET code.
> Thank you very much,
> M Yamo.|||Mee Yamo (meeyamo@.hotmail.com) writes:
> I have a production environment running on .NET with a SQL Server
> (2000, SP3). The SQL Server is on a dedicated Proliant computer with
> 2GB RAM (the actual SQLServer.exe process has dynamic memory
> assignment and can reach up to 1.6GB RAM). Nothing else is running on
> that specific computer.
> ...
> Once all the data is retrieved, the RAM is at about 400MB. From there
> on, every update I make to the data on the server causes the RAM to go
> up by a bit (that updates are done in a Transaction which of course is
> committed at the end). It seems that BLOB updates are the major
> problem in all of this. For some reason, uploading a blob of size 9MB
> causes the RAM to go up by roughly 20MB and after commit it gose down
> 10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
> process hits its upper limit (1.6GB) and at this point it starts
> slowing down.
>...
> I have a few questions:
> 1. Why is the RAM going up after BLOB updates?
> 2. Why is the RAM going up at all?
> 3. How can I tell the DB which tables should remain in the RAM at all
> time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
> do the job.

I guess since you post, you have a problem with your application. However,
your posting gives little information of what that problem might be.

The default behaviour of SQL Server is go grab as much as memory as
possible. The more data, SQL Server have in cache, the less it has to
read from disks. If there are competing applications on the machine,
this can be a problem if SQL Server does not yield memory fast enough.
But you say that this is dedicated to SQL Server, so if SQL Server gets
some more memory that is no cause for alarm.

DBCC PINTABLE is something you have little reason to play with. If you have
data that you access rarely, but when you access it, you need it quick,
then PINTABLE may be an option. For instance, the Managing Director wants a
report the first day of each months, and he cannot wait those two minutes
it would take to get the data from disk. But using DBCC PINTABLE is likely
to mean that you sacrifice overall performance.

> We have a development environment running the exact same code (it is
> the exact same in everything, except for the amount of data stored in
> the DB). This does not happen there at all.

The amount of data is surely the clue here. Assuming that you really
have performance problems, they are only likely to show when you have
a full-size database.

Since I don't know what your real problems are, it is diffiult to
give some relevant advice, but a good starting point is to review
indexing, if you have not done this already.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,

Thanks for trying to help.

Well the problem still goes on. The SQLServer process has hit its max
RAM allowance and started to slow down again (DISK ACTIVITY). I
restarted the SQLServer service and that solved it.

Why is it memory hogging? Every update seems to leave a residue on the
process's memory consumption.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9482AB02DAFF4Yazorman@.127.0.0.1>...
> Mee Yamo (meeyamo@.hotmail.com) writes:
> > I have a production environment running on .NET with a SQL Server
> > (2000, SP3). The SQL Server is on a dedicated Proliant computer with
> > 2GB RAM (the actual SQLServer.exe process has dynamic memory
> > assignment and can reach up to 1.6GB RAM). Nothing else is running on
> > that specific computer.
> > ...
> > Once all the data is retrieved, the RAM is at about 400MB. From there
> > on, every update I make to the data on the server causes the RAM to go
> > up by a bit (that updates are done in a Transaction which of course is
> > committed at the end). It seems that BLOB updates are the major
> > problem in all of this. For some reason, uploading a blob of size 9MB
> > causes the RAM to go up by roughly 20MB and after commit it gose down
> > 10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
> > process hits its upper limit (1.6GB) and at this point it starts
> > slowing down.
> >...
> > I have a few questions:
> > 1. Why is the RAM going up after BLOB updates?
> > 2. Why is the RAM going up at all?
> > 3. How can I tell the DB which tables should remain in the RAM at all
> > time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
> > do the job.
> I guess since you post, you have a problem with your application. However,
> your posting gives little information of what that problem might be.
> The default behaviour of SQL Server is go grab as much as memory as
> possible. The more data, SQL Server have in cache, the less it has to
> read from disks. If there are competing applications on the machine,
> this can be a problem if SQL Server does not yield memory fast enough.
> But you say that this is dedicated to SQL Server, so if SQL Server gets
> some more memory that is no cause for alarm.
> DBCC PINTABLE is something you have little reason to play with. If you have
> data that you access rarely, but when you access it, you need it quick,
> then PINTABLE may be an option. For instance, the Managing Director wants a
> report the first day of each months, and he cannot wait those two minutes
> it would take to get the data from disk. But using DBCC PINTABLE is likely
> to mean that you sacrifice overall performance.
> > We have a development environment running the exact same code (it is
> > the exact same in everything, except for the amount of data stored in
> > the DB). This does not happen there at all.
> The amount of data is surely the clue here. Assuming that you really
> have performance problems, they are only likely to show when you have
> a full-size database.
> Since I don't know what your real problems are, it is diffiult to
> give some relevant advice, but a good starting point is to review
> indexing, if you have not done this already.|||Mee Yamo (meeyamo@.hotmail.com) writes:
> Well the problem still goes on. The SQLServer process has hit its max
> RAM allowance and started to slow down again (DISK ACTIVITY). I
> restarted the SQLServer service and that solved it.
> Why is it memory hogging? Every update seems to leave a residue on the
> process's memory consumption.

As I tried to explain, this by design. You should not worry about it.

If you have real performance problem with long response times etc, the
people in these newsgroups can assist, but you need to provide more
information.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Performance problem, lots of disk activity, running out of memory

Fellas!!
This is a very complicated one and it took me a few days to figure out
exactly what's going on, but here's the final story:
I have a production environment running on .NET with a SQL Server
(2000, SP3). The SQL Server is on a dedicated Proliant computer with
2GB RAM (the actual SQLServer.exe process has dynamic memory
assignment and can reach up to 1.6GB RAM). Nothing else is running on
that specific computer.
Once the SQLServer is started, it hits 300MB RAM (the minimum that was
set in the configuration of the server - remember, it is dynamically
aquired).
Then there is a .NET program that requests just about all the data the
SQL Server contains (apart from a single table that contains roughly
1.6 million rows and another table that contains about 10000 rows
which are all of type IMAGE).
Once all the data is retrieved, the RAM is at about 400MB. From there
on, every update I make to the data on the server causes the RAM to go
up by a bit (that updates are done in a Transaction which of course is
committed at the end). It seems that BLOB updates are the major
problem in all of this. For some reason, uploading a blob of size 9MB
causes the RAM to go up by roughly 20MB and after commit it gose down
10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
process hits its upper limit (1.6GB) and at this point it starts
slowing down.
Some performance checks showed me the SQLServer has a lot of disk
activity, it seems it is reading and writing pages of data from/to the
HD all the time (which causes the queries to be much much much
slower).
We have a development environment running the exact same code (it is
the exact same in everything, except for the amount of data stored in
the DB). This does not happen there at all.
I have a few questions:
1. Why is the RAM going up after BLOB updates?
2. Why is the RAM going up at all?
3. How can I tell the DB which tables should remain in the RAM at all
time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
do the job.
It does not seem to have anything to do with the .NET code.
Thank you very much,
M Yamo.Mee
my 2 cents
1)
I you store image data , the actual data is not stored on the data
pages,instead it stores a 16 -byte pointer in the data row that indicates
where the actual data can be found.
2)
> We have a development environment running the exact same code (it is
> the exact same in everything, except for the amount of data stored in
> the DB). This does not happen there at all.
I think you have answered on your own question.
3)
DBCC PINTABLE is best used to keep small, frequently referenced tables in
memory. The pages for the small table are read into memory one time, then
all future references to their data do not require a disk read.
"Mee Yamo" <meeyamo@.hotmail.com> wrote in message
news:b5f0434e.0402010522.625a7a4d@.posting.google.com...
> Fellas!!
> This is a very complicated one and it took me a few days to figure out
> exactly what's going on, but here's the final story:
> I have a production environment running on .NET with a SQL Server
> (2000, SP3). The SQL Server is on a dedicated Proliant computer with
> 2GB RAM (the actual SQLServer.exe process has dynamic memory
> assignment and can reach up to 1.6GB RAM). Nothing else is running on
> that specific computer.
> Once the SQLServer is started, it hits 300MB RAM (the minimum that was
> set in the configuration of the server - remember, it is dynamically
> aquired).
> Then there is a .NET program that requests just about all the data the
> SQL Server contains (apart from a single table that contains roughly
> 1.6 million rows and another table that contains about 10000 rows
> which are all of type IMAGE).
> Once all the data is retrieved, the RAM is at about 400MB. From there
> on, every update I make to the data on the server causes the RAM to go
> up by a bit (that updates are done in a Transaction which of course is
> committed at the end). It seems that BLOB updates are the major
> problem in all of this. For some reason, uploading a blob of size 9MB
> causes the RAM to go up by roughly 20MB and after commit it gose down
> 10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
> process hits its upper limit (1.6GB) and at this point it starts
> slowing down.
> Some performance checks showed me the SQLServer has a lot of disk
> activity, it seems it is reading and writing pages of data from/to the
> HD all the time (which causes the queries to be much much much
> slower).
> We have a development environment running the exact same code (it is
> the exact same in everything, except for the amount of data stored in
> the DB). This does not happen there at all.
> I have a few questions:
> 1. Why is the RAM going up after BLOB updates?
> 2. Why is the RAM going up at all?
> 3. How can I tell the DB which tables should remain in the RAM at all
> time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
> do the job.
> It does not seem to have anything to do with the .NET code.
> Thank you very much,
> M Yamo.|||Mee Yamo (meeyamo@.hotmail.com) writes:
> I have a production environment running on .NET with a SQL Server
> (2000, SP3). The SQL Server is on a dedicated Proliant computer with
> 2GB RAM (the actual SQLServer.exe process has dynamic memory
> assignment and can reach up to 1.6GB RAM). Nothing else is running on
> that specific computer.
> ...
> Once all the data is retrieved, the RAM is at about 400MB. From there
> on, every update I make to the data on the server causes the RAM to go
> up by a bit (that updates are done in a Transaction which of course is
> committed at the end). It seems that BLOB updates are the major
> problem in all of this. For some reason, uploading a blob of size 9MB
> causes the RAM to go up by roughly 20MB and after commit it gose down
> 10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
> process hits its upper limit (1.6GB) and at this point it starts
> slowing down.
>...
> I have a few questions:
> 1. Why is the RAM going up after BLOB updates?
> 2. Why is the RAM going up at all?
> 3. How can I tell the DB which tables should remain in the RAM at all
> time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
> do the job.
I guess since you post, you have a problem with your application. However,
your posting gives little information of what that problem might be.
The default behaviour of SQL Server is go grab as much as memory as
possible. The more data, SQL Server have in cache, the less it has to
read from disks. If there are competing applications on the machine,
this can be a problem if SQL Server does not yield memory fast enough.
But you say that this is dedicated to SQL Server, so if SQL Server gets
some more memory that is no cause for alarm.
DBCC PINTABLE is something you have little reason to play with. If you have
data that you access rarely, but when you access it, you need it quick,
then PINTABLE may be an option. For instance, the Managing Director wants a
report the first day of each months, and he cannot wait those two minutes
it would take to get the data from disk. But using DBCC PINTABLE is likely
to mean that you sacrifice overall performance.
> We have a development environment running the exact same code (it is
> the exact same in everything, except for the amount of data stored in
> the DB). This does not happen there at all.
The amount of data is surely the clue here. Assuming that you really
have performance problems, they are only likely to show when you have
a full-size database.
Since I don't know what your real problems are, it is diffiult to
give some relevant advice, but a good starting point is to review
indexing, if you have not done this already.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi,
Thanks for trying to help.
Well the problem still goes on. The SQLServer process has hit its max
RAM allowance and started to slow down again (DISK ACTIVITY). I
restarted the SQLServer service and that solved it.
Why is it memory hogging? Every update seems to leave a residue on the
process's memory consumption.
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9482AB02DAFF4Yazorman@.127.0.0.1>...
> Mee Yamo (meeyamo@.hotmail.com) writes:
> > I have a production environment running on .NET with a SQL Server
> > (2000, SP3). The SQL Server is on a dedicated Proliant computer with
> > 2GB RAM (the actual SQLServer.exe process has dynamic memory
> > assignment and can reach up to 1.6GB RAM). Nothing else is running on
> > that specific computer.
> > ...
> > Once all the data is retrieved, the RAM is at about 400MB. From there
> > on, every update I make to the data on the server causes the RAM to go
> > up by a bit (that updates are done in a Transaction which of course is
> > committed at the end). It seems that BLOB updates are the major
> > problem in all of this. For some reason, uploading a blob of size 9MB
> > causes the RAM to go up by roughly 20MB and after commit it gose down
> > 10MB (total gain of roughly 10MB RAM). Eventually the SQLServer
> > process hits its upper limit (1.6GB) and at this point it starts
> > slowing down.
> >...
> > I have a few questions:
> > 1. Why is the RAM going up after BLOB updates?
> > 2. Why is the RAM going up at all?
> > 3. How can I tell the DB which tables should remain in the RAM at all
> > time (never swapped back to the HD?) - DBCC PINTABLE does not seem to
> > do the job.
> I guess since you post, you have a problem with your application. However,
> your posting gives little information of what that problem might be.
> The default behaviour of SQL Server is go grab as much as memory as
> possible. The more data, SQL Server have in cache, the less it has to
> read from disks. If there are competing applications on the machine,
> this can be a problem if SQL Server does not yield memory fast enough.
> But you say that this is dedicated to SQL Server, so if SQL Server gets
> some more memory that is no cause for alarm.
> DBCC PINTABLE is something you have little reason to play with. If you have
> data that you access rarely, but when you access it, you need it quick,
> then PINTABLE may be an option. For instance, the Managing Director wants a
> report the first day of each months, and he cannot wait those two minutes
> it would take to get the data from disk. But using DBCC PINTABLE is likely
> to mean that you sacrifice overall performance.
> > We have a development environment running the exact same code (it is
> > the exact same in everything, except for the amount of data stored in
> > the DB). This does not happen there at all.
> The amount of data is surely the clue here. Assuming that you really
> have performance problems, they are only likely to show when you have
> a full-size database.
> Since I don't know what your real problems are, it is diffiult to
> give some relevant advice, but a good starting point is to review
> indexing, if you have not done this already.|||Mee Yamo (meeyamo@.hotmail.com) writes:
> Well the problem still goes on. The SQLServer process has hit its max
> RAM allowance and started to slow down again (DISK ACTIVITY). I
> restarted the SQLServer service and that solved it.
> Why is it memory hogging? Every update seems to leave a residue on the
> process's memory consumption.
As I tried to explain, this by design. You should not worry about it.
If you have real performance problem with long response times etc, the
people in these newsgroups can assist, but you need to provide more
information.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Performance problem

After a disk failure ( disk was in raid 1 configuration ) SQL Server
start to work very slow.
SqlServer start to raise following event
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 7/3/2006
Time: 8:54:12 AM
User: N/A
Computer: SRVCA2
Description:
17052 :
This SQL Server has been optimized for 8 concurrent queries. This limit
has been exceeded by 19 queries and performance may be adversely
affected.
SQL Server worked fine before this. I replaced hard disk and rebuild of
configuration was sucesfull. In SQL events i saw that SQL Server has
performed recovery of databases.
I have SQL Server Personal edition installed.
Can someone help me with this one please.The verion of the db engine shipped with the Personal Edition has an inbuilt
restriction , that once more than a certain number of activities pass the
restriction.
There are a number of different activities which contribute to the count.
Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
the limit. Maybe you could upgrade
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sale" <aleksandar.stancic@.gmail.com> wrote in message
news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> After a disk failure ( disk was in raid 1 configuration ) SQL Server
> start to work very slow.
> SqlServer start to raise following event
> Event Type: Information
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 7/3/2006
> Time: 8:54:12 AM
> User: N/A
> Computer: SRVCA2
> Description:
> 17052 :
> This SQL Server has been optimized for 8 concurrent queries. This limit
> has been exceeded by 19 queries and performance may be adversely
> affected.
>
> SQL Server worked fine before this. I replaced hard disk and rebuild of
> configuration was sucesfull. In SQL events i saw that SQL Server has
> performed recovery of databases.
> I have SQL Server Personal edition installed.
> Can someone help me with this one please.
>|||"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:TI-dnbeidMt4RzXZnZ2dnUVZ8s-dnZ2d@.bt.com...
> The verion of the db engine shipped with the Personal Edition has an
inbuilt
> restriction , that once more than a certain number of activities pass the
> restriction.
> There are a number of different activities which contribute to the count.
> Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
> the limit. Maybe you could upgrade
Most likely the slower diskI/O during the failure was the cause here.
You may want to upgrade to something like SQL 2005 Express which removes
this particular restriction. (but has others).

> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sale" <aleksandar.stancic@.gmail.com> wrote in message
> news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
>|||I Upgrade Personal edition to Standard, and after this everything work
fine.
Thanks for your help people