April 15, 2009 at 1:19 pm
We've got a database that is facing massive time out issues since the upgrade from SQL 2005 from SQL 2000. We've do a lot of digging and are still looking into multiple area's for solutions, the hardware is not over stressed, Stored Proc's that execute i seconds in ssms can run for 120 seconds and time out on the app. A lot of it is random. One time the query will run in seconds the next time around profiler shows us that it too minutes using the same conditions.
Some of this we have been able to debunk by looking at the processes that are going on around the same time as the code being executed.
However I wanted to take a closer look at our indexes to see if they were causing some of our problem.
Enter sys.dm_db_index_physical_stats. The query that i'm running is down below
select
ips.object_id,
object_name(ips.object_id) as index_name,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.avg_page_space_used_in_percent
from
( select
[object_id],
index_id,
index_type_desc,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id('databasename'), null, null, null, 'Detailed')
where index_id<>0) as ips
inner join sys.indexes si
on si.object_id=ips.object_id
and si.index_id=ips.index_id
where ips.avg_page_space_used_in_percent 10
order by index_name
But i'm getting a lot of confusion information back. (there are thousands of rows this is just a subset)
object id index type frag in% space used in %
1865213845NONCLUSTERED INDEX00.716580183
1865213845NONCLUSTERED INDEX01.643192488
167111786 CLUSTERED INDEX00.481838399
167111786CLUSTERED INDEX66.6666666798.54625649
167111786NONCLUSTERED INDEX00.593031875
167111786NONCLUSTERED INDEX5074.82085495
For composite indexes the index object id is returned multiple times, as you can see above object_id 167111786 has multiple rows for a clustered index and different values for avg fragmentation in % as well as space used in % When i rebuild the index for the object, the worst part is the numbers don't change
alter index all on tablename
rebuild with (fillfactor=90, online=on);
I thought fragmentation in % > 10 was bad rebuild and it should go down to 0, and i also though avg page space used in % < 75 was bad and rebuild with fill factor set should set it @ 90
would appriceate any help or pointers that you could provide
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
April 15, 2009 at 1:56 pm
btb1977 (4/15/2009)
We've got a database that is facing massive time out issues since the upgrade from SQL 2005 from SQL 2000.
Did you update all statistics after the upgrade?
For composite indexes the index object id is returned multiple times, as you can see above object_id 167111786 has multiple rows for a clustered index and different values for avg fragmentation in % as well as space used in %
That'll be from the multiple levels of the index.
When i rebuild the index for the object, the worst part is the numbers don't change
How big's the table in question? That's common to see for small tables and is nothing to worry about.
Intermittent timeouts indicate blocking. I would look for the queries that are blocked and blocking most of the time and see about optimising them. This series may help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2009 at 4:29 pm
Hang on. Can I start from scratch...
If you have an upgrade from 2000 to 2005 you may well have some compilation differences.
One good way to detect this (after implementation) is to profile your RPC and Batch completed statements to find out what now runs slower. Then drill down to statements in the SPs.
I'm not an expert on the detail because I'm not one of the top experts who contribute here. But I do know that Microsoft say 90% of queries are better and 10% are worse.
You need to find the 10% that are worse and fix them. Most will probably be straightforward.
Best of Luck...
.
April 15, 2009 at 7:22 pm
Hey Gail,
Thanks for the reply, the funny thing is I had just found the 2 part series you recommended to some one else on the boards and I'm reading it now 🙂
GilaMonster (4/15/2009)
Did you update all statistics after the upgrade?
We did not update the statistics after the upgrade, I've already left for the night and will set up a job to do that tomorrow evening to update the statistics. One of the lead developers has asked that we re-compile all of the stored procedures, is this a recommended course of action?
That'll be from the multiple levels of the index.
should i should i be concerned with the avg space used % & avg fragmentation in % where index_level=0 or do those stats matter for every level that the index is located in?
How big's the table in question? That's common to see for small tables and is nothing to worry about.
Perhaps the table i did this on was a bad one to test, it only has a little over 200 rows of data and the index only takes up 1 page worth of data. That is good to know for smaller tables, if I do this against a table with thousands of rows (maybe 10's of thousands) and several pages worth of data, should I see the number change?
Gail Thanks for the reply I will look into the blocking as that could be what the culprit is!
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
April 15, 2009 at 7:34 pm
Tim Walker (4/15/2009)
Hang on. Can I start from scratch...If you have an upgrade from 2000 to 2005 you may well have some compilation differences.
One good way to detect this (after implementation) is to profile your RPC and Batch completed statements to find out what now runs slower. Then drill down to statements in the SPs.
I'm not an expert on the detail because I'm not one of the top experts who contribute here. But I do know that Microsoft say 90% of queries are better and 10% are worse.
You need to find the 10% that are worse and fix them. Most will probably be straightforward.
Best of Luck...
Hey Tim,
Thanks for the input, part of the problem we have is that before the upgrade we were not capturing baselines. So it is tough to say how the queries are performing now compared to before. The duration has been our main indicator, but it is all over the place. a simple stored procedure that tells us the role access of the user and should return in under a second, has times were it times out. We see some stored procedures time out but they are from different segments of the application, and the same stored procedures do not time out every time so it is difficult to deduce a pattern.
I don't have much experience with blocking, but i get the feeling I'm getting on the job training
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
April 16, 2009 at 1:43 am
btb1977 (4/15/2009)
Tim Walker (4/15/2009)
Hang on. Can I start from scratch...If you have an upgrade from 2000 to 2005 you may well have some compilation differences.
One good way to detect this (after implementation) is to profile your RPC and Batch completed statements to find out what now runs slower. Then drill down to statements in the SPs.
I'm not an expert on the detail because I'm not one of the top experts who contribute here. But I do know that Microsoft say 90% of queries are better and 10% are worse.
You need to find the 10% that are worse and fix them. Most will probably be straightforward.
Best of Luck...
Hey Tim,
Thanks for the input, part of the problem we have is that before the upgrade we were not capturing baselines. So it is tough to say how the queries are performing now compared to before. The duration has been our main indicator, but it is all over the place. a simple stored procedure that tells us the role access of the user and should return in under a second, has times were it times out. We see some stored procedures time out but they are from different segments of the application, and the same stored procedures do not time out every time so it is difficult to deduce a pattern.
I don't have much experience with blocking, but i get the feeling I'm getting on the job training
My apologies to Gail, I didn't appreciate what she had linked to when I posted my previous input. Her articles on this area are really good and easy to follow. If you are fairly new to SQL Server they will teach you a lot.
Good luck in finding the problem.
.
April 16, 2009 at 1:47 am
btb1977 (4/15/2009)
We did not update the statistics after the upgrade, I've already left for the night and will set up a job to do that tomorrow evening to update the statistics. One of the lead developers has asked that we re-compile all of the stored procedures, is this a recommended course of action?
No need. The stats update will invalidate all plans.
should i should i be concerned with the avg space used % & avg fragmentation in % where index_level=0 or do those stats matter for every level that the index is located in?
Just the leaf level. It's likely that the higher levels will be in cache anyway.
Perhaps the table i did this on was a bad one to test, it only has a little over 200 rows of data and the index only takes up 1 page worth of data. That is good to know for smaller tables, if I do this against a table with thousands of rows (maybe 10's of thousands) and several pages worth of data, should I see the number change?
The rule of thumb for where to start worrying about fragmentation is 1000 pages. Two things to note about fragmentation. It only affects reads from disk into memory. It's only a concern when doing scans, full table scans or large range scans. Singleton seeks are not affected by fragmentation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2009 at 4:04 am
Tim Walker (4/16/2009)
My apologies to Gail, I didn't appreciate what she had linked to when I posted my previous input.
No apology was necessary there. Just because one person posts information doesn't mean that others can't help out too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2009 at 11:31 am
GilaMonster (4/16/2009)
No need. The stats update will invalidate all plans.
Hey Gail just to clarify should i delete the system auto created statistics or should i just run sp_updatestats and update what we've got?
Thanks,
Brad
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
April 16, 2009 at 1:53 pm
Update them. If you delete the statistics you're forcing SQL to recreate them when it thinks they're necessary. Doing so could adversely affect an important query.
Use UPDATE STATISTICS rather than sp_updatestats. If you use UPDATE STATISTICS you can guarantee that the stats you request to be updated will be. with sp_updatestats, not so.
Books Online:
sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2009 at 2:19 pm
Thanks a million Gail!
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
April 17, 2009 at 1:08 am
GilaMonster (4/16/2009)
Update them. If you delete the statistics you're forcing SQL to recreate them when it thinks they're necessary. Doing so could adversely affect an important query.Use UPDATE STATISTICS rather than sp_updatestats. If you use UPDATE STATISTICS you can guarantee that the stats you request to be updated will be. with sp_updatestats, not so.
Books Online:
sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.
Hi Gail,
Would you recommend using sp_updatestats for normal maintenance procedures? Or would you use UPDATE STATISTICS for that as well?
Thanks,
Lian
April 17, 2009 at 1:31 am
Well, if you look at that little note from books Online, it indicates that sp_updatestats will only update statistics that are 'out of date', based on the value of rowmodcnt, ie, stats that the auto update would already have updated.
So, if I'm reading it right (and I do need to test), if you're running sp_updatestats because auto update stats is off, fine. If you're running sp_updatestats because the auto update doesn't run often enough for some indexes then sp_updatestats is going to skip those same statistics because it, like the auto update, thinks that they're still accurate enough.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2009 at 1:35 am
Nice thanks,
I think it's probably best then set auto_update stats on & not worry too much about including it (stats update) in my maintenance plans.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply