Where SQL Server is such an IO bound application, designing how your data is stored across files and pages is one of the most important things you can do, from a performance perspective.
There are many simple things you can do to improve IO performance in SQL Server, and I intend to post about some of these techniques over the next few weeks. But first, considering SQL's proportional fill algorithm, how can you even definitely discover what files your rows are stored on, let alone which pages?
I must admit that, until recently, I had no efficient way of doing this, but at the moment, I am lucky enough to be working alongside Charles Feddersen, who is a Senior SQL Server consultant at Avanade, (and a bit of a SQL legend) and he showed me the following tip -
If you run the following query against the AdventureWorks2008R2 Database, you will return all rows from the SalesOrderDetail table, but alongside that, you will return an extra column, returned by the function, that will show you the File:Page:Slot that each row is stored in. You can then use DBCC Page against the pages, to see how the data is being stored.
SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS 'File:Page:Slot', *
FROM AdventureWorks2008R2.Sales.SalesOrderDetail
America
United Kingdom
No comments:
Post a Comment