April 13, 2011 at 2:25 pm
I have been hired in order to evaluate and troubleshoot some performance issue on a 2008 SQL server.
I was trying to get some performance counter for the physical disks but unfortuntely the server (2008) is not showing me any data, the system administrator claims that there is a bug on the SAN driver that does not update the counters.
Then I ask if I could run SQLIO to at least evaluate the SAN and give me some peace of mind that there are no I/O bottlenecks, unfortunately the system manager does not feel that I should use SQLIO on the production box...
Do you agree with him?
April 13, 2011 at 2:35 pm
If you think there may be IO problems, I would say to run it, but only with SQL Server shutdown.
I usually run SQLIO before putting it into production, and save the results to compare against if something goes wrong later.
For systems that were already in production that seem to have IO problems, I have scheduled downtime to run SQLIO. If you are looking at expensive upgrades, it's best to have real numbers instead of speculation.
April 14, 2011 at 7:24 am
No way I would run any kind of load generation tool against a live production server, regardless of circumstance on the monitoring.
Have you looked at wait stats? You can start to figure out if I/O is an issue without having to measure it yet. If the waits imply I/O issues, then you need to work on getting the counters fixed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2011 at 7:49 am
Thank you for your advise.
I am currently checking the wait stats ( ASYNCH_IO_COMPLETION, IO_COMPLETION, LOGMGR, WRITELOG, or PAGEIOLATCH_x)
Do you suggest I should check any other wait stat?
Thanks again
April 14, 2011 at 8:08 am
I would look at sys.dm_os_wait_stats. It shows accumalative waits for SQL Server itself. That will give you an idea of what's causing problems on the machine without having to try to target individual counters, etc. Something like this is a start:
SELECT TOP 10 * FROM sys.dm_os_wait_stats AS dows
ORDER BY wait_time_ms DESC
But you can also order by waiting tasks or the other columns. It's a very fast way to narrow down your focus on the system. Depending on the waits that you see, you can then decide whether or not to spend time on the server, on physical configurations, or, as is usual, in code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2011 at 8:36 am
SQLIO stress testing will primarily affect the SAN so no you wouldn't run it on production. These sorts of tests should be carried out before the Server and SAN are put into production. To stress test the SAN effectively you will need to send a series of configured I\O requests until it literally breaks!
Stress testing the SAN while others are using it is not recommended. SQLIO doesn't just replicate SQL IO it can be used for a whole range of I\O patterns.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply