performance slow down at a regurlar basis...

Hi,
I've a strange behavior on my server.
The response time is good after a reboot of the computer, but, after a long
work on it (half day or a day)
The performance slow down, so I reboot the server to recover my performance.
Why?
How to diagnostic this problem?
Thanks.
Jerome.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25Have a look at perfmon
If you have to do this every day/after using it a while then a resource is
being depleted seriously and a reboot frees it up again.
Look at Memory, Processor to start with.
What version and SP of SQL Server ?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:e6Ke4vrCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've a strange behavior on my server.
> The response time is good after a reboot of the computer, but, after a
long
> work on it (half day or a day)
> The performance slow down, so I reboot the server to recover my
performance.
> Why?
> How to diagnostic this problem?
> Thanks.
> Jerome.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25
>|||Fairly Normal with Windows
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:e6Ke4vrCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've a strange behavior on my server.
> The response time is good after a reboot of the computer, but, after a
long
> work on it (half day or a day)
> The performance slow down, so I reboot the server to recover my
performance.
> Why?
> How to diagnostic this problem?
> Thanks.
> Jerome.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25
>|||I've the SP3 on a Windows 2000 Server + SP4 server.
I've 3 drives, and I've splitted differents database files on these 3 drives
(tempdb on 1; data of my data warehouse on 2; data warehouse log file on 3)
"Allan Mitchell" <allan@.no-spam.sqldts.com> a crit dans le message de
news:OCcnxMCDEHA.580@.TK2MSFTNGP11.phx.gbl...
> Have a look at perfmon
> If you have to do this every day/after using it a while then a resource is
> being depleted seriously and a reboot frees it up again.
> Look at Memory, Processor to start with.
> What version and SP of SQL Server ?
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Jj" <willgart@._A_hAotmail_A_.com> wrote in message
> news:e6Ke4vrCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> long
> performance.
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25

Performance problems (strange)

Hello,
I encounter serious performance degradation and what's more important -
strange SQL server behaviour after removing and adding again indexes on few
(6-8) tables.
Let me explain - I have original database, which performs well. Database
size is about 400-600 MB on disk. Then I remove few indexes from few tables
and add it again. Now database performs drastically slower. More than that,
the query schema has changed after removal/adding indexes, but database
schema is the same! So it looks strange.
Please help me and explain why query schema has changed, while database
schema is unchanged? Why database performs slower?
I use SQL Srv 2000 SP3a, Win 2000Srv, 1 GB RAM. Tested in many hardware
scenarios, also with Windows Server 2003.
Adam.
What do you mean the query schema has changed? What is occurring slower?
(Selects? Updates? etc..)
"aheczko@.nospam.nospam" <aheczko@.nospam.nospam@.discussions.microsoft.com >
wrote in message news:D6E52964-B847-4DB7-BACD-CEA5A8BECB97@.microsoft.com...
> Hello,
> I encounter serious performance degradation and what's more important -
> strange SQL server behaviour after removing and adding again indexes on
> few
> (6-8) tables.
> Let me explain - I have original database, which performs well. Database
> size is about 400-600 MB on disk. Then I remove few indexes from few
> tables
> and add it again. Now database performs drastically slower. More than
> that,
> the query schema has changed after removal/adding indexes, but database
> schema is the same! So it looks strange.
> Please help me and explain why query schema has changed, while database
> schema is unchanged? Why database performs slower?
> I use SQL Srv 2000 SP3a, Win 2000Srv, 1 GB RAM. Tested in many hardware
> scenarios, also with Windows Server 2003.
> Adam.
>
|||Selects are much slower. Also sql execution plan shown by query analyser is
different than on a original database.
So I have 2 databases, they have identical schema but SQL execution plan are
different and performance degraded on 2nd db.
Adam
"Jeff Fiegel" wrote:

> What do you mean the query schema has changed? What is occurring slower?
> (Selects? Updates? etc..)
>
> "aheczko@.nospam.nospam" <aheczko@.nospam.nospam@.discussions.microsoft.com >
> wrote in message news:D6E52964-B847-4DB7-BACD-CEA5A8BECB97@.microsoft.com...
>
>
|||Adam,
Are you *sure* the indexes are the same as before? After rebuilding your
indexes run sp_updatestats.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
aheczko@.nospam.nospam wrote:[vbcol=seagreen]
> Selects are much slower. Also sql execution plan shown by query analyser is
> different than on a original database.
> So I have 2 databases, they have identical schema but SQL execution plan are
> different and performance degraded on 2nd db.
> Adam
> "Jeff Fiegel" wrote:
>

Performance problems (strange)

Hello,
I encounter serious performance degradation and what's more important -
strange SQL server behaviour after removing and adding again indexes on few
(6-8) tables.
Let me explain - I have original database, which performs well. Database
size is about 400-600 MB on disk. Then I remove few indexes from few tables
and add it again. Now database performs drastically slower. More than that,
the query schema has changed after removal/adding indexes, but database
schema is the same! So it looks strange.
Please help me and explain why query schema has changed, while database
schema is unchanged? Why database performs slower?
I use SQL Srv 2000 SP3a, Win 2000Srv, 1 GB RAM. Tested in many hardware
scenarios, also with Windows Server 2003.
Adam.What do you mean the query schema has changed? What is occurring slower?
(Selects? Updates? etc..)
"aheczko@.nospam.nospam" <aheczko@.nospam.nospam@.discussions.microsoft.com>
wrote in message news:D6E52964-B847-4DB7-BACD-CEA5A8BECB97@.microsoft.com...
> Hello,
> I encounter serious performance degradation and what's more important -
> strange SQL server behaviour after removing and adding again indexes on
> few
> (6-8) tables.
> Let me explain - I have original database, which performs well. Database
> size is about 400-600 MB on disk. Then I remove few indexes from few
> tables
> and add it again. Now database performs drastically slower. More than
> that,
> the query schema has changed after removal/adding indexes, but database
> schema is the same! So it looks strange.
> Please help me and explain why query schema has changed, while database
> schema is unchanged? Why database performs slower?
> I use SQL Srv 2000 SP3a, Win 2000Srv, 1 GB RAM. Tested in many hardware
> scenarios, also with Windows Server 2003.
> Adam.
>|||Selects are much slower. Also sql execution plan shown by query analyser is
different than on a original database.
So I have 2 databases, they have identical schema but SQL execution plan are
different and performance degraded on 2nd db.
Adam
"Jeff Fiegel" wrote:
> What do you mean the query schema has changed? What is occurring slower?
> (Selects? Updates? etc..)
>
> "aheczko@.nospam.nospam" <aheczko@.nospam.nospam@.discussions.microsoft.com>
> wrote in message news:D6E52964-B847-4DB7-BACD-CEA5A8BECB97@.microsoft.com...
> > Hello,
> > I encounter serious performance degradation and what's more important -
> > strange SQL server behaviour after removing and adding again indexes on
> > few
> > (6-8) tables.
> > Let me explain - I have original database, which performs well. Database
> > size is about 400-600 MB on disk. Then I remove few indexes from few
> > tables
> > and add it again. Now database performs drastically slower. More than
> > that,
> > the query schema has changed after removal/adding indexes, but database
> > schema is the same! So it looks strange.
> > Please help me and explain why query schema has changed, while database
> > schema is unchanged? Why database performs slower?
> > I use SQL Srv 2000 SP3a, Win 2000Srv, 1 GB RAM. Tested in many hardware
> > scenarios, also with Windows Server 2003.
> > Adam.
> >
>
>|||Adam,
Are you *sure* the indexes are the same as before? After rebuilding your
indexes run sp_updatestats.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
aheczko@.nospam.nospam wrote:
> Selects are much slower. Also sql execution plan shown by query analyser is
> different than on a original database.
> So I have 2 databases, they have identical schema but SQL execution plan are
> different and performance degraded on 2nd db.
> Adam
> "Jeff Fiegel" wrote:
>
>>What do you mean the query schema has changed? What is occurring slower?
>>(Selects? Updates? etc..)
>>
>>"aheczko@.nospam.nospam" <aheczko@.nospam.nospam@.discussions.microsoft.com>
>>wrote in message news:D6E52964-B847-4DB7-BACD-CEA5A8BECB97@.microsoft.com...
>>Hello,
>>I encounter serious performance degradation and what's more important -
>>strange SQL server behaviour after removing and adding again indexes on
>>few
>>(6-8) tables.
>>Let me explain - I have original database, which performs well. Database
>>size is about 400-600 MB on disk. Then I remove few indexes from few
>>tables
>>and add it again. Now database performs drastically slower. More than
>>that,
>>the query schema has changed after removal/adding indexes, but database
>>schema is the same! So it looks strange.
>>Please help me and explain why query schema has changed, while database
>>schema is unchanged? Why database performs slower?
>>I use SQL Srv 2000 SP3a, Win 2000Srv, 1 GB RAM. Tested in many hardware
>>scenarios, also with Windows Server 2003.
>>Adam.
>>
>>sql

Performance problems (strange)

Hello,
I encounter serious performance degradation and what's more important -
strange SQL server behaviour after removing and adding again indexes on few
(6-8) tables.
Let me explain - I have original database, which performs well. Database
size is about 400-600 MB on disk. Then I remove few indexes from few tables
and add it again. Now database performs drastically slower. More than that,
the query schema has changed after removal/adding indexes, but database
schema is the same! So it looks strange.
Please help me and explain why query schema has changed, while database
schema is unchanged? Why database performs slower?
I use SQL Srv 2000 SP3a, Win 2000Srv, 1 GB RAM. Tested in many hardware
scenarios, also with Windows Server 2003.
Adam.What do you mean the query schema has changed? What is occurring slower?
(Selects? Updates? etc..)
"aheczko@.nospam.nospam" <aheczko@.nospam.nospam@.discussions.microsoft.com>
wrote in message news:D6E52964-B847-4DB7-BACD-CEA5A8BECB97@.microsoft.com...
> Hello,
> I encounter serious performance degradation and what's more important -
> strange SQL server behaviour after removing and adding again indexes on
> few
> (6-8) tables.
> Let me explain - I have original database, which performs well. Database
> size is about 400-600 MB on disk. Then I remove few indexes from few
> tables
> and add it again. Now database performs drastically slower. More than
> that,
> the query schema has changed after removal/adding indexes, but database
> schema is the same! So it looks strange.
> Please help me and explain why query schema has changed, while database
> schema is unchanged? Why database performs slower?
> I use SQL Srv 2000 SP3a, Win 2000Srv, 1 GB RAM. Tested in many hardware
> scenarios, also with Windows Server 2003.
> Adam.
>|||Selects are much slower. Also sql execution plan shown by query analyser is
different than on a original database.
So I have 2 databases, they have identical schema but SQL execution plan are
different and performance degraded on 2nd db.
Adam
"Jeff Fiegel" wrote:

> What do you mean the query schema has changed? What is occurring slower?
> (Selects? Updates? etc..)
>
> "aheczko@.nospam.nospam" <aheczko@.nospam.nospam@.discussions.microsoft.com>
> wrote in message news:D6E52964-B847-4DB7-BACD-CEA5A8BECB97@.microsoft.com..
.
>
>|||Adam,
Are you *sure* the indexes are the same as before? After rebuilding your
indexes run sp_updatestats.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
aheczko@.nospam.nospam wrote:[vbcol=seagreen]
> Selects are much slower. Also sql execution plan shown by query analyser i
s
> different than on a original database.
> So I have 2 databases, they have identical schema but SQL execution plan a
re
> different and performance degraded on 2nd db.
> Adam
> "Jeff Fiegel" wrote:
>

Performance problem but memory available...

Hi,
I've some performance problems on a server.
I monitor these statistics:
Pages/sec
page faults/sec
available MBytes
the result is strange during small activities on the server: (min, avg, max)
Pages/sec = 0, 30, 217
page faults/sec = 18, 350, 4000
available MBytes = 290Mb, 300,Mb 308Mb
My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
1280Mb installed.
Opening the property page of SQL server to manage the memory could take 2 to
3 minutes before I can see anything!!!
I've the same problem with some client applications, a simple query can take
a long time, and another time (5minutes) the same query takes only 5 seconds
after a reboot.
If I restart my server, then all works fine, but when my SQL Server instance
start to consume more memory, then the performance slow down. I don't
understand why, because I've some memory available.
What can I monitor to identify the problem?
Thanks for your guides.
Jrme.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09What is the processor utilization?... Sounds like you've got something
eating it all up!
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:utqpL3V9DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've some performance problems on a server.
> I monitor these statistics:
> Pages/sec
> page faults/sec
> available MBytes
> the result is strange during small activities on the server: (min, avg,
max)
> Pages/sec = 0, 30, 217
> page faults/sec = 18, 350, 4000
> available MBytes = 290Mb, 300,Mb 308Mb
> My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
> 1280Mb installed.
> Opening the property page of SQL server to manage the memory could take 2
to
> 3 minutes before I can see anything!!!
> I've the same problem with some client applications, a simple query can
take
> a long time, and another time (5minutes) the same query takes only 5
seconds
> after a reboot.
> If I restart my server, then all works fine, but when my SQL Server
instance
> start to consume more memory, then the performance slow down. I don't
> understand why, because I've some memory available.
> What can I monitor to identify the problem?
> Thanks for your guides.
> Jrme.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09
>

Performance problem but memory available...

Hi,
I've some performance problems on a server.
I monitor these statistics:
Pages/sec
page faults/sec
available MBytes
the result is strange during small activities on the server: (min, avg, max)
Pages/sec = 0, 30, 217
page faults/sec = 18, 350, 4000
available MBytes = 290Mb, 300,Mb 308Mb
My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
1280Mb installed.
Opening the property page of SQL server to manage the memory could take 2 to
3 minutes before I can see anything!!!
I've the same problem with some client applications, a simple query can take
a long time, and another time (5minutes) the same query takes only 5 seconds
after a reboot.
If I restart my server, then all works fine, but when my SQL Server instance
start to consume more memory, then the performance slow down. I don't
understand why, because I've some memory available.
What can I monitor to identify the problem?
Thanks for your guides.
Jérôme.
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09What is the processor utilization?... Sounds like you've got something
eating it all up!
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jéjé" <willgart@._A_hAotmail_A_.com> wrote in message
news:utqpL3V9DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've some performance problems on a server.
> I monitor these statistics:
> Pages/sec
> page faults/sec
> available MBytes
> the result is strange during small activities on the server: (min, avg,
max)
> Pages/sec = 0, 30, 217
> page faults/sec = 18, 350, 4000
> available MBytes = 290Mb, 300,Mb 308Mb
> My instance of SQL Server used 545Mb (700Mb in VM Size) and my server has
> 1280Mb installed.
> Opening the property page of SQL server to manage the memory could take 2
to
> 3 minutes before I can see anything!!!
> I've the same problem with some client applications, a simple query can
take
> a long time, and another time (5minutes) the same query takes only 5
seconds
> after a reboot.
> If I restart my server, then all works fine, but when my SQL Server
instance
> start to consume more memory, then the performance slow down. I don't
> understand why, because I've some memory available.
> What can I monitor to identify the problem?
> Thanks for your guides.
> Jérôme.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.581 / Virus Database: 368 - Release Date: 2004-02-09
>

Performance Problem

I am experience a very strange performance problem. A nightly job that had
been consistently running 2 hours each night is suddenly running 16 hours.
A trace reveals a section of code taking 2500-3800 milliseconds of CPU to
process. The execution plan for the select statement shows the indexes are
correctly being selected and an index s is being performed on both tables
in the select. There are approx 400,000 rows, and the trace says it is
reading every row (although index sing)
If I run the same select statement in query analyzer while the Agent job is
running it processes in about 35 milliseconds, reading less than 60 rows (As
it should)
Any ideas on why I am getting such a large performance gap'
help!
Thanks
Richard DouglassIt's probably something that changed between then and now.
"Richard Douglass" wrote:

> I am experience a very strange performance problem. A nightly job that ha
d
> been consistently running 2 hours each night is suddenly running 16 hours.
> A trace reveals a section of code taking 2500-3800 milliseconds of CPU to
> process. The execution plan for the select statement shows the indexes ar
e
> correctly being selected and an index s is being performed on both tabl
es
> in the select. There are approx 400,000 rows, and the trace says it is
> reading every row (although index sing)
> If I run the same select statement in query analyzer while the Agent job i
s
> running it processes in about 35 milliseconds, reading less than 60 rows (
As
> it should)
> Any ideas on why I am getting such a large performance gap'
> help!
> Thanks
> Richard Douglass
>
>|||Nothing has changed. The code is stable and has not been modified in almost
a year.
It makes no sense that the SELECT in the job runs for 3000 milliseconds and
a cut-n-paste of the same query runs in 40 milliseconds in query analyzer.
Both the job and QA produce the exact same execution plan (Same logical
look, both tables index sing)
"KH" <KH@.discussions.microsoft.com> wrote in message
news:7DEC1462-2C11-4340-BAA2-8DD92B47C0C5@.microsoft.com...
> It's probably something that changed between then and now.
>
> "Richard Douglass" wrote:
>
had
hours.
to
are
tables
is
(As|||Is there anything to the timing that your "nightly" job runs at night
(during backups?) and your QueryAnalyzer job you test is run during the day?
(grasping at straws?)
Message posted via http://www.webservertalk.com|||Richard, I saw a very similiar thing at one of my customers.. My suspicion
was the driver (odbc versus native sql driver. don't ask me why).. If we
ran a query in QA, it ran in sub-second, but when ran through the app it ran
in 3 to 4 seconds. Exact same parameters and select etc.
I ended up changing the query in the procedure and did a pre-select and got
both to run in the same time frame.
I know this doesn' t make sense, but have you tried dropping and re-creating
the index'
Bill
"Richard Douglass" <RichardD@.arisinc.com> wrote in message
news:e2ncx%23jNFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Nothing has changed. The code is stable and has not been modified in
> almost
> a year.
> It makes no sense that the SELECT in the job runs for 3000 milliseconds
> and
> a cut-n-paste of the same query runs in 40 milliseconds in query analyzer.
> Both the job and QA produce the exact same execution plan (Same logical
> look, both tables index sing)
> "KH" <KH@.discussions.microsoft.com> wrote in message
> news:7DEC1462-2C11-4340-BAA2-8DD92B47C0C5@.microsoft.com...
> had
> hours.
> to
> are
> tables
> is
> (As
>|||Have you tried updating the stats on those two tables? You might have
gotten a bad query plan that is being reused by the job. When you run the
query individually it will get it's own plan. Also make sure that you have
SET NOCOUNT ON set at the beginning of the code in the job.
Andrew J. Kelly SQL MVP
"Richard Douglass" <RichardD@.arisinc.com> wrote in message
news:ukuptfjNFHA.2136@.TK2MSFTNGP14.phx.gbl...
>I am experience a very strange performance problem. A nightly job that had
> been consistently running 2 hours each night is suddenly running 16 hours.
> A trace reveals a section of code taking 2500-3800 milliseconds of CPU to
> process. The execution plan for the select statement shows the indexes
> are
> correctly being selected and an index s is being performed on both
> tables
> in the select. There are approx 400,000 rows, and the trace says it is
> reading every row (although index sing)
> If I run the same select statement in query analyzer while the Agent job
> is
> running it processes in about 35 milliseconds, reading less than 60 rows
> (As
> it should)
> Any ideas on why I am getting such a large performance gap'
> help!
> Thanks
> Richard Douglass
>|||"Richard Douglass" <RichardD@.arisinc.com> wrote in message
news:e2ncx%23jNFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Nothing has changed. The code is stable and has not been modified in
> almost
> a year.
> It makes no sense that the SELECT in the job runs for 3000 milliseconds
> and
> a cut-n-paste of the same query runs in 40 milliseconds in query analyzer.
> Both the job and QA produce the exact same execution plan (Same logical
> look, both tables index sing)
It sound very much like a problem we're having on one of our projects here
as well!
Sometime during easter the performance on one og our Stored Procedures has
gone VERY bad!
Our code is also stable and has not been modified during since some time
before easter!
With the Profiler we can see that the SP call from the webapplication takes
approx. 6 seconds - but if we run the same SP call from QA it takes approx.
second!
We suspected an applied MDAC 2.8 update that had been applied during easter
to be the source of the problem, but after an uninstall and restart it's
still slow!
Are there any more people that has experienced this problem out there? And
if any should have solved the problem please explain thoroughly!
Cheers
Rene

Performance problem

As of yesterday there have been strange performance problems with SQL Server
2000. I'm hoping someone will know the best way of figuring out where the
problem is coming from.
The problem is this, certain things are taking forever on the SQL Server.
For example, reading the sysindex is taking a second or more, changing an
index on a table with 450 rows is taking a long time (been 4 minutes and am
still waiting). SQL Profiler shows that dropping the current index took
over 1 second. The table consists of two int columns, 1 varchar, and one
datetime. Returning all rows within that table takes 5 seconds through the
Enterprise Manager. Again, there are only 450 rows.
Everything was working fine and fast up until yesterday. I rebooted the
server but it didn't fix the problem. I'm defragging now, but have little
hope that that will fix it since the performance hit was huge when it
happened.
Anyone know what may have caused this or any tips on fixing it? It's a
major problem since this is a production server. Things that used to take a
few milliseconds now take several seconds and even minutes.Greg
Cold it be possible that you experience some HW problems? Maybe you have
many retries when doing disk IO?
Or, just to check - are you using SP3a? Maybe is worth checking if you are
experiencing soe DOSattack (Slammer)?
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Greg" <nospam@.nospam.com> wrote in message
news:OZmS3IxDEHA.2768@.tk2msftngp13.phx.gbl...
> As of yesterday there have been strange performance problems with SQL
Server
> 2000. I'm hoping someone will know the best way of figuring out where the
> problem is coming from.
> The problem is this, certain things are taking forever on the SQL Server.
> For example, reading the sysindex is taking a second or more, changing an
> index on a table with 450 rows is taking a long time (been 4 minutes and
am
> still waiting). SQL Profiler shows that dropping the current index took
> over 1 second. The table consists of two int columns, 1 varchar, and one
> datetime. Returning all rows within that table takes 5 seconds through
the
> Enterprise Manager. Again, there are only 450 rows.
> Everything was working fine and fast up until yesterday. I rebooted the
> server but it didn't fix the problem. I'm defragging now, but have little
> hope that that will fix it since the performance hit was huge when it
> happened.
> Anyone know what may have caused this or any tips on fixing it? It's a
> major problem since this is a production server. Things that used to take
a
> few milliseconds now take several seconds and even minutes.
>|||Perhaps someone has installed Anti Virus Software on your SQL Server and
it's scanning like a Banshee...
?
Greg Jackson
PDX, Oregon|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:eoITZPzDEHA.2308@.tk2msftngp13.phx.gbl...
> Greg
> Cold it be possible that you experience some HW problems? Maybe you have
> many retries when doing disk IO?
> Or, just to check - are you using SP3a? Maybe is worth checking if you are
> experiencing soe DOSattack (Slammer)?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
>
Thanks for the reply. There didn't seem to be any disk problems when I
defragged. I thought about this as well, but haven't checked for problems
yet.
Already using SP3a and checked for DOS attacks.|||"Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
news:OMg2Jc2DEHA.2576@.TK2MSFTNGP11.phx.gbl...
> Perhaps someone has installed Anti Virus Software on your SQL Server and
> it's scanning like a Banshee...
> ?
>
> Greg Jackson
> PDX, Oregon
>
Unfortunately, nothing was using up the CPU. I did figure out the problem.
When I disabled a third-party stats program that uses the SQL Server the
speed went back to normal.

Performance of SQL2k5 with snapshot isolation level turned on

I did several tests of performance of SQL2k5, and I see quite strange result
s.
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache
-
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:

> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>
>|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...[vbcol=seagreen]
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:
>

Performance of SQL2k5 with snapshot isolation level turned on

I did several tests of performance of SQL2k5, and I see quite strange results.
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache -
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?
As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>
|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:

> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>
>
|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...[vbcol=seagreen]
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:

Performance of SQL2k5 with snapshot isolation level turned on

I did several tests of performance of SQL2k5, and I see quite strange results.
I restored database from SQL2k, rebuilded indexes, updated statistics. I did
all what described in migration process. Backuped the resulting database. In
each of next tests I restored the database from backup. Next I run tests.
Since our application, OLTP, is very performance sensitive, we need to have
good distribution of transaction execution time. In case if I run tests on
SQL2k5, when snapshot isolation level is not turned on, I see good
distribution - less than 0.5% of transactions executing more than 200ms,
average execution time is ~5 ms. In case if I change compatibility level to
90 and turn on allow_snapshot_isolation, behavior is dramatically changing.
Near 5% of transactions start to execute more than 200 ms, average execution
time is also significally increasing. There are no transactions which are
really use snapshot isolation level, stored procedures are exacly same as
before. There is no any memory pressure, no IO bottlenecks. During the tests
counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2 GB
size, but only ~200 MB of the data was read in the tests. 1.2GB write cache -
it means what all writing during the test was done only to write cache on
CX500, tests were not so long to fill the entire write cache. Windows 2003
x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
CPU load was ~50% in tests without snapshot isolation level, and ~70% with
tests with snapshot level. 2CPU.
From the test it looks like I can not remove replication to QueryDB and run
queries on same computer where OLTP load...
Why impact of snapshot isolation level is so big?As soon as you change the isolation level to either Snapshot Isolation or
Read Committed Snapshot, SQL Server will start saving ALL updated data in
the version store in tempdb, whether or not any queries every read the
versioned data. So every changed row will have additional overhead
associated with it.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>I did several tests of performance of SQL2k5, and I see quite strange
>results.
> I restored database from SQL2k, rebuilded indexes, updated statistics. I
> did
> all what described in migration process. Backuped the resulting database.
> In
> each of next tests I restored the database from backup. Next I run tests.
> Since our application, OLTP, is very performance sensitive, we need to
> have
> good distribution of transaction execution time. In case if I run tests on
> SQL2k5, when snapshot isolation level is not turned on, I see good
> distribution - less than 0.5% of transactions executing more than 200ms,
> average execution time is ~5 ms. In case if I change compatibility level
> to
> 90 and turn on allow_snapshot_isolation, behavior is dramatically
> changing.
> Near 5% of transactions start to execute more than 200 ms, average
> execution
> time is also significally increasing. There are no transactions which are
> really use snapshot isolation level, stored procedures are exacly same as
> before. There is no any memory pressure, no IO bottlenecks. During the
> tests
> counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> GB
> size, but only ~200 MB of the data was read in the tests. 1.2GB write
> cache -
> it means what all writing during the test was done only to write cache on
> CX500, tests were not so long to fill the entire write cache. Windows 2003
> x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> tests with snapshot level. 2CPU.
> From the test it looks like I can not remove replication to QueryDB and
> run
> queries on same computer where OLTP load...
> Why impact of snapshot isolation level is so big?
>|||I understand what snapshot isolation adds some overhead, but I expected
lesser overhead. CPU consumption near 1.4 times more in case if snapshot
option turned on. It means what for most of performance sensitive OLTP
applications, which currently use SQL2k and which use replication to another
database used for longer queries, can not remove replication and run queries
on same server where main OLTP load after migration to SQL2k5. Quite
disappointing...
"Kalen Delaney" wrote:
> As soon as you change the isolation level to either Snapshot Isolation or
> Read Committed Snapshot, SQL Server will start saving ALL updated data in
> the version store in tempdb, whether or not any queries every read the
> versioned data. So every changed row will have additional overhead
> associated with it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
> >I did several tests of performance of SQL2k5, and I see quite strange
> >results.
> > I restored database from SQL2k, rebuilded indexes, updated statistics. I
> > did
> > all what described in migration process. Backuped the resulting database.
> > In
> > each of next tests I restored the database from backup. Next I run tests.
> > Since our application, OLTP, is very performance sensitive, we need to
> > have
> > good distribution of transaction execution time. In case if I run tests on
> > SQL2k5, when snapshot isolation level is not turned on, I see good
> > distribution - less than 0.5% of transactions executing more than 200ms,
> > average execution time is ~5 ms. In case if I change compatibility level
> > to
> > 90 and turn on allow_snapshot_isolation, behavior is dramatically
> > changing.
> > Near 5% of transactions start to execute more than 200 ms, average
> > execution
> > time is also significally increasing. There are no transactions which are
> > really use snapshot isolation level, stored procedures are exacly same as
> > before. There is no any memory pressure, no IO bottlenecks. During the
> > tests
> > counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data on
> > 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have 2
> > GB
> > size, but only ~200 MB of the data was read in the tests. 1.2GB write
> > cache -
> > it means what all writing during the test was done only to write cache on
> > CX500, tests were not so long to fill the entire write cache. Windows 2003
> > x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
> > CPU load was ~50% in tests without snapshot isolation level, and ~70% with
> > tests with snapshot level. 2CPU.
> > From the test it looks like I can not remove replication to QueryDB and
> > run
> > queries on same computer where OLTP load...
> > Why impact of snapshot isolation level is so big?
> >
>
>|||There is a lot to consider with this. Snapshot isolation or read committed
snapshot write every single transaction into the version store. Since the
version store is in tempdb, in a high volume environment, you are in effect
pounding tempdb with every single transaction you issue. These are intended
to be applied when you have situations when you do not want reads to block
writes, BUT there is a performance trade-off when you implement that
functionality.
There are a couple of things you can do to minimize the impact of the
version store overhead. First, you need to move tempdb to its own disk
device so that it doesn't have to compete with everything else. Secondly,
you can add additional files (equal to the nmber of processors) to tempdb
that are of equal size in order to stripe the writes. BUT, you are still
going to have a performance impact.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:C3C9D444-5CD1-4C9A-9F2F-4BCD5DF26019@.microsoft.com...
>I understand what snapshot isolation adds some overhead, but I expected
> lesser overhead. CPU consumption near 1.4 times more in case if snapshot
> option turned on. It means what for most of performance sensitive OLTP
> applications, which currently use SQL2k and which use replication to
> another
> database used for longer queries, can not remove replication and run
> queries
> on same server where main OLTP load after migration to SQL2k5. Quite
> disappointing...
> "Kalen Delaney" wrote:
>> As soon as you change the isolation level to either Snapshot Isolation or
>> Read Committed Snapshot, SQL Server will start saving ALL updated data in
>> the version store in tempdb, whether or not any queries every read the
>> versioned data. So every changed row will have additional overhead
>> associated with it.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "andsm" <andsm@.discussions.microsoft.com> wrote in message
>> news:B12BA2E7-8639-4DE6-9113-75F56E6BAB7C@.microsoft.com...
>> >I did several tests of performance of SQL2k5, and I see quite strange
>> >results.
>> > I restored database from SQL2k, rebuilded indexes, updated statistics.
>> > I
>> > did
>> > all what described in migration process. Backuped the resulting
>> > database.
>> > In
>> > each of next tests I restored the database from backup. Next I run
>> > tests.
>> > Since our application, OLTP, is very performance sensitive, we need to
>> > have
>> > good distribution of transaction execution time. In case if I run tests
>> > on
>> > SQL2k5, when snapshot isolation level is not turned on, I see good
>> > distribution - less than 0.5% of transactions executing more than
>> > 200ms,
>> > average execution time is ~5 ms. In case if I change compatibility
>> > level
>> > to
>> > 90 and turn on allow_snapshot_isolation, behavior is dramatically
>> > changing.
>> > Near 5% of transactions start to execute more than 200 ms, average
>> > execution
>> > time is also significally increasing. There are no transactions which
>> > are
>> > really use snapshot isolation level, stored procedures are exacly same
>> > as
>> > before. There is no any memory pressure, no IO bottlenecks. During the
>> > tests
>> > counters showed nearby 1 GB of free memory. IO system - EMC CX500. Data
>> > on
>> > 5x73GB FC 15kRPM drives (RAID0), log on 3 drives (RAID0). Database have
>> > 2
>> > GB
>> > size, but only ~200 MB of the data was read in the tests. 1.2GB write
>> > cache -
>> > it means what all writing during the test was done only to write cache
>> > on
>> > CX500, tests were not so long to fill the entire write cache. Windows
>> > 2003
>> > x64 Enterprise Edition, SQL2k5 x64 Developer Edition.
>> > CPU load was ~50% in tests without snapshot isolation level, and ~70%
>> > with
>> > tests with snapshot level. 2CPU.
>> > From the test it looks like I can not remove replication to QueryDB and
>> > run
>> > queries on same computer where OLTP load...
>> > Why impact of snapshot isolation level is so big?
>> >
>>
>>