Easy automation of SQL Server database maintenance

A while back I kicked off a survey asking what mechanism you use for running your regular SQL Server database maintenance. Here are the results: The “Other” responses were: 7 x “A combination of maintenance plans generated from SSMS wizard and a home-grown index maintenance script” 5 x “Combo of own and modified scripts of […]

How are per-column modification counts tracked?

Earlier today there was a question on the MVP mailing list asking how SQL Server keeps track of per-column modification counts. From 2008 onwards, the hidden system table sys.sysrscols tracks modifications to table columns using the rcmodified column. The hidden system tables (introduced in 2005 when we rewrote the entire metadata management system) are only […]

Survey: how do you create and maintain statistics

This survey is based on a suggestion from Jonathan Kehayias (blog/Twitter). In this survey I'm asking two questions: When you're designing/implementing a database, do you manually create statistics or let SQL Server create them automatically? How do you maintain statistics? Manually or letting SQL Server do it for you? Or both?  I'll probably collaborate with Kimberly on […]

How are auto-created column statistics names generated?

This is a question that came up at the Cleveland SQL Server User Group last night. An attendee had a wager with his colleagues about how the names of auto-created column-level stats are generated. Let's find out. I'm going to create a table without any indexes and then do some queries. USE master; GO CREATE […]

Index rebuilds depend on stats, which are updated by index rebuilds?!?

Jack Li, one of the Senior Escalation Engineers in Product Support, just posted details of an interesting case over on the CSS blog - his article is here. It talks about index builds and rebuilds, but the issue is the same for both, so I'll just talk about rebuilds. The jist of the problem is that […]