Problems Defragging Indices (SQL2005 64-bit)

Hi all,
I have just migrated a database from SQL 2000 32 bit to SQL 2005 64 bit. I
also upgraded the compatability level to 9. Performance was immediately
horrible in terms of query execution.
Trying to solve the issue, I ran a query to retrieve indices with avgfrag >
10%. I then tried rebuilding the indices with ALTER INDEX, and ran my query
again. The avgfrag had NOT CHANGED.
What am I doing wrong?
While we're at it, I got several timeout errors from clients connecting with
ADO.NET from with the network. Their app's connection string specifies a
timeout of 120 sec, but they were timing out well before this. Just before
my database/server "upgrade", this was NOT happening. Where to best change
this to prevent further timeouts until I can get my indices and stats in
better shape?
Thanks.
John
Hi John
Have you checked local connectivity and connectivity from (say) query
analyser to the new server? Is remote access enabled?
When migrating did you trasfer logins/users correctly, do you have orphaned
users? Have you tried scripting/recreating the indexes, I would also consider
doing this for all views/stored procedures/functions also. Make sure that you
have updated the usage and statistics as well.
John
"JT" wrote:

> Hi all,
> I have just migrated a database from SQL 2000 32 bit to SQL 2005 64 bit. I
> also upgraded the compatability level to 9. Performance was immediately
> horrible in terms of query execution.
> Trying to solve the issue, I ran a query to retrieve indices with avgfrag >
> 10%. I then tried rebuilding the indices with ALTER INDEX, and ran my query
> again. The avgfrag had NOT CHANGED.
> What am I doing wrong?
> While we're at it, I got several timeout errors from clients connecting with
> ADO.NET from with the network. Their app's connection string specifies a
> timeout of 120 sec, but they were timing out well before this. Just before
> my database/server "upgrade", this was NOT happening. Where to best change
> this to prevent further timeouts until I can get my indices and stats in
> better shape?
> Thanks.
> --
> John
|||Hello John,
I understand that you tried to use "alter index ...rebuild all" statement
to some indexes, and its fragment is not changed. Also, you saw performance
degration after database migration to 2005 64 bit version from SQL 2000.
You encountered timout errors less than 120s from clients though connection
string specifies 120s. If I'm off base, please correct me.
Did you use sys.dm_db_index_physical_stats or dbcc showcontig to get the
fragment information? Based on my experience, if a table does not include
clustered index, it may have large logical fragment and low density.
However, this does not necessarily indicate the tables/indexes are not
efficient for all queries. Generally, rebuild index for this situation does
not change the logical fragment value. You may consider use a different
fillfactor but I don't think this may be related to the performance issue
you encountered.
Also, does the issue occurs on some specific queries? Does 2 servers have
the same load? Did you see any bottleneck in memory/IO/CPU? The whitepaper
of performance troubleshooting in 2005 is as following:
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/pro.../tsprfprb.mspx
First, please install SP1 on the server if it is not installed since many
known issues are addressed in sp1.
you may want to update the statistics information by using "update
statistics" statment with "fullscan" option to see if it does any
difference. You may also want to change the compatability level to 80 to
test. On the other thand, if the old server exist, you may want to compare
the execution plan on both servers to see if there is any differences:
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
Please run sp_configure on both server to see if there is any difference.
You'd like to change "max degree of parallelism" to 1 if you have
mulitprocessors on the server to see the result.
As for tiemout issue, you may first check if the issue occurs on local
console of SQL Server if possible. Also, did you set commandtimeout or
connectiontimout? Usually commandtimeout might be cause of this issue of
long running quries.
http://msdn.microsoft.com/library/de...us/ado270/htm/
mdprocommandtimeout.asp
To isolate the client/server issue, you may want to install latest mdac and
sql native client on different machine, and make sure the issue occurs on
different clients
How to obtain the latest MDAC 2.8 service pack
http://support.microsoft.com/kb/884103/
http://www.microsoft.com/downloads/d...5AA-B4BD-4705-
AA0A-B477BA72A9CB&displaylang=en
If you isolate this to be a server side issue, you may want to check if you
encounter the following known issue and get fix from it to see if you
encoutnered the issue.
FIX: The internal deadlock monitor may not detect a deadlock between two or
more sessions in SQL Server 2005
http://support.microsoft.com/kb/915918
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello John,
I'm still interested in this issue. Did you try to solve the issue in the
weekend? If the issue persists, please get the informaiton I mentioend so
that we may further troubleshoot the issue. If you have other priorites,
please also let's know and we may work on the problem a little alter. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Peter,
I have largely resolved the issue. The steps I took included
1. greatly expanding the resources of tempdb, giving it eight equally sized
200MB mdf/ndf files to work with on a RAID 1/0 LUN.
2. Using NOEXPAND to force the optimizer to choose indexed views. (This
made a HUGE difference - like 1000% performance gains.) You can see my post
on this in SQL Server.Programming under Indexed View Issue, dated 11/22/2006).
3. Identifying an error in the 2005 scripting wizard which has been sending
me on a tail-chase regarding indices on Indexed Views. You can see my post
on this at SQL Server.Server under WARNING-SCRIPTING ENGINE UNRELIABLE, dated
11/26/2006.
4. Reading Itzak Ben-Gan's two books and doing a lot of optimization work.
Bottom line, I had a particularly gruesome query that ran in 7 seconds with
SQL 2000, and then went up to 25 seconds in SQL 2005 x64. I got it down to
1.7 seconds now, still using SQL 2005 x64. This is just one of many examples.
Thanks for your help.
John
"Peter Yang [MSFT]" wrote:

> Hello John,
> I'm still interested in this issue. Did you try to solve the issue in the
> weekend? If the issue persists, please get the informaiton I mentioend so
> that we may further troubleshoot the issue. If you have other priorites,
> please also let's know and we may work on the problem a little alter. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hi John
Thanks for the feed back,
Links to the previous threads:
2. Indexed View Issue http://tinyurl.com/yj4qvn
3. WARNING-SCRIPTING ENGINE UNRELIABLE - http://tinyurl.com/wu6aq
IMO Itzik's books in the Inside SQL Server 2005 series are must buys for any
Programmer/DBA using SQL Server 2005.
John
"JT" wrote:
[vbcol=seagreen]
> Hi Peter,
> I have largely resolved the issue. The steps I took included
> 1. greatly expanding the resources of tempdb, giving it eight equally sized
> 200MB mdf/ndf files to work with on a RAID 1/0 LUN.
> 2. Using NOEXPAND to force the optimizer to choose indexed views. (This
> made a HUGE difference - like 1000% performance gains.) You can see my post
> on this in SQL Server.Programming under Indexed View Issue, dated 11/22/2006).
> 3. Identifying an error in the 2005 scripting wizard which has been sending
> me on a tail-chase regarding indices on Indexed Views. You can see my post
> on this at SQL Server.Server under WARNING-SCRIPTING ENGINE UNRELIABLE, dated
> 11/26/2006.
> 4. Reading Itzak Ben-Gan's two books and doing a lot of optimization work.
> Bottom line, I had a particularly gruesome query that ran in 7 seconds with
> SQL 2000, and then went up to 25 seconds in SQL 2005 x64. I got it down to
> 1.7 seconds now, still using SQL 2005 x64. This is just one of many examples.
> Thanks for your help.
>
> --
> John
>
> "Peter Yang [MSFT]" wrote:
|||Hello John,
Thank you for sharing your experience on this issue and this shall
defenitely benefit the community. :-)
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
sql