dbcc freeproccache only solution??

Recently upgranded to SQL2005 sp1
Daily we have times of very poor performance. We tracked down to full table
scans occuring, indexes not used.
Perform a dbcc freeproccache to solve. Any idea why this is occurring?
Happening more often since upgrade - did notice same issue when on sql2000...
Any ideas?
tia
chris
On Fri, 11 May 2007 15:55:00 -0700, Chris
<Chris@.discussions.microsoft.com> wrote:

>Recently upgranded to SQL2005 sp1
>Daily we have times of very poor performance. We tracked down to full table
>scans occuring, indexes not used.
>Perform a dbcc freeproccache to solve. Any idea why this is occurring?
>Happening more often since upgrade - did notice same issue when on sql2000...
>Any ideas?
Many ideas.
But they all start with isolating the problem code, the DDL, and the
stats involved.
J.
|||Sounds like parameter sniffing issues. Did you update the stats after the
upgrade? Have a look at these:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
My guess is that most likely your stored proc query plans are becoming
invalid.
A simple test would be to find one called a lot and do a recompile on it and
see if the performance gets better.
If it does, there's a few things to loook at.
Are you doing a lot of updates/inserts/deletes that invalidate the stats for
your indices? If so, you can update stats more often (among other things).
Also lok for stuff like a "IF @.FOO then X else Y" Typically the first time
the procedure is called, a particular path is followed (say @.FOO Is true,
then X is followed.) This is the query plan that will be cashed. If later
the call has @.FOO is false and Y is followed the query plan will be invalid.
So that's something else to look at.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

dbcc freeproccache only solution??

Recently upgranded to SQL2005 sp1
Daily we have times of very poor performance. We tracked down to full table
scans occuring, indexes not used.
Perform a dbcc freeproccache to solve. Any idea why this is occurring?
Happening more often since upgrade - did notice same issue when on sql2000..
.
Any ideas?
tia
chrisOn Fri, 11 May 2007 15:55:00 -0700, Chris
<Chris@.discussions.microsoft.com> wrote:

>Recently upgranded to SQL2005 sp1
>Daily we have times of very poor performance. We tracked down to full table
>scans occuring, indexes not used.
>Perform a dbcc freeproccache to solve. Any idea why this is occurring?
>Happening more often since upgrade - did notice same issue when on sql2000.
.
>Any ideas?
Many ideas.
But they all start with isolating the problem code, the DDL, and the
stats involved.
J.|||Sounds like parameter sniffing issues. Did you update the stats after the
upgrade? Have a look at these:
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx
http://www.microsoft.com/technet/pr...005/recomp.mspx
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
My guess is that most likely your stored proc query plans are becoming
invalid.
A simple test would be to find one called a lot and do a recompile on it and
see if the performance gets better.
If it does, there's a few things to loook at.
Are you doing a lot of updates/inserts/deletes that invalidate the stats for
your indices? If so, you can update stats more often (among other things).
Also lok for stuff like a "IF @.FOO then X else Y" Typically the first time
the procedure is called, a particular path is followed (say @.FOO Is true,
then X is followed.) This is the query plan that will be cashed. If later
the call has @.FOO is false and Y is followed the query plan will be invalid.
So that's something else to look at.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

dbcc freeproccache only solution??

Recently upgranded to SQL2005 sp1
Daily we have times of very poor performance. We tracked down to full table
scans occuring, indexes not used.
Perform a dbcc freeproccache to solve. Any idea why this is occurring?
Happening more often since upgrade - did notice same issue when on sql2000...
Any ideas?
tia
chrisOn Fri, 11 May 2007 15:55:00 -0700, Chris
<Chris@.discussions.microsoft.com> wrote:
>Recently upgranded to SQL2005 sp1
>Daily we have times of very poor performance. We tracked down to full table
>scans occuring, indexes not used.
>Perform a dbcc freeproccache to solve. Any idea why this is occurring?
>Happening more often since upgrade - did notice same issue when on sql2000...
>Any ideas?
Many ideas.
But they all start with isolating the problem code, the DDL, and the
stats involved.
J.|||Sounds like parameter sniffing issues. Did you update the stats after the
upgrade? Have a look at these:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
My guess is that most likely your stored proc query plans are becoming
invalid.
A simple test would be to find one called a lot and do a recompile on it and
see if the performance gets better.
If it does, there's a few things to loook at.
Are you doing a lot of updates/inserts/deletes that invalidate the stats for
your indices? If so, you can update stats more often (among other things).
Also lok for stuff like a "IF @.FOO then X else Y" Typically the first time
the procedure is called, a particular path is followed (say @.FOO Is true,
then X is followed.) This is the query plan that will be cashed. If later
the call has @.FOO is false and Y is followed the query plan will be invalid.
So that's something else to look at.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

DBCC DBREINDEX and diskspace

I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?
It will fail and the index rebuild will be rolled back. You need 1.2X the
size of the data in your table in free space in order to use DBREINDEX. If
you cannot get more space, consider using DBCC INDEXDEFRAG.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.co m...
I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?
|||This helps me out alot:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure admin_DBCCDBReindex
as
declare @.Tablename varchar(128),@.db varchar(128),@.exec varchar(256)
set nocount on
set @.db = (select db_name())
declare @.Table table(TableName varchar(128))
insert into @.Table
select so.name
from sysobjects so
where so.name not like 'sys%' and
so.name not like 'dt%' and
so.name not like 'tmp%' and
so.xtype = 'u'
order by so.name
declare mycur cursor for (select TableName from @.Table)
open mycur
fetch next from mycur into @.TableName
while @.@.fetch_status = 0
begin
set @.exec = 'dbcc dbreindex (''' + @.db + '.' + 'dbo.' + @.TableName + ''')'
exec (@.exec)
BACKUP LOG [tmpReindex] TO [tmp]
WITH INIT , NOUNLOAD , NAME = N'tmp log backup', NOSKIP , STATS = 10,
NOFORMAT
fetch next from mycur into @.TableName
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.co m...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?
|||As Tom suggests, consider DBCC INDEXDEFRAG. There's a whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
that explains the difference between the two, how to choose between them,
performance comparison etc.
Let us know if you have any more questions after reading the whitepaper.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.co m...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?

DBCC DBREINDEX and diskspace

I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?It will fail and the index rebuild will be rolled back. You need 1.2X the
size of the data in your table in free space in order to use DBREINDEX. If
you cannot get more space, consider using DBCC INDEXDEFRAG.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?|||This helps me out alot:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure admin_DBCCDBReindex
as
declare @.Tablename varchar(128),@.db varchar(128),@.exec varchar(256)
set nocount on
set @.db = (select db_name())
declare @.Table table(TableName varchar(128))
insert into @.Table
select so.name
from sysobjects so
where so.name not like 'sys%' and
so.name not like 'dt%' and
so.name not like 'tmp%' and
so.xtype = 'u'
order by so.name
declare mycur cursor for (select TableName from @.Table)
open mycur
fetch next from mycur into @.TableName
while @.@.fetch_status = 0
begin
set @.exec = 'dbcc dbreindex (''' + @.db + '.' + 'dbo.' + @.TableName + ''')'
exec (@.exec)
BACKUP LOG [tmpReindex] TO [tmp]
WITH INIT , NOUNLOAD , NAME = N'tmp log backup', NOSKIP , STATS = 10,
NOFORMAT
fetch next from mycur into @.TableName
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?|||As Tom suggests, consider DBCC INDEXDEFRAG. There's a whitepaper at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
that explains the difference between the two, how to choose between them,
performance comparison etc.
Let us know if you have any more questions after reading the whitepaper.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?

DBCC DBREINDEX and diskspace

I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?It will fail and the index rebuild will be rolled back. You need 1.2X the
size of the data in your table in free space in order to use DBREINDEX. If
you cannot get more space, consider using DBCC INDEXDEFRAG.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?|||This helps me out alot:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure admin_DBCCDBReindex
as
declare @.Tablename varchar(128),@.db varchar(128),@.exec varchar(256)
set nocount on
set @.db = (select db_name())
declare @.Table table(TableName varchar(128))
insert into @.Table
select so.name
from sysobjects so
where so.name not like 'sys%' and
so.name not like 'dt%' and
so.name not like 'tmp%' and
so.xtype = 'u'
order by so.name
declare mycur cursor for (select TableName from @.Table)
open mycur
fetch next from mycur into @.TableName
while @.@.fetch_status = 0
begin
set @.exec = 'dbcc dbreindex (''' + @.db + '.' + 'dbo.' + @.TableName + ''')'
exec (@.exec)
BACKUP LOG [tmpReindex] TO [tmp]
WITH INIT , NOUNLOAD , NAME = N'tmp log backup', NOSKIP , STATS = 10,
NOFORMAT
fetch next from mycur into @.TableName
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?|||As Tom suggests, consider DBCC INDEXDEFRAG. There's a whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
that explains the difference between the two, how to choose between them,
performance comparison etc.
Let us know if you have any more questions after reading the whitepaper.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?