January 2013

How to Remove (Undo) Table Partitioning

I have seen plenty of articles and blog posts out there for how to setup and implement table partitioning, but very few for removing or undoing it.  So I thought I would cover a few ways to accomplish this while still preserving the data. There could be many reasons for removing partitioning: no longer needed, need to change partitioned tables, etc. But for our example, we just want to completely remove it from all tables. It would be nice if we could just drop the partition function and partition schema and SQL Server would handle all the rest, but it just...

Continue reading...

sp_PerformanceCounters – Get a Health Check of SQL Server’s Performance

During your career as a DBA, you’ll run across articles by SQL experts or other DBAs that just give you an idea.  A few years ago I found an article, or a poster to be more precise, that explains SQL Server performance counters.  It was written by Kevin Kline, Brent Ozar, Christian Bolton, Bob Ward, Rod Colledge, and Raoul Illyaos. http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf I was thinking this would be a nice poster for any DBA to hang up in their cubical.  But as I read down near the bottom, I saw they also mentioned getting performance counters from within SQL Server via sys.dm_os_performance_counters. ...

Continue reading...

Exam 70-432 – SQL Server 2008 Implementation and Maintenance

For years, actually more like a decade, I’ve been saying that I’m going to get certified in SQL Server.  Well finally after years of saying it, I finally did.  I passed my first exam, 70-432, this past week.  I have to say it was easier and at the same time harder than I thought it would be.  Easier, as in the content that was covered in the test was already 2nd nature to me, because I had been doing this stuff for years.  And it was harder, because of the way Microsoft asked the questions.  Even if you know the...

Continue reading...

SQL Server has encountered % occurence(s) of cachestore flush for the % cachestore (part of plan cache) due to some database maintenance or reconfigure operations

Have you seen this type of an informational message in your SQL errorlog before?  If so, then I hope the timestamp associated with it was not during the middle of your peak processing time.  This is a good indication that someone has been tinkering with some of the SQL configuration settings. SQL Server offers a lot of wonderful configuration options; many of which can be changed dynamically using sp_configure without the need of restarting the SQL Server service.  The downside to the dynamic changes is sometimes they can have a detrimental effect on performance, and that’s what this “informational” message is tell...

Continue reading...