Often when dealing with different filegroups or even different files in SQL Server 2005, I end up wanting to know where certain objects are logically and physically located. Here’s a query that will give that information: select ‘Object Name’ = so.name, ‘Index Name’ = si.name, ‘Type Description’ = si.type_desc, ‘FileGroup Name’ = sfg.Name, ‘System FileName’ [...]
So I’ve been running a trace on some SQL 2005 servers for awhile now and I’m starting to analyze them. One of the things I look at is statement top 10 duration. It was interesting to see all 10 entries to on every day I monitored to be: TextData – exec sp_readrequest @receive_timeout=600000 ApplicationName – [...]
One of the things I’ve been doing lately for an upgrade from SQL 2000 to SQL 2005 is restructuring databases, adding filegroups and files to split out the data and non clustered indexes, leaving only the system tables in the primary filegroup. This is actually fairly simple as we’re just dropping the clustered indexes and [...]