Slow Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Slow Server

Author

Topic

dboiler
Starting Member

5 Posts

Posted - 2012-02-21 : 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
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-21 : 12:13:07

My guess, badly written queries (or inadequate indexing) resulting in queries doing much more data access than necessary and churning the buffer pool as a result.

[url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url]
[url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]
and look for queries doing huge amounts of reads.

p.s. 10% is not a recommended growth increment for log files. Set it to a fixed MB value, that way all growth operations grow the same amount and take more-or-less the same amount of time.

--
Gail Shaw
SQL Server MVP

dboiler
Starting Member

5 Posts

Posted - 2012-02-21 : 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

498 Posts

Posted - 2012-02-21 : 14:58:27

Update statistics and rebuild indexes.

dboiler
Starting Member

5 Posts

Posted - 2012-02-24 : 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

498 Posts

Posted - 2012-02-24 : 16:00:55

change my_database_name to your database name. That may help.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-02-25 : 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
Aged Yak Warrior

545 Posts

Posted - 2012-02-27 : 02:53:36

dump question : You do a log backup ?

Jayam.cnu
Starting Member

45 Posts

Posted - 2012-02-27 : 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 - 2012-02-27 : 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
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-27 : 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

38200 Posts

Posted - 2012-02-29 : 19:48:04

Dave, I am responding due to your email to me. You can't defragment small indexes. How many pages are in the indexes that you say are very fragmented? It matters.

I haven't read through the entire thread here, but Gail has you on the right track. I wouldn't waste any more time on defragmenting the indexes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2012-03-01 : 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 - 2012-03-02 : 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
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-02 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources