February 13, 2008 at 6:40 am
I'm getting ready to move to SQL2k5. Rather than doing an upgrade I intend to reinstall everything and I'm going to move from a 4-disk RAID5 to 3 2-disk RAID1s. My server has a couple dozen databases on it and I'd like to spread them over the 3 new volumes based on reads and writes to prevent I/O Wait. What I'm having trouble finding is a way to get metrics that will help me decide what volume to put each database on. I can do the work to make the actual decision, I just can't find the means to get the information I need.
Can anyone point me in the right direction?
Thanks
February 13, 2008 at 7:10 am
Ok. So looking around I found :fn_virtualfilestats
But there's a new problem. It returns values on my development server but not on my production server. Is there a setting somewhere that needs to be changed?
February 14, 2008 at 8:23 am
Not that anyone really cares but I figure I'll add the answer to this so it gets indexed and can help someone else in the future.
It turns out the performance counters for SQLServer got turned off somehow. The fix is relatively easy but did require a reboot.
1) Open a command prompt and get the working directory to your SQL Server's Binn directory. In my case this was c:\program files\mssql\binn but I think default is c:\program files\microsoft sql server\mssql\binn
2) Run the following command: unlodctr MSSQLServer
3) Run the following command: lodctr sqlctr.ini
4) Stop and Restart your SQL Server service
a) net stop mssqlserver then net start mssqlserver
b) or you can do this from the sql server service manager (usually in the system tray)
Microsoft says that's all you need to do but I actually had to reboot to get the counters to show up.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply