Monday, March 26, 2012
Performance degraded after DBCC DBReindex
I have been tried to Reindex all tables' indexs as I expected this can
improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
'',0). But after that, I found that the overall performance is actually
degraded and the disk I/O seems to be heavier also. The performance is
degraded for three days already, can any one think of any reason that can
explain this issue? I have another question for statistic, do I needed to
update statistics after the reindex?
Pls. help, thanks a lot!
Yours sincerely,
HenryIn my opinion it's very important to update sql stats (proc sp_updatestats)
because like all RDBMS those stats are used by MSSQL to optimize the way it
compute your query.
Philippe RUELLO
DBA MSSQL|||stats will get auto recomputed for new indexes created when you run dbcc
dbreindex. . .if you have high IO after re-index then you need to
set statistics profile on
set showplan on
set statistic io on
and determine which tables are causing the procedures/queries to run slow. .
. .
determine where table scans and/or (nested loop joins) are used and
establish if there are useful indexes on these columns.
HTH
"Henry" wrote:
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>|||It is possible the reindex forced the recompile of a plan and it was not
optimal for most of the queries. Run profiler to see if you have some bad
queries and try recompiling them.
--
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>|||Since you have ran the dbcc reindex with 0, excessive IO signifies lot of
page splits.
so next time if u need to run dbcc reindex(u should have strong reason to
run this)
run this command seperate for each index depending upon its
usability/behavior(unique or non unique) and pass appropriate fillfactorto
avoid excessive page splits.
Thanks
--Harvinder
"Andrew J. Kelly" wrote:
> It is possible the reindex forced the recompile of a plan and it was not
> optimal for most of the queries. Run profiler to see if you have some bad
> queries and try recompiling them.
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
> > Dear Sir,
> > I have been tried to Reindex all tables' indexs as I expected this can
> > improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> > '',0). But after that, I found that the overall performance is actually
> > degraded and the disk I/O seems to be heavier also. The performance is
> > degraded for three days already, can any one think of any reason that can
> > explain this issue? I have another question for statistic, do I needed to
> > update statistics after the reindex?
> > Pls. help, thanks a lot!
> > Yours sincerely,
> > Henry
> >
>
>|||I am just rephrasing Harvinder here.
A fillfactor of 0 is really only useful for a read-only table. It will
leave absolutely no room for additional rows, or changes of rows that
causes the row to expand.
This means, that if inserts and update occur on the table after the
reindex with fillfactor=0, then page splits will occur. After that,
scanning the table will require more I/O, and because of fragmentation,
true sequential I/O will not be possible.
If you reindex regularly, then you can set the fillfactor accordingly.
For example, let's say 10% of the table is changed in one week. If you
reindex the table on a weekly basis, then a 10% fillfactor should work
quite well. It will avoid unnecessary page splits, because the
fillfactor will allow the table to grow (on average) 10 percent.
Hope this helps,
Gert-Jan
Henry wrote:
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry|||Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
the 0 in the script means just follow whatever the fillfactor being used
during the index being created? Will it automatically change to fill factor
to 0?
But any way, I will beware of it! Thx!
From,
Henry
"Gert-Jan Strik" wrote:
> I am just rephrasing Harvinder here.
> A fillfactor of 0 is really only useful for a read-only table. It will
> leave absolutely no room for additional rows, or changes of rows that
> causes the row to expand.
> This means, that if inserts and update occur on the table after the
> reindex with fillfactor=0, then page splits will occur. After that,
> scanning the table will require more I/O, and because of fragmentation,
> true sequential I/O will not be possible.
> If you reindex regularly, then you can set the fillfactor accordingly.
> For example, let's say 10% of the table is changed in one week. If you
> reindex the table on a weekly basis, then a 10% fillfactor should work
> quite well. It will avoid unnecessary page splits, because the
> fillfactor will allow the table to grow (on average) 10 percent.
> Hope this helps,
> Gert-Jan
>
> Henry wrote:
> >
> > Dear Sir,
> > I have been tried to Reindex all tables' indexs as I expected this can
> > improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> > '',0). But after that, I found that the overall performance is actually
> > degraded and the disk I/O seems to be heavier also. The performance is
> > degraded for three days already, can any one think of any reason that can
> > explain this issue? I have another question for statistic, do I needed to
> > update statistics after the reindex?
> > Pls. help, thanks a lot!
> > Yours sincerely,
> > Henry
>|||Thanks for your email, how do I identify the right percentage of sample data
to be used for updating the statistics? In general, is 30% enought?
From,
Henry
"Philippe RUELLO" wrote:
> In my opinion it's very important to update sql stats (proc sp_updatestats)
> because like all RDBMS those stats are used by MSSQL to optimize the way it
> compute your query.
> Philippe RUELLO
> DBA MSSQL
>
>|||Henry wrote:
> Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
> the 0 in the script means just follow whatever the fillfactor being used
> during the index being created? Will it automatically change to fill factor
> to 0?
> But any way, I will beware of it! Thx!
> From,
> Henry
Yes, you are right. I forgot one little step in my explanation, and that
is that if you create an index without fill factor, then by default a
fill factor of 0 is used.
So if you created the indexes without fill factor, and run DBCC
DBREINDEX(..,.., 0), then at the leaf level there will be no space left
after reindexing, and in the index tree just one entry per page.
HTH,
Gert-Jan
Performance degradation after replication rolled out
Hi all.
Any assistance would be greatly appreciated.
We recently created transactional replication to hopefully improve performance issues we were expereincing. The replication is between 2 SQL Servers (2000), and since we have introduced the replication, the performance has degraded considerably.
I will try and explain the scenario.
We have a primary db that our internal users use and we also have the newly replicated db that our website and another application use. The users are complaining that the website and the internal application is extremely slow and I was just wondering if it is possible to do an Index Tuning on both the primary db and replicated db based on trace files so as to create new indexes or would this have an impact on the replication?
Thanks in advance.
Running the index tuning wizard is a good idea. Also, generate some trace files and look through them..see if there are any CPU spikes..check out your most frequently used stored procs and look for any missing indexes. Look for the columns in all the where conditions in your T-SQL statements. Do you have indexes on those columns? Index Tuning Wizard can identify most such things. You might have to stop replication during this. You could do it on a weekend maintenance window.
|||There's also a basic whitepaper on transactional replication performance you should read through to see if there's anything that you can take advantage of - "Transactional Replication Performance Tuning and Optimization" - http://msdn2.microsoft.com/en-us/library/aa902656(sql.80).aspx.
Do you replicate a lot of updates? And do the tables on the subscriber have more than one index on it? If so, I highly recommend reading up on sp_scriptdynamicupdproc.
Friday, March 9, 2012
Performance
performance on a low cardinality column?
Thanks in advance,
You might want to provide an example query and DDL to demonstrate exactly
what you want to do.
I assume you're talking about a simple where clause against a single column
when that column is low-cardinality?
Generally speaking... you'll find that a non-clustered index is not
effective for seeking on low cardinaility data. NC indexes tend not to be
helpful when anything more than a very low percentage of data in the table
is returned. You might find that a covered index (all the columns in the
query are in the index) might be helpful.
But a query and DDL example would be nice...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"RG" <anonymous@.discussions.microsoft.com> wrote in message
news:31a701c4a5a1$108e1080$a501280a@.phx.gbl...
> In SQL Server 7, how would one improve selection
> performance on a low cardinality column?
> Thanks in advance,
Performance
Server. In my performance monitoring, my % processor
time hovers around the 35% to 40% mark. The memory seems
to be sufficient to support our needs, but users are
complaining that the system is slow.
I can identify which databases are being used that causes
the slow response time. I would like to add some indexs
on a few tables. Can I add indexes while users are in
the system?
Any other suggestions to decrease the % processor time,
besides upgrading the processors on the server?
Thank you,
JLFleming
Hi
%30-40 avg processor time did not show that you have a bottleneck with
processor.
It seems like you have slow running queries or locking-blocking issues..
I think it would be better for you to analyze you're queries with sql
profiler. And start to optimize or re-write them.
Creating index on tables will block users to access to that tables until
index creation is complete.
And my suggestion is to decide which index to create according to your
queries.
You can use SQL Profiler to capture the trace's and Index Tuning Wizard to
analyze them. (You better use ITW against the db on the test server using the
trace file that you capture from production)
Regards..
"JLFleming" wrote:
> I am trying to improve performance on my SQL 2000
> Server. In my performance monitoring, my % processor
> time hovers around the 35% to 40% mark. The memory seems
> to be sufficient to support our needs, but users are
> complaining that the system is slow.
> I can identify which databases are being used that causes
> the slow response time. I would like to add some indexs
> on a few tables. Can I add indexes while users are in
> the system?
> Any other suggestions to decrease the % processor time,
> besides upgrading the processors on the server?
> Thank you,
> JLFleming
>
|||You might want to have a looka t these below. Low processor utilization can
mean several things but one of the most common is that they are waiting on
Disk I/O to complete. If you have a disk bottleneck the processors can't do
much. The same is true for blocking. Have you run sp_who2 to see if anyone
is being blocked?
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"JLFleming" <anonymous@.discussions.microsoft.com> wrote in message
news:066e01c50eac$063c8340$a401280a@.phx.gbl...
> I am trying to improve performance on my SQL 2000
> Server. In my performance monitoring, my % processor
> time hovers around the 35% to 40% mark. The memory seems
> to be sufficient to support our needs, but users are
> complaining that the system is slow.
> I can identify which databases are being used that causes
> the slow response time. I would like to add some indexs
> on a few tables. Can I add indexes while users are in
> the system?
> Any other suggestions to decrease the % processor time,
> besides upgrading the processors on the server?
> Thank you,
> JLFleming
Performance
Server. In my performance monitoring, my % processor
time hovers around the 35% to 40% mark. The memory seems
to be sufficient to support our needs, but users are
complaining that the system is slow.
I can identify which databases are being used that causes
the slow response time. I would like to add some indexs
on a few tables. Can I add indexes while users are in
the system?
Any other suggestions to decrease the % processor time,
besides upgrading the processors on the server?
Thank you,
JLFlemingHi
%30-40 avg processor time did not show that you have a bottleneck with
processor.
It seems like you have slow running queries or locking-blocking issues..
I think it would be better for you to analyze you're queries with sql
profiler. And start to optimize or re-write them.
Creating index on tables will block users to access to that tables until
index creation is complete.
And my suggestion is to decide which index to create according to your
queries.
You can use SQL Profiler to capture the trace's and Index Tuning Wizard to
analyze them. (You better use ITW against the db on the test server using th
e
trace file that you capture from production)
Regards..
"JLFleming" wrote:
> I am trying to improve performance on my SQL 2000
> Server. In my performance monitoring, my % processor
> time hovers around the 35% to 40% mark. The memory seems
> to be sufficient to support our needs, but users are
> complaining that the system is slow.
> I can identify which databases are being used that causes
> the slow response time. I would like to add some indexs
> on a few tables. Can I add indexes while users are in
> the system?
> Any other suggestions to decrease the % processor time,
> besides upgrading the processors on the server?
> Thank you,
> JLFleming
>|||You might want to have a looka t these below. Low processor utilization can
mean several things but one of the most common is that they are waiting on
Disk I/O to complete. If you have a disk bottleneck the processors can't do
much. The same is true for blocking. Have you run sp_who2 to see if anyone
is being blocked?
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"JLFleming" <anonymous@.discussions.microsoft.com> wrote in message
news:066e01c50eac$063c8340$a401280a@.phx.gbl...
> I am trying to improve performance on my SQL 2000
> Server. In my performance monitoring, my % processor
> time hovers around the 35% to 40% mark. The memory seems
> to be sufficient to support our needs, but users are
> complaining that the system is slow.
> I can identify which databases are being used that causes
> the slow response time. I would like to add some indexs
> on a few tables. Can I add indexes while users are in
> the system?
> Any other suggestions to decrease the % processor time,
> besides upgrading the processors on the server?
> Thank you,
> JLFleming
Wednesday, March 7, 2012
Performance
performance on a low cardinality column?
Thanks in advance,You might want to provide an example query and DDL to demonstrate exactly
what you want to do.
I assume you're talking about a simple where clause against a single column
when that column is low-cardinality?
Generally speaking... you'll find that a non-clustered index is not
effective for seeking on low cardinaility data. NC indexes tend not to be
helpful when anything more than a very low percentage of data in the table
is returned. You might find that a covered index (all the columns in the
query are in the index) might be helpful.
But a query and DDL example would be nice...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"RG" <anonymous@.discussions.microsoft.com> wrote in message
news:31a701c4a5a1$108e1080$a501280a@.phx.gbl...
> In SQL Server 7, how would one improve selection
> performance on a low cardinality column?
> Thanks in advance,
Performance
Server. In my performance monitoring, my % processor
time hovers around the 35% to 40% mark. The memory seems
to be sufficient to support our needs, but users are
complaining that the system is slow.
I can identify which databases are being used that causes
the slow response time. I would like to add some indexs
on a few tables. Can I add indexes while users are in
the system?
Any other suggestions to decrease the % processor time,
besides upgrading the processors on the server?
Thank you,
JLFlemingHi
%30-40 avg processor time did not show that you have a bottleneck with
processor.
It seems like you have slow running queries or locking-blocking issues..
I think it would be better for you to analyze you're queries with sql
profiler. And start to optimize or re-write them.
Creating index on tables will block users to access to that tables until
index creation is complete.
And my suggestion is to decide which index to create according to your
queries.
You can use SQL Profiler to capture the trace's and Index Tuning Wizard to
analyze them. (You better use ITW against the db on the test server using the
trace file that you capture from production)
Regards..
"JLFleming" wrote:
> I am trying to improve performance on my SQL 2000
> Server. In my performance monitoring, my % processor
> time hovers around the 35% to 40% mark. The memory seems
> to be sufficient to support our needs, but users are
> complaining that the system is slow.
> I can identify which databases are being used that causes
> the slow response time. I would like to add some indexs
> on a few tables. Can I add indexes while users are in
> the system?
> Any other suggestions to decrease the % processor time,
> besides upgrading the processors on the server?
> Thank you,
> JLFleming
>|||You might want to have a looka t these below. Low processor utilization can
mean several things but one of the most common is that they are waiting on
Disk I/O to complete. If you have a disk bottleneck the processors can't do
much. The same is true for blocking. Have you run sp_who2 to see if anyone
is being blocked?
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"JLFleming" <anonymous@.discussions.microsoft.com> wrote in message
news:066e01c50eac$063c8340$a401280a@.phx.gbl...
> I am trying to improve performance on my SQL 2000
> Server. In my performance monitoring, my % processor
> time hovers around the 35% to 40% mark. The memory seems
> to be sufficient to support our needs, but users are
> complaining that the system is slow.
> I can identify which databases are being used that causes
> the slow response time. I would like to add some indexs
> on a few tables. Can I add indexes while users are in
> the system?
> Any other suggestions to decrease the % processor time,
> besides upgrading the processors on the server?
> Thank you,
> JLFleming|||I have very little to add to what these guys have said,
however you may also need to look at Disk Raiding and
Network speed.
Other than that is your system used as a data input tool,
reporting tool or a bit of both.
Peter
peternolan67REMOVETHIS@.hotmail.com
"It's true hard work never killed anybody, but I figure,
why take the chance?"
Ronald Reagan
>--Original Message--
>I am trying to improve performance on my SQL 2000
>Server. In my performance monitoring, my % processor
>time hovers around the 35% to 40% mark. The memory seems
>to be sufficient to support our needs, but users are
>complaining that the system is slow.
>I can identify which databases are being used that causes
>the slow response time. I would like to add some indexs
>on a few tables. Can I add indexes while users are in
>the system?
>Any other suggestions to decrease the % processor time,
>besides upgrading the processors on the server?
>Thank you,
>JLFleming
>.
>|||Our system is mostly used as a data input tool. We do
some reporting, but not much.
>--Original Message--
>I have very little to add to what these guys have said,
>however you may also need to look at Disk Raiding and
>Network speed.
>Other than that is your system used as a data input
tool,
>reporting tool or a bit of both.
>Peter
>peternolan67REMOVETHIS@.hotmail.com
>"It's true hard work never killed anybody, but I figure,
>why take the chance?"
>Ronald Reagan
>
>>--Original Message--
>>I am trying to improve performance on my SQL 2000
>>Server. In my performance monitoring, my % processor
>>time hovers around the 35% to 40% mark. The memory
seems
>>to be sufficient to support our needs, but users are
>>complaining that the system is slow.
>>I can identify which databases are being used that
causes
>>the slow response time. I would like to add some
indexs
>>on a few tables. Can I add indexes while users are in
>>the system?
>>Any other suggestions to decrease the % processor time,
>>besides upgrading the processors on the server?
>>Thank you,
>>JLFleming
>>.
>.
>