| Author |
 Topic  |
|
|
dboiler
Starting Member
5 Posts |
 Posted - 02/21/2012 : 11:46:38
|
One of two databases on this Windows Server 2008 machine with SQL Server 2008 is hogging the disk access. When I open Resource Monitor, it shows this database hitting the disk at over 5GB/min. The entire database itself is less than 3GB ins size. So I don't know why its hitting the disk so heavily. I have autoshrink turned off and the Data File is set to autogrow by 350MB, unrestricted and the Log File is set to autogrow by 10 percent, unrestricted.
Any help would be appreciated.
Dave |
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
|
|
dboiler
Starting Member
5 Posts |
Posted - 02/21/2012 : 12:49:01
|
| Thanks Gail. I should also mention that this database was working just fine on another server. It was on a legacy machine running Windows Server 2003 and SQL 2005. I then moved it to the current machine, which also is a virtual machine, running the Windows 2008 and SQL 2008. That's when the problems started. |
 |
|
|
Peter99
Constraint Violating Yak Guru
471 Posts |
Posted - 02/21/2012 : 14:58:27
|
| Update statistics and rebuild indexes. |
|
|
|
dboiler
Starting Member
5 Posts |
Posted - 02/24/2012 : 14:14:21
|
Thanks Gail and Peter99 for your input. However, I'm still having the same issues. I did some searching on the Internet and found this routine to run to determine Index Fragmentation but it always errors with Incorrect syntax near 'my_database_name' in the sys.dm_db_index_physical_stats function. I cannot see what is wrong with the query.
USE my_database_name
GO
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName],
IPS.Index_type_desc,
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count,
IPS.ghost_record_count,
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'my_database_name'), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5
GO
Any Ideas?
Dave |
|
|
|
Peter99
Constraint Violating Yak Guru
471 Posts |
Posted - 02/24/2012 : 16:00:55
|
| change my_database_name to your database name. That may help. |
|
|
|
jeffw8713
Aged Yak Warrior
USA
693 Posts |
Posted - 02/25/2012 : 08:53:36
|
Verify the compatibility level is set to 100 on the database in question. If you are not running Enterprise Edition - then the format of the command you are using will not work. You will need to change it to:
Declare @databaseID int;
Set @databaseID = db_id(N'my_database_name');
Select ...
From sys.dm_db_index_physical_stats(@databaseID, Null, Null, Null, 'Detailed') ips
I would recommend switching from 'Detailed' to 'Limited' - as you really don't need to go to the detailed level to identify fragmentation.
Jeff |
|
|
|
stepson
Yak Posting Veteran
Romania
85 Posts |
Posted - 02/27/2012 : 02:53:36
|
| dump question : You do a log backup ? |
|
|
|
Jayam.cnu
Starting Member
India
40 Posts |
Posted - 02/27/2012 : 02:58:55
|
| yup... the Query level it is perfect ... change the database name into your user-defined database name ... |
|
|
|
dboiler
Starting Member
5 Posts |
Posted - 02/27/2012 : 08:55:15
|
Jeff,
Thanks for the reply - worked perfectly. The database (not named my_database_name but just used that name in this post) is VERY fragmented. some tables are 99% fragmented. I've tried various re-indexing queries I've found from browsing but nothing seems to help with the performance. Any thoughts?
Dave |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 02/27/2012 : 09:26:41
|
Honestly, if I was in your position I'd be working through slow queries (as per the articles I specified) It is possible for query performance to change across versions of SQL (though regressions are rare) and this wouldn't be the first time I encountered a hardware upgrade resulting in slower performance (usually due to increased blocking or the like)
--
Gail Shaw
SQL Server MVP |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
influent
Constraint Violating Yak Guru
USA
361 Posts |
Posted - 03/01/2012 : 20:01:54
|
| Dave, I'd be interested to know if you're getting very different execution plans from your longest-running queries on the new server compared to the old server. |
|
|
|
dboiler
Starting Member
5 Posts |
Posted - 03/02/2012 : 09:56:19
|
Tara/Gail,
I did run part one of Gails articles and got these results:
ProcedureName TimeImpact IOImpact CPUImpact ExecutionCount
NULL 902603 46497528 290831 33138
sp_prepexec 40295 521153 3500 47
sp_execute 13532 462415 1720 12
I'm trying to work through part 2 now but am wondering about the NULL Procedure.
Dave
|
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 03/02/2012 : 10:07:00
|
Probably ad-hoc SQL. For that you'll have to check the individual (unaggregated) rows. The function that calcs the ProcedureName is far from perfect.
--
Gail Shaw
SQL Server MVP |
|
|
| |
Topic |
|
Slow Server
