Create a counter

I have to create a counter (it is possible to increase or decrease).
It is not record ID, but a part of an automatic generated name.
In case of intensive concurrent use I have a deadlock.
The steps:
1. Launch transaction with isolation level REPEATABLE READ (or SERIALIZABLE
in respect of finish are equal)
2. open the recordset
3. get old number
4. set new number
5. update
6. close record set
7. commit
If I run my program on three computer on the same database a deadlock occur.
How can I isolate the sessions without exceptions?
How can I qued up my clients for its new counter?
I am glad of any idea.
Regards,
Imre Ament"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
>
Yep that's a recipe for a deadlock. Each session gets and holds a read lock
on the table, then each tries to escalate it to an update lock. You need to
get the update lock when you first open the recordset. Then your clients
will serialize properly.
To get an exclusive or update lock with a select statement look at the
UPDLOCK and XLOCK hints.
David|||Never do something like this from the client, always use a stored procedure
and minimize the locking. How about doing something like this instead:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
> I am glad of any idea.
> Regards,
> Imre Ament
>sql

Create a counter

I have to create a counter (it is possible to increase or decrease).
It is not record ID, but a part of an automatic generated name.
In case of intensive concurrent use I have a deadlock.
The steps:
1. Launch transaction with isolation level REPEATABLE READ (or SERIALIZABLE
in respect of finish are equal)
2. open the recordset
3. get old number
4. set new number
5. update
6. close record set
7. commit
If I run my program on three computer on the same database a deadlock occur.
How can I isolate the sessions without exceptions?
How can I qued up my clients for its new counter?
I am glad of any idea.
Regards,
Imre Ament
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
>
Yep that's a recipe for a deadlock. Each session gets and holds a read lock
on the table, then each tries to escalate it to an update lock. You need to
get the update lock when you first open the recordset. Then your clients
will serialize properly.
To get an exclusive or update lock with a select statement look at the
UPDLOCK and XLOCK hints.
David
|||Never do something like this from the client, always use a stored procedure
and minimize the locking. How about doing something like this instead:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
> I am glad of any idea.
> Regards,
> Imre Ament
>

cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec

We are seeing a 29% increase in the cpu of the sql process in SQL2005 over a
5 hour perfmorance test. The application code is the same and the actions
performed during the test are the same. The increase is compared to SQL2000.
The db server is the same and was upgraded from SQL2000.
Disk Configuration: SCSI RAID 5, 3 Partitions
RAM: 3.75 GB of RAM
CPU: Quad Processor, 2.7Ghz
OS: Windows2003 SP1 Enterprise
* Perfmon shows disk read/sec and disk write/sec fairly close between
SQL2000 and SQL2005
Specifically, perfmon shows increased FullScans/sec and decreased
IndexSearches/sec.
From perfmon
FullScan/sec (avg):
2000: 6.292
2005: 54.338
IndexSearches/sec (avg):
2000: 3455.942
2005: 1441.712
Any comments or suggestions would be greatly appreciated. Thanks.Did you update the stats after you brought over the db?
Andrew J. Kelly SQL MVP
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||We saw even worse behavior. But I fixed it with these 3 things and it
resolved most of the slowness (not sure if you need one or both)
-- to update all stats
exec sp_updatestats
--rebuild all indexes
dbcc dbreindex('tablename') for each table
--set all sps to recompile on next execute
exec sp_recompile 'sp_name' for every stored procedure in your database
I identified a few other queries that just had to be re-written. They
seemed to always involve subqueries using "where not in (select ? from...)
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||I did not manually execute such a step. And if this wasnt done automatically
with the upgrade to SQL2005 then it was not done.
Is this the link for this?
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
or are there more specifc instructions you can point me to?
Thanks
"Andrew J. Kelly" wrote:
> Did you update the stats after you brought over the db?
>
> --
> Andrew J. Kelly SQL MVP
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> > We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> > a
> > 5 hour perfmorance test. The application code is the same and the actions
> > performed during the test are the same. The increase is compared to
> > SQL2000.
> >
> > The db server is the same and was upgraded from SQL2000.
> > Disk Configuration: SCSI RAID 5, 3 Partitions
> > RAM: 3.75 GB of RAM
> > CPU: Quad Processor, 2.7Ghz
> > OS: Windows2003 SP1 Enterprise
> >
> > * Perfmon shows disk read/sec and disk write/sec fairly close between
> > SQL2000 and SQL2005
> >
> > Specifically, perfmon shows increased FullScans/sec and decreased
> > IndexSearches/sec.
> >
> > From perfmon
> > FullScan/sec (avg):
> > 2000: 6.292
> > 2005: 54.338
> >
> > IndexSearches/sec (avg):
> > 2000: 3455.942
> > 2005: 1441.712
> >
> > Any comments or suggestions would be greatly appreciated. Thanks.
>
>|||No this is not done by the upgrade process. The link tells you what stats
do. To update them either run sp_updatestats to update all stats or if you
haven't run DBCC DBREINDEX in a while try running that on all tables.
--
Andrew J. Kelly SQL MVP
"George Koulis" <GeorgeKoulis@.discussions.microsoft.com> wrote in message
news:071092A9-6057-47BB-A0A1-3EC8061D088C@.microsoft.com...
>I did not manually execute such a step. And if this wasnt done
>automatically
> with the upgrade to SQL2005 then it was not done.
> Is this the link for this?
> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
> or are there more specifc instructions you can point me to?
> Thanks
>
> "Andrew J. Kelly" wrote:
>> Did you update the stats after you brought over the db?
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in
>> message
>> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>> > We are seeing a 29% increase in the cpu of the sql process in SQL2005
>> > over
>> > a
>> > 5 hour perfmorance test. The application code is the same and the
>> > actions
>> > performed during the test are the same. The increase is compared to
>> > SQL2000.
>> >
>> > The db server is the same and was upgraded from SQL2000.
>> > Disk Configuration: SCSI RAID 5, 3 Partitions
>> > RAM: 3.75 GB of RAM
>> > CPU: Quad Processor, 2.7Ghz
>> > OS: Windows2003 SP1 Enterprise
>> >
>> > * Perfmon shows disk read/sec and disk write/sec fairly close between
>> > SQL2000 and SQL2005
>> >
>> > Specifically, perfmon shows increased FullScans/sec and decreased
>> > IndexSearches/sec.
>> >
>> > From perfmon
>> > FullScan/sec (avg):
>> > 2000: 6.292
>> > 2005: 54.338
>> >
>> > IndexSearches/sec (avg):
>> > 2000: 3455.942
>> > 2005: 1441.712
>> >
>> > Any comments or suggestions would be greatly appreciated. Thanks.
>>|||Just FYI. There is no need to do all three steps. When you run DBCC
DBREINDEX on a table it will not only rebuild the index but update the stats
as well. Each time the stats are updated any query plans that are associated
with these stats will automatically be marked for recompile the next time
they get run. So in a sense you are tripling your efforts by doing all three
manually.
--
Andrew J. Kelly SQL MVP
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>> We are seeing a 29% increase in the cpu of the sql process in SQL2005
>> over a
>> 5 hour perfmorance test. The application code is the same and the actions
>> performed during the test are the same. The increase is compared to
>> SQL2000.
>> The db server is the same and was upgraded from SQL2000.
>> Disk Configuration: SCSI RAID 5, 3 Partitions
>> RAM: 3.75 GB of RAM
>> CPU: Quad Processor, 2.7Ghz
>> OS: Windows2003 SP1 Enterprise
>> * Perfmon shows disk read/sec and disk write/sec fairly close between
>> SQL2000 and SQL2005
>> Specifically, perfmon shows increased FullScans/sec and decreased
>> IndexSearches/sec.
>> From perfmon
>> FullScan/sec (avg):
>> 2000: 6.292
>> 2005: 54.338
>> IndexSearches/sec (avg):
>> 2000: 3455.942
>> 2005: 1441.712
>> Any comments or suggestions would be greatly appreciated. Thanks.
>|||Whenever you upgrade SQL, whether it is just a patch or a Service Pack, and
sqlservr.exe is updated, and especially a version update you should try and
rebuild indexes and update stats. If sqlservr.exe changes then the optimizer
most probably has changed and will not work exactly the same as it did
before the upgrade.
Chris
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>> We are seeing a 29% increase in the cpu of the sql process in SQL2005
>> over a
>> 5 hour perfmorance test. The application code is the same and the actions
>> performed during the test are the same. The increase is compared to
>> SQL2000.
>> The db server is the same and was upgraded from SQL2000.
>> Disk Configuration: SCSI RAID 5, 3 Partitions
>> RAM: 3.75 GB of RAM
>> CPU: Quad Processor, 2.7Ghz
>> OS: Windows2003 SP1 Enterprise
>> * Perfmon shows disk read/sec and disk write/sec fairly close between
>> SQL2000 and SQL2005
>> Specifically, perfmon shows increased FullScans/sec and decreased
>> IndexSearches/sec.
>> From perfmon
>> FullScan/sec (avg):
>> 2000: 6.292
>> 2005: 54.338
>> IndexSearches/sec (avg):
>> 2000: 3455.942
>> 2005: 1441.712
>> Any comments or suggestions would be greatly appreciated. Thanks.
>

cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec

We are seeing a 29% increase in the cpu of the sql process in SQL2005 over a
5 hour perfmorance test. The application code is the same and the actions
performed during the test are the same. The increase is compared to SQL2000.
The db server is the same and was upgraded from SQL2000.
Disk Configuration: SCSI RAID 5, 3 Partitions
RAM: 3.75 GB of RAM
CPU: Quad Processor, 2.7Ghz
OS: Windows2003 SP1 Enterprise
* Perfmon shows disk read/sec and disk write/sec fairly close between
SQL2000 and SQL2005
Specifically, perfmon shows increased FullScans/sec and decreased
IndexSearches/sec.
From perfmon
FullScan/sec (avg):
2000: 6.292
2005: 54.338
IndexSearches/sec (avg):
2000: 3455.942
2005: 1441.712
Any comments or suggestions would be greatly appreciated. Thanks.
Did you update the stats after you brought over the db?
Andrew J. Kelly SQL MVP
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.
|||We saw even worse behavior. But I fixed it with these 3 things and it
resolved most of the slowness (not sure if you need one or both)
-- to update all stats
exec sp_updatestats
--rebuild all indexes
dbcc dbreindex('tablename') for each table
--set all sps to recompile on next execute
exec sp_recompile 'sp_name' for every stored procedure in your database
I identified a few other queries that just had to be re-written. They
seemed to always involve subqueries using "where not in (select ? from...)
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.
|||Just FYI. There is no need to do all three steps. When you run DBCC
DBREINDEX on a table it will not only rebuild the index but update the stats
as well. Each time the stats are updated any query plans that are associated
with these stats will automatically be marked for recompile the next time
they get run. So in a sense you are tripling your efforts by doing all three
manually.
Andrew J. Kelly SQL MVP
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>
|||Whenever you upgrade SQL, whether it is just a patch or a Service Pack, and
sqlservr.exe is updated, and especially a version update you should try and
rebuild indexes and update stats. If sqlservr.exe changes then the optimizer
most probably has changed and will not work exactly the same as it did
before the upgrade.
Chris
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>

cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec

We are seeing a 29% increase in the cpu of the sql process in SQL2005 over a
5 hour perfmorance test. The application code is the same and the actions
performed during the test are the same. The increase is compared to SQL2000
.
The db server is the same and was upgraded from SQL2000.
Disk Configuration: SCSI RAID 5, 3 Partitions
RAM: 3.75 GB of RAM
CPU: Quad Processor, 2.7Ghz
OS: Windows2003 SP1 Enterprise
* Perfmon shows disk read/sec and disk write/sec fairly close between
SQL2000 and SQL2005
Specifically, perfmon shows increased FullScans/sec and decreased
IndexSearches/sec.
From perfmon
FullScan/sec (avg):
2000: 6.292
2005: 54.338
IndexSearches/sec (avg):
2000: 3455.942
2005: 1441.712
Any comments or suggestions would be greatly appreciated. Thanks.Did you update the stats after you brought over the db?
Andrew J. Kelly SQL MVP
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||We saw even worse behavior. But I fixed it with these 3 things and it
resolved most of the slowness (not sure if you need one or both)
-- to update all stats
exec sp_updatestats
--rebuild all indexes
dbcc dbreindex('tablename') for each table
--set all sps to recompile on next execute
exec sp_recompile 'sp_name' for every stored procedure in your database
I identified a few other queries that just had to be re-written. They
seemed to always involve subqueries using "where not in (select ? from...)
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||Just FYI. There is no need to do all three steps. When you run DBCC
DBREINDEX on a table it will not only rebuild the index but update the stats
as well. Each time the stats are updated any query plans that are associated
with these stats will automatically be marked for recompile the next time
they get run. So in a sense you are tripling your efforts by doing all three
manually.
Andrew J. Kelly SQL MVP
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>|||Whenever you upgrade SQL, whether it is just a patch or a Service Pack, and
sqlservr.exe is updated, and especially a version update you should try and
rebuild indexes and update stats. If sqlservr.exe changes then the optimizer
most probably has changed and will not work exactly the same as it did
before the upgrade.
Chris
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>