Index Analysis

Do you often run a few variations of index queries against your SQL Server instance and look at multiple resultsets? Once upon a time, I did too. Now I rely on the query in this post. It’s not a finished product and I’ll no doubt revisit and add a few more columns and/or logic over time. However, it’s great for pulling back index keys, include columns, fragmentation, scans/seeks and so forth.
Continue reading

Physical PageId Query

I was playing about this weekend and trying to use the DBCC PAGE command and stumbled across a function which was certainly new to me which could tell me on which page my data is stored.

use AdventureWorks2008R2
go
select
	sys.fn_PhysLocFormatter (%%physloc%%) as PhysicalRID, 
	BusinessEntityID,
	FirstName,
	ISNULL(MiddleName,'') as MiddleName,
	LastName
from Person.Person
order by LastName, FirstName, MiddleName
go

Continue reading

Deleting SQL Litespeed Backups

While SQL Litespeed offers significant savings in space, that does not mean you won’t necessarily have space issues if you don’t clean up your backups.

Back in the day when I had Litespeed at my disposal, I started off using the maintenance plans which look very similar to the maintenance plan designer that you will be used to in SQL Server 2005. It was a very basic plan consisting only of a full backup of all databases and then delete any backups over 3 days old.
Continue reading