c91d4dd5   
   From: esquel@sommarskog.se   
      
   soy (saurabh_reads@yahoo.com) writes:   
   > I have my SQL Server database spread across multiple disks. I used DMV   
   > views to find out the queries with high I/O and the tables they use.   
   > Now I want to see where these tables reside to see if I need to move   
   > them from my 'hot' drives to not so busy ones.   
   > I need to find out which underlying physical data file(s) does this   
   > table reside on.   
   > I played with sys.system_internals_allocation_units but couldn't   
   > figure it out. Any ideas?   
      
   Do you have separate filgroups or just one filegroup with multiple files?   
      
   In the latter case, it is not really meaningful to determine on which   
   file a table is located. Typically, it will be located on all files, as   
   SQL Server uses multiple files in a filegroup in a round-robin fashion.   
      
   If you use multiple filegroups, you should look at sys.allocation_units,   
   sys.partitions, sys.data_spaces and sys.filegroups. (I have not done   
   this very often myself, so I have no canned query to offer.)   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)