February 25, 2009 at 4:25 am
How do you exactly calculate the average disk queue in performon ? How do I find out the no physical disks in the Storage(SAN) ? Moreover I find that the Average Disk Queue Length Graph in my server always appears to be at 100%. Can you guide us on how to reduce this value? and what are the factore we need to concentrate on to increase the perfomance of server . We are facing serious performance issues.
Server : Sql server 2005 Standard Edition.
Winodows 2003 SP3
Thanks in Advance .
February 25, 2009 at 5:00 am
Oh yes ... SANs .... the big black magic box :hehe:
- Performance monitoring a SAN is very vendor specific, so contact your vendor if you really think that is the issue.
- SQLIO and SQLIOSim may help to test and stresstest your assumed disk setup.
- did you try PAL ? (free at www.codeplex.com/PAL/Release/ProjectReleases.aspx?ReleaseId=9623 )
- you could try my sampling technique that uses SQL2005 DMV to figure out where your waits are:
(it needs a single open query session)
/*
IOStats Wait for objects.sql
*/
Declare @NewBaseLine char(1)
Set @NewBaseLine = 'N'
Declare @dbId int
Set @dbId = db_id()
-- drop table #BaseLineIxOStat
if object_id('tempdb..#BaseLineIxOStat') is null
begin
print 'Baseline created'
Select getdate() as TsBaseline, *
into #BaseLineIxOStat
from sys.dm_db_index_operational_stats( @dbId, NULL, NULL, NULL )
Alter table #BaseLineIxOStat
add PRIMARY KEY CLUSTERED
(
[object_id],
[index_id],
[partition_number]
)
end
If @NewBaseLine = 'y'
begin
truncate table #BaseLineIxOStat
insert into #BaseLineIxOStat
Select getdate() as TsBaseline, *
from sys.dm_db_index_operational_stats( @dbId, NULL, NULL, NULL )
end
Select top 1 TsBaseline
from #BaseLineIxOStat
/*
Objects that may suffer due to pending IO requests
-- pending I/O requests
*/
SELECT isnull(count(IoP.io_handle),0) as nIOPending
, avg( IoX.page_io_latch_wait_count - BL.page_io_latch_wait_count ) as page_io_latch_wait_count_progress
, avg( IoX.page_io_latch_wait_in_ms - BL.page_io_latch_wait_in_ms ) as page_io_latch_wait_in_ms_progress
, dateadd(ss,avg( datediff(ss,TsBaseline,getdate())),0) as Baseline_Elaps_SS
, FG.data_space_id AS GroupID
, FG.name AS FilegroupName
, '['+object_schema_name(O.[object_id]) + '].[' + O.name +']' AS ObjectName
, X.index_id
, X.name as IxName
, X.type_desc
, DbF.name as DbFileName
, DbF.physical_name as PhysicalFileName
FROM sys.Objects O
INNER JOIN sys.indexes X
ON O.[object_id] = X.[object_id]
INNER JOIN sys.filegroups FG
ON X.data_space_id = FG.data_space_id
INNER JOIN sys.database_files DbF
on DbF.data_space_id = X.data_space_id
inner join sys.dm_db_index_operational_stats( 10, NULL, NULL, NULL ) IoX
on IoX.[object_id] = X.[object_id]
and IoX.index_id = X.index_id
--and IoX.partition_number = X.partition_number
inner join #BaseLineIxOStat BL
on BL.[object_id] = X.[object_id]
and BL.index_id = X.index_id
--and BL.partition_number = X.partition_number
and (BL.page_io_latch_wait_count + BL.page_io_latch_wait_in_ms) <> (IoX.page_io_latch_wait_count + IoX.page_io_latch_wait_in_ms)
cross apply sys.dm_io_virtual_file_stats(@dbId, NULL) VFS
left JOIN sys.dm_io_pending_io_requests IoP
on VFS.file_handle = IoP.io_handle
WHERE O.type = 'U'
-- and DbF.physical_name like 'J:\%'
and VFS.[file_id] = DbF.[file_id]
GROUP BY FG.data_space_id
, FG.name
, '['+object_schema_name(O.[object_id]) + '].[' + O.name +']'
, X.index_id
, X.name
, X.type_desc
, DbF.name
, DbF.physical_name
having avg( IoX.page_io_latch_wait_in_ms - BL.page_io_latch_wait_in_ms ) > 100
ORDER BY page_io_latch_wait_in_ms_progress desc, page_io_latch_wait_count_progress desc, FilegroupName, ObjectName, X.index_id
/*
Select object_name(object_id) as ObjName
, *
from sys.dm_db_index_operational_stats( 10, NULL, NULL, NULL )
database_id, object_id, index_id, partition_number
, leaf_insert_count, leaf_delete_count, leaf_update_count, leaf_ghost_count, nonleaf_insert_count, nonleaf_delete_count, nonleaf_update_count,
, leaf_allocation_count, nonleaf_allocation_count, leaf_page_merge_count, nonleaf_page_merge_count
, range_scan_count, singleton_lookup_count, forwarded_fetch_count, lob_fetch_in_pages, lob_fetch_in_bytes, lob_orphan_create_count, lob_orphan_insert_count
, row_overflow_fetch_in_pages, row_overflow_fetch_in_bytes, column_value_push_off_row_count, column_value_pull_in_row_count
, row_lock_count, row_lock_wait_count, row_lock_wait_in_ms, page_lock_count, page_lock_wait_count, page_lock_wait_in_ms, index_lock_promotion_attempt_count, index_lock_promotion_count
, page_latch_wait_count, page_latch_wait_in_ms, page_io_latch_wait_count, page_io_latch_wait_in_ms
Select top 1 TsBaseline
, *
from #BaseLineIxOStat
-- 341585
*/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 25, 2009 at 5:22 am
ALZDBA (2/25/2009)
Oh yes ... SANs .... the big black magic box :hehe:- Performance monitoring a SAN is very vendor specific, so contact your vendor if you really think that is the issue.
- SQLIO and SQLIOSim may help to test and stresstest your assumed disk setup.
- did you try PAL ? (free at www.codeplex.com/PAL/Release/ProjectReleases.aspx?ReleaseId=9623 )
- you could try my sampling technique that uses SQL2005 DMV to figure out where your waits are:
(it needs a single open query session)
/*IOStats Wait for objects.sql
*/
Declare @NewBaseLine char(1)
Set @NewBaseLine = 'N'
Declare @dbId int
Set @dbId = db_id()
-- drop table #BaseLineIxOStat
if object_id('tempdb..#BaseLineIxOStat') is null
begin
print 'Baseline created'
Select getdate() as TsBaseline, *
into #BaseLineIxOStat
from sys.dm_db_index_operational_stats( @dbId, NULL, NULL, NULL )
Alter table #BaseLineIxOStat
add PRIMARY KEY CLUSTERED
(
[object_id],
[index_id],
[partition_number]
)
end
If @NewBaseLine = 'y'
begin
truncate table #BaseLineIxOStat
insert into #BaseLineIxOStat
Select getdate() as TsBaseline, *
from sys.dm_db_index_operational_stats( @dbId, NULL, NULL, NULL )
end
Select top 1 TsBaseline
from #BaseLineIxOStat
/*
Objects that may suffer due to pending IO requests
-- pending I/O requests
*/
SELECT isnull(count(IoP.io_handle),0) as nIOPending
, avg( IoX.page_io_latch_wait_count - BL.page_io_latch_wait_count ) as page_io_latch_wait_count_progress
, avg( IoX.page_io_latch_wait_in_ms - BL.page_io_latch_wait_in_ms ) as page_io_latch_wait_in_ms_progress
, dateadd(ss,avg( datediff(ss,TsBaseline,getdate())),0) as Baseline_Elaps_SS
, FG.data_space_id AS GroupID
, FG.name AS FilegroupName
, '['+object_schema_name(O.[object_id]) + '].[' + O.name +']' AS ObjectName
, X.index_id
, X.name as IxName
, X.type_desc
, DbF.name as DbFileName
, DbF.physical_name as PhysicalFileName
FROM sys.Objects O
INNER JOIN sys.indexes X
ON O.[object_id] = X.[object_id]
INNER JOIN sys.filegroups FG
ON X.data_space_id = FG.data_space_id
INNER JOIN sys.database_files DbF
on DbF.data_space_id = X.data_space_id
inner join sys.dm_db_index_operational_stats( 10, NULL, NULL, NULL ) IoX
on IoX.[object_id] = X.[object_id]
and IoX.index_id = X.index_id
--and IoX.partition_number = X.partition_number
inner join #BaseLineIxOStat BL
on BL.[object_id] = X.[object_id]
and BL.index_id = X.index_id
--and BL.partition_number = X.partition_number
and (BL.page_io_latch_wait_count + BL.page_io_latch_wait_in_ms) <> (IoX.page_io_latch_wait_count + IoX.page_io_latch_wait_in_ms)
cross apply sys.dm_io_virtual_file_stats(@dbId, NULL) VFS
left JOIN sys.dm_io_pending_io_requests IoP
on VFS.file_handle = IoP.io_handle
WHERE O.type = 'U'
-- and DbF.physical_name like 'J:\%'
and VFS.[file_id] = DbF.[file_id]
GROUP BY FG.data_space_id
, FG.name
, '['+object_schema_name(O.[object_id]) + '].[' + O.name +']'
, X.index_id
, X.name
, X.type_desc
, DbF.name
, DbF.physical_name
having avg( IoX.page_io_latch_wait_in_ms - BL.page_io_latch_wait_in_ms ) > 100
ORDER BY page_io_latch_wait_in_ms_progress desc, page_io_latch_wait_count_progress desc, FilegroupName, ObjectName, X.index_id
/*
Select object_name(object_id) as ObjName
, *
from sys.dm_db_index_operational_stats( 10, NULL, NULL, NULL )
database_id, object_id, index_id, partition_number
, leaf_insert_count, leaf_delete_count, leaf_update_count, leaf_ghost_count, nonleaf_insert_count, nonleaf_delete_count, nonleaf_update_count,
, leaf_allocation_count, nonleaf_allocation_count, leaf_page_merge_count, nonleaf_page_merge_count
, range_scan_count, singleton_lookup_count, forwarded_fetch_count, lob_fetch_in_pages, lob_fetch_in_bytes, lob_orphan_create_count, lob_orphan_insert_count
, row_overflow_fetch_in_pages, row_overflow_fetch_in_bytes, column_value_push_off_row_count, column_value_pull_in_row_count
, row_lock_count, row_lock_wait_count, row_lock_wait_in_ms, page_lock_count, page_lock_wait_count, page_lock_wait_in_ms, index_lock_promotion_attempt_count, index_lock_promotion_count
, page_latch_wait_count, page_latch_wait_in_ms, page_io_latch_wait_count, page_io_latch_wait_in_ms
Select top 1 TsBaseline
, *
from #BaseLineIxOStat
-- 341585
*/
while exectuting this query am getting the error message 'object_schema_name' is not a recognized built-in function name
1) i have not update the service pack 3
2) cumulative patches also
3) can you tell me how to check the avg disk queue lenght .
February 25, 2009 at 5:40 am
the script also works on SP2.
By now you should at least be on SP2 with CU2 and if you can directly go to SP3.
MS did improve the product to your advantage!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 25, 2009 at 7:53 am
ALZDBA (2/25/2009)
the script also works on SP2.By now you should at least be on SP2 with CU2 and if you can directly go to SP3.
MS did improve the product to your advantage!
I have not installed any service pack(sp1,sp2,sp3) in my sql server 2005 standard edition . can i directly install the service pack 3 of sql server 2005 ? can you provide the details .how to do the service pack installation in sql server 2005 failover clutering 2005 .
Thanks in Advance .
February 25, 2009 at 8:17 am
Yes, you can go directly to sp3.
Download it from MS.
This forum thread has more info:
http://qa.sqlservercentral.com/Forums/Topic455158-146-1.aspx
Be sure to move the quorum of the cluster to the node that hosts the instance you want to upgrade !
Play it safe: Start with a full backup of ALL databases !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 25, 2009 at 9:22 am
ALZDBA (2/25/2009)
Yes, you can go directly to sp3.Download it from MS.
This forum thread has more info:
http://qa.sqlservercentral.com/Forums/Topic455158-146-1.aspx
Be sure to move the quorum of the cluster to the node that hosts the instance you want to upgrade !
Play it safe: Start with a full backup of ALL databases !
Thaks for your reply .. what is the latest cumulative update package avaliable for sqlserver 2005 sp3 ?
February 25, 2009 at 10:54 am
I think it is CU2 http://support.microsoft.com/kb/961930
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 25, 2009 at 11:22 am
sudhakara (2/25/2009)
How do you exactly calculate the average disk queue in performon ?
If you have a SAN, don't bother. That counter's near impossible to interpret properly when the storage is a SAN.
Focus on Avg sec/read, Avg sec/write and %idle time
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
February 25, 2009 at 10:15 pm
ALZDBA (2/25/2009)
I think it is CU2 http://support.microsoft.com/kb/961930
thks for your reply ..can you anyone confirm this what is the latest cumulative update package for sql server 2005 sp3 ?
February 25, 2009 at 10:19 pm
GilaMonster (2/25/2009)
sudhakara (2/25/2009)
How do you exactly calculate the average disk queue in performon ?If you have a SAN, don't bother. That counter's near impossible to interpret properly when the storage is a SAN.
Focus on Avg sec/read, Avg sec/write and %idle time
thanks for your reply ..pls post if you have some other tips to improve sql server perfomance ...
February 27, 2009 at 7:05 am
sudhakara (2/25/2009)
ALZDBA (2/25/2009)
I think it is CU2 http://support.microsoft.com/kb/961930thks for your reply ..can you anyone confirm this what is the latest cumulative update package for sql server 2005 sp3 ?
Try this link: http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
February 28, 2009 at 2:48 am
Gaby Abed (2/27/2009)
sudhakara (2/25/2009)
ALZDBA (2/25/2009)
I think it is CU2 http://support.microsoft.com/kb/961930thks for your reply ..can you anyone confirm this what is the latest cumulative update package for sql server 2005 sp3 ?
Try this link: http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx%5B/quote%5D
hi thnks ur reply but not able to access this link ..can u give me the correct one ?
February 28, 2009 at 5:56 am
sudhakara (2/28/2009)
hi thnks ur reply but not able to access this link ..can u give me the correct one ?
Works fine for me.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply