I have OLTP database, 24x5. The database used by application. The application
have new version like every half a year. During deployment of new version,
old database renamed, creating new database with same structure and move
data from old database to new with DTS. After deployment of new version I see
performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
return back to not so good state. I guess it may be caused by fragmentation?
Every week runs dbcc dbreindex, to defragment database. Database file and
log file are on separate LUNs, each RAID10. Storage device - EMC CX300. No
any other files on same drive where database files are located. Most of
tables have clustered indices, but not all tables have it - our benchmark
tests showed what for some tables better to not have clustered index. SQL2k
SP3.
Why is such performance degradation? What can be done to fix it?
What about statistics, are those kept up to date?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Hi
Your description seems to follow the sort of pattern associated with
fragmented indexes, but as you rebuild them this should not happen and you
should see an improvement after the rebuild that decays over time. Try using
DBCC SHOWCONTIG to monitor the index fragmentation, you may also want to
update the statistics more often. Check the query plans to see if they differ
when the performance is slower.
You will need to know how your LUNs map to physical discs to determine if
there is any contention, but this should not follow the performance patterns
you have described. Check out physical disc fragmentation and defragment if
necessary.
You may want to also look at the blocker script
http://support.microsoft.com/kb/271509 and monitor performance with perfmon
and compare the results when it is working well against when it isn't.
You don't say if your system databases or tempdb are located on the same
discs, if they are you may want to consider moving them. That may not solve
your problem but it would be good practices.
John
"andsm" wrote:
> I have OLTP database, 24x5. The database used by application. The application
> have new version like every half a year. During deployment of new version,
> old database renamed, creating new database with same structure and move
> data from old database to new with DTS. After deployment of new version I see
> performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
> return back to not so good state. I guess it may be caused by fragmentation?
> Every week runs dbcc dbreindex, to defragment database. Database file and
> log file are on separate LUNs, each RAID10. Storage device - EMC CX300. No
> any other files on same drive where database files are located. Most of
> tables have clustered indices, but not all tables have it - our benchmark
> tests showed what for some tables better to not have clustered index. SQL2k
> SP3.
> Why is such performance degradation? What can be done to fix it?
|||I'd look into specificallhy what is going slow. Are there specific
tranactions that you run frequently that become slow?
Can you reduce the number of indexes on the biggest tables?
Showing posts with label version. Show all posts
Showing posts with label version. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
Degradation of performance with time
I have OLTP database, 24x5. The database used by application. The applicatio
n
have new version like every half a year. During deployment of new version,
old database renamed, creating new database with same structure and move
data from old database to new with DTS. After deployment of new version I se
e
performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
return back to not so good state. I guess it may be caused by fragmentation?
Every week runs dbcc dbreindex, to defragment database. Database file and
log file are on separate LUNs, each RAID10. Storage device - EMC CX300. No
any other files on same drive where database files are located. Most of
tables have clustered indices, but not all tables have it - our benchmark
tests showed what for some tables better to not have clustered index. SQL2k
SP3.
Why is such performance degradation? What can be done to fix it?What about statistics, are those kept up to date?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi
Your description seems to follow the sort of pattern associated with
fragmented indexes, but as you rebuild them this should not happen and you
should see an improvement after the rebuild that decays over time. Try using
DBCC SHOWCONTIG to monitor the index fragmentation, you may also want to
update the statistics more often. Check the query plans to see if they diffe
r
when the performance is slower.
You will need to know how your LUNs map to physical discs to determine if
there is any contention, but this should not follow the performance patterns
you have described. Check out physical disc fragmentation and defragment if
necessary.
You may want to also look at the blocker script
http://support.microsoft.com/kb/271509 and monitor performance with perfmon
and compare the results when it is working well against when it isn't.
You don't say if your system databases or tempdb are located on the same
discs, if they are you may want to consider moving them. That may not solve
your problem but it would be good practices.
John
"andsm" wrote:
> I have OLTP database, 24x5. The database used by application. The applicat
ion
> have new version like every half a year. During deployment of new version,
> old database renamed, creating new database with same structure and move
> data from old database to new with DTS. After deployment of new version I
see
> performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
> return back to not so good state. I guess it may be caused by fragmentatio
n?
> Every week runs dbcc dbreindex, to defragment database. Database file and
> log file are on separate LUNs, each RAID10. Storage device - EMC CX300. N
o
> any other files on same drive where database files are located. Most of
> tables have clustered indices, but not all tables have it - our benchmark
> tests showed what for some tables better to not have clustered index. SQL2
k
> SP3.
> Why is such performance degradation? What can be done to fix it?|||I'd look into specificallhy what is going slow. Are there specific
tranactions that you run frequently that become slow?
Can you reduce the number of indexes on the biggest tables?
n
have new version like every half a year. During deployment of new version,
old database renamed, creating new database with same structure and move
data from old database to new with DTS. After deployment of new version I se
e
performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
return back to not so good state. I guess it may be caused by fragmentation?
Every week runs dbcc dbreindex, to defragment database. Database file and
log file are on separate LUNs, each RAID10. Storage device - EMC CX300. No
any other files on same drive where database files are located. Most of
tables have clustered indices, but not all tables have it - our benchmark
tests showed what for some tables better to not have clustered index. SQL2k
SP3.
Why is such performance degradation? What can be done to fix it?What about statistics, are those kept up to date?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi
Your description seems to follow the sort of pattern associated with
fragmented indexes, but as you rebuild them this should not happen and you
should see an improvement after the rebuild that decays over time. Try using
DBCC SHOWCONTIG to monitor the index fragmentation, you may also want to
update the statistics more often. Check the query plans to see if they diffe
r
when the performance is slower.
You will need to know how your LUNs map to physical discs to determine if
there is any contention, but this should not follow the performance patterns
you have described. Check out physical disc fragmentation and defragment if
necessary.
You may want to also look at the blocker script
http://support.microsoft.com/kb/271509 and monitor performance with perfmon
and compare the results when it is working well against when it isn't.
You don't say if your system databases or tempdb are located on the same
discs, if they are you may want to consider moving them. That may not solve
your problem but it would be good practices.
John
"andsm" wrote:
> I have OLTP database, 24x5. The database used by application. The applicat
ion
> have new version like every half a year. During deployment of new version,
> old database renamed, creating new database with same structure and move
> data from old database to new with DTS. After deployment of new version I
see
> performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
> return back to not so good state. I guess it may be caused by fragmentatio
n?
> Every week runs dbcc dbreindex, to defragment database. Database file and
> log file are on separate LUNs, each RAID10. Storage device - EMC CX300. N
o
> any other files on same drive where database files are located. Most of
> tables have clustered indices, but not all tables have it - our benchmark
> tests showed what for some tables better to not have clustered index. SQL2
k
> SP3.
> Why is such performance degradation? What can be done to fix it?|||I'd look into specificallhy what is going slow. Are there specific
tranactions that you run frequently that become slow?
Can you reduce the number of indexes on the biggest tables?
Degradation of performance with time
I have OLTP database, 24x5. The database used by application. The application
have new version like every half a year. During deployment of new version,
old database renamed, creating new database with same structure and move
data from old database to new with DTS. After deployment of new version I see
performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
return back to not so good state. I guess it may be caused by fragmentation?
Every week runs dbcc dbreindex, to defragment database. Database file and
log file are on separate LUNs, each RAID10. Storage device - EMC CX300. No
any other files on same drive where database files are located. Most of
tables have clustered indices, but not all tables have it - our benchmark
tests showed what for some tables better to not have clustered index. SQL2k
SP3.
Why is such performance degradation? What can be done to fix it?What about statistics, are those kept up to date?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi
Your description seems to follow the sort of pattern associated with
fragmented indexes, but as you rebuild them this should not happen and you
should see an improvement after the rebuild that decays over time. Try using
DBCC SHOWCONTIG to monitor the index fragmentation, you may also want to
update the statistics more often. Check the query plans to see if they differ
when the performance is slower.
You will need to know how your LUNs map to physical discs to determine if
there is any contention, but this should not follow the performance patterns
you have described. Check out physical disc fragmentation and defragment if
necessary.
You may want to also look at the blocker script
http://support.microsoft.com/kb/271509 and monitor performance with perfmon
and compare the results when it is working well against when it isn't.
You don't say if your system databases or tempdb are located on the same
discs, if they are you may want to consider moving them. That may not solve
your problem but it would be good practices.
John
"andsm" wrote:
> I have OLTP database, 24x5. The database used by application. The application
> have new version like every half a year. During deployment of new version,
> old database renamed, creating new database with same structure and move
> data from old database to new with DTS. After deployment of new version I see
> performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
> return back to not so good state. I guess it may be caused by fragmentation?
> Every week runs dbcc dbreindex, to defragment database. Database file and
> log file are on separate LUNs, each RAID10. Storage device - EMC CX300. No
> any other files on same drive where database files are located. Most of
> tables have clustered indices, but not all tables have it - our benchmark
> tests showed what for some tables better to not have clustered index. SQL2k
> SP3.
> Why is such performance degradation? What can be done to fix it?|||I'd look into specificallhy what is going slow. Are there specific
tranactions that you run frequently that become slow?
Can you reduce the number of indexes on the biggest tables?
have new version like every half a year. During deployment of new version,
old database renamed, creating new database with same structure and move
data from old database to new with DTS. After deployment of new version I see
performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
return back to not so good state. I guess it may be caused by fragmentation?
Every week runs dbcc dbreindex, to defragment database. Database file and
log file are on separate LUNs, each RAID10. Storage device - EMC CX300. No
any other files on same drive where database files are located. Most of
tables have clustered indices, but not all tables have it - our benchmark
tests showed what for some tables better to not have clustered index. SQL2k
SP3.
Why is such performance degradation? What can be done to fix it?What about statistics, are those kept up to date?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi
Your description seems to follow the sort of pattern associated with
fragmented indexes, but as you rebuild them this should not happen and you
should see an improvement after the rebuild that decays over time. Try using
DBCC SHOWCONTIG to monitor the index fragmentation, you may also want to
update the statistics more often. Check the query plans to see if they differ
when the performance is slower.
You will need to know how your LUNs map to physical discs to determine if
there is any contention, but this should not follow the performance patterns
you have described. Check out physical disc fragmentation and defragment if
necessary.
You may want to also look at the blocker script
http://support.microsoft.com/kb/271509 and monitor performance with perfmon
and compare the results when it is working well against when it isn't.
You don't say if your system databases or tempdb are located on the same
discs, if they are you may want to consider moving them. That may not solve
your problem but it would be good practices.
John
"andsm" wrote:
> I have OLTP database, 24x5. The database used by application. The application
> have new version like every half a year. During deployment of new version,
> old database renamed, creating new database with same structure and move
> data from old database to new with DTS. After deployment of new version I see
> performance improvements for like 2-3 weeks. Next, after 2-3 monthes, it
> return back to not so good state. I guess it may be caused by fragmentation?
> Every week runs dbcc dbreindex, to defragment database. Database file and
> log file are on separate LUNs, each RAID10. Storage device - EMC CX300. No
> any other files on same drive where database files are located. Most of
> tables have clustered indices, but not all tables have it - our benchmark
> tests showed what for some tables better to not have clustered index. SQL2k
> SP3.
> Why is such performance degradation? What can be done to fix it?|||I'd look into specificallhy what is going slow. Are there specific
tranactions that you run frequently that become slow?
Can you reduce the number of indexes on the biggest tables?
Tuesday, February 14, 2012
Default Server Name
I have MSSQLServer version 8 installed. I'm using it to code with .NET. I
somehow ended up with MSSQLServer using an incorrevt server name when it
starts up. It gives me a dropdown box to select the correct servername, but
that's annoying.
My question is how can I get it to use the correct servername as the default
when it loads?
Any help is really appreciated!!
Bob
"Bob" <whoopiea@.whoopie.com> wrote in message
news:%230uT7NxjEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I have MSSQLServer version 8 installed. I'm using it to code with .NET.
I
> somehow ended up with MSSQLServer using an incorrevt server name when it
> starts up. It gives me a dropdown box to select the correct servername,
but
> that's annoying.
Did you change the server name after SQL Server was installed?
> My question is how can I get it to use the correct servername as the
default
> when it loads?
If yes to the above question, use the following (from BOL):
-- substitute the previous name and new name of your servers as appropriate
sp_dropserver old_name
GO
sp_addserver new_name, local
GO
Steve
|||"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:uP4Nfd4jEHA.3844@.TK2MSFTNGP12.phx.gbl...
> "Bob" <whoopiea@.whoopie.com> wrote in message
> news:%230uT7NxjEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I
> but
> Did you change the server name after SQL Server was installed?
> default
> If yes to the above question, use the following (from BOL):
> -- substitute the previous name and new name of your servers as
appropriate
> sp_dropserver old_name
> GO
> sp_addserver new_name, local
> GO
>
> Steve
>
Steve:
Thanks.
I inadvertantantly installed the sample databases to the wrong place, and
could not access them (ie: Northwind, PUBS, etc...) After alot of
troubleshooting, I got them installed from "\\computername" to
"\\computername\NetSDK". Then I deleted the databases from the data file in
"\\computername". The NetSDK folder is where they are and when I select
"\\computername\NetSDK" from SQL Server Service Manager's screen's dropdown
menu, all is fine. However, "\\computername" always comes up in the
dropdown as the default.
I'm kindof new with programming with SQL Server and not sure how to execute
the commands you suggested (sp_dropserver old_name ... GO
sp_addserver new_name, local ... GO)
All I know is how to code to the database.. creating databases, connection
strings within VS.NET and SQL queries, etc. from the programming environment
(I'm used to DB2, Access and some others).
Any additional insight is appreciated.
Thanks again,
Bob
|||Bob,
Sorry, I misunderstood your original question, it sounded as though you had
renamed your server after the SQL Server install. Since that is not the
case -- disregard my advice.
The dropdown default is something likely stored in your computers
registry...
Steve
"Bob" <whoopiea@.whoopie.com> wrote in message
news:eMn0Pa$jEHA.2680@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> "Steve Thompson" <stevethompson@.nomail.please> wrote in message
> news:uP4Nfd4jEHA.3844@.TK2MSFTNGP12.phx.gbl...
..NET.[vbcol=seagreen]
it[vbcol=seagreen]
servername,
> appropriate
> Steve:
> Thanks.
> I inadvertantantly installed the sample databases to the wrong place, and
> could not access them (ie: Northwind, PUBS, etc...) After alot of
> troubleshooting, I got them installed from "\\computername" to
> "\\computername\NetSDK". Then I deleted the databases from the data file
in
> "\\computername". The NetSDK folder is where they are and when I select
> "\\computername\NetSDK" from SQL Server Service Manager's screen's
dropdown
> menu, all is fine. However, "\\computername" always comes up in the
> dropdown as the default.
> I'm kindof new with programming with SQL Server and not sure how to
execute
> the commands you suggested (sp_dropserver old_name ... GO
> sp_addserver new_name, local ... GO)
> All I know is how to code to the database.. creating databases, connection
> strings within VS.NET and SQL queries, etc. from the programming
environment
> (I'm used to DB2, Access and some others).
> Any additional insight is appreciated.
> Thanks again,
> Bob
>
somehow ended up with MSSQLServer using an incorrevt server name when it
starts up. It gives me a dropdown box to select the correct servername, but
that's annoying.
My question is how can I get it to use the correct servername as the default
when it loads?
Any help is really appreciated!!
Bob
"Bob" <whoopiea@.whoopie.com> wrote in message
news:%230uT7NxjEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I have MSSQLServer version 8 installed. I'm using it to code with .NET.
I
> somehow ended up with MSSQLServer using an incorrevt server name when it
> starts up. It gives me a dropdown box to select the correct servername,
but
> that's annoying.
Did you change the server name after SQL Server was installed?
> My question is how can I get it to use the correct servername as the
default
> when it loads?
If yes to the above question, use the following (from BOL):
-- substitute the previous name and new name of your servers as appropriate
sp_dropserver old_name
GO
sp_addserver new_name, local
GO
Steve
|||"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:uP4Nfd4jEHA.3844@.TK2MSFTNGP12.phx.gbl...
> "Bob" <whoopiea@.whoopie.com> wrote in message
> news:%230uT7NxjEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I
> but
> Did you change the server name after SQL Server was installed?
> default
> If yes to the above question, use the following (from BOL):
> -- substitute the previous name and new name of your servers as
appropriate
> sp_dropserver old_name
> GO
> sp_addserver new_name, local
> GO
>
> Steve
>
Steve:
Thanks.
I inadvertantantly installed the sample databases to the wrong place, and
could not access them (ie: Northwind, PUBS, etc...) After alot of
troubleshooting, I got them installed from "\\computername" to
"\\computername\NetSDK". Then I deleted the databases from the data file in
"\\computername". The NetSDK folder is where they are and when I select
"\\computername\NetSDK" from SQL Server Service Manager's screen's dropdown
menu, all is fine. However, "\\computername" always comes up in the
dropdown as the default.
I'm kindof new with programming with SQL Server and not sure how to execute
the commands you suggested (sp_dropserver old_name ... GO
sp_addserver new_name, local ... GO)
All I know is how to code to the database.. creating databases, connection
strings within VS.NET and SQL queries, etc. from the programming environment
(I'm used to DB2, Access and some others).
Any additional insight is appreciated.
Thanks again,
Bob
|||Bob,
Sorry, I misunderstood your original question, it sounded as though you had
renamed your server after the SQL Server install. Since that is not the
case -- disregard my advice.
The dropdown default is something likely stored in your computers
registry...
Steve
"Bob" <whoopiea@.whoopie.com> wrote in message
news:eMn0Pa$jEHA.2680@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> "Steve Thompson" <stevethompson@.nomail.please> wrote in message
> news:uP4Nfd4jEHA.3844@.TK2MSFTNGP12.phx.gbl...
..NET.[vbcol=seagreen]
it[vbcol=seagreen]
servername,
> appropriate
> Steve:
> Thanks.
> I inadvertantantly installed the sample databases to the wrong place, and
> could not access them (ie: Northwind, PUBS, etc...) After alot of
> troubleshooting, I got them installed from "\\computername" to
> "\\computername\NetSDK". Then I deleted the databases from the data file
in
> "\\computername". The NetSDK folder is where they are and when I select
> "\\computername\NetSDK" from SQL Server Service Manager's screen's
dropdown
> menu, all is fine. However, "\\computername" always comes up in the
> dropdown as the default.
> I'm kindof new with programming with SQL Server and not sure how to
execute
> the commands you suggested (sp_dropserver old_name ... GO
> sp_addserver new_name, local ... GO)
> All I know is how to code to the database.. creating databases, connection
> strings within VS.NET and SQL queries, etc. from the programming
environment
> (I'm used to DB2, Access and some others).
> Any additional insight is appreciated.
> Thanks again,
> Bob
>
Subscribe to:
Posts (Atom)