| Author |
Topic |
|
Jhek8866
Starting Member
8 Posts |
 Posted - 2005-01-19 : 19:28:28
|
| Hello All,Is there a way to monitor the disk utilization for a database? I know that I can monitor SQL Server's disk utilization for all the physical disks on the server in perfmon, but it would be helpful if I can break it down to the database level.I have 60 databases spread across 5 physical disks at the moment and would like to move some databases around to average out the disk usage. Any suggestions or help would be greatly appreciated. Thanks!Eric K |
|
|
RM
Yak Posting Veteran
65 Posts |
Posted - 2005-01-20 : 00:52:20
|
| sp_spaceused. Check out the details in BOL |
 |
|
|
Jhek8866
Starting Member
8 Posts |
Posted - 2005-01-20 : 16:00:04
|
| Thanks RM, but I was looking for disk IO utilization. Sorry if it wasn't clear.Eric |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-20 : 16:02:24
|
| No, you won't be able to find out how much disk IO utilization each database is using.Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-01-20 : 16:54:30
|
| I think the only way you'll be able to figure this out is to use profiler and log things to a table. Then, run select statements on that table to see the number of reads / writes for each database.Needless to say, It's going to affect the performance of your system to do this logging. You probably need to do the logging over the course of a week or so (yikes!) and then do some averages per day or something. Running profiler for a week and logging it to a table is gonna SUCK though.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Jhek8866
Starting Member
8 Posts |
Posted - 2005-01-20 : 17:31:51
|
| I'll give that a shot Michael. Thanks for the help. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-21 : 12:42:23
|
| "the number of reads / writes for each database"Not sure you can get that, per DB, can you?Number of transaction etc. is available per DB, but they might not read the disk!Kristen |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-01-21 : 13:17:08
|
| If you monitor all TSQL and Stored proc stuff, and be sure to select the "reads", "writes", and the "database id" columns, you should be able to. It's gonna dump everything for all databases into one table, but you can group / sum by database once you have it in that profiler log table.It can be done, but it's gonna affect database performance and disk performance because you are placing more strain on the system by monitoring and logging. If you system is at or near the breaking point, it might be a bad idea to do this. If you still have plenty of headroom on your server, run this now so you can load balance before things get outa hand.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-01-21 : 13:29:13
|
| SQL2K has a function called fn_virtualfilestats that will give you the information you want. Google on it and you will find some useful scripts and information.This will be much easier to use than setting something up in profiler or using perfmon or some other tool external to the database.EDIT:here is a good article on how fn_virtualfilestats can be usedhttp://www.databasejournal.com/features/mssql/article.php/10894_2244381_2-ec |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-01-21 : 13:48:44
|
| Wow that's a HANDY link!! Thanks EyeChart!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Jhek8866
Starting Member
8 Posts |
Posted - 2005-01-24 : 16:09:34
|
| Thanks eyechart. That is exactly what I was looking for. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-24 : 19:38:49
|
| Couple little notes on this funtion. It will not record anything that's doesn't run through the cache. SQL Server automatically disregards items with a very low estimated cost threshold. Since they are not stored in cache, this utility will not pick them up. It therefore gives an estimation. You can use a trace flag to change this; however, it's generally a REALLY bad idea.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-01-24 : 22:22:33
|
quote:
Originally posted by derrickleggett
Couple little notes on this funtion. It will not record anything that's doesn't run through the cache. SQL Server automatically disregards items with a very low estimated cost threshold. Since they are not stored in cache, this utility will not pick them up. It therefore gives an estimation. You can use a trace flag to change this; however, it's generally a REALLY bad idea.
I was not aware of a problem with this function. I am aware of a problem with fn_get_sql function where you need to turn on DBCC TRACEON (2861) to capture zero cost plans (set statements, commit transactions, etc.) Here is a note on that issue and fix: http://support.microsoft.com/default.aspx?scid=kb;en-us;325607Is this the same trace flag you are talking about?-ec |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-24 : 23:02:36
|
Probably.  Too many pieces stuck in my head. EEEEEEEEEkMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
Monitoring Database Disk Utilization