Rebuilding Indexes and Shrinking Databases

Is there a best practice for the time of day to rebuild indexes and shrink
databases? i.e. not in the middle of the day
What performance hit would it have performing them actions on a 20Gb
Database have on disk I/O, Processor, etc.?
Thanks
Tim
Yes it is a good practice to rebuild indexes but when it depends on your
data load. We do it a twice a week.
Don't shrink database because it causes a fragmentation to be created of
the data pages. How do you increase a size of the database?
"Tim Earnshaw" <TimEarnshaw@.discussions.microsoft.com> wrote in message
news:1EBF5F1D-C20C-4969-A5AF-2A52E784A0F5@.microsoft.com...
> Is there a best practice for the time of day to rebuild indexes and shrink
> databases? i.e. not in the middle of the day
> What performance hit would it have performing them actions on a 20Gb
> Database have on disk I/O, Processor, etc.?
> Thanks
|||Tim,
Depends on what maintenance windows you have - do it then. I usually
rebuild indexes weekly and never, ever, ever shrink databases unless
there's an emergency (run out of disk space).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Tim Earnshaw wrote:
> Is there a best practice for the time of day to rebuild indexes and shrink
> databases? i.e. not in the middle of the day
> What performance hit would it have performing them actions on a 20Gb
> Database have on disk I/O, Processor, etc.?
> Thanks
|||Thanks for the replies
The reason I ask is someone where I work was shrinking and rebuilding
indexes at 2pm and they said that its industry / microsoft standard to
rebuild indexes whenever needed. I didn't believe them and thought that I
would ask on here.
"Mark Allison" wrote:

> Tim,
> Depends on what maintenance windows you have - do it then. I usually
> rebuild indexes weekly and never, ever, ever shrink databases unless
> there's an emergency (run out of disk space).
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Tim Earnshaw wrote:
>
|||Hello Tim,
It is wrong to rebuild Indexes just because there is a command "DBCC Rebuild
... " - Atleast not everyday !!.
We used to load the contracts data into the Reinsurance Accounting DB on a
daily basis. Before the load, we used
to drop the Indexes and recreate them after loading the data. This process
used to take care of Rebuilding the Indexes
However, for the transaction accounting tables (contract tables in the
Reinsurance Accounting becomes Read Only ),
I used to manually drop them recreate them on a weekly basis. Manually
because the "Rebuild Index" process fills
the transaction log pretty fast if the table is huge. The application/DB was
a 24/7 application and one hour (6:30 PM EST)
was allocated for all sort of maintenance.
Hope this helps.
Gopi
"Tim Earnshaw" <TimEarnshaw@.discussions.microsoft.com> wrote in message
news:F97F5310-D81B-4995-A7B6-C7DEA329DBFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the replies
> The reason I ask is someone where I work was shrinking and rebuilding
> indexes at 2pm and they said that its industry / microsoft standard to
> rebuild indexes whenever needed. I didn't believe them and thought that I
> would ask on here.
>
> "Mark Allison" wrote:
|||You may also want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best
Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
-Sue
On Tue, 5 Jul 2005 01:45:02 -0700, Tim Earnshaw
<TimEarnshaw@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the replies
>The reason I ask is someone where I work was shrinking and rebuilding
>indexes at 2pm and they said that its industry / microsoft standard to
>rebuild indexes whenever needed. I didn't believe them and thought that I
>would ask on here.
>
>"Mark Allison" wrote:
|||Tim,
You might also want to check out the sample script under DBCC SHOWCONTIG in
BOL. It allows you to only reindex ones that really need it.
Andrew J. Kelly SQL MVP
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:s7tkc1t81de95nhc20tbl3ftdf8vam2ei2@.4ax.com...
> You may also want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best
> Practices
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> -Sue
> On Tue, 5 Jul 2005 01:45:02 -0700, Tim Earnshaw
> <TimEarnshaw@.discussions.microsoft.com> wrote:
>
|||Although even that example I provided doesn't take into account whether the
index is actually used for range scans and so will benefit from being
rebuilt/defragged.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OAMduScgFHA.516@.TK2MSFTNGP09.phx.gbl...
> Tim,
> You might also want to check out the sample script under DBCC SHOWCONTIG
> in BOL. It allows you to only reindex ones that really need it.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:s7tkc1t81de95nhc20tbl3ftdf8vam2ei2@.4ax.com...
>
sql

Rebuilding Indexes and Shrinking Databases

Is there a best practice for the time of day to rebuild indexes and shrink
databases? i.e. not in the middle of the day
What performance hit would it have performing them actions on a 20Gb
Database have on disk I/O, Processor, etc.?
ThanksTim
Yes it is a good practice to rebuild indexes but when it depends on your
data load. We do it a twice a week.
Don't shrink database because it causes a fragmentation to be created of
the data pages. How do you increase a size of the database?
"Tim Earnshaw" <TimEarnshaw@.discussions.microsoft.com> wrote in message
news:1EBF5F1D-C20C-4969-A5AF-2A52E784A0F5@.microsoft.com...
> Is there a best practice for the time of day to rebuild indexes and shrink
> databases? i.e. not in the middle of the day
> What performance hit would it have performing them actions on a 20Gb
> Database have on disk I/O, Processor, etc.?
> Thanks|||Tim,
Depends on what maintenance windows you have - do it then. I usually
rebuild indexes weekly and never, ever, ever shrink databases unless
there's an emergency (run out of disk space).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Tim Earnshaw wrote:
> Is there a best practice for the time of day to rebuild indexes and shrink
> databases? i.e. not in the middle of the day
> What performance hit would it have performing them actions on a 20Gb
> Database have on disk I/O, Processor, etc.?
> Thanks|||Thanks for the replies
The reason I ask is someone where I work was shrinking and rebuilding
indexes at 2pm and they said that its industry / microsoft standard to
rebuild indexes whenever needed. I didn't believe them and thought that I
would ask on here.
"Mark Allison" wrote:

> Tim,
> Depends on what maintenance windows you have - do it then. I usually
> rebuild indexes weekly and never, ever, ever shrink databases unless
> there's an emergency (run out of disk space).
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Tim Earnshaw wrote:
>|||Hello Tim,
It is wrong to rebuild Indexes just because there is a command "DBCC Rebuild
... " - Atleast not everyday !!.
We used to load the contracts data into the Reinsurance Accounting DB on a
daily basis. Before the load, we used
to drop the Indexes and recreate them after loading the data. This process
used to take care of Rebuilding the Indexes
However, for the transaction accounting tables (contract tables in the
Reinsurance Accounting becomes Read Only ),
I used to manually drop them recreate them on a weekly basis. Manually
because the "Rebuild Index" process fills
the transaction log pretty fast if the table is huge. The application/DB was
a 24/7 application and one hour (6:30 PM EST)
was allocated for all sort of maintenance.
Hope this helps.
Gopi
"Tim Earnshaw" <TimEarnshaw@.discussions.microsoft.com> wrote in message
news:F97F5310-D81B-4995-A7B6-C7DEA329DBFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the replies
> The reason I ask is someone where I work was shrinking and rebuilding
> indexes at 2pm and they said that its industry / microsoft standard to
> rebuild indexes whenever needed. I didn't believe them and thought that I
> would ask on here.
>
> "Mark Allison" wrote:
>|||You may also want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best
Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
-Sue
On Tue, 5 Jul 2005 01:45:02 -0700, Tim Earnshaw
<TimEarnshaw@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for the replies
>The reason I ask is someone where I work was shrinking and rebuilding
>indexes at 2pm and they said that its industry / microsoft standard to
>rebuild indexes whenever needed. I didn't believe them and thought that I
>would ask on here.
>
>"Mark Allison" wrote:
>|||Tim,
You might also want to check out the sample script under DBCC SHOWCONTIG in
BOL. It allows you to only reindex ones that really need it.
Andrew J. Kelly SQL MVP
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:s7tkc1t81de95nhc20tbl3ftdf8vam2ei2@.
4ax.com...
> You may also want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best
> Practices
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> -Sue
> On Tue, 5 Jul 2005 01:45:02 -0700, Tim Earnshaw
> <TimEarnshaw@.discussions.microsoft.com> wrote:
>
>|||Although even that example I provided doesn't take into account whether the
index is actually used for range scans and so will benefit from being
rebuilt/defragged.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OAMduScgFHA.516@.TK2MSFTNGP09.phx.gbl...
> Tim,
> You might also want to check out the sample script under DBCC SHOWCONTIG
> in BOL. It allows you to only reindex ones that really need it.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:s7tkc1t81de95nhc20tbl3ftdf8vam2ei2@.
4ax.com...
>

Rebuilding Indexes and Shrinking Databases

Is there a best practice for the time of day to rebuild indexes and shrink
databases? i.e. not in the middle of the day
What performance hit would it have performing them actions on a 20Gb
Database have on disk I/O, Processor, etc.?
ThanksTim
Yes it is a good practice to rebuild indexes but when it depends on your
data load. We do it a twice a week.
Don't shrink database because it causes a fragmentation to be created of
the data pages. How do you increase a size of the database?
"Tim Earnshaw" <TimEarnshaw@.discussions.microsoft.com> wrote in message
news:1EBF5F1D-C20C-4969-A5AF-2A52E784A0F5@.microsoft.com...
> Is there a best practice for the time of day to rebuild indexes and shrink
> databases? i.e. not in the middle of the day
> What performance hit would it have performing them actions on a 20Gb
> Database have on disk I/O, Processor, etc.?
> Thanks|||Tim,
Depends on what maintenance windows you have - do it then. I usually
rebuild indexes weekly and never, ever, ever shrink databases unless
there's an emergency (run out of disk space).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Tim Earnshaw wrote:
> Is there a best practice for the time of day to rebuild indexes and shrink
> databases? i.e. not in the middle of the day
> What performance hit would it have performing them actions on a 20Gb
> Database have on disk I/O, Processor, etc.?
> Thanks|||Thanks for the replies
The reason I ask is someone where I work was shrinking and rebuilding
indexes at 2pm and they said that its industry / microsoft standard to
rebuild indexes whenever needed. I didn't believe them and thought that I
would ask on here.
"Mark Allison" wrote:
> Tim,
> Depends on what maintenance windows you have - do it then. I usually
> rebuild indexes weekly and never, ever, ever shrink databases unless
> there's an emergency (run out of disk space).
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Tim Earnshaw wrote:
> > Is there a best practice for the time of day to rebuild indexes and shrink
> > databases? i.e. not in the middle of the day
> >
> > What performance hit would it have performing them actions on a 20Gb
> > Database have on disk I/O, Processor, etc.?
> >
> > Thanks
>|||Hello Tim,
It is wrong to rebuild Indexes just because there is a command "DBCC Rebuild
... " - Atleast not everyday !!.
We used to load the contracts data into the Reinsurance Accounting DB on a
daily basis. Before the load, we used
to drop the Indexes and recreate them after loading the data. This process
used to take care of Rebuilding the Indexes
However, for the transaction accounting tables (contract tables in the
Reinsurance Accounting becomes Read Only ),
I used to manually drop them recreate them on a weekly basis. Manually
because the "Rebuild Index" process fills
the transaction log pretty fast if the table is huge. The application/DB was
a 24/7 application and one hour (6:30 PM EST)
was allocated for all sort of maintenance.
Hope this helps.
Gopi
"Tim Earnshaw" <TimEarnshaw@.discussions.microsoft.com> wrote in message
news:F97F5310-D81B-4995-A7B6-C7DEA329DBFD@.microsoft.com...
> Thanks for the replies
> The reason I ask is someone where I work was shrinking and rebuilding
> indexes at 2pm and they said that its industry / microsoft standard to
> rebuild indexes whenever needed. I didn't believe them and thought that I
> would ask on here.
>
> "Mark Allison" wrote:
>> Tim,
>> Depends on what maintenance windows you have - do it then. I usually
>> rebuild indexes weekly and never, ever, ever shrink databases unless
>> there's an emergency (run out of disk space).
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602m.html
>>
>> Tim Earnshaw wrote:
>> > Is there a best practice for the time of day to rebuild indexes and
>> > shrink
>> > databases? i.e. not in the middle of the day
>> >
>> > What performance hit would it have performing them actions on a 20Gb
>> > Database have on disk I/O, Processor, etc.?
>> >
>> > Thanks|||You may also want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best
Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
-Sue
On Tue, 5 Jul 2005 01:45:02 -0700, Tim Earnshaw
<TimEarnshaw@.discussions.microsoft.com> wrote:
>Thanks for the replies
>The reason I ask is someone where I work was shrinking and rebuilding
>indexes at 2pm and they said that its industry / microsoft standard to
>rebuild indexes whenever needed. I didn't believe them and thought that I
>would ask on here.
>
>"Mark Allison" wrote:
>> Tim,
>> Depends on what maintenance windows you have - do it then. I usually
>> rebuild indexes weekly and never, ever, ever shrink databases unless
>> there's an emergency (run out of disk space).
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602m.html
>>
>> Tim Earnshaw wrote:
>> > Is there a best practice for the time of day to rebuild indexes and shrink
>> > databases? i.e. not in the middle of the day
>> >
>> > What performance hit would it have performing them actions on a 20Gb
>> > Database have on disk I/O, Processor, etc.?
>> >
>> > Thanks|||Tim,
You might also want to check out the sample script under DBCC SHOWCONTIG in
BOL. It allows you to only reindex ones that really need it.
Andrew J. Kelly SQL MVP
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:s7tkc1t81de95nhc20tbl3ftdf8vam2ei2@.4ax.com...
> You may also want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best
> Practices
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> -Sue
> On Tue, 5 Jul 2005 01:45:02 -0700, Tim Earnshaw
> <TimEarnshaw@.discussions.microsoft.com> wrote:
>>Thanks for the replies
>>The reason I ask is someone where I work was shrinking and rebuilding
>>indexes at 2pm and they said that its industry / microsoft standard to
>>rebuild indexes whenever needed. I didn't believe them and thought that I
>>would ask on here.
>>
>>"Mark Allison" wrote:
>> Tim,
>> Depends on what maintenance windows you have - do it then. I usually
>> rebuild indexes weekly and never, ever, ever shrink databases unless
>> there's an emergency (run out of disk space).
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602m.html
>>
>> Tim Earnshaw wrote:
>> > Is there a best practice for the time of day to rebuild indexes and
>> > shrink
>> > databases? i.e. not in the middle of the day
>> >
>> > What performance hit would it have performing them actions on a 20Gb
>> > Database have on disk I/O, Processor, etc.?
>> >
>> > Thanks
>|||Although even that example I provided doesn't take into account whether the
index is actually used for range scans and so will benefit from being
rebuilt/defragged.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OAMduScgFHA.516@.TK2MSFTNGP09.phx.gbl...
> Tim,
> You might also want to check out the sample script under DBCC SHOWCONTIG
> in BOL. It allows you to only reindex ones that really need it.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:s7tkc1t81de95nhc20tbl3ftdf8vam2ei2@.4ax.com...
>> You may also want to check the following article:
>> Microsoft SQL Server 2000 Index Defragmentation Best
>> Practices
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> -Sue
>> On Tue, 5 Jul 2005 01:45:02 -0700, Tim Earnshaw
>> <TimEarnshaw@.discussions.microsoft.com> wrote:
>>Thanks for the replies
>>The reason I ask is someone where I work was shrinking and rebuilding
>>indexes at 2pm and they said that its industry / microsoft standard to
>>rebuild indexes whenever needed. I didn't believe them and thought that I
>>would ask on here.
>>
>>"Mark Allison" wrote:
>> Tim,
>> Depends on what maintenance windows you have - do it then. I usually
>> rebuild indexes weekly and never, ever, ever shrink databases unless
>> there's an emergency (run out of disk space).
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602m.html
>>
>> Tim Earnshaw wrote:
>> > Is there a best practice for the time of day to rebuild indexes and
>> > shrink
>> > databases? i.e. not in the middle of the day
>> >
>> > What performance hit would it have performing them actions on a 20Gb
>> > Database have on disk I/O, Processor, etc.?
>> >
>> > Thanks
>>
>

Rebuild master.dbo.sysperfinfo

On further study I've found that the sysperfinfo table on
the servers with the missing Performance Condition alert
type is EMPTY. I know that on one server with this
condition I upgraded from Standard Edition to Enterprise
Edition, but don't know what caused the server to lose the
data in this table.
Can anyone tell me how to rebuild the data in this table
safely. I know I can't just copy it from another server.
There's nothing in the BOL on rebuilding it.
Any ideas?
Allen White
Sr. Database Administrator
Advanstar CommunicationsThe system table master..sysperfinfo is a virtual one which is dynamically
maintained by SQL Server. So you cannot manually update them as you wish.
Also, it is possible that the SQL Server is started with a -x switch which
disables certain internal statistics. There are many reasons why you do not
see any performance monitor counters, some of which are detailed below:
http://support.microsoft.com/defaul...kb;en-us;246328
http://support.microsoft.com/defaul...kb;EN-US;127207
http://support.microsoft.com/defaul...kb;EN-US;227662 (old)
- Anith
( Please reply to newsgroups only )|||Thanks, Anith,
While these didn't directly apply, I've found that I may
be able to solve the problem using the following statement
run from Command Prompt:
lotctr.exe c:\<SQL Server Install>\Binn\sqlctr.ini
It does require a reboot of the server, and I won't be
able to do that until late tonight, so I'll know tonight
if that solves my problem. I do know that none of the SQL
performance counters are available in Performance Monitor
either.
Allen

quote:

---

>--Original Message--
>The system table master..sysperfinfo is a virtual one

---

which is dynamically

quote:

---

>maintained by SQL Server. So you cannot manually update

---

them as you wish.

quote:

---

>Also, it is possible that the SQL Server is started with

---

a -x switch which

quote:

---

>disables certain internal statistics. There are many

---

reasons why you do not

quote:

---

>see any performance monitor counters, some of which are

---

detailed below:

quote:

---

>http://support.microsoft.com/default.aspx?scid=kb;en-

---

us;246328

quote:

---

>http://support.microsoft.com/default.aspx?scid=kb;EN-

---

US;127207

quote:

---

>http://support.microsoft.com/default.aspx?scid=kb;EN-

---

US;227662 (old)

quote:

---

>--
>- Anith
>( Please reply to newsgroups only )
>
>.
>

---

sql

Rebuild master.dbo.sysperfinfo

On further study I've found that the sysperfinfo table on
the servers with the missing Performance Condition alert
type is EMPTY. I know that on one server with this
condition I upgraded from Standard Edition to Enterprise
Edition, but don't know what caused the server to lose the
data in this table.
Can anyone tell me how to rebuild the data in this table
safely. I know I can't just copy it from another server.
There's nothing in the BOL on rebuilding it.
Any ideas?
Allen White
Sr. Database Administrator
Advanstar CommunicationsThe system table master..sysperfinfo is a virtual one which is dynamically
maintained by SQL Server. So you cannot manually update them as you wish.
Also, it is possible that the SQL Server is started with a -x switch which
disables certain internal statistics. There are many reasons why you do not
see any performance monitor counters, some of which are detailed below:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246328
http://support.microsoft.com/default.aspx?scid=kb;EN-US;127207
http://support.microsoft.com/default.aspx?scid=kb;EN-US;227662 (old)
--
- Anith
( Please reply to newsgroups only )|||Thanks, Anith,
While these didn't directly apply, I've found that I may
be able to solve the problem using the following statement
run from Command Prompt:
lotctr.exe c:\<SQL Server Install>\Binn\sqlctr.ini
It does require a reboot of the server, and I won't be
able to do that until late tonight, so I'll know tonight
if that solves my problem. I do know that none of the SQL
performance counters are available in Performance Monitor
either.
Allen
>--Original Message--
>The system table master..sysperfinfo is a virtual one
which is dynamically
>maintained by SQL Server. So you cannot manually update
them as you wish.
>Also, it is possible that the SQL Server is started with
a -x switch which
>disables certain internal statistics. There are many
reasons why you do not
>see any performance monitor counters, some of which are
detailed below:
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;246328
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;127207
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;227662 (old)
>--
>- Anith
>( Please reply to newsgroups only )
>
>.
>

Reboot required to revitalize server?

I'm having a problem with my database server whereby performance decreases
after 12-18 hours in production and is only restored after a reboot. [almost
like hitting a wall]
Of course, the memory usage increases throughout the day and eventually
settles with a few hundred MB of free memory. SQL Server seems to top off at
1.75GB memory usage. [3GB total] Processor usage never seems to be a
problem, and there are no problematic table locks at the time a reboot is
necessary.
Can anyone point me in the right direction to begin tracing the problem. I
realize there are a million possibilities as to the cause of the problem,
but an "I'd start here..." suggestion would be helpful.
Thanks in advance.Hi
You don't say what your system is doing or how much load there is on it. If
there are a significant number of inserts/updates your indexes could be
fragmented and/or statistics out of date. Check out DBCC SHOWCONTIG and
sp_updatestats in books online.
John
"Chris" wrote:

> I'm having a problem with my database server whereby performance decreases
> after 12-18 hours in production and is only restored after a reboot. [almo
st
> like hitting a wall]
> Of course, the memory usage increases throughout the day and eventually
> settles with a few hundred MB of free memory. SQL Server seems to top off
at
> 1.75GB memory usage. [3GB total] Processor usage never seems to be a
> problem, and there are no problematic table locks at the time a reboot is
> necessary.
> Can anyone point me in the right direction to begin tracing the problem.
I
> realize there are a million possibilities as to the cause of the problem,
> but an "I'd start here..." suggestion would be helpful.
> Thanks in advance.
>
>|||Thanks, I'll begin my research.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:401CC40F-0E95-4B2B-B18D-4089A531942F@.microsoft.com...
> Hi
> You don't say what your system is doing or how much load there is on it.
> If
> there are a significant number of inserts/updates your indexes could be
> fragmented and/or statistics out of date. Check out DBCC SHOWCONTIG and
> sp_updatestats in books online.
> John
> "Chris" wrote:
>|||Hi
I have been thinking a bit more about this! This sounds more like a lack of
resources. Does stopping/starting SQL Server have the same effect as a
re-boot? This would narrow it down to the process itself!
You may also want to rule out open transactions (DBCC OPENTRAN)
John
"Chris" wrote:

> Thanks, I'll begin my research.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:401CC40F-0E95-4B2B-B18D-4089A531942F@.microsoft.com...
>
>|||I'll try that when/if it happens again. I found that there was some
fragmentation, since my nightly index rebuilding sproc was disabled. Thanks
for the help.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E40523F0-980B-4D0F-BBCE-74D564E2DCE4@.microsoft.com...
> Hi
> I have been thinking a bit more about this! This sounds more like a lack
> of
> resources. Does stopping/starting SQL Server have the same effect as a
> re-boot? This would narrow it down to the process itself!
> You may also want to rule out open transactions (DBCC OPENTRAN)
> John
> "Chris" wrote:
>

reboot problem (not always)

Hi,
I have a server where, when I reboot it, sometimes the sql server 2005
service stop in starting mode and the performance log and alert service in
stopping mode.
this lock the computer from any remote access. (including Terminal server)
I have to do a hard reboot.
and if this doesn't works, I have to reboot again until all the services are
up and running correctly.
any idea of what can cause this lock?
why SQL Server don't starts correctly? and why the performance log service
don't stops?
thanks.
Jerome.JeJe,
Check the Application Event Log for any errors...may lead you to the cause.
HTH
Jerry
"Jeje" <willgart@.hotmail.com> wrote in message
news:OPGi4lafGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a server where, when I reboot it, sometimes the sql server 2005
> service stop in starting mode and the performance log and alert service in
> stopping mode.
> this lock the computer from any remote access. (including Terminal server)
> I have to do a hard reboot.
> and if this doesn't works, I have to reboot again until all the services
> are up and running correctly.
> any idea of what can cause this lock?
> why SQL Server don't starts correctly? and why the performance log service
> don't stops?
> thanks.
> Jerome.
>

reboot problem (not always)

Hi,
I have a server where, when I reboot it, sometimes the sql server 2005
service stop in starting mode and the performance log and alert service in
stopping mode.
this lock the computer from any remote access. (including Terminal server)
I have to do a hard reboot.
and if this doesn't works, I have to reboot again until all the services are
up and running correctly.
any idea of what can cause this lock?
why SQL Server don't starts correctly? and why the performance log service
don't stops?
thanks.
Jerome.JeJe,
Check the Application Event Log for any errors...may lead you to the cause.
HTH
Jerry
"Jeje" <willgart@.hotmail.com> wrote in message
news:OPGi4lafGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a server where, when I reboot it, sometimes the sql server 2005
> service stop in starting mode and the performance log and alert service in
> stopping mode.
> this lock the computer from any remote access. (including Terminal server)
> I have to do a hard reboot.
> and if this doesn't works, I have to reboot again until all the services
> are up and running correctly.
> any idea of what can cause this lock?
> why SQL Server don't starts correctly? and why the performance log service
> don't stops?
> thanks.
> Jerome.
>

Re-boot is OK

Does anyone know ehrn my SQL server is slow then I re-
boot it. Onces I re-boot it, the SQL server performance
become OK(normal). Does anyone know what is the problem
and how to solve it.Hi,
Rebooting is not a permanent solution.
Poor coding techniques & H/W resources not being planned
well for a SQL Box and Insufficient Hardware. Start
working on...
1. Application Tuning .
2. SQL Server Tuning ( not needed in most cases)
3. Hardware Tuning
There is a good book from Microsoft press titled " SQL
Server 2000 Performance Tuning Tech Reference" by Whalen.
Also refer to Refer www.sql-server-performance.com . They
have loads of stuff on performance tuning.
HTH
--
Regards
THIRUMAL REDDY MARAM
Sys Admin / SQL DBA
>--Original Message--
>Does anyone know ehrn my SQL server is slow then I re-
>boot it. Onces I re-boot it, the SQL server performance
>become OK(normal). Does anyone know what is the problem
>and how to solve it.
>.
>|||Hi,
During a problem situation do,
1. Use performance monitor to check the hardware usage,
1. CPU Usage
2. Memory Usage
3. Disk I/o
2. From query analyzer use sp_who to verify any blocked process.
3. Execute a select statment in sysprocess table to identify the
process/user using the resource
select substring(loginame,1,20) as login,cpu ,physical_io ,
memusage from sysprocesses
identify the user and execute a dbcc inputbuffer(spid) to check the
backend procedure / SQL he is executing.
Thanks
Hari
MCDBA
"Thirumal" <anonymous@.discussions.microsoft.com> wrote in message
news:115301c3df4b$ab6a0860$a601280a@.phx.gbl...
> Hi,
> Rebooting is not a permanent solution.
> Poor coding techniques & H/W resources not being planned
> well for a SQL Box and Insufficient Hardware. Start
> working on...
> 1. Application Tuning .
> 2. SQL Server Tuning ( not needed in most cases)
> 3. Hardware Tuning
> There is a good book from Microsoft press titled " SQL
> Server 2000 Performance Tuning Tech Reference" by Whalen.
> Also refer to Refer www.sql-server-performance.com . They
> have loads of stuff on performance tuning.
> HTH
> --
> Regards
> THIRUMAL REDDY MARAM
> Sys Admin / SQL DBA
> >--Original Message--
> >Does anyone know ehrn my SQL server is slow then I re-
> >boot it. Onces I re-boot it, the SQL server performance
> >become OK(normal). Does anyone know what is the problem
> >and how to solve it.
> >.
> >

Reasons for query execution time difference ??

Hi,
The problem faced by me is:
I tried to do a performance test on our product using Load Runner.
We captured the scripts and executed them for single user, 1 iteration.
Time taken by the entire transaction to complete at various attempts:
Attempt 1: 700 ms
Attempt 2: 17 seconds
Attempt 3: 3 seconds
Attempt 4: 18 seconds
Attempt 5: 3 seconds
Note: Before every attempt, I restored the database from a backup and
executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
indexes & statistics are up to date...
There is no difference in the Server anbd Client settings, environments and
the network settings...
Due to this inconsistent behaviour, I could not arrive at any kind of
conclusion about which query to tune/ which is creating the performance
bottle neck...
Interestingly, there are some queries, which always takes more time in all
the attempts and the response time for those queries are propotional to the
total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
Attempt 1 and 9 sec in Attempt 2 and so on...
Moreover, whenever the system response is very poor (Attempt 2 & 4) I could
see queries related to statistics
(SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
[order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
MAXDOP 1)
which are fired internally by the Query Optimizer to decide on the query
plan and these queries seems to take 50% of the transaction time...
Can any one tell me about the possible reasons for the difference in the SQL
Server behaviour and the ways to solve this...
I am using SQL Server 2000 Service Pack 4
Balasubramaniam. M
Associate Consultant
SIEMENS Information Systems Ltd.
Bangalore
India
These queries look like it is updating statistics. Is Auto update statistics
turned on?... If so, try turning it off and repeating the test to see if you
get more uniform results.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Bala" <Bala@.discussions.microsoft.com> wrote in message
news:A7CFC5E3-C966-44B0-904D-121045F86F04@.microsoft.com...
> Hi,
> The problem faced by me is:
> I tried to do a performance test on our product using Load Runner.
> We captured the scripts and executed them for single user, 1 iteration.
> Time taken by the entire transaction to complete at various attempts:
> Attempt 1: 700 ms
> Attempt 2: 17 seconds
> Attempt 3: 3 seconds
> Attempt 4: 18 seconds
> Attempt 5: 3 seconds
> Note: Before every attempt, I restored the database from a backup and
> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
> indexes & statistics are up to date...
> There is no difference in the Server anbd Client settings, environments
and
> the network settings...
> Due to this inconsistent behaviour, I could not arrive at any kind of
> conclusion about which query to tune/ which is creating the performance
> bottle neck...
> Interestingly, there are some queries, which always takes more time in all
> the attempts and the response time for those queries are propotional to
the
> total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
> Attempt 1 and 9 sec in Attempt 2 and so on...
> Moreover, whenever the system response is very poor (Attempt 2 & 4) I
could
> see queries related to statistics
> (SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
> 100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
> WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
> [order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
> MAXDOP 1)
> which are fired internally by the Query Optimizer to decide on the query
> plan and these queries seems to take 50% of the transaction time...
> Can any one tell me about the possible reasons for the difference in the
SQL
> Server behaviour and the ways to solve this...
> I am using SQL Server 2000 Service Pack 4
> --
> Balasubramaniam. M
> Associate Consultant
> SIEMENS Information Systems Ltd.
> Bangalore
> India
>

Reasons for query execution time difference ??

Hi,
The problem faced by me is:
I tried to do a performance test on our product using Load Runner.
We captured the scripts and executed them for single user, 1 iteration.
Time taken by the entire transaction to complete at various attempts:
Attempt 1: 700 ms
Attempt 2: 17 seconds
Attempt 3: 3 seconds
Attempt 4: 18 seconds
Attempt 5: 3 seconds
Note: Before every attempt, I restored the database from a backup and
executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
indexes & statistics are up to date...
There is no difference in the Server anbd Client settings, environments and
the network settings...
Due to this inconsistent behaviour, I could not arrive at any kind of
conclusion about which query to tune/ which is creating the performance
bottle neck...
Interestingly, there are some queries, which always takes more time in all
the attempts and the response time for those queries are propotional to the
total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
Attempt 1 and 9 sec in Attempt 2 and so on...
Moreover, whenever the system response is very poor (Attempt 2 & 4) I could
see queries related to statistics
(SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
[order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
MAXDOP 1)
which are fired internally by the Query Optimizer to decide on the query
plan and these queries seems to take 50% of the transaction time...
Can any one tell me about the possible reasons for the difference in the SQL
Server behaviour and the ways to solve this...
I am using SQL Server 2000 Service Pack 4
--
Balasubramaniam. M
Associate Consultant
SIEMENS Information Systems Ltd.
Bangalore
IndiaThese queries look like it is updating statistics. Is Auto update statistics
turned on?... If so, try turning it off and repeating the test to see if you
get more uniform results.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Bala" <Bala@.discussions.microsoft.com> wrote in message
news:A7CFC5E3-C966-44B0-904D-121045F86F04@.microsoft.com...
> Hi,
> The problem faced by me is:
> I tried to do a performance test on our product using Load Runner.
> We captured the scripts and executed them for single user, 1 iteration.
> Time taken by the entire transaction to complete at various attempts:
> Attempt 1: 700 ms
> Attempt 2: 17 seconds
> Attempt 3: 3 seconds
> Attempt 4: 18 seconds
> Attempt 5: 3 seconds
> Note: Before every attempt, I restored the database from a backup and
> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
> indexes & statistics are up to date...
> There is no difference in the Server anbd Client settings, environments
and
> the network settings...
> Due to this inconsistent behaviour, I could not arrive at any kind of
> conclusion about which query to tune/ which is creating the performance
> bottle neck...
> Interestingly, there are some queries, which always takes more time in all
> the attempts and the response time for those queries are propotional to
the
> total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
> Attempt 1 and 9 sec in Attempt 2 and so on...
> Moreover, whenever the system response is very poor (Attempt 2 & 4) I
could
> see queries related to statistics
> (SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELECT TOP
> 100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
> WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
> [order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE,
> MAXDOP 1)
> which are fired internally by the Query Optimizer to decide on the query
> plan and these queries seems to take 50% of the transaction time...
> Can any one tell me about the possible reasons for the difference in the
SQL
> Server behaviour and the ways to solve this...
> I am using SQL Server 2000 Service Pack 4
> --
> Balasubramaniam. M
> Associate Consultant
> SIEMENS Information Systems Ltd.
> Bangalore
> India
>

Reasons for query execution time difference ??

Hi,
The problem faced by me is:
I tried to do a performance test on our product using Load Runner.
We captured the scripts and executed them for single user, 1 iteration.
Time taken by the entire transaction to complete at various attempts:
Attempt 1: 700 ms
Attempt 2: 17 seconds
Attempt 3: 3 seconds
Attempt 4: 18 seconds
Attempt 5: 3 seconds
Note: Before every attempt, I restored the database from a backup and
executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
indexes & statistics are up to date...
There is no difference in the Server anbd Client settings, environments and
the network settings...
Due to this inconsistent behaviour, I could not arrive at any kind of
conclusion about which query to tune/ which is creating the performance
bottle neck...
Interestingly, there are some queries, which always takes more time in all
the attempts and the response time for those queries are propotional to the
total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
Attempt 1 and 9 sec in Attempt 2 and so on...
Moreover, whenever the system response is very poor (Attempt 2 & 4) I could
see queries related to statistics
(SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SELEC
T TOP
100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services]
WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
[order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_
QUEUE,
MAXDOP 1)
which are fired internally by the Query Optimizer to decide on the query
plan and these queries seems to take 50% of the transaction time...
Can any one tell me about the possible reasons for the difference in the SQL
Server behaviour and the ways to solve this...
I am using SQL Server 2000 Service Pack 4
Balasubramaniam. M
Associate Consultant
SIEMENS Information Systems Ltd.
Bangalore
IndiaThese queries look like it is updating statistics. Is Auto update statistics
turned on?... If so, try turning it off and repeating the test to see if you
get more uniform results.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Bala" <Bala@.discussions.microsoft.com> wrote in message
news:A7CFC5E3-C966-44B0-904D-121045F86F04@.microsoft.com...
> Hi,
> The problem faced by me is:
> I tried to do a performance test on our product using Load Runner.
> We captured the scripts and executed them for single user, 1 iteration.
> Time taken by the entire transaction to complete at various attempts:
> Attempt 1: 700 ms
> Attempt 2: 17 seconds
> Attempt 3: 3 seconds
> Attempt 4: 18 seconds
> Attempt 5: 3 seconds
> Note: Before every attempt, I restored the database from a backup and
> executed "DBCC DBREINDEX" and "UPDATE STATISTICS" to ensure that all the
> indexes & statistics are up to date...
> There is no difference in the Server anbd Client settings, environments
and
> the network settings...
> Due to this inconsistent behaviour, I could not arrive at any kind of
> conclusion about which query to tune/ which is creating the performance
> bottle neck...
> Interestingly, there are some queries, which always takes more time in all
> the attempts and the response time for those queries are propotional to
the
> total time taken by the entire transaction... i.e. Query 1 takes 93 ms in
> Attempt 1 and 9 sec in Attempt 2 and so on...
> Moreover, whenever the system response is very poor (Attempt 2 & 4) I
could
> see queries related to statistics
> (SELECT statman([order_id],[order_no],@.PSTATMAN) FROM (SEL
ECT TOP
> 100 PERCENT [order_id],[order_no] FROM [dbo].[ord_services
]
> WITH(READUNCOMMITTED,SAMPLE 1.863186e+000 PERCENT) ORDER BY
> [order_id],[order_no]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZE
R_QUEUE,
> MAXDOP 1)
> which are fired internally by the Query Optimizer to decide on the query
> plan and these queries seems to take 50% of the transaction time...
> Can any one tell me about the possible reasons for the difference in the
SQL
> Server behaviour and the ways to solve this...
> I am using SQL Server 2000 Service Pack 4
> --
> Balasubramaniam. M
> Associate Consultant
> SIEMENS Information Systems Ltd.
> Bangalore
> India
>

Reason for multiple filegroups/ files?

SQL2K
SP4
If my data is spread accross a Raid array, is there any real reason to use
multiple filegroups/ files? Will I see any performance benefit? Will I only
see it on VLDB's. If I do use them, is there a good way to group them
logically?
TIA, ChrisRChrisR
>Will I see any performance benefit?
Probably, if you put the filegroup on another PHYSICAL disk
>Will I only
> see it on VLDB's. If I do use them, is there a good way to group them
> logically?
Yes , it makes sence only for VLDB
Look, you will have to investigate it carefuly, I mean how you are going
to restore in case of failure or somethinmg like that , it is not so easy
to maintain and I have my doubt that nowadays we can really gain
perfiomance benefit from creating a new file group . Perhaps you might
think about adding a new datafile .NDF and seeing how it works
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%23hGlHLPKHHA.1912@.TK2MSFTNGP03.phx.gbl...
> SQL2K
> SP4
> If my data is spread accross a Raid array, is there any real reason to use
> multiple filegroups/ files? Will I see any performance benefit? Will I
> only
> see it on VLDB's. If I do use them, is there a good way to group them
> logically?
> TIA, ChrisR
>|||Add on ; please take a look into below URL's. But to make use of the full
advantage you may need multiple disk controller as Uri suggested.
http://www.databasejournal.com/features/mssql/article.php/1439801
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=42&rl=1
Thanks
Hari
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u4aAfSPKHHA.3936@.TK2MSFTNGP02.phx.gbl...
> ChrisR
>>Will I see any performance benefit?
> Probably, if you put the filegroup on another PHYSICAL disk
>>Will I only
>> see it on VLDB's. If I do use them, is there a good way to group them
>> logically?
> Yes , it makes sence only for VLDB
>
> Look, you will have to investigate it carefuly, I mean how you are going
> to restore in case of failure or somethinmg like that , it is not so easy
> to maintain and I have my doubt that nowadays we can really gain
> perfiomance benefit from creating a new file group . Perhaps you might
> think about adding a new datafile .NDF and seeing how it works
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:%23hGlHLPKHHA.1912@.TK2MSFTNGP03.phx.gbl...
>> SQL2K
>> SP4
>> If my data is spread accross a Raid array, is there any real reason to
>> use
>> multiple filegroups/ files? Will I see any performance benefit? Will I
>> only
>> see it on VLDB's. If I do use them, is there a good way to group them
>> logically?
>> TIA, ChrisR
>>
>|||At the very least I always recommend creating one secondary filegroup to
place all the user objects and leave just the system objects in the primary
filegroup. This makes it cleaner and easier to do piecemeal restores if
needed. From there it depends on how you use your data and what your
hardware config is like. If you want to separate different groups of objects
such as Non-clustered indexes from the Clustered / tables onto different
drive arrays you need at least two filegroups. If you know this is something
you may do in the future but aren't now it still may make sense to separate
them into multiple FG's to make the move easier down the road. If the db is
relatively small, your hardware is decent you may never see any advantages
of multiple files or filegroups.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%23hGlHLPKHHA.1912@.TK2MSFTNGP03.phx.gbl...
> SQL2K
> SP4
> If my data is spread accross a Raid array, is there any real reason to use
> multiple filegroups/ files? Will I see any performance benefit? Will I
> only
> see it on VLDB's. If I do use them, is there a good way to group them
> logically?
> TIA, ChrisR
>|||Thanks everyone, this is all as I suspected.
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%23hGlHLPKHHA.1912@.TK2MSFTNGP03.phx.gbl...
> SQL2K
> SP4
> If my data is spread accross a Raid array, is there any real reason to use
> multiple filegroups/ files? Will I see any performance benefit? Will I
only
> see it on VLDB's. If I do use them, is there a good way to group them
> logically?
> TIA, ChrisR
>

really strange performance problem

hey
I have a sql2000 server SP3 and I mgrate a databse from SQL7. I run teo basically equal select: he first one 2 seconds the second one 58 minutes.....

select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,20)
not in (select substring(col018_dominio,1,20)
from iis_uu_diario_resume where substring(col018_dominio,1,20)
= substring(uu_resume_ses_dummy_dummy.dominio,1,20))
option (maxdop 1)

select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,30)
not in (select substring(col018_dominio,1,30)
from iis_uu_diario_resume where substring(col018_dominio,1,30)
= substring(uu_resume_ses_dummy_dummy.dominio,1,30))
option (maxdop 1)

the only differencei s that the substring range: 20 to 30. Notice that the limit is not fixed. SOmetimes the jump in execution time happende when I change from 90 top 91......
really I dont' know. (Fields ara varchar(90) but it was the same with varchar(255). the PLAN are exactly the same. in the second case the CPU was 50% fror 58 minutes fixed.
thanks for all the help (really needed)essentialy, for every record in the "uu_resume_ses_dummy_dummy" table you are looking at every record in the "iis_uu_diario_resume" table Using only one processor.

Since you will be looking at every record you have the potential of being delayed by locks, index leaf splits and other traffic. What happens if you run these selects on a quiet system. I suspect the time diffrence is small.|||I was runnng these queries both in a "busy" server (4 cpu, 4Gb RAM) and on a really quiet server (2 CPU, 4GB RAM) with same timing. Quiet server means that basically % of CPU without that select was netween 0 and 5%|||forgot things.

1) same times without the option of processing in one CPU only
2) both table are index on the specific fields.

what you say is ok. problem is:why almost identical queries have such a big big big difference in execution time?

Really need to use this query - but it has a sub-query in it...

Hi all,

I have the following query in my application and really need to run this as it will help my application in terms of performance and efficiency. I need to get the record that was last entered into the database, and the following seems to do it in SQL Server Desktop, but not in SQL Server Mobile (as it has a Sub-Query). At the moment I have a query that returns all rows (in order) that have a DateCompleted column entered and then just skimming the top row. So i'm infact bringing back hundreds of rows, just so that I can get the first record - completed waste resources if you ask me.

Does anyone know how I can tackle this issue?

SELECT *
FROM tbl_NSP_AnswerSet
WHERE (DateCompleted IS NOT NULL)
AND DateCompleted = (SELECT MAX(DateCompleted) FROM tbl_NSP_AnswerSet)
ORDER BY DateCompleted DES

Thanks

Tryst

Hi

Create a User Define function which will calculate for you the Max datecomleted for that table and then in the where clause compaire the datecompleted.

CREATE FUNCTION GetMaxDateCompleted()
RETURNS int
AS
BEGIN
(SELECT MAX(DateCompleted) FROM tbl_NSP_AnswerSet)
END

AND

SELECT *
FROM tbl_NSP_AnswerSet
WHERE (DateCompleted IS NOT NULL)
AND DateCompleted = dbo.GetMaxDateCompleted()
ORDER BY DateCompleted DES

|||But SQL Server mobile doesn't support Functions (UDF's), does it?

Tryst

Real Impact of MS Performance Counters

; have been collecting information about 20 performance counters (memory, IO,
cpu, SQL) that refresh every 15 seconds; would that have any performance hit
in the server? what are best practices when collecting information via
performance counters?
ThanksCollecting these counter values at a 15-second interval should have
absolutely no performance impact. (If on the other hand for however small
chance it does have an impact, your machine is greatly under-spec'ed). No, I
wouldn't worry about any performance impact at all.
Linchi
"Carlos" wrote:
> ; have been collecting information about 20 performance counters (memory, IO,
> cpu, SQL) that refresh every 15 seconds; would that have any performance hit
> in the server? what are best practices when collecting information via
> performance counters?
> Thanks